Hello

I have a problem with totals

I have the following dimension

-Turnover 700
----Software 100
----Hardware 150
----Other 200
----Other total 250


The other total is a calculated cell where I make a sum of Software and
Hardware, This works fine in the cube

But the total of turnover is 700, this is wrong and must be 450
(Software + Hardware + Other)

In SSAS 2000 he does this correct
So my question is , how to exclude the calculated cell in the total of
the parent

Anyone an idea ?

Thx

If you defined "Other total" as a real member with a custom rollup
formula, you could then also add a unary operator column and use the
"~" unary operator to stop it from adding up into Turnover.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1132828339.114153.127920 (AT) g43g2000cwa (DOT) googlegroups.com>,
g4rc (AT) telenet (DOT) be says...

Darren

"Other total" is a real member, but has no custom rollup
Its a calculated cell

What do you mean by the unary operator '~'

Thx

Unary operators control how each member aggregates up to it's parent.

Possible unary operators are:

+ The value of the member is added to the aggregate value of the
preceding sibling members.

- The value of the member is subtracted from the aggregate value of the
preceding sibling members.

* The value of the member is multiplied by the aggregate value of the
preceding sibling members.

/ The value of the member is divided by the aggregate value of the
preceding sibling members.

~ The value of the member is ignored.

Note: The above was taken straight from books online

To set your own unary operator, add a column to your dimension table. I
would recommend adding the new column, updating all the rows to '+',
setting the default value to '+' and then setting the column to not
nullable. This way you don't have to worry about this column as new
values are added and you just set the value on an exception basis.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1132915834.515551.85020 (AT) g14g2000cwa (DOT) googlegroups.com>,
g4rc (AT) telenet (DOT) be says...

Darren

do you know if this is also so in SQL 2000

I think not

thx

Yes, this actually exists (and works ) in AS 2000


MC

<g4rc (AT) telenet (DOT) be> wrote

In know

but I mean the following

In SSAS 2000 I have the same dimension and i have no unary operator
defined.
And then the data of the calculated cells are not agregated in the
totals

Greetz

This are the mdx statements for a calculated cell in SSAS 2000

Calculation subcube = {[AccountCataloguesTree].&[254]}
Calculation value =
[AccountCataloguesTree].&[251]+[AccountCataloguesTree].&[252]+[AccountCataloguesTree].&[253]

This is the mdx statement for the same calculated cell in SSAS 2005

CREATE CELL CALCULATION CURRENTCUBE.[Revenue Recurring Activities 2006]
FOR '({[AccountCataloguesTree].[Parent Key].&[254]})' AS
'[AccountCataloguesTree].[Parent
Key].&[251]+[AccountCataloguesTree].[Parent
Key].&[252]+[AccountCataloguesTree].[Parent Key].&[253]'

If I understand, calculated measure is behaving differently in 2005? Could
you post a MDX you used for this calculated measure?

MC


<g4rc (AT) telenet (DOT) be> wrote

I have not played with calculated cells in AS2005 yet, so I am not sure
exactly how they differ to AS2k. One question though, do you have a
CALCULATE; statement after your CREATE CELL CALCULATION... ? I believe
this would force the hierarchy to reaggregate including the cell
calculation value.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1133164418.447552.225180 (AT) z14g2000cwz (DOT) googlegroups.com>,
g4rc (AT) telenet (DOT) be says...