locked
Entering MDX Scope command in SSAS calculations tab generates error RRS feed

  • Question

  • I have created and processed an SSAS database using SQL Server 2016. Now, I am trying to add some time calculations, I have created a "Date Calculation" dimension with values like YTD, PY YTD, Selected Date, etc.  To make it work I need to add some code that contains a Scope statement.  I can't even type the word SCOPE into the script window, it gets underlined in red and that is the end of the story, can't run anything.  What am I doing wrong? 

    Here's the example of the code I'm entering (first occurrence of the word SCOPE underlines in red in script window.):


    SCOPE ([DateOfNote].[Hierarchy].MEMBERS);
    [Date Calculation].[Date Calculation].[YTD] =
    AGGREGATE
    (
        {[Date Calculation].[Date Calculation].[Selected Date]} *
         PERIODSTODATE
         (
           [DateofNote].[Hierarchy].[Hierarchy Year],
           [DateofNote].[Hierarchy].CURRENTMEMBER
         )
    );
    [Date Calculation].[Date Calculation].[PY YTD] =
    AGGREGATE
    (
       {[Date Calculation].[Date Calculation].[Selected Date]} *
       PERIODSTODATE
       (
          [DateofNote].[Hierarchy].[Hierarchy Year],
          PARALLELPERIOD
          (
             [DateofNote].[Hierarchy].[Hierarchy Year],
             1,
             [DateofNote].[Hierarchy].CURRENTMEMBER
       )
    )
    );
    END SCOPE;
    SCOPE ([DateofNote].[Hierarchy].[All]);
    [Date Calculation].[Date Calculation].[YTD] = NULL;
    [Date Calculation].[Date Calculation].[PY YTD]=NULL;
    END SCOPE;
    [Date Calculation].[Date Calculation].[YTD Chg]=[Date Calculation].[Date Calculation].[YTD]–
    [Date Calculation].[Date Calculation].[PY YTD];
    [Date Calculation].[Date Calculation].[YTD Chg %] =
    IIF
    (
    [Date Calculation].[Date Calculation].[PY YTD] = 0,
    NULL,
    ([Date Calculation].[Date Calculation].[YTD] – [Date Calculation].[Date Calculation].[PY YTD]) /
    [Date Calculation].[Date Calculation].[PY YTD]
    );
    FORMAT_STRING([Date Calculation].[Date Calculation].[YTD Chg %]) = “#,0.0%”;
    //Create current year and month in scripts:
    CREATESET CURRENTCUBE.[Current Year]
    AS {(strtomember(“[DateofNote].[Hierarchy].[Hierarchy Year].[“+ cstr(year(now()))+”]”))}, DISPLAY_FOLDER = 'Named Sets' ;
    CREATESET CURRENTCUBE.[Current Month] AS
    TAIL
    (
    NONEMPTY
    (
    [DateofNote].[Hierarchy].[Month Name Year],
    [Measures].[Fact Notes Simple Test]
    )
    );

    Tuesday, April 4, 2017 2:58 PM

Answers

  • Hi John,

    Thanks for your response.

    In your scenario, please replace" SCOPE([Date].[Year - Quarter - Month - Date].MEMBERS))  "with  " SCOPE([Date].[Year - Quarter - Month - Date].MEMBERS); " .

    Please refer to syntax of SCOPE below:

    SCOPE(Subcube_Expression);   
       [ MDX_Statement ]; 
    END SCOPE ;


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, April 12, 2017 1:07 AM

