SCOPE and calculated members

This post on the MSDN forum:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2064643&SiteID=1

…reminded me of something interesting I found out a few months ago. It seems that whereas you can’t mix regular and calculated measures in a set used in the SCOPE statement, you can rewrite the assignment to avoid using SCOPE and do a direct assignment instead. So, for example, if you were trying to assign to a regular measure and a calculated measure using a SCOPE statement like this:

SCOPE({[Measures].[RegularMeasure],[Measures].[CalculatedMeasure]});
    this=1;
END SCOPE;

You would get the following error:

A set has been encountered that cannot contain calculated members.
MdxScript(Cube1) (line, col) A set has been encountered that cannot contain calculated members.
The END SCOPE statement does not match the opening SCOPE statement.
MdxScript(Cube1) (line, col) The END SCOPE statement does not match the opening SCOPE statement.

You could rewrite the assignment as follows using two SCOPEs:

SCOPE([Measures].[RegularMeasure]);
    this=1;
END SCOPE;

SCOPE([Measures].[CalculatedMeasure]);
    this=1;
END SCOPE;

…but this is clearly a pain, as you’re duplicating your assignment logic. What you can do instead is this:

({[Measures].[RegularMeasure],[Measures].[CalculatedMeasure]})=1;

Not as easy to read as using SCOPE, I know, especially if you’re doing something complex, but at least it works! Now I wonder why SCOPE has this problem? Probably something worth opening an issue on Connect about…

19 thoughts on “SCOPE and calculated members

  1. Chris,
     
    I couldn\’t agree more about this.  As you mentioned, it is especially annoying if doing something complex.  For example, lots of cell assignments within the single scope statement.  When adding a new calculated measure that requires these assignments, it\’s nice to specify it in one place (the scope statement) and have it inherit the logic.  I have even found myself doing the cardinal sin of "copy and paste reuse" by creating another scope statement for the new measure in order to reuse (ahem, copy and paste) the assignments.  I\’ve seen this contribute considerably to MDX-script unmanageability (I suppose it keeps us consultants in a job when no one else can figure out what on earth our MDX is doing).

     
    If there is a sound technical reason for disallowing both base and calculated measures in a single scope statement, it would be nice to at least allow reuse of logic.  Maybe something in between code snippets and using statements would suffice.  If we could at least put a placeholder at the start of the cell assignments and another at the end, we could then refer back to it from subsequent scope statements.  I’m sure this would be very easy for the AS team to implement as it would effectively just copy and paste the assignments as required when executing the MDX script.

     
    Christian
     

  2. Greetings Chris,I\’m just curious if you\’ve found any more on this topic – mostly why it\’s an issue at all.Thanks,Todd

  3. No, I haven\’t actually – I\’ve asked but never had a clear answer. Strange. I also came across a scenario where using this technique to scope across multiple measures lead to much worse performance than separate SCOPEs, but never found out why.

  4. Actually , it is a calculated measure, but for hide it from client , i created a new named calculation in DSV then i create scope with this code
    SCOPE({[Measures].[CPC GBP]});
    this=[Measures].[Total Spent GBP]/[Measures].[Clicks];
    end scope;

    finally i add it in denied memeber set that in role >{[Measures].[Spent], [Measures].[Spent GBP],[Measures].[CPC GBP]}

    it is workin for first 2 measures because they are normal measure, but last one is calculate member.

    FYI > If i login with current user , scope working and i get [CPC GBP] value true, but when i select a role then browse cube, it say
    i.e:
    MdxScript(Cube1) (line, col) A set has been encountered that cannot contain calculated members.
    The END SCOPE statement does not match the opening SCOPE statement.
    MdxScript(Cube1) (line, col) The END SCOPE statement does not match the opening SCOPE statement.
    [Measures].[CPC GBP] not fount

  5. I don’ t create measure , Firstly i create a named calculation name is > [CPC GBP] then i use this for vlue of this measure, SCOPE({[Measures].[CPC GBP]});
    this=[Measures].[Total Spent GBP]/[Measures].[Clicks];
    end scope;

    This code working good if don’t select any role.

    But when i loggin with a role, it giva error. In the each role i Denied this measure ([CPC GBP])

    Your example looking some different.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s