SSRS how to trigger subscription when new data is

Question

I have created a data driven subsciption for a report. The subscription gets data from a table in Oracle database. (query: select * from Mytable) What I want to do is to subscript reports by event(by schedule works already). For example, when new rows are inserted in the table in Oracle, this will trigger subscription and generate new reports. How do i approach this? Thanks!

Answer 1

I can think of a few approaches. With some extra database design, you could set a flag in the table for rows that have been inserted, but not yet reported on. Then you could change your data-driven subscription query to run a stored procedure that looks for the flags, returns the rows to the subscription, and updates the flag on the table. You'd then schedule the data-driven subscription to fire at an appropriate time interval--when no rows have been inserted in the table, no rows are returned to the subscription, and so it doesn't fire.

If you want the report to fire with every single insert, then another approach is to set a trigger on your table so that every time a row is inserted, it sends code over to the ReportServer database, as follows:

exec [ReportServer].dbo.AddEvent @EventType='TimedSubscription', @EventData='520580b3-bd4e-4221-9254-b220bc16ca55'

The code above is how SQL Server fires subscriptions in the jobs. You would just invoke the job directly by the trigger. The GUID in the @EventData is the SubscriptionID that you want fired. You'll have to look in the Subscription table (in the ReportServer database) to figure that out. Also, I'd suggest that you set up the data-driven subscription to be on a "One-Time" schedule, if you go this route.