Data Driven Subscription (DDS) is SSRS feature available in SQL Server Enterprise Edition only. It allows rendering many copies of the same report based on combination of parameters and deliver them by query defined destinations (emails or file location). DDS checks if report has data and pushes that like notification. That means other SQL Server editions lack of that feature. For that reason (+ customization), I have created small solution which allows filling the gap.
As always, implementation is build on assumptions (in other words, based on specific interfaces). Here is a list of assumptions:
- Not every report needs DDS.
- If report requires DDS, it must:
- have parameter UserId
- have subscription with dedicated shared schedule. That schedule should be based on day with specific time, for example 8:00AM (user local time, not server time).
- DDS report can have many subscriptions. Shared schedule can be used in many subscriptions
- Dedicated shared schedule should be:
- named with prefix, for example, “DataDrivenSubscription.”.
- paused, which means SQL Server would not call that schedule.
- UserId parameter should be based on generic lookup list of users. For example, database view [dbo].[vw_ReportSubscribers]. It should have the following fields:
- Id. It is used as DDS report parameter.
- Email. It is used for report delivery
- DisplayName. Is used as part of email invitation
- TimeZone. It allows sending report based on end user local time
- As far as shared schedule is paused, another SQL Server job has to invoke report rendering. That managing job should use frequent schedule (every 5-10 minutes) simulating long pooling. That job contains one step with SSIS package which loops user parameter collection and makes a decision which UserId report render and send.
- DDS should evaluate report data. For that reason the report subscription [Comments] field should have SQL query which check if report has data. If report has no data – it should not be rendered, no report data -no delivery.
The following diagram describes the process:
Every X minutes SQL Server agent job runs SSIS package which iterates through users collection. Stored procedure, let’s call it [dbo].[GetRenderSettingsData], checks if report has data for that user and generates xml which is used by Unit Testing SSRS Tool to render the report.
When report’s file is rendered by the tool, it is sent by SSIS package email task to end user. The delivery is based on user local time (this feature is unique for this solution and does not exist in SSRS Enterprise Edition).