Sum of distinct

[mbs]

I'm new to Cognos and have been struggling with the following problem.

We have Leads related to Opportunities via a bridging table that resolves a many-many relationship between the two. Each Opportunity has a $ Amount associated with it. Users want to report on a count of the number of unique Opportunities and the associated $ Amounts, grouped and filtered by various attributes of the Leads (for example, the Lead's Campaign Type). If many Leads relate to a particular Opportunity then that Opportunity and its $ Amount should be counted once only.

The count of Opportunities is straightforward using COUNT DISTINCT. My question is, how do I ensure that I don't inflate the $ Amount? In particular, how do I ensure an Opportunity's $ Amount is included once only in each grouping of the data and that the overall totals are correct too.

For a report of 10 Leads that relate to the same Opportunity that has an amount of $1, what I get now is:

Campaign Type	Number of Leads	Number of Opportunities	$ Amount
Campaign A	1	1	$1
Campaign B	3	1	$3
Campaign C	6	1	$6

TOTALS:

10

1

$10

What I want is:

Campaign Type	Number of Leads	Number of Opportunities	$ Amount
Campaign A	1	1	$1
Campaign B	3	1	$1
Campaign C	6	1	$1

TOTALS

10

1

$1

Thanks.

[cognos810]

Hello mbs,
I am assuming that the detail rows, for CAMPAIGN B for example, have $1 per record. Can it be different? If they always will be the same then you can use total(distinct [$ Amount]) and that will show you $1 as the total for Campaign B.

Let me know if it applies to your scenario.

Thanks,
Cognos810