Running a scheduled report in Back Date

[former_member593850]

Hi All,

I have scheduled a report in CMC, which runs every day for systemdate -1 Day (Yesterday).
Now what is happening that ETL load is getting delayed many times, so we need to run all reports manually for 5-6 day, resulting into lot of rework.

What I was looking for is some setup where we can use single instance to pass a value (e.g. 25May - 30May) and it will generate all Daily reports for this 6Days using single instance.
M not sure where to start with.Scheduler is not helping to rerun multiple reports.

[DellSC · Accepted solution]

The simple way to do this would be to make a copy of the report that contains a start date and end date parameter instead of the systemdate -1 condition.

Something that could be done long-term to make sure the report doesn't run unless the ETL load is complete would be to create a file event. In BOBJ, a file event registers the presence of a specific file after it has been absent for at least one cycle of the event server - it doesn't look at the contents of the file or its timestamp. Your ETL job would require a slight change to delete a file when it starts and then create the blank file when it finishes. The report schedule would be updated to to run at a specific time but also to depend on the file event. That way the schedule won't start until after the file event occurs, even if it reaches its scheduled time.

-Dell

[former_member593850 · Accepted solution]

Thanks Dell,
But creating a separate report is not a solution I am looking for, as this report are still in evolving phase and as the number of reports are higher it is practically impossible to keep a copy everytime.

and for ETL log file, limitation is there are multiple set of ETL jobs, and report may fetch data from different ETL jobs, so that is also quite unfeasible.

What I was thinking of if we can have some coding in other technology(may be Script) where we can invoke the report execution by passing the date for prompt, can meet our requirement I guess.

[DellSC · Accepted solution]

Long term the scheduling based on multiple events - one for each required ETL load - is still a possibility.

You'll still have to update the report, but here's a possibility for it:

1. Add a parameter for "Date Type". Possible options are "Yesterday" and "Selected Dates". You can do this in the universe or in the report itself, depending on which is best for you. If this same logic will be needed for multiple reports, it might be better to put it in the universe.

2. Add optional parameters for the start and end dates.

3. The date logic in the report, or in a universe filter, will look something like this:

(([Date Type] = "Yesterday" AND [Date Dimension] = systemdate -1) OR
([Date Type] = "Selected Dates" AND [Date Dimension] >= [Start Date] AND [Date Dimension] <= [End Date]))

Your "standard" schedule will have the date type set to "Yesterday".

You could then write a program using the RESTful Web Services SDK to schedule the report for specific dates. The beauty of the RESTful Web Services is that they are not language-specific. You can use any programming language you want or, alternatively, you could use a third-party tool like SoapUI to log in and schedule the report. All of the calls for this SDK are HTTP GET, POST, etc. and the information about the schedule is configured in either XML or JSON, so it's pretty easy to work with.

-Dell