Getting maximum count by group

[dreman]

Using CR 8.5:
I am grouping history items by customer then by vendor, need to know how to capture the most selling item. Sort within vendor group must be done by items.
example: Customer 123...
Vendor ABC,
item 123, sold 2
item 456, sold 5
item 789, sold 3
--------
group#1 (By vendor), Most sold item 456, sold 5

Please help.
Thank you
dré

 

[IdoMillet]

In Group footer 2 track the maximum quanity and related item in two variables:
---------------------------------------------
WhilePrintingRecords;
NumberVar max_q;
StringVar max_item;

IF sum({sold}, {item}) > max_q THEN
( max_q := sum({sold}, {item}) ;
max_item := {item} ;
) ;
---------------------------------------------

In Group Footer 1 show the value:
---------------------------------------------
WhilePrintingRecords;
NumberVar max_q;
StringVar max_item;
"Most sold Item: " & max_item & " sold: " & Cstr(max_q) ;
---------------------------------------------

In Group Header 1, reset the value:
---------------------------------------------
WhilePrintingRecords;
NumberVar max_q;
StringVar max_item;
max_q := 0 ;
max_item := "" ;
---------------------------------------------

Cheers,
- Ido CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[dreman]

IdoMillet:
Thank you, it worked.
dré

 

[KhushiRaina]

hi,
Even i had similar query but with little variation.
What i have to do is add a certain no. to the sold field corresponding to the max_item.
I am using the above eg to explain my point
example: Customer 123...
Vendor ABC,
item 123, sold 2
item 456, sold 5 + 1
item 789, sold 3
--------
group#1 (By vendor), Most sold item 456, sold 5+1
i.e adding some extra no. to the item having max sold value.
The problem i am facing is the max_item is known at group 2 footer and i want it before this as i have to add an extra no. to max_item sold field.
Do you have any idea how to do this.
Khushi.

 

[IdoMillet]

I believe you would need a subreport (placed in Group Header 1) and a shared variable to pass back to the main report the maximum value.

Alternatively, you could use SQL to get this value via a VIEW or a subquery.

Cheers,
- Ido CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com