Dynamic filtering criteria in ssrs

I have report which returns data from a SQL Server 2008. The user would like to be able to dynamically add filtering, e.g.

show me only data with Language = English, or show me only data for a given organizational unit or stuff like that. The number of filter criteria is big (almost 50).

But how do I do that?? I have my report, with the basic query (SELECT (fields) FROM MyView WHERE ......) inside the report data set - how can I dynamically add filtering to that??

more ▼

asked Jun 20, 2011 at 09:55 PM in Default

avatar image

215 10 12 16

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

You should have a report parameter which should be passed on to the sql query or the stored procedure in the dataset.

You can create a report parameter with set of pre defined values or values from db.

This report parameter could be a check box or drop down box or text box.You could also set default values and data types of the report parameter.

The report parameter should then be mapped to the parameter in your stored procedure or your query.

Your query should be

select value from table where column = @filerparameter

In case you have multiple select in the report parameter then

select value from table where column in (@filerparameters)

more ▼

answered Jun 20, 2011 at 10:01 PM

avatar image

2.8k 56 65 71

thanks for the response but what i want is to pass both the column and the @filterparameter so after where there will be nothing in my query it has to be passed dynamically

Jun 20, 2011 at 10:14 PM yogi

you can build the query dynamically using expressions.

Jun 20, 2011 at 11:46 PM aRookieBIdev

can you please explain how i can work it out with reports

Jun 20, 2011 at 11:47 PM yogi

http://www.sqlservercentral.com/Forums/Topic98259-150-1.aspx#bm98544 This article should be useful. You can either have a dynamic query in expressions or use a stored proc with input parameters which builds the dynamic query.

Jun 21, 2011 at 12:13 AM aRookieBIdev
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jun 20, 2011 at 09:55 PM

Seen: 4427 times

Last Updated: Jun 20, 2011 at 09:55 PM

Copyright 2017 Redgate Software. Privacy Policy