Sql-server – Send SSRS Report Many Times Based on Sql Query

sql-server-2008ssrs

I have a user who is requesting that a legacy report (email subscription) that runs with multiple categories be modified to run based on which categories exist in the given date range. Thus instead of one email with daily details on:

Widget 1 
Widget 2 
Widget 3
Widget 4

They would like 4 separate emails with just Widget n.

The kicker is that in any given day, there can be a subset of Widget n details in the schema and they only want an email if details for those widgets exist.

Is this even possible with SSRS? I can do a data-driven subscription I can't quite figure out how to fire the report multiple times for each existing widget in that day.

Best Answer

In a data-driven subscription you specify a query. Each row that the query returns corresponds to a single email, rendering a new report for each one.

So have your query do something like: SELECT WidgetID FROM dbo.Widgets

...or if it's already doing that, use CROSS JOIN dbo.Widgets to make your query return four times as many rows (assuming there are four).

Then use the WidgetID field as a parameter to your report.

Related Question