Schedule Report After Data Is Processed

  • RonMexico

    Hall of Fame

    Points: 3219

    I'm new to SSRS and I'm trying to figure out how to have a report delivered once the data is ready. The current environment is set up so that all source data is imported and then a stored procedure executes once that is imported which manipulates the data in a table for the report. The SSRS report should only run once that stored procedure is executed. Therefore, scheduling it for the same time every day will not work. The destination of the report will be on a shared drive. Is this possible?

  • Jonathan AC Roberts

    SSCoach

    Points: 16459

    RonMexico - Monday, March 25, 2019 8:37 AM

    I'm new to SSRS and I'm trying to figure out how to have a report delivered once the data is ready. The current environment is set up so that all source data is imported and then a stored procedure executes once that is imported which manipulates the data in a table for the report. The SSRS report should only run once that stored procedure is executed. Therefore, scheduling it for the same time every day will not work. The destination of the report will be on a shared drive. Is this possible?

    Get the stored procedure to write a log row to a table with a date stamp.
    Schedule the SSRS process to run at regular intervals. The first step in the SSRS process should check that the last row written to the log table was by the stored procedure. If it wasn't then the process should terminate. If it was then the SSRS process should run followed by the inserting a row into the log table with a date stamp.

  • RonMexico

    Hall of Fame

    Points: 3219

    So you are saying if the stored procedure should run at some point between 6 AM - 10 AM then I should schedule the SSRS process to run every 5 minutes (or other interval) during that time looking for the entry in the log table?

  • Michael L John

    One Orange Chip

    Points: 25626

    How about creating a schedule for the report, and disabling it, and when the procedure completes, start the job that generates the report?

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Jonathan AC Roberts

    SSCoach

    Points: 16459

    RonMexico - Monday, March 25, 2019 10:10 AM

    So you are saying if the stored procedure should run at some point between 6 AM - 10 AM then I should schedule the SSRS process to run every 5 minutes (or other interval) during that time looking for the entry in the log table?

    Yes, or use the Michael's method where the sp calls the SSRS report..

  • RonMexico

    Hall of Fame

    Points: 3219

    Thanks. I kind of like the idea of disabling the job and having the proc start the job when it finishes. Like I said, I'm new to SSRS so I'm trying to get ideas on if what I'm thinking is possible and what the best way to go about it if so.

Viewing 6 posts - 1 through 6 (of 6 total)

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