Not All Calculated Members Are Born Equal

An interesting issue popped up yesterday regarding the calculated member syntax. I had to multiply negative amounts in a financial cube for certain account categories that had negative amounts. The cube also had a Many:Many relationship between financial accounts and account groups which may be related to the issue. So, I had the following scope assignment:

CREATE
MEMBER
CURRENTCUBE.[Multiplier] /* Old Style*/

AS [Financial Account].[Financial Account Hierarchy].CurrentMember.Properties(“Multiplier”, TYPED),

VISIBLE = 0;

Scope

(

[Financial Account].[Financial Account Hierarchy].Members

);

this = [Financial Account].[Financial Account Hierarchy].CurrentMember * [Measures].[Multiplier];

End
Scope;

The Multiplier calculated member returns the Multiplier property of the current account member, which could be 1 or -1 (if the account has to be negated). Then, the scope assignment overwrites all measures for the current member. As simple as it is, the assignment didn’t work and the account amounts didn’t get converted although the Multiplier calculated member would return the correct number. I stripped out the entire cube script to only these two script definitions but I couldn’t make it to work. After some research, I came to this interesting discussion between Chris Webb and Read Jacobsen, which made me think that the issue probably has something to do with the solve order of the calculated member. As it turned out, the new style has a higher pass so the following fixed the issue:

CREATE
HIDDEN [Multiplier] = [Financial Account].[Financial Account Hierarchy].CurrentMember.Properties(“Multiplier”, TYPED); /* New Style*/

As Chris suggested, I tend to believe that it’s a bug especially given the fact that I’ve recently logged another nasty calculated member-related issue, where scope assignments would produce wrong results if the calculated members with the old style definitions are hidden.