MDX SCOPING at ALL level

Hi all,

I am trying to Translate the case statement to using SCOPE but the scoping at the ALL level does not seem to work right. It seems to set all the values for all other measures at the ALL level to the same value as well.

But I only want it to set the value for [MEASURES].[Def OTS] when I am at the [Dim OTS].[ALL] level.

What am I doing wrong?

thank you

CREATE MEMBER CURRENTCUBE.[MEASURES].[Def OTS]
AS case when isempty([Measures].[Closing Balance]) Then Null
When [Dim OTS].currentmember IS [Dim OTS].[OTS 30] Then Null
When [Dim OTS].currentmember IS [Dim OTS].[OTS 60] Then Null
When [Dim OTS].currentmember IS [Dim OTS].[OTS 90] Then Null
When [Dim OTS].currentmember IS [Dim OTS].[Bankruptcy] Then Null
When [Dim OTS].currentmember IS [Dim OTS].[Foreclosure] Then Null
When [Dim OTS].currentmember IS [Dim OTS].[Current] Then Null

When [Dim OTS].currentmember is [Dim OTS].[All] then
([Dim OTS].[REO],[Measures].[Closing Balance])
/([Dim OTS].[ALL],[Measures].[Closing Balance])

When [Dim OTS].currentmember is [Dim OTS].[REO]
then ([Measures].[Closing Balance]) /([Dim OTS].[REO],[Measures].[Closing Balance])
end,
VISIBLE = 1;

--This is the translation to SCOPE.

CREATE MEMBER CURRENTCUBE.[MEASURES].[Def OTS]
AS NULL,
VISIBLE = 1;
SCOPE ([Dim OTS].[All]);
THIS = ([Dim OTS].[REO],[Measures].[Closing Balance])
/([Measures].[Closing Balance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;

SCOPE ([Dim OTS].[REO]);
THIS = ([Measures].[Closing Balance]) / ([Dim OTS].[REO],[Measures].[Closing Balance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;

I don't think you're specifying the measure for your assignments which would mean that you're assigning to all measures in the cube. So get rid of the keyword THIS everywhere and specify the measure. Or just surround the whole thing with "SCOPE ([Measures].[Def OTS])".

Also, I'm not sure what Dim OTS looks like in terms of attributes and relationships, but you might try changing:

SCOPE ([Dim OTS].[All]);
to

SCOPE (Root([Dim OTS]));

If you've got multiple attributes, the latter will probably work more like you're expecting.|||

1. could this be related to this post ?

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1205362&SiteID=17

2. the dimension [Dim OTS] only has one attribute and no user hierarchy. The physical table itself has only one column.

3. So you mean I can do something like this below ?

Thanks

--This is the translation to SCOPE.

CREATE MEMBER CURRENTCUBE.[MEASURES].[Def OTS]
AS NULL,
VISIBLE = 1;

SCOPE ([MEASURES].[Def OTS]);

SCOPE (ROOT([Dim OTS]));

THIS = ([Dim OTS].[REO],[Measures].[Closing Balance])
/([Measures].[Closing Balance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;

SCOPE ([Dim OTS].[REO]);
THIS = ([Measures].[Closing Balance]) / ([Dim OTS].[REO],[Measures].[Closing Balance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;

END SCOPE;

|||looks good... does it work like you expect?|||

yes! it works great.

thank you

I was wondering if this also work if I included [MEASURES].[Closing Balance]

in the scope statement since I only care about this one measure at these levels

especially when the measure is NOT empty (I do not care about when this measure is empty) ?

--This is the translation to SCOPE.

CREATE MEMBER CURRENTCUBE.[MEASURES].[Def OTS]
AS NULL,
VISIBLE = 1;

SCOPE ([MEASURES].[Def OTS]);

SCOPE ([MEASURES].[Closing Balance], ROOT([Dim OTS]));

THIS = ([Dim OTS].[REO],[Measures].[Closing Balance])
/([Measures].[Closing Balance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;

SCOPE ([MEASURES].[Closing Balance],[Dim OTS].[REO]);
THIS = ([Measures].[Closing Balance]) / ([Dim OTS].[REO],[Measures].[Closing Balance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;

END SCOPE;

|||

What you've done in that last calc script you posted is override the measure it applies to, I believe. What you need to do is specify two measures in the scope. The outermost scope should be:

scope({[Measures].[Def OTS], [Measures].[Closing Balance]})

Then I think you'll get what you want assuming you want the assignments to impact both measures.

|||

yes, this works, thank you. The final statement looks like this.

CREATE MEMBER CURRENTCUBE.[MEASURES].[Def OTS]
AS NULL,
VISIBLE = 1;

SCOPE ([MEASURES].[Def OTS],[MEASURES].[Closing Balance]);

SCOPE ([ROOT([Dim OTS]);

THIS = ([Dim OTS].[REO],[Measures].[Closing Balance])
/([Measures].[Closing Balance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;

SCOPE([Dim OTS].[REO]);
THIS = ([Measures].[Closing Balance]) / ([Dim OTS].[REO],[Measures].[Closing Balance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;

END SCOPE;

|||

sorry, check the wrong cube.

No that scoping statement will not work because 2 measures appearing together (same measure dimension).

BIDS was complaining when I deploy.

so the working code is this:

CREATE MEMBER CURRENTCUBE.[MEASURES].[Def OTS]
AS NULL,
VISIBLE = 1;

SCOPE ([MEASURES].[Def OTS]);

SCOPE ([ROOT([Dim OTS]);

THIS = ([Dim OTS].[REO],[Measures].[Closing Balance])
/([Measures].[Closing Balance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;

SCOPE([Dim OTS].[REO]);
THIS = ([Measures].[Closing Balance]) / ([Dim OTS].[REO],[Measures].[Closing Balance]);
FORMAT_STRING(This) = "percent";
NON_EMPTY_BEHAVIOR(This) = [Measures].[Closing Balance];
END SCOPE;

END SCOPE;