-
How hard can it be to sort MDX descending - help please
Hello,
In SQL I just put "Order By {whateverfield}" at the end my statement, but in MDX I just can't work it out.
I know about "ORDER" and the DESC or BDESC, but can't fit it into the following without breaking it or it having no effect:
Code:
WITH
MEMBER [Measures].[ParameterCaption]
AS[Financial Date].[Short Year-Month (H)].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue]
AS [Financial Date].[Short Year-Month (H)].CURRENTMEMBER.UNIQUENAME
MEMBER [Measures].[ParameterLevel]
AS [Financial Date].[Short Year-Month (H)].CURRENTMEMBER.LEVEL.ORDINAL
SELECT
{
ORDER( [Measures].[ParameterCaption], DESC),
[Measures].[ParameterValue],
[Measures].[ParameterLevel]
}
ON COLUMNS
,
[Financial Date].[Short Year-Month (H)].CHILDREN
ON ROWS
FROM
( SELECT
( { [Head Count].[Employee Status Code (H)].[Employee Status Code].&[A] } )
ON COLUMNS
FROM
[Fact Head Count]
)
All I want to do is sort the following results on the ParameterCaption column descending.
Mezer_11-28_16-21-44.png
Many thanks in advance...
-
Add the ORDER on the ROWS and don't forget the measure
Turns out to be easy as I found out myself after a night's sleep:
Code:
WITH
MEMBER [Measures].[ParameterCaption]
AS[Financial Date].[Short Year-Month (H)].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue]
AS [Financial Date].[Short Year-Month (H)].CURRENTMEMBER.UNIQUENAME
MEMBER [Measures].[ParameterLevel]
AS [Financial Date].[Short Year-Month (H)].CURRENTMEMBER.LEVEL.ORDINAL
SELECT
{
[Measures].[ParameterCaption],
[Measures].[ParameterValue],
[Measures].[ParameterLevel]
}
ON COLUMNS
,
Order( [Financial Date].[Short Year-Month (H)].CHILDREN,
[Measures].[ParameterValue] ,
DESC
)
ON ROWS
FROM
( SELECT
( { [Head Count].[Employee Status Code (H)].[Employee Status Code].&[A] } )
ON COLUMNS
FROM
[Fact Head Count]
)
Result:
Mezer_11-29_09-41-31.png
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|