Average of values, but not on item level...

[Former Member]

Hi

I am building a query for reporting production times, but I have huge problems when I try to report on averages - for example average order time per plant or material.

I have narrowed it down to the fact that even though the value I want to average is stored at the item level (operation),it is related to a higher aggregation level (order). I'm sure there must be some way to solve this, but I just can't find it, so now I'm hoping you guys can help me out!

In my cube I have line items like this (simplified - there are more aggregation levels):

Plant, Order, Operation, operation runtime, order runtime, rowcount

Plant1, Order1, operation1, 2days, 5days, 1

Plant1, Order1, operation2, 4days, 5days, 1

Plant1, Order2, operation1, 1days, 10days, 1

Plant1, Order2, operation2, 3days, 10days, 1

Plant1, Order2, operation3, 4days, 10days, 1

Plant1, Order2, operation4, 3days, 10days, 1

Plant1, Order2, operation5, 4days, 10days, 1

Plant1, Order2, operation6, 1days, 10days, 1

Note that the operation runtimes do not sum to the order runtime (because they are calendar days and some operations run in parallel).

In the report, I want to show average "order runtime" at the different aggregation levels - and allow drill-down/up.

If I make a simple formula that divides "order runtime" by "rowcount", I get the right value per order, but at higher aggregation levels, the average is weighted with the number of operations per order, so the example above would give an average for plant1 of 8,75 insteaf of 7,5.

I've tried using exception aggregation, but I just can't seem to get it right...

[Former Member]

Thanks for the suggestions. As I wrote, I already tried playing around with exception aggregation and I also tried different constant selections but i still can't get the correct result... Can you give some more detialed guidance?

[Former Member]

<Font Face = "tahoma">Hi,

I think your result is not coming correct because when you drilldown or drillup the formula gets executed on data records at different levels.

You have to do something by which the report drilldown will not make the calculations happen at different granularity but always at the same granularity. For this, you should make use of "Constant Selection" property that is used for query elements. Please see if you can make use of in some way or the other.

Hope it was useful.

Regards,

Abhijit

</Font>

[Former Member]

Are you on version 3.5 or 7?

[Former Member]

Sorry, forgot to mention... I'm on 3.5.

[Former Member]

Good, cos I know it can be done in 3.5. Well, it is 'just' a matter of working out how to get Exception Aggregation to work. Yes its not easy (though when you have worked out how to do it, you will wonder what the problem was!!).

Some key points to remember:

You must base the Exception on a basic key figure, not a restricted or calculated one.

Your choice of characteristic on which to base the aggregation is important.

Hope that helps,

Patrick