Suppressing Blank Reports from SSRS Data Driven Subscription

goodmanro
goodmanro used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

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

Author

Commented:
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
Commented:
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
SELECT
     st.Staff_ID,
     st.EmailTo,
     st.EmailFrom,
     st.Format,
     st.Active
FROM SubscriberTable st

WHERE EXISTS (
                         SELECT
                              ct.Staff_ID,
                              ct.ContractNum
                          FROM ContractTable ct
                          WHERE
                               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

Commented:
Great! see you again in another SSRS question.

Regards,
Jason

Author

Commented:
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