locked
Scoping Two members within a single dimension RRS feed

  • Question

  • Hi,

    I need to scope two members within a dimension shop.

    if i select two members that is Region and TOC within dimension shop no values should be displayed?





    Thanks

    SJ
    Jothi S
    Monday, January 12, 2009 8:07 AM

Answers

  • Hi Prakash,

    This is a good question.  I am not sure scope statement supports multiple-selection set.  Maybe we could hack it by using sort of flags to indicate which member or both members are selected then come up with some calculation to handle the following three senarios.

    1. Only [Region] is selected, return its value
    2. Only [TOC] is selected, return its value
    3. Both are selected, return null

    Let me know if you have a better idea.

    Thanks.

    Tuesday, January 13, 2009 3:49 PM
    Answerer
  • maybe something like

    Scope( {[Shop].[Shop].[Region], [Shop].[Shop].[TOC]});

    This = IIf(Intersect((EXISTING [Shop].[Shop].[Region].members), {[Shop].[Shop].[Region], [Shop].[Shop].[TOC]}).count=2,
                NULL,
                Measures.currentmember;
    End Scope;
    Tuesday, January 13, 2009 4:57 PM
    Answerer
  • if you have your Set of Shop Regions in the Where-Clause i think the query should work
    but well, i haven't tested it so far ...

    where do you select your Shop Regions?
    on rows, columns, where, subselect, ...?
    Tuesday, January 13, 2009 9:58 PM
    Answerer
  •   ok, i have rebuilt your scenario on adventure works:

    MDX Script:
    CREATE DYNAMIC SET CURRETNCUBE.Categories AS {
    EXISTING [Product].[Category].[Category].members
    };

    SCOPE([Product].[Category].&[4]);
        this=IIf(Intersect(Categories,
                                    {[Product].[Category].&[4], [Product].[Category].&[1]}).count=2,
                NULL,
                [Measures].currentmember);
    END SCOPE;

    SCOPE([Product].[Category].&[1]);
        this=IIf(Intersect(Categories,
                                    {[Product].[Category].&[4], [Product].[Category].&[1]}).count=2,
                NULL,
                [Measures].currentmember);
    END SCOPE;

    this works so far if your filter set is in the where clause
    if it is on axis, NULL is displayed for the 2 categories

    give it a try

    greets,
    gerhard
    Wednesday, January 14, 2009 8:35 AM
    Answerer
  • you can simply place the set into the scope directly - this way you do not ned dynamic sets which are not available in AS2005

    SCOPE([Product].[Category].&[1],[Product].[Category].&[4]);
        this=IIf(Intersect(EXISTING [Product].[Category].[Category].members,
                                    {[Product].[Category].&[4], [Product].[Category].&[1]}).count=2,
                NULL,
                [Measures].currentmember);
    END SCOPE;
    Wednesday, January 14, 2009 4:09 PM
    Answerer
  • hi,

    unfortunatelly i dont have AS 2005 in my current environment where i could test it out

    but the syntax of the statement should be valid in 2005 and 2008 and should also return the same results on both servers
    at least i dont know of any changes of SCOPE(), IIf() or Intersect() from 2005 to 2008
    Thursday, January 15, 2009 8:35 AM
    Answerer

All replies

  •  Hi,

    You may try something likethe following:

    Scope( {[Shop].[Shop].[Region], [Shop].[Shop].[TOC]});

    This = NULL;

    End Scope;

    Monday, January 12, 2009 5:52 PM
    Answerer
  • Hi Yang,

    According to your solution, the measure will become NULL, even if a single member in the set is selected(i,e) it scope takes the members in set using OR. But is there a solution to this problem if I want the scope to work only when both the these members are selected.. (i,e) AND withing a set?

    Thanks!
    Tuesday, January 13, 2009 2:55 PM
    Answerer
  • Hi Prakash,

    This is a good question.  I am not sure scope statement supports multiple-selection set.  Maybe we could hack it by using sort of flags to indicate which member or both members are selected then come up with some calculation to handle the following three senarios.

    1. Only [Region] is selected, return its value
    2. Only [TOC] is selected, return its value
    3. Both are selected, return null

    Let me know if you have a better idea.

    Thanks.

    Tuesday, January 13, 2009 3:49 PM
    Answerer
  • Thanks Yang,


    I think it's impossible to do with a scope.

    I dont have any great ideas on this but I believe we could achieve this by using some combination of Filter and Exists! Let me leave it for experts to give a better solution.
    Tuesday, January 13, 2009 4:53 PM
    Answerer
  • maybe something like

    Scope( {[Shop].[Shop].[Region], [Shop].[Shop].[TOC]});

    This = IIf(Intersect((EXISTING [Shop].[Shop].[Region].members), {[Shop].[Shop].[Region], [Shop].[Shop].[TOC]}).count=2,
                NULL,
                Measures.currentmember;
    End Scope;
    Tuesday, January 13, 2009 4:57 PM
    Answerer
  • Hi Gerhard,

    I did some test.  It seems that Scope() statement workes only on per member/slice basis, so the existing set will only contain the current member, thus the count will always be 1.  So it is difficult to get desired value for the set within scope.

    Maybe this can only be done on the MDX level?



    Tuesday, January 13, 2009 9:45 PM
    Answerer
  • if you have your Set of Shop Regions in the Where-Clause i think the query should work
    but well, i haven't tested it so far ...

    where do you select your Shop Regions?
    on rows, columns, where, subselect, ...?
    Tuesday, January 13, 2009 9:58 PM
    Answerer
  • Hi,

    I used a where clause to contain the both member in a set.  My observation is:  the scope() statement applied once for each member in the where clause, so it didn't return NULL for each member, then they were aggregated outside the scope, thus it didn't return NULL.

    So I am thinking that it is only possible to do it using calculated member or MDX directly.
    Tuesday, January 13, 2009 11:32 PM
    Answerer
  •   ok, i have rebuilt your scenario on adventure works:

    MDX Script:
    CREATE DYNAMIC SET CURRETNCUBE.Categories AS {
    EXISTING [Product].[Category].[Category].members
    };

    SCOPE([Product].[Category].&[4]);
        this=IIf(Intersect(Categories,
                                    {[Product].[Category].&[4], [Product].[Category].&[1]}).count=2,
                NULL,
                [Measures].currentmember);
    END SCOPE;

    SCOPE([Product].[Category].&[1]);
        this=IIf(Intersect(Categories,
                                    {[Product].[Category].&[4], [Product].[Category].&[1]}).count=2,
                NULL,
                [Measures].currentmember);
    END SCOPE;

    this works so far if your filter set is in the where clause
    if it is on axis, NULL is displayed for the 2 categories

    give it a try

    greets,
    gerhard
    Wednesday, January 14, 2009 8:35 AM
    Answerer
  • Gerhard Brueckl said:

      ok, i have rebuilt your scenario on adventure works:

    MDX Script:
    CREATE DYNAMIC SET CURRETNCUBE.Categories AS {
    EXISTING [Product].[Category].[Category].members
    };

    SCOPE([Product].[Category].&[4]);
        this=IIf(Intersect(Categories,
                                    {[Product].[Category].&[4], [Product].[Category].&[1]}).count=2,
                NULL,
                [Measures].currentmember);
    END SCOPE;

    SCOPE([Product].[Category].&[1]);
        this=IIf(Intersect(Categories,
                                    {[Product].[Category].&[4], [Product].[Category].&[1]}).count=2,
                NULL,
                [Measures].currentmember);
    END SCOPE;

    this works so far if your filter set is in the where clause
    if it is on axis, NULL is displayed for the 2 categories

    give it a try

    greets,
    gerhard


    Thanks Gerhard,

    I have 2 questions on your solution

    1. Why can't we use the entire list of products in place of the set that you have created? Is this just for improving performance as we are comparing it with less number of products?

    2. Why do we need 2 scopes? Can we specify the scope as

    SCOPE([Product].[Category].&[1],[Product].[Category].&[4]);
        this=IIf(Intersect(Categories,
                                    {[Product].[Category].&[4], [Product].[Category].&[1]}).count=2,
                NULL,
                [Measures].currentmember);
    END SCOPE;

    cheers!
    Wednesday, January 14, 2009 9:42 AM
    Answerer
  • 1)
    we need to use EXISTING to get only the Categories in the current (Query) Scope
    without EXISTING the WHERE-clause would have no effect on our calculations

    2)
    yes, this would also work (tested it)
    while creating this solution i had an error before thats why i was splitting it up because i thought it would solve the error (which was not the case)
    after solving the real error i just did not convert it back to 1 SCOPE statement :)

    Wednesday, January 14, 2009 9:56 AM
    Answerer
  • Gerhard Brueckl said:

    1)
    we need to use EXISTING to get only the Categories in the current (Query) Scope
    without EXISTING the WHERE-clause would have no effect on our calculations

    2)
    yes, this would also work (tested it)
    while creating this solution i had an error before thats why i was splitting it up because i thought it would solve the error (which was not the case)
    after solving the real error i just did not convert it back to 1 SCOPE statement :)



    1) Oops.. I am sorry, that was a dumb question! thanks for your crystal clear explanation.

    2) I expected this answer :)

    Wednesday, January 14, 2009 10:02 AM
    Answerer
  • Hi Gerhard,

    Here you created Dynamic Set, which is a AS2008 feature, I am wondering if using a reglar set in AS2005, should the script still work?

    Thanks,

    Yongli
    Wednesday, January 14, 2009 4:01 PM
    Answerer
  • you can simply place the set into the scope directly - this way you do not ned dynamic sets which are not available in AS2005

    SCOPE([Product].[Category].&[1],[Product].[Category].&[4]);
        this=IIf(Intersect(EXISTING [Product].[Category].[Category].members,
                                    {[Product].[Category].&[4], [Product].[Category].&[1]}).count=2,
                NULL,
                [Measures].currentmember);
    END SCOPE;
    Wednesday, January 14, 2009 4:09 PM
    Answerer
  • Hi Garhard,

    Thanks for your time.  I could not get this working on AS2005 in my cube.  As I stated before, the existing function only return one member in the scope statement (It will go through the scope statment twice total, one for each member), maybe this is the problem in AS2005.

     

    Wednesday, January 14, 2009 7:13 PM
    Answerer
  • hi,

    unfortunatelly i dont have AS 2005 in my current environment where i could test it out

    but the syntax of the statement should be valid in 2005 and 2008 and should also return the same results on both servers
    at least i dont know of any changes of SCOPE(), IIf() or Intersect() from 2005 to 2008
    Thursday, January 15, 2009 8:35 AM
    Answerer