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