MDX中的Order函数和Crossoins问题 [英] Issue with Order function and Crossoins in MDX

问题描述

我们有一张表格，该表格在[产品].[产品类别]和[地理].[地理]的列和CROSSJOIN上具有一个度量[折扣金额].

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

我们使用ORDER函数通过[折扣金额]选项"BDESC"对实体进行排序.

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:

• 修改现有的Crossjoin(与以前一样，但没有服装)；
• 为[服装]实体添加新的Crossjoin，并扩展所选成员[所有类别];

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

和SQL Server Management Studio中的结果表:

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

如果我们尝试将排序类型更改为"DESC"(分层)，则子级可以正确显示，但无法在表中进行排序:

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.

谢谢.

推荐答案

在这里-我拿出了很多Hierarchize实例来尝试观察树木的树木.我使用的不是元组，而是使用[Product].[Product Categories].[Product Categories]:

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

这篇关于MDX中的Order函数和Crossoins问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！