compound data filters in SSRS

Question

There must be a way in SSRS to have multiple conditionals for separate data filters? I have an input report level filter @reportparameter, and data item "Checknum" I need to do something resembling the following:

if @reportparameter = "C" and Left(Fields!Checknum,2) = "NC", filter

otherwise

if @reportparameter = "E" and Left(Fields!Checknum,2) = "VR", then filter

two separate conditionals, both compound statements.

What does the SSRS Dataset look like, as far as syntax?

The dataset filter depends on what your datasource is and what type of query you use.. and where you want to place the filter! — Harry

Hannover Fist Hannover Fist · Accepted Answer · 2018-06-05T22:15:29

If you want this as part of the query, you could add it to the WHERE clause:

WHERE (@reportparameter = 'C' and Left(Fields!Checknum,2) = 'NC')
OR (@reportparameter = 'E' and Left(Fields!Checknum,2) = 'VR')

But if you want to do it in the dataset filter, your filter Expression would be like

=IIF((Parameters!reportparameter.Value= "C" AND LEFT(Fields!Checknum.Value, 2) = "NC") 
  OR (Parameters!reportparameter.Value= "E" AND LEFT(Fields!Checknum.Value, 2) = "VR")
     , 1, 0)

The type would be Integer and the Value would be 1.

compound data filters in SSRS

1 Answers