incorrect expansion order when sorting

  • vlb

    I've noticed a problem when expanding nodes - sometimes children of a parent appear in the wrong place when I sort a column.  I was able to reproduce the problem with the FoodMart access database.  When I open up "All Products" and expand the Good brand, I see all the Good products underneath, which is correct.  However, if I sort the LowFat true column, the Good products wind up listed underneath the Walrus brand.

    This is my schema:

    <Cube name="Product">
       <Table name="product"/>
       <Dimension name="Product">
          <Hierarchy hasAll="true">
             <Level name="Brand Name" column="brand_name" uniqueMembers="false"/>
             <Level name="Product Name" column="product_name" uniqueMembers="true"/>
       <Dimension name="LowFat">
          <Hierarchy hasAll="true">
             <Level name="low_fat" column="low_fat" uniqueMembers="false"/>
       <Measure name="units_per_case" column="units_per_case" aggregator="sum" formatString="Standard"/>

    Here's the MDX:

    SELECT Crossjoin(
    {[Measures].[units_per_case]}) ON COLUMNS,
    {[Product].[All Products]} ON ROWS
    FROM Product

    If anyone has any suggestions as to why this is happening or how I can fix it, I would really appreciate it.


    • vlb

      I did a little more investigating and found that this problem only occurs when there are multiple rows that have the same measure number (i.e., many rows that have 500 as the unit sales measure).  When this happens, the rows are listed in alphabetical order and the hierarchy levels are ignored). 


      + Atomic             8
      + Choice                 8
      - Good                 8
      + Gulf Coast             8
      + Musial                 8
      + Pearl                 8
      + Portsmouth             8
      + Thresher             8
      + Top Measure             8
      + Walrus                 8
          Good Chablis Wine         1
          Good Chardonnay         1
          Good Chardonnay Wine     1
          Good Imported Beer     1
          Good Light Beer         1
          Good Light Wine         1
          Good Merlot Wine         1
          Good White Zinfandel Wine     1 

      Notice that although the Good brand is expanded (shown with a - next to it), its products are not listed immediately under it.  Instead, Good is listed in alphabetical order with the other brands, and its products get left behind at the end of the list.

      Has anyone seen something similar?


      • Andreas Voss
        Andreas Voss

        It seems that you use "break hierarchy" sort mode instead of "keep hierarchy". You can change this with the "sort settings" toolbar button. Does it work with "keep hierarchy" or do you still have the problem?

    • vlb

      Thanks for the reply.  I didn't change the default sort settings.  I checked - it's "keep hierarchy ascending", "number of rows for ranking = 10".  I tried changing it to "break hierarchy ascending" and it sort of works - the Good products are now listed with the Good brand as they should be, but always at the bottom, like this:

      + Atomic 8 
      + Choice 8 
      + Gulf Coast 8 
      + Musial 8 
      + Pearl 8 
      + Portsmouth 8 
      + Thresher 8 
      + Top Measure 8 
      + Walrus 8 
      - Good 8 
      Good Chablis Wine 1 
      Good Chardonnay 1 
      Good Chardonnay Wine 1 
      Good Imported Beer 1 
      Good Light Beer 1 
      Good Light Wine 1 
      Good Merlot Wine 1 
      Good White Zinfandel Wine 1 

      I'm really curious as to what's causing this.  Any other ideas?

      Thanks again,