• Replies 4 replies
  • Subscribers 961 subscribers
  • Views 163 views
  • Users 0 members are here
Related

Automated Report Only If It Populates

Jolene Robichaud
$organization

Is there a way to set up a Schedule for a Report so that the Report is triggered/sent ONLY if the Report is populated with information?   Therefore, if I schedule it to run once every day but the majority of days it's blank, I don't want it to actually send on the majority of those days, I want it to send ONLY on the days that it actually populates with information.

Is this possible?

Parents
  • $organization

    Yes, although it depends on how the report is populated. You need to throw something like the below into a stored procedure and then schedule the stored procedure to run a bit before the report is due to run. 

    E.g. this one checks a local view to see if there is a show on tonight before running a show report for Visitor Services:


    CREATE PROCEDURE [dbo].[LRP_Report_Check]
    AS
    BEGIN TRY
    if((select COUNT(ROW_NO) FROM LV_FOH_OVERVIEW)<1)
    BEGIN
    update gooesoft_schedule_queue
    SET status = N'c'
    WHERE schedule_id = 462
    END
    END TRY

    BEGIN CATCH
    PRINT 'Error = ' + CAST(@@ERROR AS VARCHAR(8))

    END CATCH

Reply
  • $organization

    Yes, although it depends on how the report is populated. You need to throw something like the below into a stored procedure and then schedule the stored procedure to run a bit before the report is due to run. 

    E.g. this one checks a local view to see if there is a show on tonight before running a show report for Visitor Services:


    CREATE PROCEDURE [dbo].[LRP_Report_Check]
    AS
    BEGIN TRY
    if((select COUNT(ROW_NO) FROM LV_FOH_OVERVIEW)<1)
    BEGIN
    update gooesoft_schedule_queue
    SET status = N'c'
    WHERE schedule_id = 462
    END
    END TRY

    BEGIN CATCH
    PRINT 'Error = ' + CAST(@@ERROR AS VARCHAR(8))

    END CATCH

Children
No Data