SQL Server Reporting Services Empty Report from a Data Driven Subscription


By:   |   Updated: 2020-04-10   |   Comments   |   Related: > Reporting Services Dynamic Reports

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.

create new ssrs 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 [email protected] and [email protected]
GROUP BY de.EmployeeKey,de.FirstName,de.LastName

Here is an example of the output:

report design

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.

create ssrs report subscription

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

create ssrs report subscription

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

create ssrs report subscription

Then click "Edit dataset".

create ssrs report subscription

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 
create ssrs report subscription

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 [email protected];

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];
query ssrs report data

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.

ssrs data source
ssrs data source

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

..ssrs data source
ssrs data source

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.

ssrs email delivery options

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.

ssrs report parameters

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

ssrs subscription settings

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.

ssrs subscription settings
test ssrs subscription
test ssrs subscription

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

This is how the report looks on the receiving end.

ssrs sample report output
Next Steps

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



Last Updated: 2020-04-10


get scripts

next tip button



About the author
MSSQLTips author Ohad Srur Ohad Srur is a Microsoft Solution Expert (MCSE) - Data Platform and the managing director of Data-Best, a consulting company that focuses on assisting to K-12 independent schools.

View all my tips





Comments For This Article





download


Recommended Reading

Create Dynamic SSRS Reports Using a Query as an Input Parameter

Data Driven Colored Text for Reporting Services Reports

Creating Dynamic Report Filters in SQL Server Reporting Services SSRS

Show and Hide Reporting Objects in SQL Server Reporting Services

Conditional report rendering based on render formats for SSRS Reports





get free sql tips
agree to terms


Learn more about SQL Server tools