Cross Tab Report - Grand Total

[Jonathan212]

I’ve got myself in a pickle. I have a report that derives total surgeon hours used per day (and total distinct cases) in the operating room. I am grouping by “surgeonname” and “dayoftheweek”.

I am happy to report that output in section GF2 (by day of the week, e.g., Monday, Tuesday ….) for each surgeon is correct. That is, total hours used are correct. In addition, output in section GF1 (a summary of hours used for all days of the week, e.g., Monday plus Tuesday, etc.…) appears to be correct.

My problem manifests itself in the cross-tab.
I have row1: surgeonname
I have column1: Dayoftheweek
Summaries are:
Distinct count of case number
Max of Total Hours per day

Formula for “totalhoursper day”is:
Sum ({@RoomTime3}, {@weekdaydescription})+{@10min}

Roomtime3 is:
({@PtoutofRoom} - {@PtinRoom})/60

I first did a sum of total hours per day – but I got results I did not expect.
When I used max total hours per day – I got the results I expected for every day of the week. However, the grand total was incorrect (in the cross tab). And that is where I am stumped.

Existing (and incorrect) Output:
Mon Tue Wed Thu Fri Total
Jonathan 114.00 70.00 136.00 1,439.00 0.00 1,439.00

How might I change the total of 1439 so that is 1759. I realize this is the max value, but I don’t want that value in the grand total. I am using CR10.

Thanks in advance. Jonathan

 

[lbass]

If you change the summary to "sum" is the grand total correct (even tho the others are now incorrect)?

-LB

 

[CoSpringsGuy]

I am certain someone will have a better solution for this but here is a way I have solved that in the past.

NOTE: This will only work if your total hours is an absolutely unique number.

create a formula
if total hours = maximum(total hours,@group)
then total hours
else 0

use that formula in the crosstab with a sum function rather than a max and the total will be what you are looking for.

With your scenario I am not sure this will work because I would guess there will be the case where there may be more than one total hours that are the same. Maybe that idea will spark some creative thought?

_____________________________________
Crystal Reports XI Developer Version

 

[Jonathan212]

Thanks LB and CoSprings for reviewing this.
LB: that was my thought as well. When I do a sum – it does a grand sum all the weekday totals correctly, but then the weekday totals become incorrect. In my example above, it changed the numbers to 228 (for Monday), 70 for (Tuesday), 272 (for Wednesday), 50365 (for Thursday) and O (for Friday). Notice the doubling of values on Monday and Wednesday. Tuesday stayed the same. I will investigate the data on Thursday and review the intgrity of that data.

I suppose it could be an error in data input into our system (e.g., a huge time error input for a record) or it might be one of my formulas. I have included some of the formulas I am using in case you see something really peculiar.

Totalperday
Sum ({@RoomTime3}, {@weekdaydescription})+{@10min}

Roomtime3
({@PtoutofRoom} - {@PtinRoom})/60

Usedplusturnover
Sum ({@RoomTime3}, {pcmSurgeons.name})+{@surgeonalldaytotal}

10min
DistinctCount ({pcmCase.caseNumber}, {@weekdaydescription})*10

Ptoutofroom
(time({pcmOpTime.timePatientLeaveRoom}))

surgeonalldaytotal
DistinctCount ({pcmCase.caseNumber}, {pcmSurgeons.name})*10

 

[Jonathan212]

LB and COSpringsGuy,
I've got it and thanks once again for your help.
I was using different formulas in different Group sections and I am pretty sure that was the source of my errors.

I was using a formula "totalperday" to arrive at a weekday total and "usedplusturnover" to arrive at grand total (for all days).

I suppressed some fields in the cross tab and it now accomplisehs what I need.

Please accept my thanks for the time and effort you always seem to be able to invest in helping.

Jonathan

 

[lbass]

No, I wasn't suggesting there was an error. If the grand total is correct using a sum, then create two crosstabs, one that uses the max, and one that uses the sum and has no columns. Then overlay one crosstab over the other, suppressing unwanted labels and grid lines.

-LB

 

[Jonathan212]

Thanks LB.
I realize this may not be the most appropriate space for an inquiry as to how I can make a snail mail donation to this Board. I understant that I can do a donation via Paypal - but I would rather send a check directly. I find the Board extremely valuable and appreciate your help, as well as that of CoSpringsGuy, SynapseVampire and Madawac to name a few.

 

[lbass]

Jonathan,

If you'd like to donate, click on the 'donate' link at the bottom of the page. If you scroll down, there is a mailing address you can use. That's thoughtful of you, and I'm sure it will be appreciated.

-LB

 

[Jonathan212]

Noted.