dinsdag 20 september 2011

SSRS: 5 Attempts and 2 solutions with reportfilter and multiple conditions (grmph)

Introduction
This is annoying me for a long time now: filter records based on multiple conditions in Reporting Services (SSRS). I'm working with power users and they are using reportbuilder 2.0 and they want to select multiple conditions for rows in a tablix. Simple? Intuitive? Easy for endusers? Not quite (in my opinion).

The problem

Attempt 1
Suppose i've  a simple fact with a department dimension and i've 5 factrecords. I know that department A and E has a factrecord. Therefore i'm using them to test this filterbehaviour. Now i want to select two departments with the report filter. On Technet i've found the following example :


The first thing (as most of us will do and as my poweruser were doing) is trying Department_Description = Department A, Department E



and no results appear. Okay, well i know something about SQL and there is an IN operator available for this kind of filtering. In SQL you willl type something like this :

WHERE Department IN ('Department A', 'Department B')

Attempt 2
So the next thing i tried was the IN operator


But no luck here too.

Attempt 3
The next step is using quotations


This gives me some errors. Now what? Coming back to the Filter window shows this:




Reporting Services has changed the constraint to this :  """"Department A, Department B"""". That seems a bit awkward. I think that three " are needed for escaping other " characters. When i'm building dynamic sql i need to use these too.

Attempt 4
Perhaps single quotes?

Nope!


Attempt 5
Round Brackets perhaps?



Nothing again... grmph...Getting desperate here! When i return back to the window every time """" are added to the condition. 



The solutions (?)

Solution 1
On MSDN i've found an example. I  need to set the expression to =(Fields!Department_Description.Value = "Department A" OR Fields!Department_Description.Value = "Department E")

and set the Filter to boolean.



Solution 2
Another solution i've found on a site of data inspirations :




And the expression is : =SPLIT("Department A" + "," + "Department E", ",")



Conclusion
The solution proposed on the MSDN forums is not very intuitive. The other example from the site data inspirations is a bit more intuitive but you have to use the SPLIT function.

Reportbuilder is used by end users for building Self Service BI reports, but this filter functionalitity is hard to understand. I hope Microsoft do a better job building the successor of Reportbuilder.

Greetz,
Hennie

1 opmerking:

  1. Hi Hennie,

    Exactly what I was looking for.
    When multi-value-parameters are used value should be:

    =split(join(Parameters!multiparameter.value,","),",")

    (that is whitout the (0) a UI insert of the parameter will add to .value)

    But you knew that.

    Greets
    Ruurd Schoonheim

    PS: again triple "te gek" rating!

    BeantwoordenVerwijderen