Filter Dimensionmembers with OR on different Levels

• Michael Torsson

  SSC Eights!

  Points: 881

  More actions

  December 10, 2008 at 4:47 am

  #196901

  Hi

  Is there a way to filter a Dimension on members on different hierarchie-levels?

  Example

  ProductArea

  -> PurchaseHeadmanager

  ProductCategory

  -> ProductSubcategory

  PurchaseManager

  -> ProductNumber

  ProductDescription

  I need to populate a filtered set with this information based on the name of person

  TSQL: WHERE PurchaseHeadmanager = "NAME" OR PurchaseManager = "NAME"

  Thanks

  Michael

• denglishbi

  SSCrazy Eights

  Points: 8831

  More actions

  December 10, 2008 at 5:10 am

  #910225

  I believe you are just going to want to create a named set or at least a set of dimension members that you would like to reference from your dimension hierarchy and these could be on different levels. Here would be a simply example from Adventure Works (where the hierarchy is Product Categories with the following levels -- Category-Subcategory-Product):

  select {} on 0,

  {[Product].[Product Categories].[Category].&[4],

  [Product].[Product Categories].[Product].&[310]} on 1

  from [adventure works]

  I guess I am not sure how you are trying to generate this filter or use it, so that might be the next area that you will need to take a look at, but you can definitely get a list based off different levels.

  ----------------------------------------------------------------------------------------
  Dan English - http://denglishbi.wordpress.com

• timaroundtheglobe

  Grasshopper

  Points: 12

  More actions

  December 12, 2008 at 4:58 am

  #911561

  Hi, you can use the mdx descendants expression to retrieve children from multiple levels in a cube dimension

  http://msdn.microsoft.com/en-us/library/ms146075.aspx

• Michael Torsson

  SSC Eights!

  Points: 881

  More actions

  December 13, 2008 at 1:21 am

  #912013

  Thanks both of you

  I will try it next week.

  /Michael

• denglishbi

  SSCrazy Eights

  Points: 8831

  More actions

  December 13, 2008 at 5:18 am

  #912027

  Sounds good. In my example I didn't actually reference a named set, but it is a set of members from the hierarchy at different levels. Guess I am not sure how you would use descendants unless that is also used in a named set perhaps. You can create the set on the fly in your query or within the cube. In the query it would look like this:

  with set [MySet] as {[Product].[Product Categories].[Category].&[4],

  [Product].[Product Categories].[Product].&[310]}

  select {} on 0,

  [MySet] on 1

  from [adventure works]

  ----------------------------------------------------------------------------------------
  Dan English - http://denglishbi.wordpress.com

• Michael Torsson

  SSC Eights!

  Points: 881

  More actions

  January 21, 2009 at 1:31 pm

  #930679

  Hi

  Thanks for your input, it almost worked with

  union (Descentants(PurchaseHeadmanager), Descendants(PurchaseManager))

  ProductArea

  -> PurchaseHeadmanager

  ProductCategory

  -> ProductSubcategory

  PurchaseManager

  -> ProductNumber

  ProductDescription

  My problem is still that the same Purchaseheadmanager and the Purchasemanager can exists multiple times in the same level

  Kalle Andersson

  - Fruit

  - Apples

  - Stina Andersson

  - Golden delicious

  - Ingrid Marie

  - Anders Karlsson

  - Royal Gala

  - Orange

  -Stina Andersson

  - Brand 1

  - Anders Karsson

  - Brand 2

  Stina Andersson

  - Bread

  - Toast

  - Hans Hansson

  - Fiber

  - Bengt Bengtsson

  - Regular White

  If I make a request for Stina Andersson, I want this

  Stina Andersson

  - Bread

  - Toast

  - Hans Hansson

  - Fiber

  - Bengt Bengtsson

  - Regular White

  - Apples

  - Stina Andersson

  - Golden delicious

  - Ingrid Marie

  - Orange

  -Stina Andersson

  - Brand 1

  My Union took the the first Stina Andersson i the two levels and made a Union, skipping the orange part i this example.

  Hope that this example can bring some light to the problem to were Iam thinking wrong.

  regards

  Michael

• Michael Torsson

  SSC Eights!

  Points: 881

  More actions

  January 21, 2009 at 3:47 pm

  #930770

  Hi

  I solved my problem with filters in my descendants funcion calls

  union (Descentants(Filter(PurchaseHeadmanager)), Descendants(Filter(PurchaseManager)))

  Before I applied the Descendantsfunction on the dimensionmember, when I actually needed a set of members because the same member name could exist mutiple times.

  Thanks for your time, I will be back 😉

  /Michael