ytd report for fiscal year

[karthik.cognos]

Hi,

I have to create a filter in my report in report studio which picks up data for the current fiscal year. My fiscal year starts on 1st April 20XX and its a ytd report. I have trouble getting the year from the sysdate function. Can somebody plz help?

[Suraj]

extract (year, sysdate())

works fine.

you can also use
extract (year, current_date)

[almeids]

or for Oracle sysdate function:
to_char(sysdate,'YYYY')
wrap with to_number() if you need it numeric

[karthik.cognos]

Hi Guys, Thank you for your reply, but what if I had to run the report say on..Feb 1 2009. Then my filter would pick up the date range between April 1 2009 and Feb 1 2009 which is a false case...Can you plz help?

[almeids]

Well, then, there's a problem with your filter too.  I assumed once you had the year isolated you would know what to do with it.  My bad.  "Plz" forgive me.

Your filter needs to test for the current month.  In pseudocode:

(currentmonth>3 and dbdate>=april1thisyear) or (currentmonth<4 and (dbdate>=april1lastyear or dbdate<april1thisyear))

Alternatively you could embed similar logic in a fiscal year data item...
(if (date>april1) then (thisyear) else (lastyear)
...and keep the filter simple.

The former approach may yield better performance.

[karthik.cognos]

Hi Almeids, this helps...

based on your logic, i have created a data item with this defination:

CASE
WHEN(to_number(to_char({sysdate},'MM')IN(1,2,3)))
THEN(to_number(to_char({sysdate},'YYYY')-1))
ELSE(to_number(to_char({sysdate},'YYYY')))
END

and in my filter i have:
[Sale Date] >= to_date('01-APR-'||'[Data Item1]')

now when i run the report, i get an error which says...
UDA-SQL-0114 The cursor supplied to the operation "sqlOpenResult" is inactive. UDA-SQL-0107 A general exception has occurred during the operation "open result". ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Any help would be greatly appreciated!!
Thanks!

[almeids]

KC, no offense, but your troubleshooting and self-help skills could really use some work.

In your data item you are calculating a numeric value, but then you are concatenating it with a string in your filter.  So why the numeric conversion?  You are also subtracting 1 from a character value in your THEN clause.  These constructs are forcing Oracle to do all kinds of implicit type conversion; combined with your liberal and seemingly arbitrary use of parentheses I'm not surprised you are getting an error.

Having reread your original post, it doesn't sound like you need any flexibility in the report to select previous fiscal years.  So, I withdraw my previous advice and offer this filter instead:

to_char([Sale date],'YYYY')=to_char(add_months({sysdate},-3),'YYYY')

Good luck.

[Suraj]

almeids,
Nice logic
Doesn't to_char add runtime as it is not date index anymore?
Just curious but I think we can't avoid this in situations like this.

[almeids]

Yeah, but it runs fine with Great Outdoors. ;)
Besides, it's Oracle so you can always fix it with a function-based index!

[rockytopmark]

I have to chime in... there should be a TIME conformed dimension table added to the datamart.  All fact tables should be related to this dimensions.  It is often spoken that the Time dimension is the single most powerful (and important!?!?) dimension!!

This would eliminate the need for such workarounds.

[almeids]

I have to chime in... there should be a TIME conformed dimension table added to the datamart. 

Agreed.
In this case, even a fiscal year embedded in the facts would be an improvement!