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 2005-Stored procs with multiple results

Hello
In Reporting Services 2000, only the first result set returned from
stored procedure is recognized. Does anyone know if this is stil
true in Reporting Services 2005? It appears to be in beta 2, but
just want to make sure I'm not missing anything. Has anyone heare
that using multiple result sets from a stored procedure will be a
added featureI have not heard anything about that. If you are seeing the same
functionality in Beta 2 then my guess is that nothing has changed in this
area.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"erobert1" <robclark496@.msn-dot-com.no-spam.invalid> wrote in message
news:LN-dnWugPNVOC0HfRVn_vg@.giganews.com...
> Hello,
> In Reporting Services 2000, only the first result set returned from a
> stored procedure is recognized. Does anyone know if this is still
> true in Reporting Services 2005? It appears to be in beta 2, but I
> just want to make sure I'm not missing anything. Has anyone heared
> that using multiple result sets from a stored procedure will be an
> added feature?
>|||Hi
I logged a suggestion with Microsoft on this and will post thei
answer if I get one. In the mean time if anyone else has informatio
on this please post it here too|||Hi
I got a response from my post on the Microsoft product feedbac
center. It will be considered for a future release. If this featur
is important to you, you can vote for it at
http://lab.msdn.microsoft.com/productfeedback/viewFeedback.aspx?feedbackid=38a81133-5e82-4a31-a6b3-2877929373c

Reporting Servers - Export to Multiple tabs in Excel

I need to create a Reporting Services report that contains multiple result sets. I want each result set to export to a different tab in Excel. I want to be able to name the tab. I would also like the headings in Excel to show up in the Header section of Excel, rather than in the body of the spreadsheet. Is there a way to accomplish this task.

Put multiple tables in your report and then put page breaks before them. Each manual page break becomes a new tab.

For headers, you need to use a Excel DeviceInfo setting, SimplePageHeaders (see http://msdn2.microsoft.com/en-us/library/ms155069(en-US,SQL.90).aspx). If you want this to be the default, you can add it to the RSReportServer.config file (see http://msdn2.microsoft.com/en-us/library/ms156281.aspx).

|||Is there a way to specify the name of the Excel tab, instead of Sheet1?|||

I would like to do this also. Is there any way to name each Sheet?

|||Unfortunately no, you cannot control the sheet naming.|||

Thanks for answering Mike.

Do you know if there are any plans to add this capability?

|||

I can find no reference to this in the documentation and it appears to be ignored - presumeably this is RS 2005 only? (I'm using 2000 SP2).

Would this get round my current problem of having lots of additional columns/merged cells to accommodate all the header layout when exported to Excel? Ideally I want the data structure in Excel to be a straight forward matrix of the data values in the report table which isn't feasible when exported to Excel with our default headers which contains a lot of labels containing context information.

Reporting Servers - Export to Multiple tabs in Excel

I need to create a Reporting Services report that contains multiple result sets. I want each result set to export to a different tab in Excel. I want to be able to name the tab. I would also like the headings in Excel to show up in the Header section of Excel, rather than in the body of the spreadsheet. Is there a way to accomplish this task.

Put multiple tables in your report and then put page breaks before them. Each manual page break becomes a new tab.

For headers, you need to use a Excel DeviceInfo setting, SimplePageHeaders (see http://msdn2.microsoft.com/en-us/library/ms155069(en-US,SQL.90).aspx). If you want this to be the default, you can add it to the RSReportServer.config file (see http://msdn2.microsoft.com/en-us/library/ms156281.aspx).

|||Is there a way to specify the name of the Excel tab, instead of Sheet1?|||

I would like to do this also. Is there any way to name each Sheet?

|||Unfortunately no, you cannot control the sheet naming.|||

Thanks for answering Mike.

Do you know if there are any plans to add this capability?

|||

I can find no reference to this in the documentation and it appears to be ignored - presumeably this is RS 2005 only? (I'm using 2000 SP2).

Would this get round my current problem of having lots of additional columns/merged cells to accommodate all the header layout when exported to Excel? Ideally I want the data structure in Excel to be a straight forward matrix of the data values in the report table which isn't feasible when exported to Excel with our default headers which contains a lot of labels containing context information.