| Author |
SSAS 2005: Total with calculated cells
|
|
| g4rc@telenet.be 2005-11-24, 7:23 am |
| 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
| |
| Darren Gosbell 2005-11-25, 3:23 am |
| 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@g43g2000cwa.googlegroups.com>,
g4rc@telenet.be says...
> 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
>
>
| |
| g4rc@telenet.be 2005-11-25, 7:24 am |
| 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
| |
| Darren Gosbell 2005-11-26, 3:23 am |
| 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@g14g2000cwa.googlegroups.com>,
g4rc@telenet.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
>
>
| |
| g4rc@telenet.be 2005-11-28, 3:24 am |
| 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@telenet.be> wrote in message
news:1133160046.688122.16360@o13g2000cwo.googlegroups.com...
> Darren
>
> do you know if this is also so in SQL 2000
>
> I think not
>
> thx
>
| |
| g4rc@telenet.be 2005-11-28, 3:24 am |
| 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
| |
|
| If I understand, calculated measure is behaving differently in 2005? Could
you post a MDX you used for this calculated measure?
MC
<g4rc@telenet.be> wrote in message
news:1133162521.790562.289290@g14g2000cwa.googlegroups.com...
> 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
>
| |
| g4rc@telenet.be 2005-11-28, 3:24 am |
| This are the mdx statements for a calculated cell in SSAS 2000
Calculation subcube = {& #91;AccountCatalogue
sTree].&[254]}
Calculation value =
& #91;AccountCatalogue
sTree].&[251]+& #91;AccountCatalogue
sTree].&[252]+& #91;AccountCatalogue
sTree].&[253]
This is the mdx statement for the same calculated cell in SSAS 2005
CREATE CELL CALCULATION CURRENTCUBE.[Revenue Recurring Activities 2006]
FOR '({& #91;AccountCatalogue
sTree].[Parent Key].&[254]})' AS
'& #91;AccountCatalogue
sTree].[Parent
Key].&[251]+& #91;AccountCatalogue
sTree].[Parent
Key].&[252]+& #91;AccountCatalogue
sTree].[Parent Key].&[253]'
| |
| Darren Gosbell 2005-11-28, 7:24 am |
| 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@z14g2000cwz.googlegroups.com>,
g4rc@telenet.be says...
> This are the mdx statements for a calculated cell in SSAS 2000
>
> Calculation subcube = {& #91;AccountCatalogue
sTree].&[254]}
> Calculation value =
> & #91;AccountCatalogue
sTree].&[251]+& #91;AccountCatalogue
sTree].&[252]+& #91;AccountCatalogue
sTree].&[253]
>
> This is the mdx statement for the same calculated cell in SSAS 2005
>
> CREATE CELL CALCULATION CURRENTCUBE.[Revenue Recurring Activities 2006]
> FOR '({& #91;AccountCatalogue
sTree].[Parent Key].&[254]})' AS
> '& #91;AccountCatalogue
sTree].[Parent
> Key].&[251]+& #91;AccountCatalogue
sTree].[Parent
> Key].&[252]+& #91;AccountCatalogue
sTree].[Parent Key].&[253]'
>
>
| |
| g4rc@telenet.be 2005-11-28, 7:24 am |
| I put the calculate statement after my calculated cell but there is no
difference
The only solutions is to work with the unary operator
I think this is a great difference between SSAS 2000 and SSAS 2005
| |
| Darren Gosbell 2005-11-28, 8:25 pm |
| In article <1133173249.914835.138070@g47g2000cwa.googlegroups.com>,
g4rc@telenet.be says...
> I put the calculate statement after my calculated cell but there is no
> difference
>
Sorry, I should have been more clear in my previous response. I think
that the calculate should be before the calculated cell. The CALCULATE
statement is what does the basic aggregations. I was wondering if you
may have had a CALCULATE after your calculated cell which was causing
the results of the cell calculation to rollup with the raw data.
> The only solutions is to work with the unary operator
>
> I think this is a great difference between SSAS 2000 and SSAS 2005
>
I can see that there would be times when people will want the results of
calculated cell included and times when they don't. I'm happy that we
have at least found a solution with unary operators.
--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell
| |
| g4rc@telenet.be 2005-11-29, 3:23 am |
| When I go to the calculations I see the following
- on position 1 : Command = CALCULATE
- on position 2 till 25 = some calculated member
- on position 26 : calculated cell ; command = CREATE CELL CALCULATION
CURRENTCUBE.[Revenue Recurring Activities 2006] FOR
'({& #91;AccountCatalogue
sTree].[Parent Key].&[254]})' AS
'& #91;AccountCatalogue
sTree].[Parent
Key].&[251]+& #91;AccountCatalogue
sTree].[Parent
Key].&[252]+& #91;AccountCatalogue
sTree].[Parent Key].&[253]'
I can send you a email with a image of it
Thx
|
|
|
|