Report design Ideas to have 2 nested entities on rows with outer entity have calculted measures

Navan Mehra

I have a question about a report structure and would appreciate any ideas to achieve it. There are 2 entities - Measures & Channel

Measures have the following members - The last 2 AOV members are calculated using formulae achieved through rules

Weight

Volume

Revenue

AvgOrderVal/Order = Revenue/Vol

AvgOrderVal/kg = Revenue/Weight

Now the structure of the report is as follows - The measure entity is to be outside while the inner entity is channel, as shown below

The case is, if the AOVs are calculated via rules, they cannot be applied here as rules work on the innermost entity in the layout. If we precalculate the 2 AOV measures at base level, the result is incorrect at aggregate levels.

Is there any way to display such reports?

Robert Barkaway

Hi Navan, you can achieve this using Refer To and an algorithm, in my example here I've created an entity like yours which allows me to hold the different measures in the one cube for reporting, my layout is by the measure and then City, but using Refer To and I can have blocks which always show the specific measures, and I can then use the algorithm to set specific calculations depending on the code of the measure to be displayed. This method isn't as flexible or easy to maintain as using a rule, but it does get around the issue of the measure not being the innermost entity in the grouping.