Using SSRS Expressions to see which options a user has selected from a multivalued parameter?

if-statement reporting-services parameters multivalue
Sev09 picture Sev09 · Jun 11, 2013 · Viewed 13.3k times · Source

I'm trying to set up a filter against a "Totals" column in an SSRS report using an expression for the field.

I have a multivalued parameter setup with the values value1, value2, value3, and value4 as options.

Before the filter, this totals column just simply added the integer values from the different columns. I want to add functionality to where you can check which of the values the user has selected.

Similar to below (Pseudo code, because I can't figure out the syntax):

=IIF(Parameters!<Parameter>.Label="value1",Fields!value1.Value,0) +IIF(Parameters!<Parameter>.Label="value2",Fields!value2.Value,0) +IIF(Parameters!<Parameter>.Label="value3",Fields!value3.Value,0) +IIF(Parameters!<Parameter>.Label="value4",Fields!value4.Value,0)

All values in the fields being added are simple integers. I'd appreciate any help anyone can provide. Thanks!

Answer

Ian Preston picture Ian Preston · Jun 11, 2013

Assuming a parameter called Include, you can use an expression like this:

=IIf(InStr(Join(Parameters!Include.Value, ","), "value1") > 0, Fields!value1.Value, 0)
    + IIf(InStr(Join(Parameters!Include.Value, ","), "value2") > 0, Fields!value2.Value, 0)
    + IIf(InStr(Join(Parameters!Include.Value, ","), "value3") > 0, Fields!value3.Value, 0)
    + IIf(InStr(Join(Parameters!Include.Value, ","), "value4") > 0, Fields!value4.Value, 0)

This uses the Join function to create a comma-delimited string of the selected parameters, checks for a particular parameter value being selected, then add either the corresponding value or 0 to the total.

Here's a simple report with the expression:

enter image description here

All values selected:

enter image description here

Two values selected:

enter image description here

Related questions

How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More
Python's equivalent of && (logical-and) in an if-statement

Here's my code: def front_back(a, b): # +++your code here+++ if len(a) % 2 == 0 && len(b) % 2 == 0: return a[:(len(a)/2)] + b[:(len(b)/2)] + a[(len(a)/2):] + b[(len(b)/2):] else: #todo! Not yet done. :P return I'm getting …

Read More
&& (AND) and || (OR) in IF statements

I have the following code: if(!partialHits.get(req_nr).containsKey(z) || partialHits.get(req_nr).get(z) < tmpmap.get(z)){ partialHits.get(z).put(z, tmpmap.get(z)); } where partialHits is a HashMap. What will happen if the …

Read More