date/time parameters to schedule on report.

[BizzyLizzy]

We have a report that runs 3 times a day. At the moment this is being run manually with the staff member inputing date and time parameters required. Eg. If the report is being run at 6.00am today then the parameters would be yesterdays date and the time set at 14.00. This displays all data from 14.00 yesterday up to 6.00am today. This report then gets run again at 9.00 am for all data between 6 and 9, then run again at 14.00 for all data from 9am to 14.00.

The problem we now have is that we are in the process of automating the reports and scheduling to email and I would like the report to pick up the correct data depending on when it is being run (ie. 6am 9am and 2pm). I have tried the following but have so far failed to make it work.

if CurrentDateTime = CDateTime (1900, 01, 01, 06, 00, 00) then DateAdd ('h',-16,CurrentDateTime) else
if CurrentDateTime = CDateTime (1900, 01, 01, 09, 00, 00) then DateAdd ('h',-3,CurrentDateTime) else
if CurrentDateTime = CDateTime (1900, 01, 01, 14, 00, 00) then DateAdd ('h',-5,CurrentDateTime)


I am probably being completely thick here but I just cant seem to get my head around it.

I hope somebody can help as this is driving me nuts!!

We are running CR 8.5 against MS SQL database.

Lizzy

 

[lbass]

If this is a record select formula, I think it should look something like this:

if time(CurrentDateTime) = Time (06, 00, 00) then {table.datetime} in DateAdd ('h',-16, CurrentDateTime) to Currentdatetime else
if time(CurrentDateTime) = Time (09, 00, 00) then {table.datetime} in DateAdd ('h',-3, CurrentDateTime) to currentdatetime else
if time(CurrentDateTime) = Time(14, 00, 00) then {table.datetime} in DateAdd ('h',-5, CurrentDateTime) to currentdatetime

I'm not sure whether you need to factor in report processing time here--there is only one second here when the time = time(06,00,00).

-LB

 

[BizzyLizzy]

thanks for that Ibass. That looks like it should work. You are probably right that I need to factor in some processing time - any suggestions as to how I go about this?

As you can probably gather I am fairly new at this and writing formulae allways seems to give me a headache!!

Lizzy

 

[lbass]

This is a technical question I can't answer--I'm not sure whether the record select would act like a snapshot in time, so that when it was exactly 6:00 it would apply the criteria and the processing time is irrelevant, or whether it would start to process and then error because the currentdatetime changed during the processing--but I bet there are people here who DO know. I'm just not one of them. If nobody jumps in, I would just try the above and see. I'm guessing, in retrospect, that the processing time is irrelevant--since there is a difference between data time and print time.

The other factor is that the above formula assumes that the report is run exactly at the time indicated, which I assume you are ensuring through your automation process...

-LB

 

[BizzyLizzy]

Yeah the scheduling package that we are trialing will run the report at the times I tell it to! I have tried your formula and it works just great!! I am now attempting (!!!) to factor in some time delays etc, but as I said earlier it does my head in.

Thanks very much for your help. It has certainly got me a lot further down the track than I was.

Lizzy