SSRS Run Subscription only if report has discrepancy

Question

I have an SSRS report I would like to include in the body of an email and schedule it to run every night using a subscription, but I only want the email to be sent if there is a discrepancy in the report. i can have a text box on the report with a true or false value indicating discrepancy.

Answer 1

If you have the Enterprise Edition of SQL Server, you can use the Data Driven Subscriptions to trigger your report to only run when there's a discrepancy.

If you do not have the Enterprise Edition, you can create a Stored Procedure to check to see if there's a discrepancy for your report and then trigger the report if so.

I usually create a one-time subscription with the time that's already past so the report server won't trigger it. After creating the subscription, click on it to edit the subscription and copy the subscription ID from the Address Bar of the web browser.

You would create a stored procedure that uses your same query from the report to check for any discrepancy but instead of returning records, just get a count or other indicator:

DECLARE @RECORDS INT = (SELECT COUNT(*) FROM TABLE WHERE <SAME CRITERIA AS REPORT>)
DECLARE @SUBSCRIPTION_ID VARCHAR(100) = '<YOUR REPORT'S SUBSCRIPTION'S DI FROM ADDRESS BAR>'

IF @RECORDS > 0 
    EXEC ReportServer.dbo.AddEvent @EventType = 'TimedSubscription', @EventData = @SUBSCRIPTION_ID; 

Then when the Stored Procedure runs, it will check the query and if there are any records will trigger your subscription to send the e-mail subscription with the embedded report.

You would then need to scedule a job that runs the SP on whatever schedule is needed for the report.