Dynamic Data Driven Subscription in SSRS

Business Problem:

In one of our report requirement, we need to feed the data files to robot for every one hour. For that, we have created an hourly subscription for that report. As a result, we were sending many empty files to robot, which makes the system resource degradation.

To overcome this issue we have to create a subscription to send the data files only if it has data.

Business Solution Given:

Here we are going to see, how to overcome this issue.

Step 1:

Create the report and deploy the report in the report manager.

Step 2:

Create a new timely subscription for the report.

Right click on the report – Subscription – New Subscription

I have chosen report delivery type as email, rendering format as excel and the scheduling time as 1 hour.

Now the report will run and send the data file for every 1 hour. I have received a data file with no data. Please find the attached subscription mail for the same.

Step 3:

Now are going to create a dynamic subscription for this report.

1. Create a new data driven subscription for the report.

Right click on the report – Subscription – data driven subscription

2. Specify the data source for the report.

3. Copy the below SQL and validate in the command tab that returns a list of recipients.

SQL:

select ‘dgurumurthy@ups.com’ as email from sysibm.sysdummy1
where 1 <= (
select count(shpmnt_nbr) from shp_shpmnt
where creation_tmst between sysdate – 1 hour and sysdate )

Example:

If we have, shipment number created for last 1 hour then the count value will get satisfied and it will display the email address.

4. Specify the corresponding delivery setting options.

In the To option, choose the get the value from the database option, it will list the values specified in the previous step.

5. Follow the next steps.

6. Choose the schedule timing and then click finish.

7. New data driven subscription has been created.

Step: 4

We should get the data files only it has data.

I have received a data file having shipment data.

If we do not have shipments created for the last 1 hour then we will not get the data file and the status will be updated as 0 processed.