SQLTeam.com | Weblogs | Forums

SSRS Data Driven Subscription


#1

Hi everyone
I need some help creating data driven subscription. What I need to do is make sure report is not sent out when the attachment is empty. Basically if report procedure returns no data then don't send email (with an empty attachment)
I can not find clear answer anywhere I looked online on how to do it. There are bunch of articles saying it could be done but no clear example.
Would someone please help
thank u very much


#2

If you are able to build a data driven subscription it's really fairly straight forward. The first part of the data driven subscription is used to build the mail list and/or parameters of the report in question. It's then normally references in the second part- configuring the running of the actual report. With me so far?
The basic trick is simply to base the first part of the data collection to be the SQL for the attachment (or report) If it returns 0 records, the subscription simply stops- nothing to report.
For instance, if you want a report of employees with missing email addresses- you create the report itself that shows the results you want and in your data driven setup, use something like this in the first part:
SELECT emp_id
FROM t_employee
WHERE ([termination_date] is null AND email IS NULL)
Further into the data driven subscription setup, you normally use the results of this dataset to set parameters or something- in this case you don't. It's simply there to determine if there are any pertinent records and fail if not.