MS SQL Server OLAP - write a better calculated memeber in AS2005

Author

write a better calculated memeber in AS2005

charles

2005-11-23, 8:24 pm

Hi,

I have a cube with many dimensions as follows:
Dimension 1
Dimension 2
Dimension 3
Dimension 4
Dimension 6
Dimension 7
Dimension 8
Dimension 9
Date

Calculation logic:
If all dimension 1,2,3,4,5,6,7,8,9 other than date are in total level
Then
Use calculation logic 1
Other wise
Use calculation logic 2

What I write as follows:
IIF (
[Dimension 1].[Dimension 1].CURRENTMEMBER.Name ='All'
AND [Dimension 2].[Dimension 2].CURRENTMEMBER.Name ='All'
AND [Dimension 3].[Dimension 3].CURRENTMEMBER.Name ='All'
AND [Dimension 4].[Dimension 4].CURRENTMEMBER.Name ='All'
AND [Dimension 5].[Dimension 5].CURRENTMEMBER.Name ='All'
AND [Dimension 6].[Dimension 6].CURRENTMEMBER.Name ='All'
AND [Dimension 7].[Dimension 7].CURRENTMEMBER.Name ='All'
AND [Dimension 8].[Dimension 8].CURRENTMEMBER.Name ='All'
AND [Dimension 9].[Dimension 9].CURRENTMEMBER.Name ='All'),
calculation logic 1,
calculation logic 2

Question: Is there simple way to write all dimensions other than date
in top level?

Any help is appreciated,

Charles

Deepak Puri

2005-11-24, 3:23 am

Using nested scope in the MDX calculation script for the cube could
work, something like this (assuming that the All member for Date is the
DefaultMember):
[color=darkred]
CREATE MEMBER CURRENTCUBE.[MEASURES].[CalcMeas1] AS
<calculation logic 2>;
Scope(Root(), [Measures].[CalcMeas1]);
Scope(Descendants([Date].[Date].DefaultMember));
This = <calculation logic 1>;
End Scope;
End Scope;[color=darkred
]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.droptable.com ***

charles

2005-11-24, 8:23 pm

Hi Deepak, Thank you very much for your help, It works! Charles

Sponsored Links