Report studio, i need to show a report in following format. Data displays fromStart date of the year to Today in each year.
Year count
2006 1000
2007 890
2008 1500
2009 1600
2010 750
How i can make the report from all the year 1st Jan to Todays date?
Regards
Mathew
Limiting the report to YTD (year to date) is very much dependent on your data source.
Are you using a dimensional source or a relational source? If it's relational, which one?
Also, how do you want to handle leap years? By day of year (makes leap years slightly off) or by month/day (means you have to include Feb 29 in all places where you show the day level).
hi paul,
Thanks for your reply. I am using sql server database .
I use the following sql statement.
select YEAR(Sale_date),count(*) from Sale_Fact
where month("Sale_Fact "."Sale_date") <= month(getdate())
and day("Sale_Fact "."Sale_date") <= day(getdate())
Group by YEAR(Sale_date)
It filter records from days and I need all record from 1st jan to 2nd Nov in all years
Regards
Mathew
I don't have access to an SQL Server database at the moment, but try something like the following:
select YEAR(Sale_date),count(*) from Sale_Fact
where month("Sale_Fact "."Sale_date") * 100 + day("Sale_Fact "."Sale_date") <= month(getdate()) * 100 + day(getdate())
Group by YEAR(Sale_date)
I'm 95% certain there's a more efficient way of doing this, but can't remember.
Dear Paul,
Thanks it is working. Excellent logic. If you get any better logic, can you please update to me.
Regards
Mathew