Data Driven Subscriptions for SSRS Report with Parameters from multiple data sources

Just to give the context of the problem I faced recently.. our application is very data intensive, GB of data received from the customer every day and hundreds of customers. Hence we have kept each customer data in individual database. So the reports, based on the customer selection, has to pull data from different databases which are laid out in multiple instances. I have blogged about how to change the data source dynamically here.

Few days back there was a request to set subscription for these reports. Though it looked simple, it was not the case. The problem was that the parameters too had dynamic data sources. But in SSRS data driven subscription, you can specify only one data source.

Two options  I had was

1) Make on instance as primary, i.e. your report data source should point to this. Then create hundreds of linked servers

2) Use OPENDATASOURCE to connect to all the instances dynamically. The drawback with this approach is that if your instance is mixed mode authentication type and you use a SQL login, then you have to embed the password which does not fly well with security.

And I had to go with the first one 😦

Advertisements

About Suresh Kumar D

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

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