Simulate SQL Server Reporting Services Data Driven Subscriptions

Problem

You want to take advantage of the SQL Server Reporting Services Data Driven Subscriptions, but it’s not supported in your edition of Reporting Services. In this tip I will show you that with a little scripting work you can have the same functionality.

Solution

If we are planning to emulate the behavior of the SQL Server Reporting Services Data Driven Subscriptions first we need to identify the job that fires the report execution. For those of you who don’t know, Reporting Services subscriptions are processed as SQL Server Agent Jobs. Unfortunately the Job names aren’t descriptive about the subscription per se, but we can translate the Job name with a simple query.

use ReportServer
GO
SELECT  a.Path,
a.Name,
b.ScheduleID,
a.Description
FROM    dbo.Catalog a
        INNER JOIN dbo.ReportSchedule b ON b.ReportID = a.ItemID;

Now that we have identified the SQL Server Agent Job responsible for the subscription let’s take a look at what it does. As we can see in the next query, there is one single statement that runs a stored procedure named AddEvent. This stored procedure receives two parameters: @EventType to specify what kind of event we are adding to the queue and @EventData that in this case is the SubscriptionID column of the Subscription table in the ReportServer database.

exec [ReportServer].dbo.AddEvent @EventType='TimedSubscription',
 @EventData='089d8744-bbf2-4734-8a71-392dd956121b'

The Subscription table contains all the subscription configuration settings. There are three columns that we must focus on if we want to emulate a Data Driven Subscription. These columns are of type NTEXT, but contains XML data. In the next sections I will describe them and show an example of the XML data.

ExtensionSettings

This column contains the main subscription settings, like the report render format, email recipients list or the file share path, depending on the delivery extension used.

<ParameterValues>
<ParameterValue>
<Name>TO</Name>
<Value>Daniel</Value>
</ParameterValue>
<ParameterValue>
<Name>IncludeReport</Name>
<Value>True</Value>
</ParameterValue>
<ParameterValue>
<Name>RenderFormat</Name>
<Value>MHTML</Value>
</ParameterValue>
<ParameterValue>
<Name>Subject</Name>
<Value>@ReportName executed at @ExecutionTime</Value>
</ParameterValue>
<ParameterValue>
<Name>IncludeLink</Name>
<Value>True</Value>
</ParameterValue>
<ParameterValue>
<Name>Priority</Name>
<Value>NORMAL</Value>
</ParameterValue>
</ParameterValues>

Parameters

This section contains the Report’s input parameters.

<ParameterValues>
<ParameterValue>
<Name>version_string</Name>
<Field>VERSION</Field>
</ParameterValue>
</ParameterValues>

DataSettings

This is the column that contains the Data Driven subscription settings like the query definition and the results mapping.

<DataSet>
<Fields>
<Field>
<Alias>TO</Alias>
<Name>TO</Name>
</Field>
</Fields>
<Query>
<CommandType>Text</CommandType>
<CommandText>SELECT  TOP 1 'someone@somedomain.com ' AS [TO]
FROM    sys.dm_exec_requests
WHERE   blocking_session_id > 0
</CommandText>
<Timeout>30</Timeout>
</Query>
<CaseSensitivity>False</CaseSensitivity>
<AccentSensitivity>False</AccentSensitivity>
<KanatypeSensitivity>False</KanatypeSensitivity>
<WidthSensitivity>False</WidthSensitivity>
</DataSet>

As you may guess, in order to simulate a Data Driven Subscription you must dynamically change the values of the ExtensionSettings and Parameters columns. We don’t need to change the value of DataSettings column because if our edition of Reporting Services doesn’t support Data Driven subscriptions, the report server will omit its value.

Sample

To show how to emulate a Data Driven Subscription I will use my previous tip Automatically Send Performance Dashboard Reports during a database event using Reporting Services.

After you create a standard subscription to the report and identify the SQL Server Agent Job name that fires the report execution with the first query on this tip, you have to change the job’s step code to make it only process the report when there is a blocking session on the SQL Server instance. The following query is one way we can achieve that.

