Share via

Top Tip - Dynamic Database connections for SQL Server 2000 Reporting Services reports

  • Article

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

https://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_extend_dataproc_5c2q.asp

* Use the SOAP API by calling SetDataSourceContents:
https://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_lz_2ojd.asp

* Use the linked server functionality of SQL Server; please check this thread:
https://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=848bac6b-98a2-4de7-abfd-bf199a99b660&sloc=en-us

* 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.