In SQL Reporting Services, how to filter a dataset according to a parameter?

Question

I've got an unfiltered dataset to deal with so as to generate a report (SQL Server 2005 btw).

Let's say I've got a Name column, and I'd want to add a parameter to my report, so as to select only the names containing some characters.

Does one know how to deal with these filters?

I've tried with no luck these:

=Fields!Name.Value Like =Parameters!FilterName.Value

=Fields!Name.Value = = "%" + Parameters!FilterName.Value + "%"

I'd like to be able to get the names 'foo' and 'foobar', if I give the parameter 'oo'

Edit: I know the filtering should be done on the SQL server side, but I don't control the dataset I'm given (a webservice gives it with a no-parameters method), so I have to filter on the report side.

Solution 1

I answer to my own question, the filter expression is in fact:

=Fields!Name.Value Like ="*"+ Parameters!FilterName.Value + "*"

i.e. use "*" instead of "%", in fact.

Solution 2

The preferred practice is to go with an exact match.

You can build a multi value parameter list from the data set returned from you query or an alternative query, thereby ensuring an exact match can be achieved.

The following reference may prove useful.

http://msdn.microsoft.com/en-us/library/ms155917.aspx

Solution 3

I would suggest you apply the filter as part of the SQL statement that is executed to bring back the DataSet instead of trying to apply filters in the Reporting Services report. I think the SQL Engine is much more optimized to handle these types of filters/queries.

Solution 4

Your answer got me on the right track :) ! Maybe it is different for SQL Server 2005; But the following solution worked for me:

=Fields!AppName.Value    Like    ="*" & Parameters!FilterApplication.Value & "*"

View more solutions

Comments

[Vinzz]

Thanks, this link led me to the report filter example page, that I outlloked so far, which led me to the right answer.

[Vinzz]

I know that, and I wish I'd be able to filter as soon as possible, but alas, the webservice isn't mine, and it's owner don't care about soap bloat, and want me to filter data.