0

In MS Reporting Services, how do you set a dynamic dataset, based on a selectable Report database parameter?

For example, I have one dataset, Insurers, that has a text SQL Query, instead of a stored procedure, and I need to use the database that is selected by the user in the Report database parameter as the datasource for the Insurers dataset.

Bryan
  • 3,629
  • 2
  • 28
  • 27
  • Perhaps you could use a 'Stored Procedure' dataset in SSRS and integrate [dynamic sql](https://msdn.microsoft.com/en-us/library/ms188001.aspx)? – BJones Mar 09 '17 at 17:22
  • Another way could be to create multiple tablix that correlate with different datasets. So essentially you would create a separate query for each database and the same number of tablix. Then using your "Database" parameter you can change the visibility of each tablix to show or hide. This may be cumbersome if you have a lot of different databases. – BJones Mar 09 '17 at 17:28
  • @bjones Keep in mind that the dataset for each tablix will still be executed, even when visibility is set to hidden. That could be a lot of unnecessary network traffic and processing, as well as a potentially huge performance hit. – Wesley Marshall Mar 11 '17 at 04:15

2 Answers2

1

Assuming the database is always going to be in the same instance, you can do this more simply by just using an expression string for your query and concatenating the database parameter into the FROM clause of the query.

Dave Bennett
  • 788
  • 1
  • 7
  • 13
0

I was able to figure it out.

In the Data Source Properties, I select Embedded connection. And then in the connection string, I created a dynamic expression.

= "Data Source=" & Parameters!ServerName.Value & ";Initial Catalog=" & Parameters!Database.Value & ";Persist Security Info=True;User ID=userID;Password=password;Connection Timeout=260;"

And then on the Report Server, after deploying the report, I select custom datasource, and put in the username and password for the datasource. The connection string should show the text next to it.

Bryan
  • 3,629
  • 2
  • 28
  • 27