Reporting Service 2005: Dynamic Data Set

Question

Hi there

Let say I have query like this:

SELECT column1, column2, column3, column4, column5, Qty, Amount FROM Source

I want to make this dynamic, so the user will be able to select Column1 etc by using checkbox. If the user select Column2, Column3 the query will be :

SELECT Column2, Column3, SUM(QTY), SUM(AMOUNT) FROM Source GROUP BY Column2, Column3

In term of query, I can build this no problem in stored procedure but how to put this into Reporting Service in term of displaying it dynamically? Cause I know that the Table Report Item you have to set the value of field that is going to be displayed?

I am appreciated your help

Answer 1

You can do this by not changing the query and hiding columns. You can prompt the user with the columns and have a Show/Hide or something similar.

Here is an example of hiding columns I googled.

Answer 2

The key is that the Field element used to populate columns in a table can use an indexer like so: Field("Column1").Value. You can pass a string array containing these column names as a parameter to your report and use them as Field indexers.

Also most elements in the report can be controlled by expressions including visibility and grouping elements.