1

I have measure which holds dedicated value for each item in hierarchy level. Can you help me how can I design this measure to avoid aggregations for each item across all children items?

I tried to do something like this, but I did something incorrectly, because Generate function in this case returns too many items.

SCOPE 
  (
    [Measures].[Test]
   ,[Organization Structure].[Parent Id].MEMBERS
  );
  This = 
      [Measures].[Core Totals Target]
    - 
      Sum
      (
        Generate
        (
          (EXISTING 
            [Organization Structure].[Parent Id].MEMBERS)
         ,Descendants
          (
            [Organization Structure].[Parent Id].CurrentMember
           ,1
           ,SELF
          )
        )
       ,[Measures].[Core Totals Target]
      );
END SCOPE;

Do you have an idea how to solve this issue?

whytheq
  • 34,466
  • 65
  • 172
  • 267
wbargiel
  • 69
  • 7
  • you have added the tag `mdx` - please add your mdx. – whytheq May 26 '15 at 09:22
  • why is Generate required in that script? You are just generating descendants of hierarchy X, on hierarchy X. – whytheq May 26 '15 at 12:42
  • Please have a look at https://msdn.microsoft.com/en-us/library/ms145526.aspx. Generate allows you to use CurrentMember if in your query you are using a set of members – wbargiel May 26 '15 at 13:20
  • I know as I have experience of `Generate`: `currentmember` used inside generate references each member of the set supplied as first argument . It just seemed strange to me generating a set per member of set X for set X. You explained in your comment to my answer - I did not understand your use case. I have no experience using `SCOPE` so I'll keep out of this one. – whytheq May 26 '15 at 15:38

1 Answers1

0

Not too sure of the purpose of Generate within your script. It looks like it is doing the same as this:

SCOPE 
  (
    [Measures].[Test]
   ,[Organization Structure].[Parent Id].MEMBERS
  );
  This = 
      [Measures].[Core Totals Target]
    - 
      Sum
      (
         Descendants
          (
            [Organization Structure].[Parent Id].CurrentMember
           ,1
           ,SELF
          )
       ,[Measures].[Core Totals Target]
      );
END SCOPE;
whytheq
  • 34,466
  • 65
  • 172
  • 267
  • Thank for the answer, but your example will work for single selection, but in my case it must work for set. Foe example if you will use set of [Organization Structure].[Parent Id] members the error will occurred because CurrentMember won't exist. This it the reason why I tried to use Generate function – wbargiel May 26 '15 at 12:51
  • good point. I will leave my "answer" as it might help others: atleast to know what not to do. Unfortunately my experience of `scope` within `cube scripts` is very limited - sorry I'm not more help. – whytheq May 26 '15 at 12:55