locked
Scope Question RRS feed

  • Question

  • Hi I am experimenting with Scope statements with a project I am working on.

    In particular I have a user hierarchy which when then select lower the lowest level I want it to produce a different value.

    I've replicated this in adventure works.

    scope ([Product].[Category].[Category].members, [Measures].[Internet Sales Amount] );
        this = [Measures].[Internet Sales Amount];
    end scope;
    
    scope ([Product].[Subcategory].[Subcategory].members, [Measures].[Internet Sales Amount]  );
        this = [Measures].[Internet Sales Amount];
    end scope;
    
    scope ([Product].[Product].[Product].members, [Measures].[Internet Sales Amount] );
        this = [Measures].[Internet Tax Amount];
    end scope;

    However, when going through the hierarchy the internet sales amount always equals the internet tax amount.

    If I create a null measure such as and then apply it to the scope it works

    CREATE MEMBER CURRENTCUBE.MEASURES.UseAsDefaultMeasure 
     AS NULL, 
    VISIBLE = 1;
    
    scope ([Product].[Category].[Category].members, MEASURES.UseAsDefaultMeasure  );
        this = [Measures].[Internet Sales Amount];
    end scope;
    
    scope ([Product].[Subcategory].[Subcategory].members, MEASURES.UseAsDefaultMeasure  );
        this = [Measures].[Internet Sales Amount];
    end scope;
    
    scope ([Product].[Product].[Product].members, MEASURES.UseAsDefaultMeasure  );
        this = [Measures].[Internet Tax Amount];
    end scope;
    

    Why is this the case?

    Also is it possible to change the scope so you can select multi members?

    Regards

    Monday, August 10, 2015 8:34 AM

Answers

  • The problem here is that non-calculated measures always re-aggregate after you apply a scope to a lower level. So once you override the bottom level the new values aggregate up through the other levels. You can stop this behaviour by applying a FREEZE statement to the parent level(s) before assigning to the lower level. But I tend to avoid using FREEZE if I can as it has been know to have performance issues. I usually try to use a calculated measure like your second approach in cases like this.

    http://darren.gosbell.com - please mark correct answers

    Tuesday, August 11, 2015 10:50 PM

All replies

  • Hi aivoryuk,

    In this scenario, you use scope() statement in order to show different fact data for different level members. However, it shows same measures when slicing with hierarchy. Right?

    In this scenario, you apply a tuple with existing measure in your three separated scope() statements. When slicing on hierarchies, it will not determine scope each other so that these three scope() statement will conflict and will only keep one measure for all members appearing on rows. If you use a custom measure, it has no fact data related to any members. So when you apply those scope() statement, it works as applying condition on this custom measure.

    So for your requirement, the more proper way is using nested scope() statement.

    scope([Measures].[Internet Sales Amount]);
    
    scope ([Product].[Category].[Category].members);
        this = [Measures].[Internet Sales Amount];
    end scope;
    
    scope ([Product].[Subcategory].[Subcategory].members);
        this = [Measures].[Internet Sales Amount];
    end scope;
    
    scope ([Product].[Product].[Product].members);
        this = [Measures].[Internet Tax Amount];
    end scope;
    
    end scope;
    

    Regards,


    Simon Hou
    TechNet Community Support


    Tuesday, August 11, 2015 3:33 PM
  • Hi Simon

    I tried your suggestion and it was still doing what I had posted in the first code snippet so it doesn't seem to work.

    It also possible to get scope statements to work with multi select members (excel, performancepoint etc)

    Regards

    Tuesday, August 11, 2015 6:33 PM
  • The problem here is that non-calculated measures always re-aggregate after you apply a scope to a lower level. So once you override the bottom level the new values aggregate up through the other levels. You can stop this behaviour by applying a FREEZE statement to the parent level(s) before assigning to the lower level. But I tend to avoid using FREEZE if I can as it has been know to have performance issues. I usually try to use a calculated measure like your second approach in cases like this.

    http://darren.gosbell.com - please mark correct answers

    Tuesday, August 11, 2015 10:50 PM