Bogus Sums on a Cross Tab

[tmozer]

Using Crystal Reports 8.5 with Oracle tables.....

I have a series of double cross tab reports that essentially do the same thing for different time periods. They count assignments and cases for four offices and four units (4 X 4). The only different between assignments and cases is that there can only be one active case per unit/office but there can be more than one assignment. Therefore the difference in the two totals is that assignments are summed with a count and cases are summed with a distinct count (of a field called {case key}). The style of have chosen is that the row grand totals are to the right and the column grand totals are at the top. Column totals are always mathimatically correct for both sets of numbers. Row totals are always are correct for assignments. But cases totaled across the rows are usually not correct!

Obviously CR is not just adding up the rows. It must be doing a separate distinct count to get the totals, and doing something different than the count in each row-cell......

What should I look for to correct this??

 

[lbass]

It looks like there must be some cases that cut across assignments, so that a distinctcount of all cases across assignments is some number lower than adding the distinctcount of individual cells. I think the only way you can deal with this is by doing a manual crosstab where your column total is the sum of the individual cell results accumulated with a variable.

-LB

 

[tmozer]

Yes, I believe there are cases which span offices (the same case/assignment exists in more then one location).

So now comes a problem I have run into before. How do I save the results of my cross tab sums so that I can manually add them?? An example would be great......

 

[lbass]

As far as I know you can't use the results of crosstabs in other calculations. If you didn't want to create the entire crosstab as a manual crosstab, you could do a sort of hybrid, where you suppressed the column totals and calculated the correct figures in the body of your report (even if entirely suppressed). This would require that your crosstab be in the report footer (or a group footer), however.

Before I give you further help though, could you please give me your crosstab layout? Are offices the rows and units the columns? When you say cases totaled across rows are not correct, do you mean that the column total is not correct? A sample layout would help.

Where are the crosstabs located? Is there some sort of date group?

-LB

 

[tmozer]

Wish there was a way to either cut and paste images or attach files (unless there is and I have not figured out how.....)!

Anyway:

Cumulative Weekly Cases/Assignments Received

CRIM DNA DRUG TOX TOTAL

Total 4 2 99 32
4 2 99 36 141

C 0 2 15 8
0 2 15 8 29

E 0 0 4 4
0 0 4 4 8

N 0 0 29 14
0 0 29 18 47

S 0 0 51 6
0 0 51 6 57

C, E, N and S are the offices. The units are at the top. The first number is cases. I have suppressed the left case totals since they were not always summing the rows.

 

[lbass]

In your main report, let's say you have an outer group (Group #1) on time periods, and that the crosstabs are in the group footer (if they are in the group header, move them to the group footer). Next insert a group on office (Group#2) and then another group on unit (Group #3). Create formulas like the following (this example is for three offices, C, E, N):

//{@reset} to be placed in the group #1 header:
whileprintingrecords;
numbervar C := 0;
numbervar E := 0;
numbervar N := 0;

//{@accumC} to be placed in the Group #3 header or footer:
whileprintingrecords;
numbervar C;
if {table.office} = "C" then
C := C + distinctcount({table.casekey},{table.unit});

//{@accumE} to be placed in the Group #3 header or footer:
whileprintingrecords;
numbervar E;
if {table.office} = "E" then
E := E + distinctcount({table.casekey},{table.unit});

//{@accumN} to be placed in the Group #3 header or footer:
whileprintingrecords;
numbervar N;
if {table.office} = "N" then
N := N + distinctcount({table.casekey},{table.unit});

//{@displC} to be placed in the group #1 footer:
whileprintingrecords;
numbervar C;

//{@displE} to be placed in the group #1 footer:
whileprintingrecords;
numbervar E;

//{@displN} to be placed in the group #1 footer:
whileprintingrecords;
numbervar N;

If the crosstab will always have the same number of units, you could place the last three formulas in their appropriate row in the crosstab (after suppressing the incorrect figures). If the column number can vary, I would set these totals off to the side as if they were from a separate crosstab.

-LB