January 14, 2009 at 4:20 am
Hi All,
My first post on this web site and am really hoping somebody here can help.
I am designing a financial consolidation datawarehouse that uses several different account structures, cashflow, fixed asset etc and a combination of standalone and cumulative measures that are all dimensioned across time.
Inside the account dimensions are separate hierarchies that need different instructions on how to rollup across time based on the a value in the account dimension and the dimension defining a standalone or cumulative measure.
I am trying to control these rollups using scopes in MDX and calculated measures in calculations. Examples below.
SCOPE (
.[Period TR Hierarchy].[Reporting Quarter].members,
[Account Balance Sheet].[UnOP Hierarchy].[Unary Operator].&[~] ,
[Measures].[actuals]);
THIS = (CASE WHEN [Account Balance Sheet].[UnOP Hierarchy].Currentmember
IS [Account Balance Sheet].[UnOP Hierarchy].[Unary Operator].&[~]
THEN sum([Measures].[ActualsQuarterMeasure])
ELSE sum([Measures].[Actuals])
END);
END SCOPE;
CREATE MEMBER CURRENTCUBE.[MEASURES].ActualsQuarterMeasure
AS (
.[Period TR Hierarchy].Currentmember.Children(2), [Measures].[Actuals]),
FORMAT_STRING = "#,#",
VISIBLE = 0;
The measures rollup correctly across time, the problem I am having is in the scope, telling the mdx to apply the correct measure based on the unaryoperator value in the dimension. I haven't even got round to using the 3rd dimension value to control the rollups..
Thanks in advance for your help
Kevin
January 15, 2009 at 11:06 am
I looked at some of my code, and I think you should declare the calculated measure first, and the scope after that. See the source I found (that is working properly).
Taking a look at your requirements however, i am not sure that you are on the right path. Could it be that Account Intelligence and Time Intelligence solutions might tackle your problem in a more efficient way?
CREATE MEMBER CURRENTCUBE.[MEASURES].[ForecastBias]
AS NULL
, //calculation defined in scope
FORMAT_STRING = "Percent",
VISIBLE = 1;
SCOPE ( FILTER ( [Time].[Year Month].members
, isempty ( ( [Measure Type].[Measure Type].&[2]
, [Measures].[Volume - C]
)
)
)
, [MEASURES].[ForecastBias]
);
this=IIF ( [ForecastVolume (FB)]<> 0
, ( [Measures].[ActualVolume (FB)]
- [Measures].[ForecastVolume (FB)]
)
/ [Measures].[ForecastVolume (FB)]
, NULL
);
End scope;
all those smilies should be reverted to single closing hyphens ...
Peter Rijs
BI Consultant, The Netherlands
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply