Suppressing Blank Reports from SSRS Data Driven Subscription

goodmanro used Ask the Experts™
I am using the data-driven subscription feature of SQL Server Enterprise.  My subscription is setup and working nicely; however, I'd like to suppress reports that have no data in the primary data table in the report.

When setting up the subscribers in the subscription, I pull the data from Data Set 1.  The report data actually pulls from Data Set 2 (located on a different server).  Is there a way to cross reference the number of results returned from the primary report data set (Data Set 2) when setting up the subscriber list?  The Subscriber list is also located in the SSRS report server database...I tried using the Execution Log to view the RowCount, but this doesn't work when the subscription is sent out as an Excel attachment.

Any thoughts out there?  Thank you in advance.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®


Thanks huslayer...this is helpful.  In your example, the SQL code in the SQL Agent Job simply does a count on a standard table.  Is there a way to pass a parameter to the SQL Agent Job to do something like this?

SELECT * FROM Table2 WHERE EmailAddress = @EmailTo

Open in new window

Jason YousefSr. BI  Developer
No I'm not aware or think there's a direct way of passing parameters to the SQL agent job.
You could use SSIS for that !

You could use a stored procedure for the definition of your report that accepts the e-mail as an input parameter and runs the sql agent job from within the SP.
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

Thanks.  I ended up using a WHERE EXISTS clause in my SQL statement.  In order to do this, I had to move my Subscription data table into my primary data source.  This is due to the fact that SSRS data driven subscriptions require you to use one data connection for the subscriber list.  Here is an example of my code for anyone else that may be running into a similar issue:

In Step 3: Create a data-driven subscription
FROM SubscriberTable st

                          FROM ContractTable ct
                               ct.Staff_ID IN (st.Staff_ID)

AND st.Active = 'True'

Open in new window

This may not be ideal for anyone who has to keep the data in separate data sets...but seems like a good workaround for us.
Jason YousefSr. BI  Developer

Great! see you again in another SSRS question.



The solution that I found was not recommended by others and solved the problem.  I have marked my own solution so that it may be shared with others that face a similar challenge.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial