Maximum of Summed Values 1

[shelby55]

Hello

I am using Crystal 9.0 reporting from an Access 2003 database.

With LB's help I was able to produce values and then text for the minimum and maximum month values using code like:

if groupnumber = 1 etc. //for minimum values and
if groupernumber = distinctcount({@dmonth}) then ....//for maximum values

I have the group of dmonth where the values are calculated which is hidden from the graphs and narrative. But what if I want the maximum sum of alc days for all of the months? If I choose maximum(sum({alcdays},{@dmonth})) then I get an error.

Thanks.

 

[Madawc]

It won's let you summarise a summary, part of how Crystal workes.

Check the Top N - that would allow you to show groups in order of some value.

Alternatively you would need to use a variable to check each group and collect the group total if it is greater than the existing value.

The use of Crystal's automated totals is outlined at FAQ767-6524.


Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[lbass]

You could create a formula like this to be placed in a {@dmonth} group section:

whileprintingrecords;
numbervar maxsum;
if sum({alcdays},{@dmonth}) > maxsum then
maxsum := sum({alcdays},{@dmonth}) else
maxsum := maxsum;

Then in the report footer, you would just use the following to display the results:

whileprintingrecords;
numbervar maxsum;

-LB

 

[shelby55]

Hi

Thanks for replying Madawc but LB's suggestion worked. Thanks LB!

To take this one step further, how do I determine via code what month the maximum value was in?

So in the original code you gave me, I could determine month because of the "if groupnumber>distinctcount({@dmonth})" for maximum month. But I want to say if the highest ever alc days is 300 and it occurred in August 2010.

Thanks.

 

[lbass]

If you want to refer back to former code, it would help to have the link, as I can't tell the context from your post.

Change the comparison formula to:

whileprintingrecords;
numbervar maxsum;
datevar maxsumdt;
if sum({alcdays},{@dmonth}) > maxsum then (
maxsum := sum({alcdays},{@dmonth});
maxsumdt := {table.date}
);

Then reference the date in a separate formula and format it to display the month.

-LB

 

[shelby55]

Hi

Thanks LB...you are a genius!!!