CODEWITHSUNDEEP
sql-server-2008ssrs-2008
Aditi Paliwal
Aditi Paliwal

Reputation: 157

Dynamically Change the Datasource in Sql Reporting Serivces 2008

I want to use the same Reports(.rdl) to generate the reports for 24 databases.

For instance if there is a user table in my reports(.rdl), then the datasource for the report(.rdl) should change dynamically. Now, I'm making 24 different Reports for 24 databases.

I want a single report(.rdl) with the datasource changed at the runtime.

Upvotes: 1

Views: 1752

Answers (2)

Aditi Paliwal
Aditi Paliwal

Reputation: 157

I found an alternative solution to my problem. I created a single (.rdl) Report file which used the user which had a right in all the databases gave the datasource as this user, and the database as in which I made single procedure in a database, suppose admin database(used for all procedure which are common to all), In that procedure I executed the procedure which is present in other databases like this:

set @sSql='Execute '+ @databasename + '.'+ @username + '.'+'GetData'
EXEC (@sSql)

where username and Databasename are present in the table(for all database) and a column name sentDate which is update to todays date after executing the procedure for that databaseName, so that the datasource moves to next after username and databasename after executing the procedure. And made the subscription to run after 10 min, so that it can send the reports of all databases, by getting data form different database in each execution.

Upvotes: 0

IUnknown
IUnknown

Reputation: 898

The way to achieve this could be adding 3 parameters to your report (.rdl) file

ServerName

DatabaseName

and if required

Password

Then change the connection string for the datasource like

="data source=" & Parameters!ServerName.Value & ";initial catalog=" & Parameters!DatabaseName.Value

Only small issue with this approach is the paramnames are visible in the URI so caveat emptor

Upvotes: 1

Related Questions