A use case has been presented where users wish to receive email notification of certain records matching various criteria. Additionally, the notification subscription should only notify the user if records exist matching the criteria. The notification via subscriptions should only happen once for any given record.
This use case is unique since the typical approach is to create a report with criteria parameters and then use a daily/weekly/etc timed subscription to run the report and email to the users. This methodology sends the report to the users even if no records match the actual report run-time criteria. Also, this may allow the report to keep a particular record in a report for as long as the record meets the criteria, and thus doe not fulfill the “one-time” only notification desired above.
To address this use case we are investigating the use of data driven subscriptions for a solution. The concept is to use a subscription history table that records all records for which notifications are sent to users. Additionally, the query driving the report will look at this table to determine if any new records match the other reporting criteria. If not, the email distribution will be nulled, to keep users from being notified of no matching records.
I would love to hear other suggestions on how to create this type of report notification/alerting. Again we want to send notification one-time per matching record, regardless if the record shows up multiple times in the raw report criteria results. And, we want nothing to be sent to the users if no matching records exist.
Blessings
Popularity: 21% [?]
If the SSRS report dataset comes from a stored procedure you can perform some other operations in addition to providing data to the report. So you could update a matching table that stores user FK, Record FK, and a bit field or datetime field for sent. The select statement can do a NOT EXISTS on that matching table so records don’t get reported again. You may need a temp table to ensure the results in the SELECT statment and UPDATE statement are exactly the same.
One thing to note in SQL Server 2005 at least, Visual Studio won’t properly read fields into the dataset if the SP has more complex T-SQL before the SELECT statement. However, you can add fields to the dataset manually OR refresh the dataset from the SP then add the additional T-SQL.
God bless,
Kevin
Thanks Kevin. You described, much better than I, the approach I am looking to take unless there is a better idea out there. I remember a few years ago reading about Notification Services. Hmmm Wondering if that is an approach for the notification aspect, less report. (Dangerous to write my stream of conscious thoughts)
Thanks for the tip. Blessings!
I had the same idea of using SSRS for Alerts… Have you made any progress?
Thanks for the follow-up. No. Actually we got moved to another higher priority project and have not come back to this. Curious if anything in R2 will address the need. I haven’t seen anything yet.
Blessings.
Excellent post..Keep them coming
Thanks for sharing.