"I've tried and tried but nothing comes." - B.C.
I want to find out this about LBI reports: - What ran and did they run on time? - How long did the reports that ran take to run?
I can get information on what ran from ERS_REPORTS. The most important fields are: REPORTID, INSTANCEID, CREATED, REPORTNAME. This is cumulative metadata on when each report ran, equivalent to the History for reports in LBI.
I can get information on the schedules from the Quartz scheduler. Using a formula to adapt times from UNIX datetime stamp format, I can see when reports are scheduled and when they ran. The main file I query is EJS_TRIGGERS and the most important fields are TRIGGER_NAME, TRIGGER_GROUP, JOB_NAME, JOB_GROUP, DESCRIPTION, NEXT_FIRE_TIME, PREV_FIRE_TIME, and START_TIME. This metadata is the equivalent of looking at the Manage Schedules screen in LBI.
The problem is linking the two data sets together. The link should be EJS_TRIGGERS.JOB_NAME (string) to ERS_REPORTS.REPORTID (number). And, I can't get it to work. Crystal just refuses to tie the two together. I've tried using DESCRIPTION to tie them together, and I got some results, but it just doesn't seem to work properly. It doesn't tie in everything, just a percentage of the reports. Not helpful.
So, if you've read this far, you're definitely a geek like me. And maybe you can help me out -- How can I link the two fields together, one with a string that embeds a number, the other that's a true number? Any help would be appreciated. (Also, you can use this information to create your own report that will show you how LBI reports are doing, which can help you track LBI performance.
select * from ers_reports r left outer join ejs_triggers t on r.reportid = t.job_name
When I use the above sql, I return all of the report instances, as well as any data from ejs_triggers (if it exists).