Reporting Services and multiple data sources

I have a group of reports that are the same for 3 different companies, the difference is the data connection. Is there a way I can change the data connection based on a variable passed in at the time of report execution?

thanks

There are several options of how to achieve this on RS 2000:
* Use a custom data processing extension
http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_extend_dataproc_5c2q.asp
* Use the SOAP API by calling SetDataSourceContents:
http://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. Check MSDN for details:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_12_9ooj.asp
* If the databases are on the same server (SQL 2000), 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 multiple times with different names using different data sources. Then write a main report that shows/hides the correct subreport based of whatever criteria you want. (Drawback: bad performance)

If you are running RS 2005, you can use the expression-based connection string approach: Finish the design of the datasets with a constant connection string and make sure everything works. Then, go back to the data tab and open the dataset/data source dialog and change the connection string to be an expression. Use string concatenation to plug in the parameter value. Here is an example of how the RDL would look for a parameter-based connection string:
<DataSources>
<DataSource Name="Northwind">
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>="data source=" &amp; Parameters!ServerName.Value &amp; ";initial catalog=Northwind;"</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<ReportParameters>
<ReportParameter Name="ServerName">
<DataType>String</DataType>
<Prompt>ServerName</Prompt>
</ReportParameter>
</ReportParameters>
You can also check this blog posting: http://blogs.msdn.com/bwelcker/archive/2005/04/29/413343.aspx

-- Robert

|||Are you also able to set credentials for access to the Data Source via this approach?or does it only allow you to modify the Data Source itself|||The credentials can only be changed if you go with the SetDataSourceContents approach or if you have your own custom data extension which controls the credentials for the actual data source access independently.

-- Robert

Reporting Services - statistics

Where can I find statistics (if it is publicly available) on how many companies worldwide are using SQL Server Reporting Services?

I doubt that is publicly available. Why do you ask?

|||
My company's management want to "make an informed decision" about a new project design/implementation and need convincing about what technologies are the best and most widely used.

I'd like to use "foul language" with lots of F-words, but for some reason I think that would not be very persuasive and they'd come to some wrong conclusions regarding myself...

Hence, the need for statistics.|||

I'm not sure where you would get statistics, but here are some customers that use SQL 2005.

http://www.microsoft.com/sql/solutions/upgrade/default.mspx#EID

|||I'm not sure this will suffice, but I'll use it...

Thank you very much!|||You're welcome. Out of curiosity, what are you comparing it to?sql

Reporting Services

I have SQL Enterprise 2000 installed on my computer to access companies database and chage data as needed. They recently asked me to look into the reporting services function available with sql. How can I play around with it and practice. Is this a program I buy from microsoft?

Thanks,

Carl

Hi Carl,

Here is a link :

http://msdn.microsoft.com/sql/bi/reporting/default.aspx

you can find everything here starting from articles to very helpful and intersting webcasts.

Enjoy

--Amde