SSRS Subscription with Multiple Data Sources

0
votes

Using the subscription functionality of SSRS, I have automatically run reports in a scheduled manner and sent out e-mails with the results of the report. I have only done this using a single data source. My question is, can I do this while using multiple data sources?

My goal is to just run the same report across a collection of data sources, and then have all of the results from each data source get sent out in one e-mail as a subscription.

In my specific case, I just need a single row for each data source. My intent would be to form a table, with one row articulated for each data source.

1
sqlreporting-services
Your question isn't very clear. Have you configured multiple data sources in the report? Sounds like you are discussing two things: both combining data from multiple data sources versus combining multiple reports into one. – Jamie F
In my experience, defining a subscription in SSRS means that you define the data source outside of the RDL file that is your report--and instead in the subscription itself. I can configure multiple data sources in a report. What I am unclear about is the subscription definition. – nairware
OK. I think you are referring to Data-Driven subscriptions ( msdn.microsoft.com/en-us/library/ms159150.aspx ) Typical SSRS subscriptions don't allow the data source to be defined in the subscription: it needs to be defined in the report (or at least connected to a shared datasource at the report level.) Do you really need Data-driven subscription functionality? I try to avoid these, as they aren't available with SQL Server Standard Edition. I suggest you investigate the structure of typical SSRS subscriptions. – Jamie F

1 Answers

1
votes

Using the subscription functionality of SSRS, I have automatically run reports in a scheduled manner and sent out e-mails with the results of the report. I have only done this using a single data source. My question is, can I do this while using multiple data sources?

This isn't clear because a single report can only be matched to a single subscription.

My goal is to just run the same report across a collection of data sources, and then have all of the results from each data source get sent out in one e-mail as a subscription.

Do this in a single report that uses multiple data sources.

In my specific case, I just need a single row for each data source. My intent would be to form a table, with one row articulated for each data source.

Sounds like you have two options here:

  1. Use Linked Servers:
    • Linking Servers Link 1.
    • Create Linked Servers Link 2.
    • Write a query that returns a single line for each source and use UNION ALL to create a single result set/'table'.
  2. Create a report that uses the multiple data sources.
    • Have a single row table for each source and then arrange the tables to look like a single one when rendered.

Create a single subscription for your new report that combines multiple data sources.