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]
)
It will return the output as preceding.
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.
Please Accept And Up Vote If It Helped You
Kindest Regards
Sibeesh Venu
http://sibeeshpassion.com/
Member
572 Points
76 Posts
Sorting all the columns in MDX queries
Feb 03, 2016 12:41 PM|Sibeesh Venu|LINK
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.
It will return the output as preceding.
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.
Kindest Regards
Sibeesh Venu
http://sibeeshpassion.com/
Member
572 Points
76 Posts
Re: Sorting all the columns in MDX queries
Feb 03, 2016 12:44 PM|Sibeesh Venu|LINK
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] } )I have included NonEmpty conditions for each measures. Hope this will help some one.
Kindest Regards
Sibeesh Venu
http://sibeeshpassion.com/