Can you have a report mail based on when a field in a table is updated?

  • I send out a report with a subscription. But I set it up to mail out at 8:00 am daily and I would like it to mail out when the data has been updated ... sometimes at 6:00 am sometimes at 6:30 am etc.

    Is that possible?

  • You might want to check out data alerts:

    Data Alerts (SSRS)

    If you control the process that updates the data, you might schedule the reports at the end of the process.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • I've set up something similar in the past. I set up a subscription which I only wanted to fire if something failed. The way I accomplished this is to create a standard subscription then go into the job and alter the SQL statement from:

    exec ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData='829468de-eb84-4930-b9be-7b4f3b00165e'

    To:

    DECLARE @FieldToCheck = (SELECT FieldToCheck FROM dbo.Fields)

    IF @FieldToCheck > 5 --JA: if we have sent more than 5 more orders than received back, fire off error report

    exec ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData='829468de-eb84-4930-b9be-7b4f3b00165e'

    It worked like a charm.

    You could use something like this, although you would need more infrastructure to get it to work because you need to capture the field at the time of change so you could test against the 'current' value.

    EDIT -----------------------------------

    After checking out data alerts from above, you should definitely try that first before resorting to a hack like what I've suggested. =D

    -------------------------------------------------------------------------------------------------
    My SQL Server Blog

  • Thanks so much I'll play with this.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply