Skip to Content
0
May 18, 2010 at 02:32 PM

BEx Counting DocNumber-Position

26 Views

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