I created a financial statement cube where there is a row field called
'Gross Margin'. I did the calculation for 'Gross Margin' inside a
database view, so I dont have 'Gross margin' formula on the cube.

If I expand the cube up to the lowest level of detail, I can see that
the 'Gross Margin' amount is correct on the statement. Eg.
Country A
Brand 1
Gross margin=10%

Country A
Brand 2
Gross margin=20%

When I colapse the Brand, I will get:
Country A
Gross margin=30% (gross margin for Brand 1 + gross margin for Brand
2)

This is not what I want. I want the gross margin for Country A to be
calculated based on the formula I specified in the database view.

I was told that I can use calculated cell function for this but it's
only available on Enterprise edition.

Is there a way around this if I'm not using calculated cell?

Any input will be greatly appreciated.

Thanks
Josephine

Hi Josephine,
You need to formula as a caclulated member (not cell).

It is important to realise that aggregations up the tree are based on
values lower down the tree. In this case, as you have found, the GM for
country is the aggregation or sum of the GM;s for it's constituent
members, not as you might expect a calculation at the country level.

All you need to do is create a calculated member as:

[Sales] - [Costs]

(or whatever the relevant measures are.

If you do it this way then the GM for country will be based on the
sales and costs for country, which is precisely what you want.

See how you go.

Peter.

Dear Peter,

I cannot use calculated member because it will show as a column field.
I need the GM as a row field as a part of the financial statement.

Any other ideas?

Cheers
Josephine

Hi Josephine,

I'm not sure I understand.

We have a similar issue and have implemented a Calculated Member for
"Gross Margin", in much the same way you have. I'm a bit confused by
what you're saying about collapsing the brand. The Gross Margin member
must exist at some level of the hierarchy, unless you've implemented it
at multiple levels in the hierarchy (which is reasonable). If you have
done this, then why not do the calculations for country level Gross
Margin in your source data too, and forget about trying to roll up to a
parent.

Calculated cells could do this (i think), but they're pretty flipping
complicated!!

Sorry if I'm off the track here.

Peter

Hi:

Why don`t use custom rollup formulas?... I think that Account dimension of
FoodMart Database could be useful for you.

Regards

Rodrigo


"Peter" wrote: