Technical Articles
Using Schedule Events in Scheduling Reports in BOBJ (No File Event)
Problem Statement:
As part of insurance reporting, there are few claims number related reports which are created out of the transaction tables for current day (today’s date). These specific kinds of claims are inserted into transaction tables between 9:30 AM CST to 9:45 AM CST from front end application.
From the information standpoint, the report has complex table level filters which could not be replicated at the query level. There were multiple such reports which had the similar logic and scheduled at different time intervals. Under such scenarios, if the report is scheduled, it will send empty reports to the business through email notification if there are no claim numbers in the system for current day.
The business requirement was to send email notification with attachment only if the data is available in the report. Although publication can be used to achieve this functionality, but it could not be used in this scenario because publication does not check for the report level filters and sends email notification based on report query refresh. In addition, we did not want to create the dependency on database team to send a file whenever the claim number data is available in transaction table for current day which could be used as a file based trigger in the report schedule.
Solution Approach:
Login to Business Objects CMC tool and create a Success based Schedule Event.
- Create a Custom calendar with dates selected on which the reports should run.
- Create a Schedule Event in CMC named “Trigger_Success_Event”.
Report Details and Scheduling:
- Create 1st report schedule at 9:45 AM CST which will generate the report output to a shared drive in .xlsx format and also trigger the Event “Trigger_Success_Event” after the schedule runs successfully.
- Create the 2nd report (copy of the first report with same conditions) and use the excel output generated at Step 1 having the claim number as an input to the 2nd data provider as an additional filter ( claim number in list < claim_numbers from excel output>). The excel output of 1st report will be replaced regularly based on run date of schedule on the share drive. Use the 3rd data provider in this report with a 1/count(claim_number) measure object with the query filter condition ( claim_number in list < claim_numbers from excel output>). The report will fail if there is no data available in the report due to Divide by 0 error. This will help avoid sending the blank reports to the business users if there is no data available in the report.
- Schedule the 2nd report at 9:40 AM CST and it will be waiting for the event “Trigger_Success_Event” generated by the 1st report schedule. Also fill the appropriate end user email details in the recipient section with the 2nd report as attachment. Although this report is scheduled at 9:40 AM CST, it will be dependent on the success instance of the 1st report and will be waiting for the event “Trigger_Success_Event” to start running and the 2nd report instance will get completed only after 9:45 AM CST.
Conclusion:
In this way, we can avoid dependency on upstream teams for sending file events and business logic can be handled at WebI level by creating the above logic. There could be multiple ways to achieve this functionality, but the solution explained above is one of them.