none
Struggling with MDX SCOPE

    Question

  • Hello All,

    I am trying to change the value of a calculated measure from budget to actuals or vice versa depending on the value of three dimension members by using SCOPE in the cubes MDX.

    The scope of the measure has to change for a combination of these three dimensions:
    1: Version (hard coded scenario dim with values budget, actual and difference)
    2: Time: Quarter
    3: Budget name

    Following MDX works fine if i only scope dimension 1: Version. The measure Amount is showing budget or actuals depending on the Scenario version dimension.

    CREATE MEMBER CURRENTCUBE.Measures.Amount as Measures.[actual_amount];
    
    MDX script:
    SCOPE ([Scenario].[Scenario].[Budget], Measures.Amount);
    
      this = Measures.[budget_amount];
    
    END SCOPE;


    When I try to introduce dimensions 2 and  / or 3 i never get to see a budget value again (the scenario dimension doesn't have values on budget any more. I tried this:

    SCOPE ([Scenario].[Scenario].[Budget], [Time].[Quarter.&[1],[Budget].[Budget Name].&[1], Measures.Amount);
    
      this = Measures.[budget_amount];
    
    END SCOPE;
     




    -- same for Quarter 2 where the measure has to show actuals
    SCOPE ([Scenario].[Scenario].[Budget], [Time].[Quarter.&[2],[Budget].[Budget Name].&[1], Measures.Amount); this = Measures.[actual_amount]; END SCOPE;
    I supposed this approach would work but it doesn't show any budget values. Next I tried to nest the scopes:
    SCOPE ([Scenario].[Scenario].[Budget];
    
      this = Measures.[budget_amount];
    SCOPE ([Scenario].[Scenario].[Budget], [Time].[Quarter.&[2],[Budget].[Budget Name].&[1], Measures.Amount);
    
      this = Measures.[actual_amount];
    
    END SCOPE;
    END SCOPE;
    

     

    This does show budgets but actuals do not show. The inner scope doesn't seem to have effect.

    I think I am missing some basic understanding of the SCOPE statement here. Is there anyone who can help me out?

    Many Thanks!

    Tuesday, June 02, 2009 11:49 AM

Answers

  • ok, if you use
    SCOPE ([Scenario].[Scenario].[Budget], [Time].[Quarter.&[1],[Budget].[Budget Name].&[1], Measures.Amount);
      this = Measures.[budget_amount];
    END SCOPE;

    do you have a [budget_amount] for ([Scenario].[Scenario].[Budget], [Time].[Quarter].&[1],[Budget].[Budget Name].&[1])
    or is your budget_amount only linked to for example [Budget].[Budget Name].&[2] and not to [Budget].[Budget Name].&[1]

    if this is the case you would have to use
    SCOPE ([Scenario].[Scenario].[Budget], [Time].[Quarter.&[1],[Budget].[Budget Name].&[1], Measures.Amount);
      this = (Measures.[budget_amount],[Budget].[Budget Name].&[2]);
    END SCOPE;

    hth,
    gerhard
    - www.pmOne.com -
    Tuesday, June 02, 2009 3:43 PM

All replies

  • hi,

    you have an error in your syntax - you missed the ] after [Time].[Quarter

    SCOPE ([Scenario].[Scenario].[Budget], [Time].[Quarter].&[2],[Budget].[Budget Name].&[1], Measures.Amount);
      this = Measures.[actual_amount];
    END SCOPE;

    the statements as they are should be correct

    greets,
    gerhard
    - www.pmOne.com -
    Tuesday, June 02, 2009 12:03 PM
  • Hello Gerhard,

    Thanks for your reply. The syntax error is not in my original code (only because of some editing in the code block after pasting from the cube).

    I also expected the statement would work but the scenario version dimension would not show the budget member anymore thus showing no results at all (hide empty behaviour i suppose). I am 100% sure the Time, Budget name and the actual_amount have results for the defined subcube.
    Tuesday, June 02, 2009 12:29 PM
  • are budget_amount and actual_amount in different measuregroups?
    how are they linked to the dimension - same granularity?

    greets,
    gerhard
    - www.pmOne.com -
    Tuesday, June 02, 2009 1:06 PM
  • Yes, budgets and actuals are in different measuregroups sharing some dimensions. The grain is different.
    Tuesday, June 02, 2009 2:08 PM
  • ok, if you use
    SCOPE ([Scenario].[Scenario].[Budget], [Time].[Quarter.&[1],[Budget].[Budget Name].&[1], Measures.Amount);
      this = Measures.[budget_amount];
    END SCOPE;

    do you have a [budget_amount] for ([Scenario].[Scenario].[Budget], [Time].[Quarter].&[1],[Budget].[Budget Name].&[1])
    or is your budget_amount only linked to for example [Budget].[Budget Name].&[2] and not to [Budget].[Budget Name].&[1]

    if this is the case you would have to use
    SCOPE ([Scenario].[Scenario].[Budget], [Time].[Quarter.&[1],[Budget].[Budget Name].&[1], Measures.Amount);
      this = (Measures.[budget_amount],[Budget].[Budget Name].&[2]);
    END SCOPE;

    hth,
    gerhard
    - www.pmOne.com -
    Tuesday, June 02, 2009 3:43 PM
  • Hi Gerhard,

    Still no values so far... I even tried the following with hardcoded value 11111 but still nothing (empkty result) on the coordinate of the dimensions specified.
    SCOPE ([Scenario].[Scenario].[Budget], [Time].[Quarter].&[2],[Budget].[Budget Name].&[1], Measures.Amount);
      this = 11111;
    END SCOPE;
    


    Wednesday, June 03, 2009 8:26 AM
  • if you write an mdx like

    SELECT
    ([Scenario].[Scenario].[Budget], [Time].[Quarter].&[2],[Budget].[Budget Name].&[1], Measures.Amount) ON 0
    FROM [<Cube>]

    what is returned?

    do you get one empty column?
    or no columns at all?


    - www.pmOne.com -
    Wednesday, June 03, 2009 8:34 AM
  • Hello Gerhard,

    I have managed to get it working in many cases because of your post earlier with this example:

    SCOPE ([Scenario].[Scenario].[Budget], [Time].[Quarter.&[1],[Budget].[Budget Name].&[1], Measures.Amount);
      this = (Measures.[budget_amount],[Budget].[Budget Name].&[2]);
    END SCOPE;

    Appearantly i tried to assign a a measure which did not exist in the scope at all.

    I still have many problems with scopes and have sed a different post on the forum. Thanks again for your help so far.
    Thursday, June 04, 2009 3:54 PM