A question that crops up from time to time with SQL Server 2000 Reporting Services is 'how do I dynamically change the data source used buy a report - from within a report?' To do this is quite straight forward in SQL 2005 Reporting Services but not so with SQL 2000 Reporting Services. I am grateful to Robert Bruckner for the following tips with SRS2000:
* Use a custom data processing extension
* Use the SOAP API by calling SetDataSourceContents:
* Use the linked server functionality of SQL Server; please check this thread:
* If the databases are on the same server, use a dynamic query text (i.e. ="select * from " & Parameters!DatabaseName.Value & "..table")
* If you're just toggling between two or three databases, you can publish the same report 3 times with 3 different names using 3 different data sources and write a main report that shows/hides the correct subreport based on whatever criteria you want.