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

[former_member1197068]

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:
whileprintingrecords;
numbervar sumvalue := sum({value},{account});
sumvalue

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 ;
maximum(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

[abhilash_kumar · Accepted solution]

Hi,

1. Modify the formula on GF1 to:

WhilePrintingRecords;
NumberVar array amt;
Numbervar max_sector;
If maximum(amt) > max_sector then
 max_sector := maximum(amt);
maximum(amt);

2. Create a formula to display the max and place this on Report Footer:

WhilePrintingRecords ;
Numbervar max_sector;
max_sector;

-Abhilash

[former_member1197068 · Accepted solution]

This worked perfect, thanks Abhilash!

[ido_millet]

Declare a global variable Reports_Sector_Max and assign to it the Sector_Account_Max value if it's larger than the current value in Reports_Sector_Max.

You could use the same logic to simplify how you establish the Sector_Account_Max using a single number variable instead of an array.

In any case, you can get rid of the sumvalue formula.