I have a Time Dimension, [Time].[Fiscal Year] which has three levels: Year, Qtr, Month.
My situation requires that I get the First member of the first Qtr (month) and the last member of the last Qtr (month) given only the Year. I am close to the solution, if this is possible, and really need some help to finish this. Here is a speific scenario:
From my application, user selects "Fiscal Year" as one of the fiters. Year = 2007 (No additional level is given to select).
The calculation I am trying to form is for % growth ((Measures for FY 2007 - Measures for FY 2006) / Measures for FY 2006).
My problem is that the the total periods for FY 2007 do not yet equal the total periods for 2006. So what I want to do is calculate the growth by summing the periods that exist in 2007 at the month level. Then summing those same periods for the previous period. I can do this by getting the first existing member of the first month and the last existing member of the last month. To expand on the scenario:
FY 2007 has 6 Months of data:
FY 2007
Q1
July
Aug
Sep
Q2
Oct
Nov
Dec
FY 2006 has all 12 Months of Data (Q1, Q2, Q3, Q4)
Given only FY 2007, I need to retrieve Member July and Dec.
The closest I have come to this is:
SET [FirstMonth] AS
'{(Descendants([Time].[Fiscal Year].[2007].FirstChild, 2, SELF))}'
SET [LastMonth] AS
'{(Descendants([Time].[Fiscal Year].[2007].LastChild, 2, SELF))}'
This gives me:
[FirstMonth]
July
Aug
Sep
[LasstMonth]
Oct
Nov
Dec
When running in Select statement. Can this even be accomplished with MDX?
Take a look at OpeningPeriod() and ClosingPeriod() MDX functions - something like:
OpeningPeriod([Time].[Fiscal Year].[Month]) and ClosingPeriod([Time].[Fiscal Year].[Month])
http://msdn2.microsoft.com/en-us/library/ms145992.aspx
>>
SQL Server 2005 Books Online
OpeningPeriod (MDX)
Updated: 17 July 2006
Returns the first sibling among the descendants of a specified level, optionally at a specified member.
...>>
|||Nice! Thank you very much.
No comments:
Post a Comment