May 12, 2011 at 9:46 am
I am trying to bring back the last 13 months of data from the cube.
SELECT
({HEAD(TAIL(DESCENDANTS([RecvDate].[ProgMonth].[ProgMonth],[ProgMonth]),14),13)}, [Measures].[Invoice Fact Count])
On 0
FROM [Medical Payments]
(last member is unknown. Using the head tail to exclude that member)
This brings back the data for the last 13 months but starting with May 2010.
I want to reverse the order so that it brings back May2011 first.
I tried adding the order function but that groups the months together and orders them eg May2010, May 2011, Jun2010, Jun2011
May 17, 2011 at 3:38 pm
Your general approach with the ordering of the field is correct - and the result is unavoidable.
To get around this, use a numeric key for the months in your database, and reflect that in the hierarchy structure (instead of using the month name).
Then do a descending sort using the [...member...].currentmember.properties("key") as the criteria and you'll get your answer.
May 18, 2011 at 6:21 am
I figured out how to do it.
SELECT [Measures].[Invoice Fact Count] ON COLUMNS,
ORDER(
{HEAD(TAIL(DESCENDANTS([RecvDate].[ProgMonth].[ProgMonth],[ProgMonth]),14),13)}, [RecvDate].[ProgMonth].CurrentMember.Properties('KeyColumns')
,DESC)
On ROWS
FROM [Medical Payments]
Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply