Finding the maximum from a distinct count 1

[matahari]

Using: Crystal Designer 8
DB: SQL2K

I need to sum quantity amount based on the maximum distinct count of items within a grouping. Example:

GroupHeader#1 Part Produced: ABC123
GroupHeader#2 PartUsed
ProductionNo PartUsed Qty Produced
1 MA111 500
2 MA111 100
3 MA111 200
GroupFooter#2 Total MA123: 800

1 CA222 500
2 CA222 100
3 CA222 200
4 CA222 -100
GroupFooter#2 Total CA222: 700

1 BB333 500
2 BB333 100
3 BB333 200
GroupFooter#2 Total BB333: 800

GroupFooter#1 Total Qty Produced for ABC: 700

I need to get the Summary Qty Produced (GroupFooter#1) for all the ProductionNumbers(1,2,3,4) for that Part Produced (ABC123) which is 700.

(1) What I have done is create a Running Total - distinct count.
(2) I need to identify the maximum value from the distinct count for each PartUsed Group.
(3) Then I need to display the Qty Produced summary for the maximum count. In the above example: Total CC222 has the maximum count = 4 - Qty Produced Sum = 700.

My problem: I cannot figure out the syntax to do (2) and (3).

Would really appreciate some help. Thanks in advance.

~matahari

 

[synapsevampire]

Tek-tips is so unreliable, people using it must lose faith in the people helping here based on this. Every day about 3-5 posts get lost because the site malfunctions, I wish they'd give up on Coldfusion and use something industrial strength here.

Anyway, here's a basic tutorial:

To get a distinctcount, and use that distinctcount in aggregates, you'd want to use a forula, as in:

whileprintingrecords;
numbervar MyDC:=
distinctcount({table.field},{table.group})

So to use it in an inner grouping:

Outer group header formula:
numbervar MyDC:=0;
numbervar MySum:=0;
numbervar MyMax:=0;

Inner grouping footer:
whileprintingrecords;
numbervar MySum;
numbervar MyMax;
numbervar MyDC:=
distinctcount({table.field},{table.group});
MySum:=MySum+MyDC;
If MyMax < MyDC then
MyMax:=MyDC;
MyDC

The above formula sets 2 variables and displays the current distinct count.

Then you can refrerence the variables later in the outer group footer, as in:

whileprintingrecords;
numbervar MySum;
numbervar MyMax;
"My sum = " & MySum & " and my max = " & MyMax

Hopefully this illustrates well enough how to perform advanced aggregates using formulas to accomplish what you need.

-k

 

[matahari]

That's AWESOME!!
With a little tweaking, I got what I needed!

**BOWING DOWN BEFORE synapsevampire**

Thank you TONZ!

~matahari~