WHERE FirstName NOT LIKE @filterThe difficulty was that the report was being presented from a snapshot so the filter needed to be placed in the form of an expression. Again, this does not seem overly difficult except that there is no NOT LIKE comparison operator in SSRS, but fortunately we can embed VB.NET in reports.
In this example I have a single report using my local instance of SQL 2008 and the AdventureWorks2008 as the data source and a dataset populated using the below query:
From the Report menu select Report Properties and enter the below VB.NET code in the Custom Code box:
Public Function NotLike(ByVal val As String, ByVal filter As String)As Boolean
If val.Contains(filter) Then
So you should end up with something like this
Now let's get an idea of how this works by displaying the results in a table side by side with the FirstName column from our dataset.
While in the Design tab of BIDS drag and drop a table from the toolbox on the design work surface. In the first details field of the table drag and drop your FirstName column from your dataset from the Report Data tab. Right click the second details field and choose Expression and enter the following expression:
Now preview the report and take a look at what our embedded code is providing:
We can see that all first names that do not contain "ld" returns True, so they are NOT LIKE the filter text we entered, while names that do contain the filter, like Donald, returns False.
We need to create a parameter that will hold the user provided provided filter value. Go back to the Design tab and from the Report Data tab right click the Parameters folder and choose Add New Parameter. The parameter will be called Comparison and the prompt will be Comparison string and will be a text data type:
From the Report Data tab right click the dataset and select Dataset Properties:
Go to the Filters tab and add a filter. In the Expression open the expression builder and enter the below:
In the Value text box enter =True:
Now click the preview tab again and in the Comparison string parameter prompt enter ld and click View Report. The data set is now filtered on the expression utilizing our embedded code only where a value of True is returned representing values that are not like the provided string comparison.
This is a rather simplistic example of utilizing embedded code to filter a report, but should provide a good starting point. If you are interested in reviewing more in depth information on embedded code within SSRS or creating and using custom code references in expressions then please take a look at the documentation on MSDN:
Custom code references:
Adding code to a report: