Stumped on a cross-tab

[Snakeroot]

I have a cross-tab in a footer that looks like this:

11 113 12 123 1a 22 M Total
B 852 1,110 98 0 445 1,596 858 1,596
C 1,194 843 517 257 0 9,117 2,561 9,117
N 1,521 622 134 7 1,110 12,748 9,178 12,748
O 824 0 219 0 341 13,423 9,596 13,423
W 1,712 0 169 1,763 71 1,691 1,358 1,763

(I would ultimately like a total of the columns too)

Cross-tab config:
Rows: Command_QTY.Franc
Columns: Dommand_QTY.FullName
Summarized Fields: Max of @Details_Ref_QTY

When I select "Max" for summarized fields, I get the results I want, but the Total is wrong. For example, row 1 should be 4,959. The total I would like for columns would be on the basis that row 1 = 6,103.

How would I go about this? TIA!

 

[Madawc]

Depending on your Crystal version, you could have a crosstab that had both totals and maximum. Then suppress the totals for 'maximum', using field formatting.

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 Windows XP & Crystal 10

 

[Snakeroot]

I did try that also, but the totals were incorrect.

You're right, I did fail to mention what version. I'm using CR XI w/ SQL 2000 backend.

 

[lbass]

The column totals are easily done, but not the row totals--they would require row-specific coding, so the question is how many rows/columns might there be in your actual data? It might be easier to do a manual crosstab.

-LB

 

[Snakeroot]

Thanks for the response LB, not sure how I overlooked it. To answer your question, the sample table shown above is the exact size/dimensions of my output. So, 7 columns (8 if you count the total column) and 5 rows (6 if you count the total row). The amount of rows could eventually grow to around 70 rows or so.

Based on that info, what do you think would be my best course of action?

 

[lbass]

If the number of columns is static, then I would just create a manual crosstab. Since you say the crosstab needs to be in a footer section, you might need to create the crosstab in a subreport, where you insert a group on your row field, and then create formulas like this, one for each column:

//{@11}:
if {table.columnfield} = '11' then
{@Details_Ref_QTY}

//{@113}:
if {table.columnfield} = '113' then
{@Details_Ref_QTY}

etc. Then place the seven formulas in the detail section and insert a maximum on each one at the group level. Drag the groupname into the group footer and suppress the group header and detail section. Then create a formula for the row total:

sum([maximum({@11},{table.groupfield}),maximum({@11},{table.groupfield}), ...maximum({@M},{table.groupfield})])

For the column total, you will have to create a formula for the group footer like this:

whileprintingrecords;
numbervar sum11 := sum11 + maximum({@11},{table.groupfield});
numbervar sum113 := sum113 + maximum({@113},{table.groupfield}); //etc.

Then in the subreport report footer, add one formula per column to display the result:
//{@displaysum11}:
whileprintingrecords;
numbervar sum11;

//{@displaysum113}:
whileprintingrecords;
numbervar sum113;

-LB