December 3, 2009 at 4:49 pm
I would like to be able to sort this DATE parameter drop down dataset in DESCENDING order.
WITH MEMBER [Measures].[ParameterCaption] AS '[Date].[Day].CURRENTMEMBER.MEMBER_CAPTION'
MEMBER [Measures].[ParameterValue] AS '[Date].[Day].CURRENTMEMBER.UNIQUENAME'
MEMBER [Measures].[ParameterLevel] AS '[Date].[Day].CURRENTMEMBER.LEVEL.ORDINAL'
SELECT non empty
{
[Measures].[ParameterCaption]
, [Measures].[ParameterValue]
, [Measures].[ParameterLevel]
} ON COLUMNS ,
[Date].[Day].[Day]
ON ROWS
FROM [WAGER - Slot Revenue]
In T-SQL it would be a piece of cake, since you can actually order things by themselves, but I am flummoxed on how to do this with MDX.
December 11, 2009 at 10:02 am
Sorting is a pain in MDX, or at least thats how I always felt about it. If your query has just one dimension on rows and one on columns, it will make life much simpler.
The ORDER keyword can be applied to a set, and you just need to give it a number to sort on, and tell it which way to go with ASC or DESC.
The ASC or DESC issue gets confusing as you can also specify BASC or BDESC as well. The "B" means "break" and it's saying whether you want the rows (or columns) to be sorted within their heirarchical groups, or do you want to break the hierarchy altogether when you sort, and totally reorder the rows.
Here's a simple example
ORDER({[Time].[Month].members}, [Measures].[Gross Sales], BASC)
This means you want to list all Months in ascending order of Gross Sales value. You are breaking the hierarchy, so that the Months do not remain grouped within their parent Years, and can be completely intermingled by the sort operation.
January 14, 2010 at 12:41 pm
The catch is, I'm want to sort on the DATES. There are no numbers associated with this data (in this instance), its simply the drop down for the user to pick their starting and ending dates for the report. In a mo-better world, my date heirarchies would already be properly sorted, but the yahoos who build the cubes have been entirely too casual about it.
February 3, 2010 at 11:15 am
I found a method of doing it:
WITH
MEMBER [Measures].[DateSort] AS [Date Dimension].[DATE].CURRENTMEMBER.Properties('Key')
MEMBER [Measures].[ParameterValue] AS '[Date Dimension].[DATE].CURRENTMEMBER.UNIQUENAME'
SELECT NON EMPTY
{ [Measures].[DateSort]
, [Measures].[ParameterValue]
} ON COLUMNS,
NON EMPTY
ORDER(
{[Date Dimension].[DATE].[DATE]}
, [Measures].[DateSort], DESC)
ON ROWS
FROM [HobbyDivision]
Essentially, I take the DATE Key (which is an integer value) and turn it into a measure, then I can use that measure in the ORDER. 😀 Simple stuff, but like most of MDX for me, definitely not intuitive.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply