SSRS Empty Report from a Data Driven Subscription

Problem

SQL Server Reporting Services (SSRS) has a great feature called Subscriptions for delivering reports at a specific time or in response to an event. Reporting Services supports two ways of delivering reports: Standard and Data-Driven. The Standard option allows you to send only to a specific email address with specific parameters. In contrast, the Data-Driven option will enable you to run a query at run-time so you can dynamically change the recipients and report parameters; this approach is much better and easier than using a SQL Server Agent Job to perform this task. While the Data-Driven feature is very flexible, and would resolve most use cases, there is not currently an out-of-the-box option to disable the delivery of empty reports to users.

Solution

In this tip, we will use the AdventureWorksDW2014 database (available here) to demonstrate how to disable the delivery of empty reports.

In this demo, we are required to send a daily report to each employee with his/her summary of sales completed for that day.

We will use SSRS Data-Driven Subscriptions to deliver reports. Due to the simplicity of the example, we technically can resolve it by a simple Data-Driven query that excludes anyone who has not had any sales for the required data. However, in real life, we might need to create a more complicated report, and then this option will not be viable. Hence, this demo will demonstrate how to deliver the report only to the required recipients.

First, we will prepare the report.

We will embed the following query:

SELECT de.EmployeeKey,Concat(de.FirstName,' ',de.LastName) as FullName,sum(frs.SalesAmount) as Sum_per_employee
FROM dbo.DimEmployee de
JOIN dbo.FactResellerSales frs on de.EmployeeKey=frs.EmployeeKey
WHERE de.CurrentFlag=1 and de.SalesPersonFlag=1 and frs.OrderDate=@Date and de.EmployeeKey=@EKey
GROUP BY de.EmployeeKey,de.FirstName,de.LastName

Here is an example of the output:

Next, we will create a stored procedure to return only the required recipient list. Our example is straightforward, but in a more complex solution, we can create more complex logic. What we will do in our example, is write the required logic to locate the recipient list and save them into a global temporary table.

CREATE PROC uspGetSellerList 
AS
 
IF object_id('tempdb..##SellerListTemp') is not null
BEGIN
    DROP TABLE ##SellerListTemp 
END
 
SELECT de.EmployeeKey,Concat(de.FirstName,' ',de.LastName) as FullName,de.EmailAddress
INTO ##SellerListTemp 
FROM dbo.DimEmployee de
JOIN dbo.FactResellerSales frs on de.EmployeeKey=frs.EmployeeKey
WHERE de.CurrentFlag=1 and de.SalesPersonFlag=1 and frs.OrderDate= '2013-11-28'-- Normally we will put here the current date TRY_CONVERT(date,GETDATE()) 
GROUP BY de.EmployeeKey,de.FirstName,de.LastName,de.EmailAddress

Next, we will prepare the report subscription. For this step, we will need to open the ReportServer portal and locate our report, then click the ellipsis, and select Manage.

Click on Subscription from the side menu, and select “New Subscription”.

Enter a meaningful name for the subscription, and select Data-driven subscription.

Then click “Edit dataset”.

And paste the following code into the Query textbox.  This code will execute first the stored procedure and will store the required recipient list in a global temporary table. Then the query will fetch the required report parameters (EmployeeKey and EmailAddress). The parameters values will be used to pass into the report during the execution time of the subscription. If the query returns more than one record, then the subscription will iterate on each record and will deliver the email accordingly.

exec dbo.uspGetSalerList;
SELECT EmployeeKey, EmailAddresss
FROM ##SellerListTemp 

If we need to check the report execution history, we can pass the Report ID to the [ExecutionLogStorage] table.

-- Check report execution history
declare @reportid AS VARCHAR(8000)
SELECT @reportid=rs.ReportID 
FROM ReportServer.dbo.ReportSchedule rs 
WHERE rs.SubscriptionID='660BB3D7-A616-42B8-9B62-739A04D8F4E2';
SELECT * 
FROM [ReportServer].[dbo].[ExecutionLogStorage] 
WHERE ReportID=@reportid;

To get the Subscription ID / Report ID, we can use the following query.

-- Get Subscription ID 
SELECT 
    S.[SubscriptionID],rs.ReportID,
    C.[Name] + ' (' + S.[Description] + ')' AS [SubscriptionName]
FROM [dbo].[Subscriptions] S
INNER JOIN [dbo].[Catalog] C ON S.[Report_OID] = C.[ItemID]
      JOIN ReportServer.dbo.ReportSchedule rs on rs.SubscriptionID=s.SubscriptionID
WHERE s.InactiveFlags=0
ORDER BY [DisplayName];

To finalize the data-source setup, we need to select the connection to the database. Creating a shared data source would be the easiest way; we can always re-use it later for other reports. If you already set it up, then click on the ellipsis and select your data source.

After the connection is set, we need to click Validate, and then we can apply

..

Next, we will select the Email delivery option, and open the drop-down for the “To:” field to “Get Value from dataset”, and select the matching field from the query entered previously in the Data-Driven section.

Lastly, we will set the parameter for Employee Key to “Get value from dataset” and pick the EmployeeKey from the Value drop-down. And when ready, we will create the subscription by hitting the Create subscription button.

Now, we should see in the Result column it says “New Subscription”.

To test our report, we can tick the box next to the Edit field, and press Run.

Note: In some older versions of SSRS, the Run option does not exist. You can use the link available at the “Next Steps” section, to learn how to run the report on demand from SSMS.

Now the reports will only be delivered to the required recipients.

This is how the report looks on the receiving end.

Next Steps

Check out these resources on MSSQLTips.com for more information:

• Report launcher to run SSRS report subscriptions on demand – This tip elaborates on how to execute any report from SSMS
• Check out the SQL Server Reporting Services Tutorial.

Ohad Srur

Ohad Srur is the Managing Director of Data-Best based in Sydney, Australia, which is a consulting company that focuses on assisting to K-12 independent schools to improve their use of their technology systems. Ohad loves to develop database solutions, and wrangle with code and data to automate processes as much as possible. Ohad is a Microsoft Solution Expert (MCSE) Data Platform since 2016 and has been working with the SQL Server stack since 2011. Other than SQL Server stack, he likes to work with PowerShell and Python.