SSRS : Simulating NOT IN and NOT LIKE Functionality In SSRS Filter Expressions

  • Article

Context

Recently I came across a question in one of the forums asking how NOT IN functionality could be implemented in SSRS filter expressions. SSRS doesn't provide an option to create a filter condition based on NOT IN operator by default. This is quite evident from the available list of operator options you can see from the filter tab.

However there is a workaround that we can apply to implement the functionality in SSRS. This article explains how you can simulate the functionality of NOT IN and NOT LIKE operators within SSRS filters with the help of an illustration.

 

Illustration

Consider the below dataset:

SELECT 'Acura'  AS 'Make',  'TL'  AS 'Model', 2014 AS  'Year' ,30000 AS 'Cost'
UNION
SELECT 'Acura'  AS 'Make',  'TL'  AS 'Model', 2013 AS  'Year' ,25000 AS 'Cost'
UNION
SELECT 'BMW'  AS 'Make',  '328'  AS 'Model', 2014 AS  'Year' ,35000 AS 'Cost'
UNION
SELECT 'BMW'  AS 'Make',  '528'  AS 'Model', 2013 AS  'Year' ,50000 AS 'Cost'
UNION
SELECT 'BMW'  AS 'Make',  'Z4'  AS 'Model', 2012 AS  'Year' ,40000 AS 'Cost'
UNION
SELECT 'BMW'  AS 'Make',  'BMW i'  AS 'Model', 2014 AS  'Year' ,120000 AS 'Cost'
UNION
SELECT 'BMW'  AS 'Make',  'X3'  AS 'Model', 2013 AS  'Year' ,40000 AS 'Cost'
UNION
SELECT 'Lexus'  AS 'Make',  'ES 350'  AS 'Model', 2013 AS  'Year' ,45000 AS 'Cost'
UNION
SELECT 'Lexus'  AS 'Make',  'RX'  AS 'Model', 2013 AS  'Year' ,50000 AS 'Cost'
UNION
SELECT 'Lexus'  AS 'Make',  'CT'  AS 'Model', 2014 AS  'Year' ,32000 AS 'Cost'
UNION
SELECT 'Ford'  AS 'Make',  'F-150'  AS 'Model', 2011 AS  'Year' ,25000 AS 'Cost'
UNION
SELECT 'Ford'  AS 'Make',  'Escape'  AS 'Model', 2012 AS  'Year' ,25000 AS 'Cost'
UNION
SELECT 'Ford'  AS 'Make',  'Fusion'  AS 'Model', 2013 AS  'Year' ,30000 AS 'Cost'
Order By  Make,Model

This dataset provides details on the various car makes and models along with their year of manufacture and the estimated cost
The requirement is to display this in a SSRS report. The report needs to have two parameters which will allow users to pass make and model values which they don't wish to see in the report data. This is a typical scenario where you need implement exclusion logic inside SSRS. To add onto that requirement specifies that users should be able to select multiple options through the parameters which would require the use of multivalued parameters.

This is a typical scenario which would require the use of NOT IN functionality. One of first advises that is given mostly is to try to do it at the database end. But this would not be possible in all cases for example if there are multiple regions within report where this dataset is getting shared and some of the regions doesn't require filter action etc. 

Since SSRS doesn't provide NOT IN operator within filter we need to apply a workaround for simulating the NOT IN functionality. We make use of InStr function available in SSRS for this purpose. 

InStr function searches for a string pattern within a string value and returns the starting position of the pattern within the string.
This function can be utilized for simulating the NOT IN functionality. Since it is a multivalued parameter the selected values would be sent as a comma separated list. This means that we just need to make sure only the records which is having a field value that cannot be identified as a pattern within the comma separated list needs to be retrieved from the dataset.
So in short we can use a filter expression as below:

Expression
=InStr("," & Join(Parameters!ParameterName.Value,",") & ",","," & Fields!FieldName.Value & ",")
 
Operator
=
 
Value
=Cint(0)

Now lets see how we can implement this in our case.
For this:

  • Create a SSRS report with the above dataset
  • Create a embedded datasource for that and use query above for the dataset
  • Add a tablix and drag the dataset fields to it.
  • Set the parameter filter for the Make field. For this create a parameter and specify values for it. 
  • Make sure you specify default value as N/A so that it does not do any filter when report is initially rendered

Once done the report looks like below:

Now we need to implement a filter for the dataset based on this parameter.

Translating earlier filter expression into our scenario we can modify it as below:

Expression
=InStr("," & Join(Parameters!MakeExclusion.Value,",") & ",","," & Fields!Make.Value & "," )
 
Operator
=
 
Value
=Cint(0)

Now lets run the report and see the result:

As you see the report shows entire dataset as we've not yet applied any filter
Select couple of values from the parameter and now see the result:

As you see the report excludes the vehicles of the selected Makes from the result. Notice the Acura and BMW car details are missing from the above resultset. This clearly shows that the simulation for the NOT IN filter is working correctly.

NOT LIKE Operator Simulation

Now lets see how the functionality of NOT LIKE operator can be implemented in SSRS. NOT LIKE looks for a pattern and excludes from the resultset those records which has the pattern available inside the specified column values.
For illustrating this lets add one more parameter to the above report (say ModelExclusion). This parameter would be used for excluding the Models which have the specified pattern from the resultset.

The filter expression used would the below:

Expression
=IIf(Parameters!ModelExclusion.Value = "N/A",Cint(0),InStr(Fields!Model.Value,Parameters!ModelExclusion.Value))
  
Operator
=
  
Value
=Cint(0)

If you compare this expression with the one we implemented for NOT IN we can see that the order of arguments have reversed inside InStr function.  In case of NOT IN simulation we pass parameter value as first argument and field as the second. This is because in that case parameter is multivalued and values will be passed in comma separated format and the emphasis is to make sure Make value of the vehicle is not one among the value list i.e. we need to search the pattern of the value within the parameter selection list.

In contrast in the case of NOT LIKE its a single value that is getting passed from the parameter and the intention is to exclude records where field values contain this pattern. Hence first argument would be the actual field i.e. Model and second argument would be the parameter selection value.
Now lets see how the report looks with the new parameter. I've assigned default value as N/A and report when running with default values will give you the full resultset as below:


Now we can pass a pattern with Model parameter. For this illustration lets pass value as 50 and we will get output as below:

As you see from the above resultset will exclude the two entries which has the Make value with the pattern 50 inside it ie F-150 of Ford and ES350 of Lexus indicating that NOT LIKE filter was applied as expected. This is how we can simulate the NOT LIKE operator inside SSRS filters.

You can also pass values for both parameters and combination of filters get applied.
See the below example:

Examining the resultset above you can see that resultset excludes details of Acura and Ford Cars and also filters out two models of BMW which had pattern 28 within its Model ie 328 and 528 indicating that both the parameter were used for filtering the resultset.

Summary

As seen from the above illustrations both NOT LIKE and NOT IN functionalities though not directly supported as operators within SSRS filters can be implemented with the help of InStr function inside SSRS filter expressions

See Also

InStr function

Expression

=InStr(``"," & Join(Parameters!MakeExclusion.Value,``","``) & ``","``,``"," & Fields!Make.Value & ``"," )

 

Operator

=

 

Value

=Cint(0)