Data from 1st Jan to Today

[mathewmvar]

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

[CognosPaul]

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).

[mathewmvar]

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

[CognosPaul]

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.

[mathewmvar]

Dear Paul,

Thanks it is working. Excellent logic. If you get any better logic, can you please update to me.

Regards
Mathew