All replies

  • Hi,

    Thanks for your question.

    If i understand your correctly, you are creating a [Date Calculation] dimension in SSAS calculations tab, right?

    we can not create a dimension in SSAS calculations tab, we can only create calculated member, calculated measure, named set in it.

    In your scenario, you may add these calculated member(YTD, PY YTD, Selected Date, etc.) under dimension [DateofNote].


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by alexander fun Wednesday, April 5, 2017 9:23 AM
    Wednesday, April 5, 2017 3:08 AM
  • Hi Wilson,
    Thanks for your reply but I am not trying to create the dimension.  That I have done in the normal way and it seems to be OK.  I'm basically trying to do what is shown here:

    https://devinknightsql.com/2014/08/14/mdx-time-calculations-built-to-scale/

    The problem is when I get to the part that says "Hit the new script button to get started."  I hit the button, start to type, immediately get an error as soon as I type the word Scope.  Doesn't like Scope for some reason.  I'm sure it's something simple, but I don't see what.

    See attached images.  (BTW, I have changed Date Calculations to DateCalcs to match devinknightsql's example more closely.)

    Thanks!

    Here is the dimension:

    Wednesday, April 5, 2017 3:44 PM
  • Hi,

    Thanks for your response.

    In this scenario, [DateOfNote].[Hierarchy].MEMBERS include [DateOfNote].[Hierarchy].[All] as well as all the children,the scope might have been meant to override the value for the All member only,please try to exclude [ALL] from the scope.

    See my sample scope below:
    Scope(DESCENDANTS([DateOfNote].[Hierarchy],,AFTER))
    For more information, please refer to Scope Problems with MDX Calculated Members .

    If my reply is helpful to you, please kindly mark it as an answer. By doing so, it will benefit all community members who are facing similar issues. Your contribution is highly appreciated. 

    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com



    Thursday, April 6, 2017 6:14 AM
  • I guess I have not made it clear, I cannot even type in the word "Scope"

    As soon as I get to the letter e, I get a red underline.

    I did enter your suggested statement, and red line under Scope as always.

    There must be something wrong environmentally.  Could I be typing into the in the wrong window?

    See attached image.

    Similarly,

    Thanks for any help you can provide!

    John

    Thursday, April 6, 2017 2:55 PM
  • Hi John,

    Thanks for your response.

    As i can see from your attached image, you are in "Form view", that's why you can not type a word.

    Please click on the "Script View" button ,then you will be able to edit your scope statement.

    If my reply is helpful to you, please kindly mark it as an answer. By doing so, it will benefit all community members who are facing similar issues. Your contribution is highly appreciated.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Friday, April 7, 2017 7:20 AM
  • I made sure I was in script view, still it does not work. I can do other calculations, which work.  But the word "Scope" seems to not be recognized. I tried putting directly before and after the word Calculate; after all my Create Member statements, but no matter where I put it, an error as soon as I enter the word Scope.  See attached screen shot. My "Create Member" statements are working fine, btw.

    Thanks for sticking with me, I need to get this resolved to move ahead in my project.

    See the below image: (see the word scope at the bottom)

    Friday, April 7, 2017 8:29 PM
  • Hi John,

    Thanks for your response.

    It is a normal behavior. After you finished all the scope statement, it will disappear automatically.
    SCOPE(Subcube_Expression);   
       [ MDX_Statement ] ; 
    END SCOPE ;

    If my reply is helpful to you, please kindly mark it as an answer. By doing so, it will benefit all community members who are facing similar issues. Your contribution is highly appreciated.

    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, April 7, 2017 10:32 PM
  • The underlining never disappears and it is not possible to process the cube. I have entered some custom measures and they worked.  As soon as I type the word SCOPE, it's all over.  It will be a shame if I'm not able to use this statement because the alternative is not going to be good.  Please see image below, SCOPE is at the bottom. (Can you supply a screenshot of a working SCOPE statement? )


    Tuesday, April 11, 2017 6:30 PM
  • Hi John,

    Thanks for your response.

    In your scenario, please replace" SCOPE([Date].[Year - Quarter - Month - Date].MEMBERS))  "with  " SCOPE([Date].[Year - Quarter - Month - Date].MEMBERS); " .

    Please refer to syntax of SCOPE below:

    SCOPE(Subcube_Expression);   
       [ MDX_Statement ]; 
    END SCOPE ;


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, April 12, 2017 1:07 AM
  • OK, it processed. The Scope remains underlined in red, even after processing, but it did successfully deploy and process.

    Thanks for your help!

    • Proposed as answer by essenbati Thursday, March 12, 2020 2:22 PM
    Wednesday, April 12, 2017 2:55 PM