Pull the same report from different databases-CodePudding

I need to be able to pull the same report but from Different Databases on the same server. ie different backups at certain points in time. The Stored procedures All Reside in a Reporting Database on said server. On the Report I would like to have a drop down of the Databases(ie Genesis_1, Genesis_2 etc then run the reports with their other parameters. Is this possible or am i looking at it from the wrong perspective.

CodePudding user response：

You can do it with dynamic SQL.

Create a report parameter @DBName and use this T-SQL as the query of your dataset:

DECLARE @SQL NVARCHAR(MAX);
IF EXISTS (SELECT 1 FROM sys.databases WHERE name = @DBName)--avoid sql injection
BEGIN
 
    SET @SQL  = CONCAT('SELECT * FROM ',QUOTENAME(@DBName),'.[dbo].[myTable]');
    EXECUTE sp_executesql @SQL;
END

CodePudding user response：

Build the query in the Dataset expression.

Instead of using a regular query in the Query window, press the expression ( Fx ) button and build the expression as string and insert the Database parameter in it.

="SELECT * FROM " & Parameters!DATABASE.Value & ".dbo.TABLE"

CodePudding user response：

The way I would typically do this is to make the data source connection dynamic.

Note: This will only work if you use an embedded datasource, not a shared datasource.

First create your report based on any one of your databases.

Once the report is working, add a report parameter (e.g. DatabaseName) and set values for each of your databases.

Then in the DataSource properties edit the connection string via the [fx] button and set it to something like this...

="Data Source=MyServerName;Initial Catalog=" & Parameters!DatabaseName.Value

This method has the added advantage that the same connection can be used for multiple datasets in the same report without making all the datasets dynamic.