Different aggregation on the same metric for different levels of a hierarchy

[CoolP]

Hi Experts,

I have a dimension ' D' with hierachy 'H' and Levels 'L1','L2','L3','L4' and a metric 'M'.

When the user drills up from L4 to L3, I want the aggregation on metric 'M' to be 'SUM'.
When the user drills up from L3 to L2, I want the aggregation on metric 'M' to be 'count distinct' (let's say)

How can we achieve that in FM 10.1?

Thanks
CoolP

[norkos]

Hi,

Im affraid you can't do it at all...
You can't set different rollup methods on different levels in the same hierarchy.

But in RS there is a workaround.
You can create two different measures with different rollup methods (M1, M2)
After you insert both measures into your crosstab, you can manage with conditional styles wich one will appear.
Follow the task list to achive it:
- insert hierarchy H into the rows of the croostab
- insert the two measures into the columns
- create a calcutated data item 'level' which returns with the level of the actual hierarchy item. Use the following expression: ordinal(level(currentmember([Namespace].[D].[H])))
- Set the calculated item as a property of the hierarchy in the rows
- create two conditional styles like this: [Query].[level] = 2 and [Query].[level] <> 2, and use them on the coulmns containing the two measures. Set box type to none if the item not met the condoistion.

If your crosstab contains L3 items than you will see only the column contains the measure with sum rollup. On L2 you will see the column with the count rollup.
So this solution will imitate a normal drill in the user's POV ;)

[CoolP]

Hi Norkos,


Thanks for your reply. If it is in Report Studio,we have more control to play around. In my case, I want this to happen in analysis studio as well. It is not for a particular report.


Thanks
Prasan