对MDX查询中的所有列进行排序

问题描述

下面是我的MDX查询。这里我已经对一列应用了排序，我如何对所有列进行排序，其背后的原因是，我没有列应该有任何值，如（null），null，empty，unknown等。

Below is my MDX query. Here I have applied sorting for one column, how can I sort all the columns, the reason behind this is, I no column should have any values like (null), null, empty, unknown etc.

WITH MEMBER PageSize AS
  1
MEMBER [PageNumber] AS
  1
MEMBER [Measures].[Orderby Measure] AS
  [Measures].[Budget Delivered COGS]
MEMBER [Orderby] AS
  "BASC"
SET ROWAXISWOF AS
  NonEmpty (
    ( [Time Periods].[Fiscal Year].[Fiscal Year].Members, [Time Periods].[Fiscal Quarter].[Fiscal Quarter].Members, [Time Periods].[Fiscal Month].[Fiscal Month].Members, [Time Periods].[Fiscal Week].[Fiscal Week].Members ),
    {
      { [Measures].[Budget Delivered COGS] },
      { [Measures].[Break Even Delivered] }
    }
  )
SET ROWAXISWF AS
  Filter ( ROWAXISWOF, [Measures].[Budget Delivered COGS] )
SET ROWAXIS AS
  IIf ( 'Filter' = "NotFilter", ROWAXISWF, ROWAXISWOF )
MEMBER [Measures].[MaxRowCount] AS
  ROWAXIS .Count
SET ROWAXIS_Count AS
  IIf (
    'Paging' = "Paging",
    (
    CASE
    WHEN [Orderby] = 'BASC'
    THEN Tail (
      TopCount ( ROWAXIS, PageSize * PageNumber, [Measures].[Orderby Measure] ),
      PageSize
    )
    ELSE Tail (
      BottomCount ( ROWAXIS, PageSize * PageNumber, [Measures].[Orderby Measure] ),
      PageSize
    ) END ),
    ROWAXISWOF
  )
SELECT ( IIf (
  'Paging' = "Paging",
  (
  { [Measures].[Budget Delivered COGS], [Measures].[Break Even Delivered], [Measures].[MaxRowCount] } ),
  { [Measures].[Budget Delivered COGS], [Measures].[Break Even Delivered] }
) ) ON COLUMNS,
ROWAXIS_Count ON ROWS
FROM (
  SELECT (
  { [Time Periods].[Fiscal Year].&[2011], [Time Periods].[Fiscal Year].&[2012], [Time Periods].[Fiscal Year].&[2013], [Time Periods].[Fiscal Year].&[2014], [Time Periods].[Fiscal Year].&[2015] } ) ON COLUMNS
  FROM [Homestore Sales]
)

在查询Break Even Delivered标头时，它显示（null）作为第一个值。我们怎样才能使非空的值成为最重要的？如果是一列，我们可以只应用排序。我的要求是，如果任何单元格包含值，则第一个单元格中的列不应具有空值。请建议我任何想法。在此先感谢。

In that query for the Break Even Delivered header it is showing (null) as the first value . How can we make the value which are not null to be on top? if that is for one column we can just apply a sorting. My requirement is, no column should have null values in the first cell if any of the cell contains values. Please suggest me any idea. Thanks in advance.

推荐答案

SET ROWAXISWOF AS
  NonEmpty (
  NonEmpty (
    (
    [Time Periods].[Fiscal Year].[Fiscal Year].Members, [Time Periods].[Fiscal Quarter].[Fiscal Quarter].Members, [Time Periods].[Fiscal Month].[Fiscal Month].Members, [Time Periods].[Fiscal Week].[Fiscal Week].Members
    ),
      { [Measures].[Budget Delivered COGS] }),
      { [Measures].[Break Even Delivered] }  )

我已为每项措施包含NonEmpty条件。希望这会对某人有所帮助。

I have included NonEmpty conditions for each measures. Hope this will help some one.

这篇关于对MDX查询中的所有列进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！