Can I Include an hour / minute total in a Cross Tab 1

[Sange]

Hi,

I am using Crystal Reports v9 with an oracle database.

I am currently developing a report which will calculate the total amount of community work hours/minutes performed at each community work location.

The report is to be broken down by order type and location and also by month over a financial year.

Ideally the order type and location would make up the rows of the cross tab and the months would be in columns.

To calculate the hours / minutes worked. I first used a datediff (start date / end date) to calculate the seconds worked for each record and then used another formula to calculate this amount into hours and minutes. If I sum this formula in a standard report, it works fine, however I cannot select this same formula within a cross tab.

Unfortunately the report has to be the exact total of hours and minutes worked as not everybody works to the hour.

Is there anyway I can achieve this in a crosstab?

Thank you in advance.

 

[Madawc]

Why is this in Enterprise rather than one of the report forums?

But I'll try to answer. If you start from minutes and calculate an hour-minute total, it becomes a 'string', just as if it was someone's name. That can't be summed.

You can sum and convert minutes for a formula, as you have been doing. I don't think that is possible for a crosstab.

I think you may need a subreport in the report footer which groups in the same way you wanted the crosstab. The total for each group can be converted into hours and minutes.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[lbass]

Please show the content of your formulas.

-LB

 

[Sange]

Thank you for your replies. Not quite sure how I ended up in Enterprise, I did intend to post this in Crystal Reports Formulas. I'm not sure if I can move into the correct one now.

lbass my first formula ({@datediff_sec}) calculates the difference in seconds between the community work start date/time and end date/time ie:

DateDiff("s", {startdatetime, {enddatetime)

I then created a second formula which I placed at the group level. This formula summed the datediff and converted it to hours and minutes ie:

whileprintingrecords;
numbervar dur:= sum({@datediff_sec}, {AGENCY_CODES.AGENCY_DESCRIPTION});
numbervar hrs;
numbervar min;
stringvar hhmm;

hrs:= trutruncate(truncate(dur/60)/60);
ncate(truncate(dur/60),60);

hhmm := totext(hrs, "00") + ":" + totext(min, "00");
hhmm

Is there anyway of getting this information into a crosstab?

 

[lbass]

Use sum of your original datediff formula as the summary in your crosstab and use agency description as the row. Then in preview mode, select the inner cell->right click->format field->display string->x+2 and enter:

whileprintingrecords;
numbervar dur:= currentfieldvalue;
numbervar hrs;
numbervar min;
stringvar hhmm;
hrs := Truncate(Truncate(dur/60)/60);
min := Remainder(Truncate(dur/60),60);
hhmm := totext(hrs, "00") + ":" + totext(min, "00");
hhmm;

-LB

 

[Sange]

Hi LBass,

You have done it again! Your suggestion works beautifully and I can honestly say I don't think I would have been able to work this one out myself. Thank you so much for all the time and effort you have just saved me. Most appreciated.

Sange