Dynamic Datasource | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Dynamic Datasource

I have two databases but one set of reports (at least I hope). Does anyone know if it is it possible to dynamically change the datasource of a report at runtime? If not could anyone offer a good solution to not duplicating these reports? Thanks, Matt
I am researching the same thing. I have found that you can change the datasource at run time by creating a Data Processing Extension. I have run the Reporting Services example and was able to see the new datasource. I am in the process of figuring out how to pass the new connection to the report through the parameters collection. Hope this helps you get started.
check out the reporting services example at: C:program FilesMicrosoft SQL ServerMSSQLReporting ServicesSamplesEntensionsFsiDataExtension
You can check microsoft.public.sqlserver.reportingsvcs newsgroup for "Pass DatabaseName as a Parameter" http://msdn.microsoft.com/newsgroup…-DC48AB0DA5C9&dglist=&ptlist=&exp=&sloc=en-us You can use a similar text sql statement as below where LSQLServerName is the Linked sql server name wihh instance name (you can omit this if you do not need a linked server name for situations where databases lay on different instances) = "Exec " & Parameters!LSQLServerName.Value & "." & Parameters!DatabaseName.Value & ".dbo.StoredProcedureName " & Parameters!ParameterOne.Value