CODEWITHSUNDEEP
sql-serverreporting-services
DenStudent
DenStudent

Reputation: 928

SSRS - one filter for multiple columns

I have created a report with following table:

 ID | User   | SalesA | SalesB | SalesOk | Status   | StatusOk
 1  | AAAA   | 100    | 100    | 1       | Active   | 1
 2  | BBBB   | 200    | 100    | 0       | Active   | 1
 3  | CCCC   | 600    | 600    | 1       | Inactive | 0
 4  | DDDD   | 100    | 0      | 0       | Inactive | 0

I want to filter this table on different conditions such as:

I have managed to do this in 2 different filters (using 2 different parameters in my query), but I want to know if it is possible to do it in one filter.

So instead of this (which I have now and works right) on my report:

AS IS

I want something more like this:

TO BE

Are there any ways to accomplish this? I am currently using SSRS 2012

Upvotes: 0

Views: 1939

Answers (3)

Alan Schofield
Alan Schofield

Reputation: 21683

You can test the list of selected parameter values in both filters.

Option 1 without a "No filter" option

Lets say your parameter is called @Selection and your label/value pairs look like this...

'Show Sales OK'     'SalesOK'
'Show Active Users' 'ActiveUsers'

...then the filters would be something like

Expression = [SalesOK]
Operator = "="
Value (expression) =IIF(Array.IndexOf(Parameters!Selection.Value, "SalesOK") > -1,1,0)

Then do the same for the StatusOK field changing the text to 'ActiveUsers'

Option 2 with a "No filter" Option

If you want to say that if a parameter option is not set then ignore it (i.e. If Sales OK is set but Active Users is not then filter on on Sales OK then it's a little trickery but not much..

First, add an extra parameter option say "No Filter" and set the value to be SalesOKActiveUsers so you label/value pairs look like this...

'Show Sales OK'     'SalesOK'
'Show Active Users' 'ActiveUsers'
'No Filter'         'SalesOKActiveUsers'

(we do this so we can test two parameter values in a single filter expression)

Then change the filters to be something like this NOTE WE NOW CHECK <> rather than =

Expression = [SalesOK]
Operator = "<>"
Value (expression) =IIF(Array.IndexOf(Parameters!Selection.Value, "SalesOK") > -1, 0, 2)

Note: We've set the false value to 2 as we are test the column value is not equal to it, if you add more column values, just change the 2 to something that isn't in the table and change the expression to match (e.g. -999)

No if you select No Filter from the drop down then all records are shown.

Upvotes: 1

Zorkolot
Zorkolot

Reputation: 2017

To run an equivalent SQL query in SQL Server using your example data:

DECLARE @temp TABLE(ID int, [User] char(4), SalesA int, SalesB int
                   ,SalesOk bit, [Status] varchar(8), StatusOK bit)
INSERT INTO @temp VALUES
 (1, 'AAAA', 100, 100, 1, 'Active', 1)
,(2, 'BBBB', 200, 100, 0, 'Active', 1)
,(3, 'CCCC', 600, 600, 1, 'Inactive', 0)
,(4, 'DDDD', 100, 0,   0, 'Inactive', 0)

DECLARE @selection varchar(100) = 'Show Sales Ok' 
                                  --'Show Active Users'

SELECT * 
  FROM @temp
 WHERE CASE WHEN @selection = 'Show Active Users'
             THEN [StatusOK]
            WHEN @selection = 'Show Sales Ok'
             THEN [SalesOk]
       END = 1

In the example I used @selection as varchar(100) for clarity. Try setting it to values 'Show Sales Ok' and 'Show Active Users'

Run the query with the 2 different values for @selection and notice it filters different fields based on the value.

To apply this to the SSRS report, comment out the @selection datatype declaration and put a similarly named parameter @selection in the SSRS report. You populate the parameter 'Available Values' with a SQL queried Dataset.

Ex.

SELECT 'Show Sales Ok' AS [SELECTION]
UNION ALL 
SELECT 'Show Active Users'

Upvotes: 2

iamdave
iamdave

Reputation: 12243

You can do this by setting up an iif statement in your filtering condition that checks both of your requirements and returns a 1 or a 0 if they match or not. You then check against this 1/0 to filter the data.

Eg:

Expression: =iif(Fields!SalesA.Value = Fields!SalesB.Value, iif(Fields!StatusOK.Value = 1, 1, 0), 0)

Upvotes: 0

Related Questions