min/max with timeline in between

[acessn]

Hi.

Using CR 9.0 with Microsoft sql server

In my detail section I output shift start and end times like this: (The detail section will be supressed)

Time({VP_SCHEDULE.SHIFTSTARTDATE})
Time({VP_SCHEDULE.SHIFTENDDATE})

In my example, this gives me five records

Start-time...End-time
08:00........16:00
09:00........17:00
14:00........20:00
16:00........22:00
13:00........19:00

What I need to do is to determine the minimum start-time, and the maximum end-time and output this in my group footer.
(I'm grouping on Date)

I've come a long way if I'm able to do this, but in addittion I would like to build some sort of timeframe out of these two values.

Say that minimum start-time is 08:00 and maximum end-time is 22:00, I would like to build a string for output in the footer which goes like:

08..09..10..11..12..13..14..15..16..17..18..19..20..21..22

Any ideas of how to go forward with this? I think I have to solve it this way, because I can't get my Gantt-chart to display proper data in the X-axis.

Thanks.

Regards, Bjorn

 

[MisterMo]

try this

Local StringVar str := "";
Local NumberVar i;
For i := Minimum ({VP_SCHEDULE.SHIFTSTARTDATE})
To Maximum ({VP_SCHEDULE.SHIFTENDDATE}) Do
(
Local NumberVar MyNum := i;

str := str + totext(MyNum) + '.. '
);
str


Mo

 

[MisterMo]

Oops I forgot,

place it in a formula and then place at the foot of your report

Mo

 

[acessn]

Hi.

That doesn't seem to work, because Minimum requires a number, and {VP_SCHEDULE.SHIFTSTARTDATE}is a Date. When trying to convert it with ToNumber(ToText({VP_SCHEDULE.SHIFTSTARTDATE})) it says that Minimum requires a field...

Regards Bjorn

 

[MisterMo]

create a formula for each field

MinHour = Hour({VP_SCHEDULE.SHIFTSTARTDATE})
MaxHour = Hour({VP_SCHEDULE.SHIFTENDDATE})

this will convert it to number then you can use
Min({MinHour}) and max({MaxHour})

Mo

 

[acessn]

Hi Mo, and thanks for your assistance.

This got me a bit closer, as it actually prints out 8 to 22. But it doesn't get it right, as these numbers vary from day to day.

On monday it can be 8-22 while on Tuesday it can be 10-21. This formula prints out 8-22 every day, because they are the min-max values of all. I need them per day.

Thanks!

Regards, Bjorn