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