One to Many MDX Puzzle. Help !!

[TTSimon]

Data As Follows...

Order|Order  |Order    |Item  |Received  |Received
#    |Line # |Line Qty |Code  |Date      |Qty
-----|-------|---------|------|----------|--------
1    |1      |8        |xxxxx |20040410  |8 
1    |2      |20       |aaaaa |20040412  |10 
1    |2      |20       |aaaaa |20040503  |5 
2    |1      |12       |bbbbb |20040512  |6 
2    |1      |12       |bbbbb |20040520  |6

I need to produce a cube that shows total Ordered qty ordered against Received Qty.

Dimensions would be Time.Months Time.Week. Item Supplier, Item Season etc.

For example, we would like to know in a given month what goods were Received against Orders. So for Order 1-Line 1 we know 8 were ordered and 8 were received, but for Order 1-Line 2, the goods arrived in batches. 20 were ordered 10 arrived one day, 5 another, so we would like to show total 15 received against total 20 ordered.

Obviously basic measures will SUM the duplicate Order detail rows giving incorrect totals.

I need something like DISTINCT SUM, but am not aware it exists in MDX.

The solution will be used by Excel users, so needs to creatable in Analysis Manager as a Measure or Calculated cell.

In our Reporting package (Cognos Impromptu) I can achieve this by summing the Average of each Order/Line No combination for a known dimension.

Does a solution exist for MDX that would work for any given Rows / Columns and Page filters, or would they only work for a given Dimension, in which case I would need multiple Order Totals for Each possible dimension (Total by Month, Total by Supplier etc)

Thanks in advance for any help with this

Simon.

 

[pabloj]

You wrote:

In our Reporting package (Cognos Impromptu) I can achieve this by summing the Average of each Order/Line No combination for a known dimension.

Why not do this in AS, I believe you can build a calculated measure or better add it as a new column in the fact table.

Stick to your guns