CODEWITHSUNDEEP
sqldynamicreporting-services
Creative Mind
Creative Mind

Reputation: 13

ssrs dynamic table creation

I am passing a tableName from one report to another drill through report in SSRS. My drill through report should create a table dynamically based on dynamic dataset.

Is this possible or I am trying to achieve impossible?

Upvotes: 0

Views: 2986

Answers (1)

Chris Latta
Chris Latta

Reputation: 20560

Almost everything in Reporting Services is an expression, including the Sql query statement. You can pass the table name as a parameter and then use this to dynamically build a query.

Right-click the dataset, click Dataset Properties and click the expression editor beside the query box, which is the fx button. Now we can edit the query as a string expression.

Let's say you wanted your report to print a Code and Description from various lookup tables with the specific table name passed as a parameter when you drill down. Edit the dataset's Sql expression to look like the below:

="SELECT Code, Description "
&" FROM " & Parameters!TableName.Value
&" ORDER BY Code "

When the Sql statement is based on an expression, the Fields collection is not updated automatically so you have to add the fields manually. In the Dataset Properties edit the Fields collection and add the fields that are in the dataset. Note that, even though you are using different tables, the fields in the dataset need to be consistent so you can link them to your table cells.

If the fields from your table have different names then you need to alias them to the names you have added to your Fields collection in your Sql. Lets say ThisTable has fields Id and Name and ThatTable has Code and Description. 

="SELECT " & IIF(Parameters!TableName.Value = "ThisTable", "Id", "Code") & " AS Code, "
& IIF(Parameters!TableName.Value = "ThisTable", "Name", "Description") & " AS Description "
&" FROM " & Parameters!TableName.Value
&" ORDER BY Code "

So we have aliased all fields to be returned as simply Code and Description.

Of course, this will get messy if you have a lot of different field names and a lot of different tables. In that case you may be better off creating views to do the aliasing for you and using the views in your dataset or using a stored procedure.

Upvotes: 1

Related Questions