Post reply

Schedule Report After Data Is Processed

  • March 25, 2019 at 8:37 am

    #2031609

    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?

  • March 25, 2019 at 9:15 am

    #2031679

    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.

  • March 25, 2019 at 10:10 am

    #2031685

    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?

  • March 25, 2019 at 10:12 am

    #2031686

    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/

  • March 25, 2019 at 10:43 am

    #2031694

    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..

  • March 25, 2019 at 11:37 am

    #2031706

    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.

  • July 15, 2019 at 1:47 pm

    #3663022

    I'm getting back to this topic so I have a couple questions. The goal is to have only one subscription that can fire every report. Currently I see two options:

    1. I found code to create a proc that gets the report path and name passed to it and then will run the subscription using that report name and path. Each proc that I need to be complete before the report can run would then have this new code added to the end so that the report generates when data is ready and not on a schedule. This seems most feasible with the downside of altering procs to contain the execution code at the end.
    2. A method that I would rather use, but I don't know if it is possible, is to have a data driven subscription that looks at a log table and then executes the report only when the log table has an entry for the proc being complete. The subscription itself would be on a schedule and would run all reports that were ready at that particular time. Is this a possibility?

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

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