SSRS email subscription event trigger - Data values changed (not Timed subscription)

Question

I want an SSRS report to send a report subscription (email or file share) based on a Data Value change in the report data set.

Let’s say 500 rows of the Newest Data in a table all have "Update Time= 9/19 1:40 pm” and then 50 minutes later that table is updated to insert 200 new rows with "Update Time=9/19 2:30pm”. The data value change in this example is the ‘Update Time’ has changed on all records. I want SSRS to be triggered at 2:30pm based on in this change in the dataset records, and then SSRS will email the new 200 records.

Can SSRS do this kind of delivery? Is “data-driven” the answer? (Doesn’t look to me like data-driven solves this problem; but perhaps I am just not understanding?). Does snapshot update achieve this? (I thought snapshots are also time driven?).

SQL Version:

Microsoft SQL Server 2016 (SP2-CU2) (KB4340355) - 13.0.5153.0 (X64) 
    Jun 28 2018 17:24:28 
    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Datacenter 6.3 <X64> (Build 9600: ) (Hypervisor)

NOTE: I will use RANK() windowing function to achieve that grouping pretty easily-- It would group everything as 1 (200) rows, 2 (500) rows, etc. I would keep only group 1, so all of the records would have a new ‘Update Time’.

https://docs.microsoft.com/en-us/sql/reporting-services/subscriptions/subscriptions-and-delivery-reporting-services?view=sql-server-2017#triggering-subscription-processing.

Triggering subscription processing

The report server uses two kinds of events to trigger subscription processing: a time-driven event that is specified in a schedule or a snapshot update event.

A time-driven trigger uses a report-specific schedule or a shared schedule to specify when a subscription runs. For on-demand and cached reports, schedules are the only trigger option.

A snapshot update event uses the scheduled update of a report snapshot to trigger a subscription. You can define a subscription that is triggered whenever the report is updated with new data, based on report execution properties that are set on the report.

Processing a data-driven subscription

Data-driven subscriptions can produce many report instances that are delivered to multiple destinations. The report layout does not vary, but the data in a report can vary if parameter values are passed in from a subscriber result set. Delivery options that affect how the report is rendered and whether the report is attached or linked to the e-mail can also vary from subscriber to subscriber when the values are passed in from the row set.

Data-driven subscriptions can produce a large number of deliveries. The report server creates a delivery for each row in the row set that is returned from the subscription query.

Hannover Fist Hannover Fist · Accepted Answer · 2018-09-20T23:59:00

As far as I can see, you are correct. The snapshot update method would require snapshots to be created and snapshots are only created on a schedule or manually by a user.

What might have less overhead than a datadriven subscription is to have a stored procedure that runs the simplest query to detect if any pertinent data has changed and, if so, trigger the subscription.

EXEC dbo.AddEvent @EventType = 'TimedSubscription', @EventData = '00f4ecee-891d-445f-ae81-24ef62d3fb53'

Where the Event Data is your subscription ID (copy the Edit subscription link to get the ID)

Of course you'd have to run an SSIS job to execute your stored procedure every 15 min or so but I think it's better than the data driven subscription.

SSRS email subscription event trigger - Data values changed (not Timed subscription)

1 Answers