I've got a solution where I need to apply some adjustment to a measure for a given scope:
Scope ([Organization].[Organization].&[13], [Product].[Division Temperature].&[Frozen], [Measures].[FOB]); This = [Measures].[FOB] - 1.25; End Scope;This works fine, but given that this solution uses role based security to limit access to specific dimension data, there are some roles that don't have access to the [Organization].[Organization].&[13] and the MDX fails parsing.
Is there any way to make a conditional MDX that I can use to only apply the given MDX SCOPE when the role has access to the dimensions used in the statement ?
Trond
I guess it is safe to ignore the entire SCOPE for the users who have no access to Organization 13. One quick way to do it, is to change the ScriptErrorHandlingMode to IgnoreAll (although this will start ignoring all other errors). More details here: http://www.sqljunkies.com/WebLog/mosha/archive/2005/05/02/13315.aspx
HTH,
Mosha (http://www.mosha.com/msolap)
|||Note that ScriptErrorHandling has some limitations (in RTM and SP1) whereby certain kinds of script errors are not ignored. This will be fixed in SP2.
|||Thank you for the input - ScriptErrorHandling did indeed work but I ended up with a different solution in the end.
I guess I was so into using SCOPE that I didn't see any other tool/way to do it. Once I took a new look at it, I ended up with not using SCOPE at all, but rather a CASE:
Case When [Organization].[Organization].CurrentMember.Name = "Norway" And [Product].[Division Temperature].CurrentMember.Name = "Frozen" Then ([Measures].[Net Sales] / [Measures].[Volume]) - 1.25 Else ([Measures].[Net Sales] / [Measures].[Volume]) EndMemo to self: Read up on MDX before the next SQL 2005 project !
|||
Another approach that appears to work (at least in Adventure Works) is to first test the member using IsError(), like:
Create Set CurrentCube.[OrgSet] As iif(IsError(StrToMember("[Organization].[Organization].&[13]")),{}, {[Organization].[Organization].&[13]}); IF [OrgSet].Count > 0
THEN ([Measures].[FOB], [Product].[Division Temperature].&[Frozen],
[OrgSet]) = [Measures].[FOB] - 1.25 END IF; |||
Please note, however, that this solution is far less efficient then the one using SCOPE. Likewise, Deepak's suggestion of using IF/END IF is also less efficient.
Probably you would get the best performance by combining Deepak's idea of building named set with SCOPEs, i.e.
CREATE SET orgs = ...;
SCOPE (orgs);
...;
END SCOPE;
|||But I couldn't figure out how to build the set, such that the assignment wouldn't update any cells when the targeted member is not accessible to the role/user. The empty set that I substituted for the secured dimension seemed not to narrow the scope, so all accessible members got updated...|||Deepak, the empty set in the SCOPE translates to the empty subcube, and therefore no cells are affected by the assignmets inside this SCOPE. Therefore, since for the user with dimension security the named set will end up empty - it will behave as that portion of MDX Script didn't exist.|||Mosha, thanks for clarifying that - looks like my version didn't work because I also included the measure in the scope, like:
>>
Scope({[Measures].[FOB]}, [OrgSet]);
This = [Measures].[FOB] - 1.25; End Scope; >> Instead, if only the Org set is specified in the scope, then the empty set defines an empty subcube. So '({[Measures].[FOB]}, {})' isn't empty, but '({})' is:>> Scope([OrgSet]); ([Measures].[FOB]) = [Measures].[FOB] - 1.25; End Scope; /* OR */
Scope([Measures].[Order Quantity]);
Scope([OrgSet]);
this = [Measures].[Order Quantity] * 2;
End Scope;
End Scope;
>>
);