Show all records/some records based on parameter value

2
votes

I have stored procedure which returns around 100 rows. One column is Category.

In SSRS, I've created DataSet related to that stored procedure. On Report body I have Tablix, which I relate to DataSet.

Now, I have additional parameter, called FilterColumn, which consist all different categories from dataset, plus one row called "All products".

How to filter tablix based on that parameter to show me whether products from specific categories or all products?

3
reporting-servicesssrs-2008ssrs-2012
Is the parameter single or multi-valued? Is there any reason you can't filter at the stored procedure level, e.g. the data is used elsewhere in the report? – Ian Preston
In fact, it's for the demonstration purpose, to show filter variation. Parameter is single-valued. – veljasije
One more, sorry... Your parameter is called Sorting, but it's actually used to filter the Tablix. Is this correct? It seems slightly confusing. – Ian Preston
@IanPreston Thanks for suggestion, I've corrected question text :) In fact, story is about filtering, not sorting – veljasije

3 Answers

4
votes

You need to set up a filter similar to the following:

enter image description here

Where the expression is:

=IIf(Parameters!FilterColumn.Value = Fields!Category.Value
        or Parameters!FilterColumn.Value = "All products"
    , "Include"
    , "Exclude")

This matches the row Category based on the parameter value, or, if the value = All products, will include all rows.

As mentioned in the comments and the other answer, this is possible in the SP too, but since it seems to be specifically to demonstrate the functionality this should do the trick at the report level.

2
votes

I have created some solution and worked for me:

enter image description here

In Expression field, I put first expression: 

1.  Iif(Parameters!FilterColumn.Value = "All", 1, Fields!Category.Value)

In Value field, I put second expression:

2.  Iif(Parameters!FilterColumn.Value = "All", 1, Parameters!FilterColumn.Value)

So, when I choose "All" value in parameter, then first expression will result 1, and second expression will result 1, and i have 1 = 1 which is true for all rows, and I got all rows in table.

When I choose specific category from parameter, then in first expression result will be Fields!Category.Value and in second expression will be Parameters!FilterColumn.Value. Simple, I got Fields!Category.Value = Parameters!FilterColumn.Value, or just give me rows where category is that choosen in parameter.

0
votes

Pass the Additional Parameter to your store procedure, so you send data that is already sorted to your report. This will avoid multiple Tablix created depending on your parameter selection.