March 10, 2013 at 1:09 am
This shouldn't be as hard as it seems; I want to always get the first month of the year (January) regardless of what month is the current member. I'm going to use this later on (if I get it to work) for a calculated member. The Date dimension's calendar hierarchy is Year --> Quarter --> Month.
So I've tried:
-- Returns back the year okay --
SELECT {} ON ROWS,
Ancestor([Date].[Calendar].CurrentMember, [Date].[Calendar].[Year]) ON COLUMNS
FROM [RoyaltyPMA]
WHERE [Date].[Month].&[201209];
But shouldn't this return back the first quarter of the year?:
-- Returns back nothing --
SELECT {} ON ROWS,
Ancestor([Date].[Calendar].CurrentMember, [Date].[Calendar].[Year]).FirstChild ON COLUMNS
FROM [RoyaltyPMA]
WHERE [Date].[Month].&[201209];
Or, couldn't I get the .FirstSibling of the .CurrentMember (if CurrentMember is at the month level) like. I've also unsuccessfully tried using the OpeningPeriod.
It must be too late at night; any pointing me in the correct direction would be appreciated.
Thanks,
Rob
March 10, 2013 at 10:53 am
I'm sure there must be a better way, but I did get this to work by:
MEMBER [Measures].[Yearly Adv Req] AS
(
Ancestor( [Date].[Calendar].CurrentMember, [Date].[Calendar].[Year] ).FirstChild.FirstChild,
[Measures].[Advertising Req]
)
Rob
March 14, 2013 at 11:10 am
It's probably how I would have done it
Alternatively you could do
MEMBER [Measures].[Yearly Adv Req] AS
(Descendants(Ancestor( [Date].[Calendar].CurrentMember, [Date].[Calendar].[Year] ),2).Item(0)
, [Measures].[Advertising Req] )
Mack
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply