If you had multiple databases and would like to dynamically use the connection string for SQL Server Reporting Services, how would you do it? Here are some steps
1. Open Visual Studio, if Report Server project does not exist, create it
2. DO NOT Create any Shared Data Sources and Shared Datasets
3. Go to Reports, right click, then click on Add—>New Item—>Report
4. Click on Newly Created Report, then from left side of the pane (Report Data), right click on Data Source—>Add Data Source
a. Give a Name to Data Source: SQLConnection
b. Choose Embedded connection, type : Microsoft SQL Server
c. Provide a Connection string either pasting or building through Edit
d. Test Connection
5. Right click on Datasets, then click on Add Dataset
a. Give a dataset name
b. Choose : use a dataset embedded in my report
c. Choose a data source, This will be SQLConnection by default
d. Choose Query type, stored procedure is recommended
e. Click on OK
6. Once Report is created, go to parameters and add a new parameter called DataSourceName of Data type text and visibility must be hidden
- Before deploying the report go into Data Sources—>SQLConnection, and in place of connection string, replace the connection string with “[@DataSourceName]”
7. Deploy the above report
8. Now go to SSRS Report Manager, select the above report, click the option to manage, go into Data Sources, select Credentials stored securely in the report server, provide User Name and Password and Click on Apply.
9. Run the report from UI
You are all set.