Dsiplaying counts based on priority

[gkw123]

Hi,

I am having an issue where I have to count the number of families according to a certain program code. The information I have looks like this.

Family# program Code $amount

X 2 $$$
Y 1 $$$
X 1 $$$
Z 1 $$$
W 2 $$$



This is what the report should look like.

Code Family count Total Amount

1 3 $$$
2 1 $$$

As you can see the family 'X' should only be counted once under code '1'

However in the actual report, it is being counted twice under both code 1 and 2 :

Code Family count Total Amount

1 3 $$$
2 2 $$$

I have grouped by {code}, and added running totals to count the families. This is just an example, in the actual report there will be many codes and I have to count the family only once according to the assigned priority. I cannot suppress the extra records because I need all the data to calculate the {Total Amount}.

Is there any way to accomplish this?
Thanks
gkw123

 

[IanWaterman]

Group report by Program code then add summaries to group footer.

Sum for Total amount and distinct count for Family

Ian

 

[lbass]

What is the rule that says the family gets counted under code1 and not code2? In other words, what gives the code1 count priority over the code2?

-LB

 

[gkw123]

@Ian - I tried that but it didn't work.
The family with two codes still get counted twice under the two codes. I have to find a way to know that when a family has been counted once under the higher priority code, it should not be considered again for the rest of the report.

@lbass - There are about 7 different codes and they are ranked from highest priority to lowest according to the client who requested this report. We already know that hierarchy. There is no specific pattern or formula to determine which code has higher priority. The codes are completely random.

Thanks a lot for your help guys.

 

[lbass]

Well, where are you seeing the ranks then? How do YOU know which code to count? Do you have to depend upon the sort order to get the correct result? If this is the case, and the highest priority always appears first, then you could do distinct counts of family by creating a formula like this:

//{@accum} for the detail section:
whileprintingrecords;
stringvar fam;
numbervar code1;
numbervar code2;
if not({table.family} in fam) then
fam := fam + {table.family}+",";
if not({table.family} in fam) then (
if {table.code} = 1 then
code1 := code1 + 1 else
if {table.code} = 2 then
code2 := code2 + 1
); //etc.

//{@displcode1} for the report footer:
whileprintingrecords;
numbervar code1;

//{@displcode2} for the report footer:
whileprintingrecords;
numbervar code2;

-LB