Count per unique record

[#1]

Another question:  I have two tables with a one to many relationship.  I want a count from the table where there is only one record and a sum on the table with many records.  How do I do that? 

[Sokkorn]

Hi,

Suppose that you have two table below

1. Invoice header:

Inv	Customer
1	A
2	B
3	C

2. Invoice details

Inv	Item	Amount
1	I001	10
1	I002	20
1	I003	30
2	I001	40
2	I002	50

So you link both table via [Invoice header],[Inv]=[Invoice details].[Inv]

From this we will got one to many relationship.

Now try to create Straight table with

     1. Dimension: Inv and Customer

     2. Expression

          a. Count(Inv)

          b. Sum(Amount)

It should give you

Inv	Customer	Count(Inv)	Sum(Amount)
1	A	1	60
2	B	1	90
3	C	1	0

Regards,

Sokkorn