report designing and scheduling

[techytechy]

Hi guyz,

whats the best way to report designing and scheduling process, so that the scheduled reports if due to some reason dont run and hence need to be rerun for the dates that were missed? We want to rerun them with minimal manual changes needed. We dont want to include any date parameters but only date selection criteria.

Environment: crystal reports x1, crystal enterprise x1, sql server

Thanks

 

[MJRBIM]

By "only date selection criteria" - I assume you mean using features like LastFullDay, LastFullWeek in the RECORD SELECTION formulas, correct..?

That will only work if you can confirm that your instances run correctly when they are scheduled, and fix those errors in time for the formulas to still work correctly.

EXAMPLE - You use the "LastFullDay" formula for "Retail Store Sales" reports scheduled Daily (Monday to Sunday) at 6 AM. However,the MS-SQL DB is down at 6 AM on Sunday morning for patches, so ALL you scheduled instances FAIL. You need to have someone who is able to monitor and reschedule your failed instances to complete before midnight on Sunday - or you will not be able to generate a valid instance for the SATURDAY sales in the HISTORY of those "Daily" report objects.

If you don't notice the FAILURE of the Sunday 6 AM instances until Monday morning, the "LastFullDay" formula will not return Saturday data - because Saturday is not the "LastFullDay" at the run-time of the instance.

We get around this problem by using e-mail alerts on ALL Instance failures, and the APOS Instance Manager tools to "Re-Schedule with Edit" when Failures happen...but it still needs a tech to remote connect from home on Sunday morning to use those tools.

 

[techytechy]

Thanks @MJRBIM for the response.

Yes, we have the same situation that you mentioned. We are using date formulas such as 'currentdate -1', 'currentdate -3' etc in the report.

So, from your suggession I am concluding that if we want to get the data for any other past dates, we need get the reports from the enterprise into the designer then change the date criteria in each report in crystal designer then save them back in Enterprise and then run them again?
Then later change them back to old select criteria?
If we have many reports then this is lot of manual work.

With APOS Instance Manager tools dont you need to change each reports criteria in designer?

Environment: crystal reports x1, crystal enterprise x1, sql server


Thanks

 

[MJRBIM]

I would advise against using (CurrentDate) or (Today) in any of your formulas if you are looking at HISTORICAL instances in the RPT format.

Always use (DataDate) - so that the formulas don't get recalculated at view-time for the instances.

If you are using (DataDate-1) as a formula then YES you need to change the RPTs if you "miss a day".

Our solution is constant monitoring/notification for INSTANCE failures so that we are alerted if anything fails. We then work to fix the issue, and reschedule the failed instances using InstanceManager before the time elapses where we would "miss a day".

In one situation, that happend on January 1st a few years back - we couldn't get the DBAs to fix the issue until January 3rd. Our solution (after-hours) was to change the DATE on our CE cluster back to Jan 1st - run all the instances as Jan 1, then reset the DATE on our CE cluster to Jan 3rd.

I would suggest doing that without having a good, long think about what impacts that might have on your BOE servers and dependent applications. We did this back in CE-10.

 

[techytechy]

Thanks @MJRBIM

To check your comment on using DATADATE instead of CURRENTDATE, I inserted {DATADATE}{DATATIME} and {CURRENTDATE}{CURRENTTIME} and {PRINTDATE}{PRINTTIME} side byside in the report footer. When I refresh the report all of the above showed the same current time of the system in the preview mode. Then I went to desiner mode and after waiting for a while without refreshing went back to preview mode to check if any of the above datetimes have changed but still all the above remained at previously shown time.

So, in what cases one date would differ from the other in the above three date fields?

Thanks

 

[MJRBIM]

If you are looking at a SCHEDULED instance from HISTORY that was run at some point in the past...

{CURRENTDATE} = date at time of viewing
{DATADATE} = data the sql ran against the DB
{PRINTDATE} = data the RPT file was sent to the printer

 

[techytechy]

Thanks for the info.

But that also means that if I use datadate in the selection formulas and would like to run some reports by changing the CE system date to some random date then this will not affect the selection as it has datadate not the currentdate?

Thanks,

 

[MJRBIM]

DataDate is the DATE on the BOBJ Servers when the database query was run, so it will effect the selection criteria if you change the DATE on the BOBJ Servers.

 

[techytechy]

Oh sorry it was other way round. I tested by putting Datadate and currentdate in the report and changed the system date to Jan 1 2008. Datadate field showed Jan 1 2008 while currentdate field still showed todays date-Mar 16,2008.

And may I know With APOS Instance Manager tools dont you need to change each reports criteria in designer?
How is that tool helpful?

Thanks

 

[MJRBIM]

As I said in an earlier part of the thread, we use the APOS Instance Manager to monitor and reschedule FAILED instances before the time elapses where we would "miss a day".

EXAMPLE - If 1,000 instances FAIL on Sunday morning at 2 AM, we use to APOS tool to find all the Instances - and RESCHEDULE them so that they are all completed before midinight on the Sunday.

We don't change the RPTs (which would be a huge manual job) - we just monitor-instances all the time.

 

[kskid]

With Business Objects Enterprise XI under the Schedule tab, I have set up e-mail notification for failed jobs. I address and fix them when I check my e-mail in the morning or during the day.

-lw

 

[jpeters01]

We have two versions of each report on the Enterprise Server, the Auto Reports which run everyday, and a Manual version that we use when the Scheduled report fails. We can use the date parameter to pull the reports for any
date(s) we need.

 

[techytechy]

Thanks for the comments.

@jpeters01, two versions is a good idea. But as I said earlier in the post that we dont want to use date parameters. Dates ranges are embedded in the report selection formula.

So, the solution proposed by MJRBIM to change the CE system date seems to save time and effort if we have to run the reports for any different dates that we didnot set in the report selection criteria. For eg in the case, if we want the data for "datadate-7" instead of date "datadate-1" currently set up in the selection criteria of the report.

Thanks

 

[techytechy]

If the report is based on sql command I will have to use getdate()[as I am using sql server] in the WHERE clause instead of DATADATE in selection criteria. In this case would changing the CE system date effects the date criteria in the where clause of sql command?

Thanks!