SSRS Filter by parameter and Value condition

Question

I am trying to add an parameter that will allow the user to filter by unit Cost. I.e. If for parameter Unit cost, User select "All Costs", it will not perform any filter and will show all items. However, if for the parameter Unit cost, the user selects "Greater than 0" it would only display items that have unit cost > 0.

I have declared the parameter with two available values "U" and A". However, what would the Parameter condition look like? I tried adding the condition which was =IIF(Parameter!Text.Value = "U", UnitCost, NOTHING) > 0. This doesn't seem to be working though. Can anyone provide suggestions as to how this would be done.

Where are you performing the filter? Also where are you using the conditional expression? — alejandro zuleta, Nov 21 '16 at 00:00
Filter is on the Tablix. I can either do that or on the Dataset either way...the condition that I have doesn't seem to be working. And I was using an IIF statement as the expression (as mentioned above) > as the operator and Value as 0. But this should only work if tis true. But I know this isn't correct. I'm just not sure what that would even look like. — a415, Nov 21 '16 at 00:07

score 4 · Accepted Answer · answered Nov 21 '16 at 00:27

You can use an expression to determine if a row should be filtered or not based on the parameter selected value.

Add a new Filter condition in your tablix and use these settings and expressions:

In Expression textbox use:

=Switch(
Parameters!Text.Value = "All", "Include",
Parameters!Text.Value = "U" AND Fields!UnitCost.Value > 0, "Include",
Parameters!Text.Value = "A" AND Fields!UnitCost.Value > 10, "Include",
true, "Exclude"
)

In the Value textbox use:

="Include"

Note your parameter should have an available value as conditions to filter you need.

In this case I use A parameter value to filter the UnitCost values greater than 10 and U value to filter UnitCost values greater than 0. Customize to meet your requeriment.

Let me know if this helps.

SSRS Filter by parameter and Value condition

1 Answers1