Wednesday, March 7, 2012

Dynamic YTD based on user selection on Month

I have a user how wants to be able to see the YTD numbers based on the month they select in an attribute hierarchy with in the Time Dimension.

I would like to create a calculated member called YTD that they can use to get this information based on the Month they select in the Month attribute

The Time Dimensions are as follows: Fiscal time(Year, Qtr, Month) Month(Month)

Measure = Net Sales

View Format should be

Years

2000 2001 2002 2003 2004 2005

Customer $ $ $ $ $ $

YTD should work if the user drills down to Qtr or Month, if they selected a member in the Month attribute or not.

The Time dimension is only loaded with the years that are in the cube so if the user selects Dec but no data is in the cube for Dec 2005. 2005 should still show up on the report.

One of the issues I have run into is determining if the user is looking(selected) something from the Fiscal Hierarchy of from the Month Attirbute. If I use [Time].[Month].currentmember.name I get the name of the month the user selected in Month Attribute but if the user selects "All" months in the Month attribute but selects the Months from the Fiscal hierarchy I still get the month name and not the value "All".

Please Help.

Here is an example of a calculated member that I added to the "AdventureWorks" cube that will return the "Calendar" YTD internet sales amount based on what has been selected as the current member of the Date.Calendar hierarchy. The MDX will also work in AS2K.

Create Member CurrentCube.[Measures].[CYTD Internet Sales Amount]

AS

Aggregate(

PeriodsToDate(

[Date].[Calendar].[Calendar Year],

[Date].[Calendar].CurrentMember),

Measures.[Internet Sales Amount]),

Format_String = "Currency",

Non_Empty_Behavior = { Measures.[Internet Sales Amount] };

HTH,

- Steve

No comments:

Post a Comment