Ranging a dimension on two levels in an MDX query

• Sam Peascod

  SSCrazy

  Points: 2497

  More actions

  September 9, 2007 at 6:42 pm

  #118556

  I have an Items dimension which has a product category hierarchy. The hierarchy has three levels I'm interested in:

  1. Category Group

  2. Category

  3. Item Code

  I want to give users two pairs of report parameters: start/end category and start/end item code. Basically I want the cross join of two ranges from the same dimension. Obviously I can't cross join the same dimension twice. How can I do this?

• Dave Balsillie

  SSCertifiable

  Points: 5589

  More actions

  September 11, 2007 at 6:18 am

  #732752

  I believe you're talking about doing this in MSRS or a similiar tool. As well, you haven't specified any conditions / contstraints on the selections, so I'll guess a little bit.

  For your parameters , you'll want 4 queries set up. (I'm only going to list the set definitions.) For the first query, it'll be something like:

      set FirstCat as [Products].[Category].Members

  The second set, you'll probably want to make sure that the available list item are all >= to the selected value so far

      Set SecondCat as SubSet( [Products].[Category].Members, StrToMember(@myFirstCat).Ordinal ) 

  I assume, you'd like to constrain the item codes, first by the myFirstCat, then by mySecondCat:

     Set FirstItem as Decendants( StrToMember(@myFirstCat), [Item Code], Self)

    and

    Set SecondItem as Decendants( StrToMember(@mySecondCat), [Item Code], Self)

  Then, for your report query, use a UNION statement

  Set ProductList UNION( StrToMember(@myFirstCat):StrToMember(@mySecondCat)

                                , StrToMember(@myFirstItem):StrToMember(@mySecondItem)

                                )

  Hope this helps.