Skip to Content
Oct 04, 2019 at 08:51 PM

Crystal Reports: Having trouble creating a Max of Array variable in my Report footer


First time poster, please be gentle :)

I have a data set like below:

Sector Account Value
AAAAA 111111 2.02
AAAAA 111111 1.01
AAAAA 111111 0.46
AAAAA 222222 3.19
AAAAA 222222 5.31

I need to group at the account level and sum the value's together to create the account value, but also group at the Sector level and provide the Max account value for that sector. Then in the report footer, provide the Max account value overall.

Thought the best way to do this was through formulas while using Arrays and Variable. Here's what i did and it's got me to be able to get the Max at the sector level, but still trying to figure out the formula for the max overall

Keep in mind Group 1 is Sector and Group 2 is Account

Created this formula and displaying in the GH1:
WhilePrintingRecords ;
If NOT InRepeatedGroupHeader then
Numbervar Array amt ;
NumberVar c := DistinctCount({Account},{Sector}) ;
Redim amt[c];
NumberVar i := 1 ;
else ''

Created this formula and displaying in GF2 to give the account value for each account:
numbervar sumvalue := sum({value},{account});

Created this formula and put it on the far right side of GF2 away from display:
WhilePrintingRecords ;
NumberVar array amt ;
NumberVar i; amt[i] := ( Sum ({value}, {account}) ) ;
i := i + 1 ;

Created this formula and displaying in GF1 to give the max account value for each Sector:
WhilePrintingRecords ;
NumberVar array amt ;

With this set up, The report is summing 3.49 for account 1 and 8.50 for account 2 and finding the max out of the 2 and putting it in GF1 8.50 which is exactly what i want:

PH Sector Account Value
D AAAAA 111111 2.02
D AAAAA 111111 1.01
D AAAAA 111111 0.46
GF2 AAAAA 111111 3.49
D AAAAA 222222 3.19
D AAAAA 222222 5.31
GF2 AAAAA 222222 8.50
GF1 AAAAA 8.50
RF ?

How do I get the Max overall account value in the Report Footer though? For instance, say Sector A has a max value of 8.50, sector B has a max value or 10.31, and Sector C has a max value of 6.00. RF's max value should be 10.31. Any help would be much appreciated!

Thank you