Issue with Order function and Crossoins in MDX

Question

We have a table with one Measure [Discount Amount] on the COLUMNS and CROSSJOIN of [Product].[Product Categories] and [Geography].[Geography] on ROWS axis.

We use ORDER function to sort entities by [Discount Amount] with option "BDESC".

MDX:

SELECT 
  NON EMPTY 
    {[Measures].[Discount Amount]} ON COLUMNS
 ,NON EMPTY 
    Order
    (
      {
        Hierarchize
        (
          {
            CrossJoin
            (
              {
                Hierarchize
                (
                  {
                    [Product].[Product Categories].[All Products]
                   ,[Product].[Product Categories].[All Products].Children
                  }
                )
              }
             ,{Hierarchize({[Geography].[Geography].[All Geographies]})}
            )
          }
        )
      }
     ,[Measures].[Discount Amount]
     ,BDESC
    ) ON ROWS
FROM [Adventure Works];

Table looks good and displays information as we want it.

Then we want to expand [All Categories] element for entity [Clothing]. To do that we need to make several changes:

• modify existing Crossjoin (as before but without Clothing);
• add a new Crossjoin for [Clothing] entity with expansion of selected member [All Categories];

new MDX:

SELECT 
  NON EMPTY 
    {[Measures].[Discount Amount]} ON COLUMNS
 ,NON EMPTY 
    {
      Order
      (
        {
          Hierarchize
          (
            {
              CrossJoin
              (
                {
                  Except
                  (
                    {
                      Hierarchize
                      (
                        {
                          [Product].[Product Categories].[All Products]
                         ,[Product].[Product Categories].[All Products].Children
                        }
                      )
                    }
                   ,{[Product].[Product Categories].[Category].&[3]}
                  )
                }
               ,{Hierarchize({[Geography].[Geography].[All Geographies]})}
              )
            }
          )
         ,CrossJoin
          (
            {[Product].[Product Categories].[Category].&[3]}
           ,{
              Hierarchize
              (
                {
                  [Geography].[Geography].[All Geographies]
                 ,[Geography].[Geography].[All Geographies].Children
                }
              )
            }
          )
        }
       ,[Measures].[Discount Amount]
       ,BDESC
      )
    } ON ROWS
FROM [Adventure Works];

and result table in SQL Server Management Studio:

As you can see all children of [Clothing] entity loses their hierarchy and has been displayed as separate entities in table.

But we want these elements to be under [All Geographies] of [Clothing].

If we try to change sorting type to "DESC" (hierarchical) then children displays correctly but ordering in table doesn't work:

So we are searching for solution how to make work ordering and expanding in such tables.

Thank you.

Answer 1

Here you go - I took out a lot of your instances of Hierarchize to try to see the wood for the trees. Instead of just using the measure to order I used a tuple with the addition of [Product].[Product Categories].[Product Categories]:

SELECT 
  NON EMPTY 
    {[Measures].[Discount Amount]} ON COLUMNS
 ,NON EMPTY 
    {
      Order
      (
        {
            Except
            (
              {
                [Product].[Product Categories].[All Products]
               ,[Product].[Product Categories].[All Products].Children
              }
             ,[Product].[Product Categories].[Category].&[3]
            )
          * 
            [Geography].[Geography].[All Geographies]
         ,
            [Product].[Product Categories].[Category].&[3]
          * 
            {
              [Geography].[Geography].[All Geographies]
             ,[Geography].[Geography].[All Geographies].Children
            }
        }
       ,([Measures].[Discount Amount],[Product].[Product Categories].[Product Categories])
       ,BDESC
      )
    } ON ROWS
FROM [Adventure Works];