Hi,
Iu2019m working on a query that should show how many items has been delivered late.
Each item (Document Number u2013 Item Pos) can have none, one , or many u201CDelay Reasonsu201D that makes the item not being delivered on time.
The u201CDelay Reasonu201D can be for instance: Logistics, Technical, Filters, Credit and so onu2026. and probably others in the future.
Very shortly, and a simplified view of the problem we can imagine a cube like this:
Doc Number - Item Pos - Delay Reason
1000 - 1 - LOGISITCS
1000 - 1 - TECHNICAL
1000 - 2 - TECHNICAL
1000 - 3 - ( empty )
1000 - 4 - ( empty )
1000 - 5 - LOGISITICS
1000 - 6 - FILTERS
2000 - 1 - ( empty )
2000 - 2 - CREDIT
2000 - 3 - FILTERS
2000 - 3 - CREDIT
What I would like to know, although it can seem stupid in this little example, is how many items have been delivered late.
In the example the value must be 6 (1000-1,1000-2,1000-5,1000-6,2000-2,2000-3).
We only count 1 per DocNumber-ItemPos pair, independently on how many times has been u2018impactedu2019 for some u2018Delay Reasonu2019
If I had a unique RowId into the cube per each row, I could use the u2018Exception aggregationu2019 COUNT and use this u2018RowIdu2019 as a Reference Charcact. Iu2019ve tested this and work fine for me. I create this RowId by concatenating the u2018Doc. Numberu2019 and the u2018Item Posu2019 in the transformation.
The question is: ¿Is that a common practice? ¿Do I have any other way to do the same without the need of the RowId (nested aggregations probably)? ¿Pros and cons?
Thanks
Edited by: Lluís BA on May 18, 2010 4:36 PM
Edited by: Lluís BA on May 18, 2010 4:41 PM