Fiscal Year problem

[rcaesar]

Hi Gurus,

I have searched quite a bit for an apt solution for a fiscal year for a Report (version C8.4)

This is what I have:

Our Fiscal year is September 1 to August 31.
We have a Begin_Date column

What I want to do is have a Fiscal Year Prompt. The user should be able to select  a particular year.
If 2011 is selected, then the report should return data from Sept. 1, 2010 to Aug. 31, 2011

I would also like a Fiscal year Column in the Report in the report (If the prompt has an all option)

At some point I will incorporate fiscal quarters or months. I have sincerely tried to do this, but not successful.

I have the Fiscal Year prompt (static), and all my if statements are going crazy :-)

Is there a function I could use?

Appreciate any help or suggestions.

Thanks

[PRIT AMRIT]

I have the Fiscal Year prompt (static), and all my if statements are going crazy :-)

Let's see how your expression looks like?

[rcaesar]

Thanks,

I have something like this. I know I am not thinking properly, and feel my approach is not correct:

Extract(year, [Begin_Date]) = ?pYear?  // this part works


If(extract(month,[Begin_Date]) >=9 and  extract(month,[Begin_Date]) <=12) 
         Then( (?pYear) -1)
Else
   ?pYear?

[Mpotla]

Hi rcaesar,

We can use below expression which would return the fiscal calender .

Calculate below data items
Strt_prd=(?P_Year?-1)*100+09
End_Prd=?P_Year?*100+08

Now add a detail filer in the report with expresion

(extract(Year,[Begin_date])*100+extract(month,[Begin_Date]))
between Strt_prd and End_Prd

You can use expressions for Strt_prd and End_Prd  directly in the filter expression instead of calculating them separately and using it.

Hope this would help!

Regards,
McMohan.

[rcaesar]

Hi McMohan,

That was awesome. It worked perfectly.

Thank you so much.