SSRS: Changing data source dynamically

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

Advertisements

About Suresh Kumar D

Hardcore SQL Developer and aspiring Database Architect.
This entry was posted in SSRS. Bookmark the permalink.

12 Responses to SSRS: Changing data source dynamically

  1. Pingback: Data Driven Subscriptions for SSRS Report with Parameters from multiple data sources | Kirukkal a.k.a Scribbling

  2. JB says:

    Works great! I had the same problem. Same database layout, but in 3 different databases. I didn’t want to have to create (and maintain) the same report, 3 different times. So I used this method of dynamically changing the datasource by passing the parameter at runtime and it works perfectly. Thanks!

  3. Pingback: Dundas Dashboard – My observations | Kirukkal a.k.a Scribbling

  4. adhiman says:

    When i deploy my dynamic data source to the report server . i am getting an error “Cannot create a connection to data source ‘DataSource'”. I am doing the same thing as you have stated. It works fine on my BIDS.

    • Adhiman,

      Did you try updating the credential once again. If that also does not work, try hard coding the server information in the expression and deploy. If that works, then the issue might be that the parameter is not passing correct server and db.

  5. adhiman says:

    Thanks Suresh for your reply. I figured that out. It was a permissions issue with the service account

  6. Alex says:

    Does this work if we add the login and the password in parameter?

    • Alex says:

      I tried this to connect SSRS to an oracle database :
      =”Data Source=” & Parameters!ServerName.Value & “;Unicode=True;User ID=”& Parameters!Login.Value &”;Password=”& Parameters!Pwd.Value &”;”

      When I launch it, I have a credential issue :
      One or more data source credentials required to run the report have not been s
      pecified

      • Alex,

        I think this is not possible, because in the report manager data source dialog, the connection string and credential are separated. And the credential doesn’t accept expression.

  7. AndyOwl says:

    Here’s another example, allowing a user to choose what they want to show in the report from a drop list. See http://www.wiseowl.co.uk/blog/s293/dynamic-data.htm.

  8. Pingback: SSRS – Changing Datasource Dynamically | Wasatch NVC

  9. Pingback: Running Reporting Services opposing a exam database | Zickler

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s