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: Table with BDESC sorting

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:

Table with DESC sorting

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

Thank you.

If you are not bound to mdx only, use openquery in sql to retrieve raw set via mdx and sort it by sql Order BY — George
@George - looks like they must be bound to some MDX tool, as surely no-one would consciously throw in all those hierarchize ?! — whytheq

whytheq whytheq · Accepted Answer · 2015-02-20T13:51:04

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];

Issue with Order function and Crossoins in MDX

1 Answers