Reporting Services: dynamically add filters to server report

Question

I have some server-based reports (*.rdl) which returns data from a SQL Server 2008 view. 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 reasonably small (four or five).

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

I was hoping I might be able to define dataset filters, but those seem to have to be in place when the RDL gets rendered, too (e.g. they become part of the report RDL itself).

The possible values for each criteria are too numerous that I could simply add them as a list and define an IN (list of values) kind of filter.....

Any ideas?? Thoughts? Am I missing something?

---

Solution: I solved it by using the filtering option on the report data set, which I set to a "dummy" filter in the beginning which reports back everything, and then I update it to filter on the actual column and value that the user specifies in the UI. Works quite well so far!

Answer 1

It's not dynamic (in the sense that the user would have to re-run the report every time), but if you only have four or five parameters with many values the easy solution would be to add a multi-select parameter to the report for each of them which defaults to "All".

Is there a reason why the user can't re-run the report with different parameters?

Or have I misunderstood what you mean by dynamic?

Answer 2

if I understand your question :

you can use parameters for filtering. if you have an application that use ReportViewer control for showing report, pass this conditions as parameter to your report. you can manage visibility or data filtering by this parameters.