SSRS report : How to change DataSource dynamically

Question

I have 5 different sql servers instances with same initial catalog/database and I want to create a consolidate SSRS report which can hit all these 5 servers dynamically.

To achieve this I have created a datasource DDL with hard coded values as Server1,Server2...

All I want is when I choose Server 1 in datasource ddl then my main dataset should connect to Server 1 database,I can achieve this by dynamic connection string in local environment but when I deploy it to report server it does not work.

Please suggest whether it is feasible or not ?

How are you changing the data source in your local environment, exactly? And, by DDL you mean RDL, yes? — R. Richards, Jul 10 '16 at 12:24
are u want to set connection from your asp.net connection string ? — Sanjay Radadiya, Jul 10 '16 at 18:16

score 0 · Answer 1 · answered Jul 12 '16 at 16:49

You can achieve this by creating 5 reports in 1 and only displaying the active one.

Add all 5 data sources to your report
Setup a data set for each of the 5 data sources (sounds like you can just copy the first data set definition and change the data source)
Copy whatever charts or tablixes are using the dataset 5 times so that each copy uses a different data source
Setup a report parameter called something like "datasource" and specify 5 available values (these shoulr relate to your 5 data sources)
Setup each of the charts or tablixes to only appear if the "datasource" parameter matches their selected data source

As an extra bonus you can lay each of the charts and tablixes on top of each other so that from the user perspective it looks like there is only 1 chart/tablix and it changes data. In reality 5 different tablixes are appearing/disappearing just all in the same position.

SSRS report : How to change DataSource dynamically

1 Answers1