Crystal X1 - Scheduling Formula

[keeno]

Hi

We are using Crystal X1 on Oracle Database and also use BO enterprise Server.

We currently have a report which currently requires us to specify a Submit Date in order for it to be run. The report looks for all tickets which have submit date/time of 08:30am the previous day. Except a Monday when it will need all tickets which have a submit date/time of the previous Friday.

Is there any formula which can be written which will accomodate these 2 requirements. if Day = Tues - Fri then submit date = previous day at 08:30am. If day = monday then submit date = previous Friday at 08:30am.

If there is it will enable us to be able to schedule the report to be published without manual intervention

Really appreciate anyones help on this

Many Thanks

David

 

[kskid]

I run my report using the weekly option and specifying Monday through Friday.

My recoird select contains the following:

If Monday, then select everything from Friday 08:30 to Monday 08:29:59). Otherwise, select everything from previous day at 8:30 through today at 08:29:59.


(
if dayofweek(currentdate) = 2 then
{TICKET.ORDER_DATE} in [DATETIMESERIAL(DATE(CurrentDate - 3),TIME(8,30,00)) to_ DATETIMESERIAL(DATE(currentdate - 1),TIME(8,30,0))]
else
{TICKET.ORDER_DATE} IN DATETIMESERIAL(DATE(currentdate - 1),TIME(8:30,0)) TO_ DATETIMESERIAL(DATE(currentdate),TIME(8:30,0))
)

 

[keeno]

Thanks. When i apply this formula:

(
if dayofweek(currentdate) = 2 then
{@Reported Date} IN DATETIMESERIAL(DATE(CurrentDate - 3),TIME(8,30,00)) to_ DATETIMESERIAL(DATE(currentdate - 1),TIME(8,30,0))]
else
{@Reported Date} IN DATETIMESERIAL(DATE(currentdate - 1),TIME(8:30,0)) TO_ DATETIMESERIAL(DATE(currentdate),TIME(8:30,0))
)

I get an error highlighting DATETIMESERIAL saying "A number, curreny amount, boolean, date, time, date-time, or string is expected here"

I have never used this DATETIMESERIAL Function before if you could advis eme what im doing wrong that would be great.

Thanks

David

 

[dgillz]

Do you really need a date time or just a date? I would try this:

if dayofweek(currentdate) in 3 to 7 then //Tues thru Sat
{@Reported Date} IN Dateadd("d",-1,CurrentDate)
else
if dayofweek(currentdate)=1 then //Sunday
{@Reported Date} IN Dateadd("d",-2,CurrentDate)
else
if dayofweek(currentdate)=2 then //Monday
{@Reported Date} IN Dateadd("d",-3,CurrentDate)


Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[keeno]

Time is not necessarily needed no. However....

For fear of annoying you. I get the followng error message when using this Date only formula. the error is focussing on the following part of the formula:

Dateadd("d",-1,CurrentDate)

The error message is "A Date-Time Range is required here"

Thanks

 

[keeno]

Its ok. i am an idiot.

It works fine.

many Thanks for your help

 

[kskid]

Sorry it took so long to reply... been busy with another report

Instead of datetimeserial, it should have been datetime.

My mistake.