Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

SCOPE and calculated members

with 19 comments

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…

Written by Chris Webb

August 30, 2007 at 5:08 pm

Posted in MDX

19 Responses

Subscribe to comments with RSS.

  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
     

    Christian

    August 31, 2007 at 5:22 am

  2. I read your blog but I want to know from you how can I understand it better?

    leena

    September 10, 2007 at 7:34 pm

  3. 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

    Todd

    April 18, 2008 at 8:59 pm

  4. 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.

    Chris

    April 19, 2008 at 12:06 pm

  5. Hi Chriss,

    Firstly i have question about my error.

    i write scope and calculate as you said, but i get same error. his measure is hidden and when client login with their user, it give same error.

    And who is own this topic, i see same issue there: http://dbaspot.com/sqlserver-olap/376341-measures-calculated-members-scope-statement.html

    mehmet

    February 13, 2012 at 9:08 am

  6. 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

    mehmet

    February 13, 2012 at 10:19 am

  7. mehmet

    February 13, 2012 at 10:19 am

  8. 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.

    mehmet

    February 13, 2012 at 2:43 pm

    • Yes, but I still think the error is coming from the MDX Script because it’s trying to scope on a measure that it can’t see. Have you tried using a named set inside your Scope statement in the way I suggested?

      Chris Webb

      February 13, 2012 at 4:16 pm

      • I think error is like you think
        I have tried using a named set inside your issue but maybe use wrong because i didn’t understand well.
        I am try all way that i see on the net.

        mehmet

        February 13, 2012 at 4:28 pm

      • Hi ,

        There are any control to calculate measure or not(or visible=false) with MDX when client login?

        mehmet

        February 14, 2012 at 8:06 am

      • You need to use Perspectives for that, or (I guess) the Personalization Extensions (http://msdn.microsoft.com/en-us/library/cc627355(v=sql.110).aspx).

        Chris Webb

        February 14, 2012 at 8:12 am

      • It will take long time , Maybe we can solve problem with named set , maybe i use wrong.

        mehmet

        February 14, 2012 at 8:49 am

  9. how can i Set ScriptErrorHandlingMode to IgnoreAll ?

    mehmet

    February 13, 2012 at 3:39 pm

    • It’s a property on the cube itself – click on the cube in the BIDS Cube Editor and you’ll see it. However I strongly recommend you don’t use it because it means that you won’t see any errors at all, even ones you don’t want to ignore.

      Chris Webb

      February 13, 2012 at 4:17 pm

      • Ok I understood

        mehmet

        February 13, 2012 at 4:27 pm

  10. Hello Chris,

    I have posted a question in the Microsoft forums w.r.t Scope and calculated measures with a multi select option and here is the link

    http://social.technet.microsoft.com/Forums/en-US/sqlanalysisservices/thread/e0a4975f-8f19-40d0-90ff-4fcca1d301c1

    Kindly request you let me know if this is a bug in SSAS or is it something that I need to re-design my scope statements.

    Please let me know if there are any solutions.

    Ram

    April 27, 2012 at 4:44 am


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

Follow

Get every new post delivered to your Inbox.

Join 3,302 other followers

%d bloggers like this: