In my company dimention

comp corpdiv dept01 accounts...
comp corpdiv dept02 accounts..
...
comp div4 dept41 accounts...
comp div4 dept42 accounts...

In my report dimentions

rpt balance fixedasset accounts..
rpt balance currasset accounts..
...
rpt p&l COGS accounts..
rpt p&l Sales accounts..
...

I'd like to get a gross profit number from "COGS - Sales". I need to have this "gross profit" number be calculated for division "corpdiv" only but available for other divisions because div3, div4 don't have COGS nor Sales. How do I accomplish that? Do I create a calculated member? or use somesort of rollup? Any input is appreciated

OK... Here is a thought. Hopefully someone out there can shed me some light and help me out. I have spent quite sometime on this

Can I create a calculated memeber with the Max function: Max(Sales - COGS). Only corp_div has both Sales and COGS figures so everyother division will return 0. How do I write this in MDX? I am fairly new to MDX


----- anna wrote: ----

In my company dimention

comp corpdiv dept01 accounts...
comp corpdiv dept02 accounts..
..
comp div4 dept41 accounts...
comp div4 dept42 accounts...

In my report dimentions

rpt balance fixedasset accounts..
rpt balance currasset accounts..
..
rpt p&l COGS accounts..
rpt p&l Sales accounts..
..

I'd like to get a gross profit number from "COGS - Sales". I need to have this "gross profit" number be calculated for division "corpdiv" only but available for other divisions because div3, div4 don't have COGS nor Sales. How do I accomplish that? Do I create a calculated member? or use somesort of rollup? Any input is appreciated

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])'

- Deepak

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