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

Dynamic YTD

Is it possible to show YTD information (considering our fiscal year starts in October) and have it dynamically change year-to-year? What I need to be able to display is information from Oct1 through Sept 30, but I don't want to go in every year and change the date range. Is this possible?

Short answer is, yes, it is possible.

But the specifics depend on how you are retrieving/filtering your data... is it:

1- Using a SQL relationship datasource (so we need to make a T-SQL query that does what you want)

2- Using an Analysis data source (so we'd need to make an MDX query that does what you want)

3- Any data source, then filtering the data in Reporting Services (we'd need to make a VB Expression then)

4- Something else entirely (in which case we'll need to know how you're getting your data)

#3 could introduce some performance issues, #1 is the easiest to implement.

Let us know which you use, and can help from there.

G

|||

Thanks for the reply. I am pulling data using SQL (#1).

Would you have an example of a T-SQL statement that I could look at?

|||

Yes, something like this should work

DECLARE @.FY as datetime

SET @.FY = CASE WHEN MONTH(GetDate()) < 10 THEN

CAST(CAST(Year(DateAdd(yy,-1,GetDate())) as varchar(4)) + '-10-01' as datetime)

ELSE

CAST(CAST(Year(GetDate()) as varchar(4)) + '-10-01' as datetime)

END

SELECT * From YourTable

WHERE YourDateColumn BETWEEN @.FY AND DateAdd(yy,1,@.FY)

If you can't run a script and declare a variable to reuse it, just replace the @.FY with the CASE...END function to start with.

The function can be improved a tad still, but this gives you the idea. Check your boundaries, I'm not sure off the top of my head if the 1st of October of the next year will be included as written. The BETWEEN operator may not meet your needs.

G

|||

This is what I needed. Thanks, G.

-Jody

Dynamic YTD

Is it possible to show YTD information (considering our fiscal year starts in October) and have it dynamically change year-to-year? What I need to be able to display is information from Oct1 through Sept 30, but I don't want to go in every year and change the date range. Is this possible?

Short answer is, yes, it is possible.

But the specifics depend on how you are retrieving/filtering your data... is it:

1- Using a SQL relationship datasource (so we need to make a T-SQL query that does what you want)

2- Using an Analysis data source (so we'd need to make an MDX query that does what you want)

3- Any data source, then filtering the data in Reporting Services (we'd need to make a VB Expression then)

4- Something else entirely (in which case we'll need to know how you're getting your data)

#3 could introduce some performance issues, #1 is the easiest to implement.

Let us know which you use, and can help from there.

G

|||

Thanks for the reply. I am pulling data using SQL (#1).

Would you have an example of a T-SQL statement that I could look at?

|||

Yes, something like this should work

DECLARE @.FY as datetime

SET @.FY = CASE WHEN MONTH(GetDate()) < 10 THEN

CAST(CAST(Year(DateAdd(yy,-1,GetDate())) as varchar(4)) + '-10-01' as datetime)

ELSE

CAST(CAST(Year(GetDate()) as varchar(4)) + '-10-01' as datetime)

END

SELECT * From YourTable

WHERE YourDateColumn BETWEEN @.FY AND DateAdd(yy,1,@.FY)

If you can't run a script and declare a variable to reuse it, just replace the @.FY with the CASE...END function to start with.

The function can be improved a tad still, but this gives you the idea. Check your boundaries, I'm not sure off the top of my head if the 1st of October of the next year will be included as written. The BETWEEN operator may not meet your needs.

G

|||

This is what I needed. Thanks, G.

-Jody