I am working on an application which stores each customer data in a separate database. That is one customer information in one DB. We have hundreds of such customers and hence hundreds of such databases on multiple instances.
To report on top of these customer databases, I need to dynamically change the SSRS data source connection. For eg. if user selects “Customer A” then I have to point the data source to , say, “Instance A” whereas if he selects “Customer B”, it has to be pointed to “Instance B”.
I thought this is not possible in SSRS untill a colleague of mine pointed me to the expression button in the data source properties window.
To test it, I created a small report which accepts Server Name (Instance) as parameter, a data set to query the sysdatabases table. In the data source, instead of hard coding the server name, I passed the Server Name parameter. The expression for that looks like
=”Data Source=” & Parameters!ServerName.Value &”;Initial Catalog=master”
When the data source is deployed to the Report Server,it appears as below