Search code examples
sqlsql-serverdatabasereporting-servicesms-reports

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

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.

Solution

  • 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.