I have a data driven subscription that is set up to email and uses a sql statement to generate the delivery settings and report parameters for each recipient (so each person does not reveive one email per row in the result set). I'm told that this is a good work around for creating a subscription that will NOT fire an email if there are no results returned, however I can not seem to get this to work...it continues to send an email with a blank report if there is no data for the previous day. Any thoughts/help would be appreciated. thanks in advance!If you want a fixed recipient list you want to do the following:
Assume your report data set comes from table DataTable, Pseudo SQL:
select Top 1 * from DataTable where InsertDate >= DATEADD(day, -1, GETDATE())
This will return exactly one row (one delivery) anytime there is data to report on. If you have multiple data sets in your report you'll need to play with the Joins to get this behavior.
Then in your subscription in the TO field, provide the static list of recipients.
If you need a dynamic list of recipients, I'd write a stored proc that returns the list of recipients if there was data in the DataTable, or no rows if there wasn't.
-Lukaszsql
No comments:
Post a Comment