CrystalXI- How do you Get the Max Value of a Formula in a Group Foote 1

[minifelt]

Hello,
My report has 2 groups.

I have a formula in Group Footer 2 that takes the total number of lines for each Group in Group 2. The formula is "lineCount"

I would like to add a formula in Group Footer 1 that will take the maximum value of "LineCount". The result I get is the last "lineCount" value in Group 2. Is there a way to set a variable that will remember the largest line count?

Thank you much.

 

[pontupo]

There may be a simpler way to do this, but you could create a formula like this:

@maxLineCount (placed in the group 2 footer)
WhilePrintingRecords;
NumberVar maxCount;

IF (@lineCount > maxCount) THEN maxCount := @lineCount

Then you can display the result like so:

@displayMaxLineCount (placed in group 1 footer)
WhilePrintingRecords;
NumberVar maxCount;
maxCount;

If you want to print out the max for each group, you'll have to have a formula to rest the value of maxCount each time around. Perhaps something like:

@resetMaxLineCount (placed in group 1 header)
WhilePrintingRecords;
NumberVar maxCount := 0;

Otherwise maxCount will just contain the maximum count to that point over the whole records set. Hope this helps...

Pont

 

[minifelt]

Hello Pont,
Thank you for taking the time to help me. Your suggested solution partly works. What I really need to do is to display/derive ONLY ONE number in GF1 footer that represents the maximum line count of all the groupings in Group footer 2. This one/constant number should be the maximum line count of each of the grouping in Group Footer 2. My report returns 6 groups (i.e. Group by Facility) in Group 2 with varying line counts (each line count representing days of operation for each facility). I would like to take the highest number of operating days and use it as a denominator to calcualte the average daily sales for the company in Group 1.

I followed your solution, but it's returning a different count depending on which Facility (Group 2 grouping) you drilldown to.

Hope you can continue to help me with this. Thank you much.

Bette

 

[lbass]

The suggested solution should work if you are displaying the results before any drilldown. I think drilling down into groups would cause the variable to lose its accumulated value so the result would be incorrect there.

-LB

 

[minifelt]

Hello LB,
You're absolutely correct. This is where the trouble lies. My calculation in GF1 becomes different once I drilldown. Do you have any idea on how I can take just the maximum count, make it a constant for Group Footer 1, so that it doesn't change during drilldown?
Thank you.
Bette

 

[lbass]

What is your formula for {@linecount}?

-LB

 

[minifelt]

Formula @LineCounter:
whileprintingrecords;
numbervar intLineCount:=intLineCount+1;

Formula to ResetLineCounter:
whileprintingrecords;
numbervar intLineCount;
if not inrepeatedgroupheader then intLineCount:=0;

Thank you.

 

[lbass]

I don't see why you need a variable for the linecount. You should be able to just insert a count on any recurring field, but to get the maximum group summary, you would need to use a command like:

select count(table.`field`) as grpcnt, table.`group1field`, table.`group2field`
from table
group by table.`group1field`, table.`group2field`

Link the command to the main table on the two group fields.

Then in the report you can create a formula:

maximum({command.grpcnt},{table.group1field})

I think this should retain the value during drilldown, though I'm not 100% sure.

-LB