Filtering out members using a scope statement

• michael.nicolas

  SSC Enthusiast

  Points: 156

  More actions

  October 9, 2014 at 8:18 am

  #148504

  Hi,

  One of our clients requested how he could easily monitor a set of items based on sales.

  I created a dynamic set for him, but the fact that no totals are available I'm wondering how useful this solution could be.

  From what I know grand totals are not possible on a set (or are they?).

  Trying to get a solution with a total, before presenting the solution to the client, I tried combining a calculated measure with scope, but as you can guess by reading this post.., without success.

  I've playing with Adventure Works and this is the calculated measure I created:

  CREATE MEMBER CURRENTCUBE.[Measures].[Top 80% Internet Sales]

  AS NULL,

  VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Internet Sales' ;

  and this is the scope statement:

  SCOPE([Measures].[Top 80% Internet Sales]

  ,[Product].[Product].Members);

  THIS = AGGREGATE(TopPercent([Product].[Product].[Product],80,[Measures].[Internet Sales Amount])

  ,[Measures].[Internet Sales Amount]);

  BACK_COLOR(THIS) = RGB(255,128,255);

  SCOPE([Product].[Product].[Product]);

  THIS = [Measures].[Internet Sales Amount];

  BACK_COLOR(THIS) = RGB(255,128,0);

  END SCOPE;

  END SCOPE;

  and attached are the results of the above scope statement.

  I cannot get the items that made up the 80% of internet sales yet the grand total amount is correct.

  Is what I'm trying to do logical or I'm completely stupid?

  If what I'm trying to do sounds logical, could anyone point me to the right direction?

  Thanks in advance!

  Best Regards,

  Nicolas