Schedule Daily Sales reports only after Performances?

We do something similar. We want to be sure that some exception reports only go out when there is an exception. Receiving an empty exception report every day, defeats the purpose of it. We want the users to be sure that they only receive it when there is a real exception. We have a schedule task that runs every night and checks for exceptions and if it finds one it shedules the correspondig report. Here is a sample of the code that we use slightly adapted to your need.

 

DECLARE @request_id INT,

  @schedule_id INT

 

--If there is a performance today

IF EXISTS (SELECT 1 FROM T_PERF WHERE CONVERT(VARCHAR, perf_dt, 101) = CONVERT(VARCHAR, GETDATE(), 101))

BEGIN

  --Get next id for report request

  EXEC @request_id = dbo.ap_get_nextid_function @type = 'RR'

 

  --Create report request

  INSERT INTO gooesoft_request (id , report_id, user_id, type, request_date_time, printer_name, public_flag, ug_id, email_recipients)

    VALUES (@request_id, 'id_of_your_report', 'uid_of_who''s_scheduling_the_report', 'h', GETDATE(), 'Email-PDF', 'Y', 'User''s_group_id', 'someone@somewhere.com')

 

  --Insert one record for each parameter. Be sure to match the parameters' id correctly

  INSERT INTO gooesoft_request_parameter (request_id, report_id, parameter_id, value, query_nonquery_flag)

    VALUES (@request_id, 'id_of_your_report', 1, 'parameter_value', 'N' )

  INSERT INTO gooesoft_request_parameter (request_id, report_id, parameter_id, value, query_nonquery_flag)

    VALUES (@request_id, 'id_of_your_report', 2, 'parameter_value', 'N' )

 

  SELECT @schedule_id = MAX(id) + 1

  FROM gooesoft_report_schedule

 

  --Delete previous schedule and re-schedule it

  DELETE FROM gooesoft_report_schedule

  WHERE name = 'Name of the report schedule'

 

  INSERT INTO gooesoft_report_schedule (id, name, request_id, type, interval, day_of_week, day_week_number, start_date, end_date, start_time, end_time, deactivate_flag )

    VALUES (@schedule_id, 'Name of the report schedule', @request_id, 'o', 0, 0, 0, CONVERT(VARCHAR, GETDATE(), 101), CONVERT(VARCHAR, DATEADD(dd, 90, GETDATE()), 101), DATEADD(mi, 2, GETDATE()), '1900-01-01 00:00:00.000', 'a' )

 

END

 

 

Fernando Margueirat
Business Analyst
The National Ballet of Canada
470 Queens Quay West
Toronto, Ontario
M5V 3K4
P: 416 345 9686 x453
F: 416 345 8323

 

 

 

 

 

Has anyone had experience with scheduling a daily report that generates Box Office reports at the Production level, but only if there are Performances in that Production for the day?

The scenario is that we have various coordinators that require Box Office reports for their Productions at the end of any day that has their Performances.

I know of Shared reports that can present sales information at the Production level, but not reports that are smart enough to only run if there have been performances inside that Production.

As these reports will likely be emailed, I am trying to avoid having empty reports emailed on days when there were no Performances for the relevant Production.

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Shared Reports Forum. You may reply to this message to post to the forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!