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

Question

Hello,

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 ad 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 tried with no luck this:

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


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

Id 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.

Answer 1

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

Answer 2

Go to the properties for the data set, go to the filter tab. in the expression dropdown list click the field you want to filter by (Name). click the operator and select like. in the value box click the expression selection. a new window will open. go down to your parameters list and add the parameter to the equation. click ok.

Answer 3

Go into the DataSet and define a filter there.

You can also apply filters in the data region (eg table) dialog boxes too (can't recall where exactly)

Answer 4

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

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

'*' instead of '%', in fact.

Answer 5

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.

Answer 6

I'd rather do the filtering on the backend. I have written a series on the basics of SSRS 2005 (and it is still in progress). For the basic parameter, see my latest post on the series (Using Parameter): http://dbalink.wordpress.com/2009/05/02/how-to-report-authoring-in-ssrs-2005-part-iv-adding-parameters/