x

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

yogi gravatar image

yogi
215 9 10 12

(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

aRookieBIdev gravatar image

aRookieBIdev
2.3k 53 57 62

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x559
x113

asked: Jun 20, 2011 at 09:55 PM

Seen: 3137 times

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