?
Solved

Maximum value of group

Posted on 2008-11-17
16
Medium Priority
?
1,018 Views
Last Modified: 2012-06-22
I have a report with a group "X".  I have a count of field "Y" added at the group "X" header.  Is there a way that I can find the maximum count of the grouped fields?

I tried
Maximum(Count ({RPT521_20081117.TDL TRAN TX ID}, {RPT521_20081117.CODER NAME}))

but am getting an error that the Count can not be summarized.
0
Comment
Question by:gdspeare
  • 8
  • 8
16 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 22979924
It can't be.

You will have to do a manual max.

In the report header add a formula
WhilePrintingRecords;
Global NumberVar MaxCount := -1;
''

In the group header or footer
WhilePrintingRecords;
Global NumberVar MaxCount;
If MaxCount < Count({({RPT521_20081117.TDL TRAN TX ID}, {RPT521_20081117.CODER NAME})) then
   MaxCount := Count({({RPT521_20081117.TDL TRAN TX ID}, {RPT521_20081117.CODER NAME}))
''

In the report footer
WhilePrintingRecords;
Global NumberVar MaxCount;
MaxCount

mlmcc
0
 

Author Comment

by:gdspeare
ID: 22980379
mlmcc -

Thanks for responding.  I think I have followed your instructions.  I have added 3 formulas to the report.  Formula 1 is in the report header, formula 2 in the group header, and formula 3 in the report footer.

What formula 2 is doing, however, is looking at the row above it and if the count of items is greater than the row above it is adding that count (if row 2 is more than row 1 row 2 value displays).  On the other hand if the second row is smaller than the first a zero is displaying.

Formual 3 is pulling the max for the entire group.

0
 
LVL 101

Expert Comment

by:mlmcc
ID: 22981104
For the formula 2 it is just finding the max count for the report.

You still need to use your count formula for the count for the group.

mlmcc
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:gdspeare
ID: 22985074
mlmcc -

Sorry for dense about this topic, but...

I tried to add a summary on @Max_Count_2 (my second formula) as well as creating a formula, but I am getting back to the original error that the formula can not be summarized.

I have added my 3 formulas below to confirm that I followed your first set of instructions.

Thanks,
David
@Max_Count_1 - LOCATION: REPORT HEADER
WhilePrintingRecords;
Global NumberVar MaxCount := -1;
 
@Max_Count_2 - LOCATION: Group 1 header
WhilePrintingRecords;
Global NumberVar MaxCount;
 
If MaxCount < Count ({RPT521_20081117.TDL TRAN TX ID}, {RPT521_20081117.CODER NAME}) then
  MaxCount := Count ({RPT521_20081117.TDL TRAN TX ID}, {RPT521_20081117.CODER NAME}) ELSE
{@Max_Count_3}
 
@Max_Count_3 - LOCATION: REPORT FOOTER
WhilePrintingRecords;
Global NumberVar MaxCount;
MaxCount

Open in new window

0
 
LVL 101

Expert Comment

by:mlmcc
ID: 22987440
Are you trying to display the MaxCount in each group header?


@Max_Count_2 - LOCATION: Group 1 header
WhilePrintingRecords;
Global NumberVar MaxCount;
 
If MaxCount < Count ({RPT521_20081117.TDL TRAN TX ID}, {RPT521_20081117.CODER NAME}) then
    MaxCount := Count ({RPT521_20081117.TDL TRAN TX ID}, {RPT521_20081117.CODER NAME});
MaxCount

What values are being displayed?

mlmcc
0
 

Author Comment

by:gdspeare
ID: 22987820
I am going to attempt to add it to multiple groups, but right now i'm just trying to figure it out.
I have attached a screenshot (.pdf) of what my @max_count_2 formula is doing.


coder-productivity-v1-11.17.2008.pdf
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 22988788
Does my formula give the same result?

mlmcc
0
 

Author Comment

by:gdspeare
ID: 22997075
Yes,  using

If MaxCount < Count ({RPT521_20081117.TDL TRAN TX ID}, {RPT521_20081117.CODER NAME}) then
  MaxCount := Count ({RPT521_20081117.TDL TRAN TX ID}, {RPT521_20081117.CODER NAME})

as @Max_Count_2 produces the same result
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 22997371
Can you upload the report?

Make a copy and change the extension to txt

mllmcc
0
 

Author Comment

by:gdspeare
ID: 22997552
Not quite sure how to save a cr as a txt file...
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 22998197
Make a copy.  Then change the extension to TXT.  It is still a report file but the extension is TXT.
EE doesn't accept RPT files

mlmcc
0
 

Author Comment

by:gdspeare
ID: 22998839
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 22999540
It looks like it should work.

I made a minor change to the 2nd formula

See if that works

mlmcc
Copy-of-Coder-Productivity-V1-11.txt
0
 

Author Comment

by:gdspeare
ID: 23000364
mlmcc -

I really appreciate you help on this. I actually had already returned the same results as your adjustment.  What the change does is keep a running log of the maximum number up to that point in the group.  I want to return the maximum number.

If you look at the attached you will will understand what I mean.  I wanted the value of 1,580 to be returned all the way down.
Unless the answer is obvious to you at this point, I am just going to do the calculation in my view on the server as a subquery.

David
copy-of-coder-productivity-v1-11.pdf
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 2000 total points
ID: 23001269
The way it is calculated you will only have the maximum value after the report is run.  If you need it before that then you need to use a summary function or do it in the SQL

mlmcc
0
 

Author Closing Comment

by:gdspeare
ID: 31517621
Excellent ideas.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses
Course of the Month13 days, 16 hours left to enroll

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question