Deepak, I tried the formula to create a new report dimension member in my Finance cube. The number came our right when I browse to compay->corpdiv, let's say it is $40,000. However, when I browse to company->div4, the calculated member GrossProfit turned 0. It reflected the fact that there is no sales nor cogs in div 4. How can I make the "GrossProfit" number stay as $40,000 when I am not at company->corpdiv.



Not sure what you mean by "available for other divisions", but here's a
calculated member on the report dimension. This always substitutes
[corpdiv] at the division level:

'iif([comp].CurrentMember.Level is [comp].[division],
([rpt].[p&l].[Sales],[comp].[corpdiv])
-([rpt].[p&l].[COGS],[comp].[corpdiv]),
[rpt].[p&l].[Sales]-[rpt].[p&l].[COGS])'

Maybe this iif() test is not working correctly:

'[comp].CurrentMember.Level is [comp].[division]'

Is [comp].[division] the name of the division level, so that
[comp].div3, [comp].div4, etc are at this level?

You can check this using copy/paste in MDX editor.


- Deepak

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