Sum groups without grouping

[catbert]

Hi,
Can anyone help with this:

I have a report looking at hours worked to calcuate pay (so quite important to get right). Rates are paid at one level for the first 37 hours, and a different rate thereafter.

I have a report which calculates per person these figures. However I then need to sum these hours grouped by location, and workers can move between different locations through the week - so the cumulative totals have to be calculated based on grouping by worker and ordered by date. For a simplistic example see below:
Date[tab]Worker[tab]Location[tab]Hours[tab]Cum[tab]>37[tab]>37
Mon[tab][tab]1[tab] [tab]1[tab][tab][tab][tab]8[tab] 8[tab] 8[tab] 0
Tue[tab][tab]1[tab] [tab]2[tab][tab][tab][tab]8[tab] 16[tab] 8[tab] 0
Wed[tab][tab]1[tab] [tab]2[tab][tab][tab][tab]8[tab] 24[tab] 8[tab] 0
Thu[tab][tab]1[tab] [tab]2[tab][tab][tab][tab]8[tab] 32[tab] 8[tab] 0
Fri[tab][tab]1[tab] [tab]2[tab][tab][tab][tab]5[tab] 37[tab] 5[tab] 0
Sat[tab][tab]1[tab] [tab]1[tab][tab][tab][tab]8[tab] 4[tab] 0[tab] 8
Sun[tab][tab]1[tab] [tab]1[tab][tab][tab][tab]8[tab] 6[tab] 0[tab] 8


As you can see if I grouped location, all the hours for locaiton would be counted as being below the 37 hour threshold. When in fact 8 hours are below and 16 are above.

I can calulate all these figures, both cumulative for the worker, and seperately for each day dividing hours into < and > 37. But how do I then add these back up by location. I have hundreds of workers and location so I can't use explicit "sum if id = 1".

At the moment I export to excel, sort by worker then location and add subtotals - works perfectly but I would like a complete solution in crystal if possible. Any suggestions Gratefullyreceived.
Thanks in advance, CB

 

[Madawc]

Looks like a Crosstab - you find Crosstab on the Insert menu, and it guides you through the details

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

 

[catbert]

HI, thanks for the quick response,

i tried a cross tab, but none of the calculated fields are avaiable to add to the crosstab. I am a little hazy on where to go next. Do I have to create a variable for each field to make them avaliable - not entirely sure how to go about it.
Each of the calulations I have is already based on the cumulative figure which is a variable:

whileprintingrecords;
numbervar CumulHoursTot :=
CumulHoursTot + {@ShiftHours}

Cheers
CB

 

[Madawc]

I think a crosstab will not be able to use values that are themselves summaries. You can however get ground that, doing something like

if {your.code} = "T"
then 1
else 0

You add this at detail-line level. You can then use it for summaries, and probably also crosstabs.

The use of Crystal's automated totals is outlined at FAQ767-6524. This includes running totals, which are another method but a lot more work.

PS. It helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever. Methods sometimes change between versions, and higher versions have extra options.

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