Hello,

I have create a virtual cube together with a calculated member.
The calculated member is conducted by a measure and a member property
in the lowest level a dimension. When I rollup to see the data in a
higher level, it shows an error because there is no property in the
higher level (I guess). I don't want the calculated member to be
re-calculated, but I want it to be summerized. Help me please !

Best Regard,

Anote

Perhaps this statement can help you as a start:
IIF(Not IsEmpty([Measures].[Sales Count]),

[Measures].[Store Sales]/[Measures].[Sales Count],1)

This is from the sales cube in foodmart.



"Anote Sangkunakupt" <anote_un (AT) hotmail (DOT) com> wrote

Thanks for that. I have tried this statement. The result is it
displays zero instance of #ERR. However, it is not what I really want.
What I want is, when I roll up to a higher level, I want the
calculated member to be added up.

For example, at the lowest level

Group Customer Factor1 Factor2 Amount Net Amount
(Measure)(Measure)(Measure)(Calculated member)
if group = a, Factor1 *
amount
if group = b, Factor2 *
amount
A 100 1.1 1.2 100 110
A 101 1.2 1.1 100 120
B 102 0.9 1.1 100 110
================================================== ===================
At a higher level, what I want is

Group Amount Net Amount (Calculated member)
A 200 230
B 100 110

================================================== ====================
However, what it displays nows is

Group Amount Net Amount (Calculated member)
A 200 0 or #err
B 100 0 or #err

================================================== ====================

Any suggestion

Best Regards,

Anote

"Thomas Ivarsson" <thomas.ivarsson (AT) exallon (DOT) sigma.se> wrote

You can reaggregate the calculated member but you would
need to use calculated cells and recalculate each
dimension. This means that all of your aggregations are
useless and the performance will be pretty bad.


news:<egIZeJSRDHA.2852 (AT) tk2msftngp13 (DOT) phx.gbl>...

You can aggregate a calc member without resorting to calc cells. Here's an
article that explains how:

http://www.tomchester.net/articlesdo.../salesper.html

Depending on dimension size, this can be slow to execute.

tom @ the domain below
www.tomchester.net


"Dave Shroyer" <dshroyer (AT) obs3 (DOT) com> wrote

Tom Chester,

I cannot access the
http://www.tomchester.net/articlesdo.../salesper.html. How I can get this
information? I think it could help me a lot on my task.

Best Regards,
Anote

"Tom Chester" <publicNOSPAM (AT) tomchester (DOT) net> wrote

From the sample data, I assume that Factor1 and Factor2 are Member
Properties of your Customer Dimension; and which one is applied depends
on the member of the Group Dimension. In that case, try defining
Calculated Measure [Net Amount] as:

Sum({[Group].[A]}*
Descendants([Customer].CurrentMember,,LEAVES),
[Measure].[Amount]* StrToValue(
[Customer].CurrentMember.Properties("Factor1"))),0) +
iif(IsAncestor([Group].CurrentMember,[Group].[b]),
Sum({[Group].[b]}*
Descendants([Customer].CurrentMember,,LEAVES),
[Measure].[Amount]* StrToValue(
[Customer].CurrentMember.Properties("Factor2"))),0)



- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

I have tried the SQl statement in Tom Chester's article. It works and
provides the result that I want. However, the performance is badly
slow. Does anyone have any suggestion for improving the performance in
this situation.

Thanks in advance

Anote

"Tom Chester" <publicNOSPAM (AT) tomchester (DOT) net> wrote

a

Deepak Puri <deepak_puri (AT) progressive (DOT) com> wrote