Hi,
I have to report on the differences between tables in one database.
Initially , I will be scheduling my report to run on everyday and have a date filter in each report, which will bring the records which are added/updated just one day prior to the schedule date. My Filter will be like
where Date >= 'Sysdate' - 1
Suppose the report runs sucessfully on Jan 3rd, Jan 4th but failed on Jan 5th, the report on Jan 6th should show the data between Jan 4th( the last sucessfull report run date) and Jan 6th , and not between Jan 5th and Jan 6th. The filter in Jan 6 report has bring in the records from Jan 4th. In order to do this, I need to filter on 'Last
Successfull Report Run date' for all my reports rather than just one day prior to the scheduled date. How would I get this 'Last Sucessfull Report Run date' value? My filter condition will be like
Date >= 'Last Sucessfull Report Run Date'
I can look at the 'Run history' in Schedule Management, but this becomes manual.
In addition to the above scheduling, I already created the same report with prompts, so that the user can enter the 'Start Date' and ' End Date' and report diplays all the record inbetween. As this is an urgent issue , I would be gratefull if I get help ASAP.I'm using cognos 8
You would have to setup something in your data source to make a history of the queries/reports.. Then make the report insert (with some SQL) when it executes, in order to be able to query it again later.
It would not be easy, and most likely require some custom SQL and hacking around with the report.
I think the best solution is to run the report for the prior day, and if it fails manually re-run it for a specific date range.
Regards,
Thanks for your suggestions, Mike.
I'm doing a daily schedule of the report and if I see the status of a report as failed, I will insert the date when the report last ran sucessfully prior to the failure as the Start Date for my Prompt. Only disadvantage is that this has to be done manually.
regards
In C8, Event Studio has the ability to retain information about it's last run, but I don't beleive that there is any functionalilty in RN to retain it. I beleive mikegreen is right, the easiest solution is to schedule the report and run a prompted version when necessary.
BTW, in C8, exception testing is incredibly easy, since you can create Outer Joins quite easily. Simply query sample1, then sample2 create a query for each with outer joins to opposing table and Voila! exception testing!