IF EXISTS ( SELECT  0
  FROM    sys.dm_exec_requests
  WHERE   blocking_session_id > 0 )
  BEGIN 
  EXEC [ReportServer].dbo.AddEvent @EventType = 'TimedSubscription',
  @EventData = 'a0d292d9-3776-4bc0-be0e-8c71502d5e73'
END

Now suppose you have a products catalog report that you want to send to your customers in New York. With a Data Driven subscription you can use a function that returns the email addresses of your customers. Because the number of customers, therefore email recipients, can change over time, see how we can incorporate the same functionality with an edition that doesn’t include this feature.

To change the email recipient dynamically, we must copy the value of ExtensionSettings column of the Subscriptions table for the report to be subscribed. The following query will get us that information.

use ReportServer
GO
SELECT  Description ,
        CAST(ExtensionSettings AS XML) ExtensionSettings ,
        CAST(Parameters AS XML) Parameters ,
        CAST(DataSettings AS XML) DataSettings
FROM    dbo.Subscriptions

I created the following scalar function in the AdventureWorksDW2012 database that returns the email address of the persons from New York.

USE AdventureWorks2012
go
CREATE FUNCTION GetPersonEmail ( )
RETURNS VARCHAR(100)
AS
    BEGIN
        DECLARE @Out VARCHAR(1000) 
        SELECT  @out = SUBSTRING(( SELECT TOP ( 10 )
                    ';' + e.EmailAddress AS [text()]
           FROM     Person.Person P
                    INNER JOIN Person.EmailAddress E ON E.BusinessEntityID = P.BusinessEntityID
                    INNER JOIN Person.BusinessEntityAddress BEA ON BEA.BusinessEntityID = P.BusinessEntityID
                    INNER JOIN Person.Address ADDR ON ADDR.AddressID = BEA.AddressID
                    INNER JOIN [Person].[StateProvince] ST ON ST.StateProvinceID = ADDR.StateProvinceID
           WHERE    ST.StateProvinceCode = 'NY'
                    AND ST.CountryRegionCode = 'US'
         FOR
           XML PATH('')
         ), 2, 1000)  
        RETURN @out
    END

After copying the data of ExtensionSettings we have to edit the job step in SQL Server Agent and add a bit of logic to the code. The next query is to replace the job step code. As you can see I do an update to the ExtensionSettings column of the Subscriptions table with the concatenation of the value we have previously copied with the results returned by the function.

  UPDATE  ReportServer.dbo.Subscriptions
  SET     ExtensionSettings = '<ParameterValues>
  <ParameterValue>
  <Name>TO</Name>
  <Value>'
  + CAST(AdventureWorks2012.dbo.GetPersonEmail() COLLATE Latin1_General_CI_AS_KS_WS AS VARCHAR(100))
  + '</Value>
  </ParameterValue>
  <ParameterValue>
  <Name>IncludeReport</Name>
  <Value>True</Value>
  </ParameterValue>
  <ParameterValue>
  <Name>RenderFormat</Name>
  <Value>MHTML</Value>
  </ParameterValue>
  <ParameterValue>
  <Name>Subject</Name>
  <Value>@ReportName executed at @ExecutionTime</Value>
  </ParameterValue>
  <ParameterValue>
  <Name>IncludeLink</Name>
  <Value>True</Value>
  </ParameterValue>
  <ParameterValue>
  <Name>Priority</Name>
  <Value>NORMAL</Value>
  </ParameterValue>
  </ParameterValues>'
  WHERE   SubscriptionID = 'A0D292D9-3776-4BC0-BE0E-8C71502D5E73'
  
EXEC [ReportServer].dbo.AddEvent @EventType = 'TimedSubscription',
  @EventData = 'a0d292d9-3776-4bc0-be0e-8c71502d5e73'

Next Steps

In the following tip you can find more information about How to easily identify a scheduled SQL Server Reporting Services report.
Are you new to Reporting Services? This tutorial will be the best place to start: SQL Server Reporting Services Tutorial.
You can find more tips on SSRS here and on SQL Server Agent here.

Resources

Community

Subscribe