对MDX查询中的所有列进行排序 [英] Sorting all the columns in MDX queries
问题描述
下面是我的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.
推荐答案
我自己完成了。我更改了ROWAXISWOF如下。
I have done it by myself. I have changed the ROWAXISWOF as follows.
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查询中的所有列进行排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!