I recently had to resolve this issue. I was running SQL Server Standard Edition and needed to have an SSRS subscription that behaved like a data-driven subscription. Data driven subscriptions are available in Enterprise Edition SQL Server, but not Standard.

What is a Data-Driven Subscription

This is a very useful tool in the Enterprise Edition. A regular subscription is simply a scheduled time when a report will run and can be sent to email addresses or saved to a network share. The individual subscription will only run once and utilizes the same report parameters and recipient information.

A data-driven subscription lets you run a database query that returns no results or a set of results. The report is then run as many times as there are row results in the original database query. This allows you to 2 important things:

  • Not run the report if it does not have any meaningful results. e.g. Don’t send anything out.
  • Run multiple reports at once with customized parameters (who it is sent to, the subject of the email, the parameters used to generate the report, etc). This allows you to scale subscriptions really easily without having to go in and create a subscription for each recipient manually every time.

But I Do Not Have SQL Server Enterprise Edition

This means you do not have the data-driven feature when creating subscriptions. If you are doing a lot of data-driven report subscriptions, it may be worth your while to pay for that license as it will be less development time and more intuitive for users to understand what is happening than what I will describe below.

So we will have to come up with an alternative method to achieve something similar.

The Design

  • Publish an SSRS report if it is not published already.
  • Create a regular subscription using the SSRS interface.
    • Set any parameter values that are constant.
    • You may set the other parameters, but they will be overwritten anyways.
    • Set the schedule to have a stop date in the past, this makes sure it is active, but will not run on its own.
  • Create a stored procedure to run our report.
    • Code example in detail below.
    • Needs to run a query that provides how many times you want the report to run and what the override parameters should be.
    • Will loop over the results to:
      • Override the parameters stored in the table.
      • Execute the report.
      • Wait until the report is finished.
      • Continue.
  • Create a SQL Agent job to run the stored procedure on a schedule.

A Note Waiting For Each Report To Finish

Calling EXEC ReportServer.dbo.AddEvent does not block the script until the report is finished. It merely adds the report to the queue to be processed. If the report takes a while to run, this script may overwrite the parameters and settings being used. So it is important to have a loop monitoring the Events table to make sure the report has completed before continuing.

Stored Procedure Example

This example will determine how many reports need to be created and run each report consecutively by waiting for the report to finish before triggering another one.

--Steps to create data-driven like subscription in SQL Server 2016 Standard Edition:
--1. generate a subscription for a single user and note the subscription_id
--2. create stored procedure to update settings and generate reports
--3. schedule a job to run this procedure

-- Notes to Remember
-- Need to use a Job to run this and schedule.  
-- The subscription_id will be generated at the time the report subscription is created
-- The subscription should be set to active with a stop date in the past so it doesn't run on its own if not desired, but is an active report subscription.

IF EXISTS ( SELECT 1 FROM sysobjects WHERE id = object_id('DataDrivenSSRSSubscription_MyExampleReport) AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
DROP PROCEDURE dbo.DataDrivenSSRSSubscription_MyExampleReport
END
GO

CREATE PROCEDURE [dbo].[DataDrivenSSRSSubscription_MyExampleReport]
@subscription_id varchar(50),
@parameter_id int

AS
BEGIN
    DECLARE @v_email varchar(256), @v_parameter1 varchar(256)
@settings varchar(4096), @params varchar(4096), @qry varchar(1024), @wait smallint
           
    DECLARE db_cursor CURSOR FORWARD_ONLY READ_ONLY FOR  
SELECT
*
FROM Some_Table
WHERE Some_Table.parameter_id=@parameter_id

    OPEN db_cursor  
    FETCH NEXT FROM db_cursor INTO @v_email, @v_parameter1
   
    -- looping through the list
    WHILE @@FETCH_STATUS = 0  
    BEGIN  
        -- wait until report has been cleared from the reporting queue
SELECT @wait = COUNT(*) FROM ReportServer.dbo.Event WHERE EventData=@subscription_id
WHILE @wait > 0
BEGIN
WAITFOR DELAY '00:00:01'
SELECT @wait = COUNT(*) FROM ReportServer.dbo.Event WHERE EventData=@subscription_id
END

        -- update parameters before generating report
SET @settings = '<ParameterValues>
  <ParameterValue><Name>TO</Name><Value>'+@v_email+'</Value></ParameterValue>
</ParameterValues>'

SET @params = '<ParameterValues>
  <ParameterValue><Name>Parameter1</Name><Value>'+@v_parameter1+'</Value></ParameterValue>
</ParameterValues>'
UPDATE ReportServer.dbo.Subscriptions
SET extensionsettings = @settings,
parameters = @params
WHERE subscriptionid = @subscription_id

        IF @@error = 0
        BEGIN
            -- run report and send email
EXEC ReportServer.dbo.AddEvent @EventType='TimedSubscription',
@EventData=@subscription_id
        END                
        FETCH NEXT FROM db_cursor INTO  @v_email, @v_parameter1
    END  
   
    CLOSE db_cursor  
    DEALLOCATE db_cursor

END    

References

These are some really good references that may be easier to understand. One note though, they do not cover “waiting for each report to finish before moving on to the next report.”