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

dynamic sql to loop over fiscal years

thanks for reading.

i'm interested in improving the format of this query. consider me clueless today, if you will. :) how can i fix this to make it dynamically move over the years? is there something i can do with set manipulation that is smarter than this?

the goal of this query is to return cases per year, where "year" is defined as (Oct 1, YYYY - Sep 30, YYYY+1) instead of the typical YYYY

problem is, i have to write it as some cludgy dynamic sql looping over an incremented year. i don't know of any other way.

again, thanks for reading ... and any help in advance.

SELECT count(*) as 'Data Points', '2001' as 'Experiment Year'
FROM tbl_experiment_data

WHERE start_date BETWEEN '9/30/2001' AND '10/01/2002'
and completion_date BETWEEN '9/30/2001' AND '10/01/2002'
and status = 'CaseClosed'

UNION

SELECT count(*) as 'Data Points', '2002' as 'Experiment Year'
FROM tbl_experiment_data

WHERE start_date BETWEEN '9/30/2002' AND '10/01/2003'
and completion_date BETWEEN '9/30/2002' AND '10/01/2003'
and status = 'CaseClosed'

UNION

...

expected output...

Data Points______ Experiment Year
32_____________ 2001
102____________ 2002
... ...Create a table called ExperimentYears, populate it with ExperimentYear char(4), YearStart datetime, YearEnd datetime. Then do just one SELECT similar to yours:

SELECT count(*) as [Data Points], ExperimentYear
from tbl_experiment_data ted
inner join ExperimentYears ey
on ted.start_date between ey.YearStart and ey.YearEnd
and ted.completion_date between ey.YearStart and ey.YearEnd
where ted.status = 'CaseClosed'
group by ey.ExperimentYear|||We use a separate calendar table. something like this:

create table FiscalCalendar
(FiscalYear int,
StartDate datetime,
EndDate datetime)

This should reduce your query to something like

select count(*), fc.FiscalYear
from tbl_experiment_data a, FiscalCalendar fc
where a.startdate between fc.startdate and fc.enddate
and a.enddate between fc.startdate and fc.enddate
group by fc.fiscalyear

Been a while since I messed with this, so experiment with this for a bit. As a curiosity, what happens to experiments that start in one fiscal year and end in the next?|||as i'm re-reading my post now i can see that maybe it wouldn't even work as is because i have the count(*) without a 'group by'

still, i hope these sorts of mistakes can be overlooked as i ask for help.

it also occurred to me just now that maybe i could use a user-defined function that returns the value of the year as redefined by the "year" range above.

that way i could rewrite the query like this...

==============
SELECT count(*) as 'Data Points', getFiscalYear(start_date) as 'Experiment Date'
FROM tbl_experiment_data

WHERE DATEPART(YEAR, start_date) = getFiscalYear(start_date)
and DATEPART(YEAR, completion_date) = getFiscalYear(completion_date) and status = 'CaseClosed'

GROUP BY getFiscalYear(start_date)

ORDER BY getFiscalYear(start_date)

==============

any comments? criticisms? other ideas?

thanks again for reading ... and your input

oh, great! i just noticed the responses now too. thank you. i'll try these ideas out.|||SELECT count(*) as 'Data Points',
year(dateadd(d, 92, StartDate))-1 as 'Experiment Year'
FROM tbl_experiment_data
WHERE status = 'CaseClosed'

The year(dateadd(d, 92, StartDate))-1 function returns the experiment year by addint 92 days (Oct +Nov +Dec) and then subtracting 1 from the year. Note that if you just subtracted days you would have to account for leap years.

You will need to decide what to do if an experiment starts in one year and ends in the next. Your original code would skip those instances entirely.