I am in a process of generating reports using SSRS. I have multiple servers with multiple oracle databases on each server. I am wondering if I can create multiple shared data sources, and shared data sets, and create one reports by switching data sets and data sources.
Otherwise I will have to create multiple reports for each data source, which can run into 100's of reports.
Any suggestion help would be highly appreciated..
Thanks
Nirmal
Almost everything in Reporting Services is an expression, including the SQL Statement of the dataset. This means it can be altered on the fly. Assuming the datasource credentials you use can access the databases you want to get to, then you just supply the database as a parameter and you're good to go. Of course, for databases on other servers you will need to use linked servers so the server you connect to can link across to the other server to access the database.
We have a table with a nice user readable name for the database such as "End of Financial Year 2009" which holds the database name for that data. Create a dataset to use this as a parameter - display the nice name as the label and get the server+databasename connection string from the value.
Then your dataset just looks like:
="SELECT * FROM " & Parameters!Database.Value & "TableName"
This assumes the databases have the same structures as far as the report's needs are concerned.
You have to set the fields manually but it gives you flexibility.
