Solved

Fiscal Year Report (formulas)

Posted on 2008-09-29
10
1,741 Views
Last Modified: 2012-05-05
Hi All,
I am trying to generate the cross tab crystal report, fiscal year is defined from April-March ex: April-2007 - March 2008, ie; in my report i should gel the values from april of current year to march of next year, i have the query which gets the data, but it is not getting the datafor jan,feb and mar of the next year, it isgetting the data of the same year, how can i overcome this problem.

Can anyone help me in developing the formula within crystal which gives me the exact report that iam looking at.

Help greatly appreciated.(TIA)
0
Comment
Question by:Mohammed_Triad
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 17

Expert Comment

by:MIKE
ID: 22596600
Try this in your record selection formula:

({?Fiscal Year} = "CURRENT FISCAL YEAR" and {Orders.Order Date} in date((if month(currentdate) in [4 to 12] then year(currentdate) else year(currentdate)-1),4,1) to date((if month(currentdate) in [4 to 12] then year(currentdate)+1 else year(currentdate)),3,31))
or
({?Fiscal Year} = "PRIOR FISCAL YEAR" and {Orders.Order Date} in date((if month(currentdate) in [4 to 12] then year(currentdate)-1 else year(currentdate)-2),4,1) to date((if month(currentdate) in [4 to 12] then year(currentdate) else year(currentdate)-1),3,31))

Replace my Datatable field with YOUR DATE field.....

M
0
 
LVL 34

Expert Comment

by:James0628
ID: 22620141
How do you define "current year"?  In particular, assuming that it's based on the date when you run the report, which year should the report use when it's run in Jan, Feb or Mar?  Do you want the fiscal year that is about to end, or the one that's about to begin?

 James
0
 

Author Comment

by:Mohammed_Triad
ID: 22667466
There is afield as cyear (nvarchar ), through which i am passing the parametre to my report, the begining month is apr of present year and the ending month is march of next year.
ex: If i pass 2008 in my cyear as parametre  value then i should get the data in the report for the months and year as defined below:
April-08
May-08
 '
 '
December -08
January-09
Febbruary-09
March-09
lets say if i run the report in august, it will give the data for the months that are available else it will not show the month itself,
I have defined a record selection formula it is workingfine till december but it is not getting the vales for january 2009----march 2009, for testing i have entered the dummy data for the year 2009 in the month of january ---march.

Now it came into very crucial stage, help greatly appreciated.
TIA
({vOIForecast.yofo_Year} = {?Yofo_Year} and ({vOIForecast.yofo_Month}='April'

                                          or {vOIForecast.yofo_Month}='May' 

                                          or {vOIForecast.yofo_Month}='june' 

                                          or {vOIForecast.yofo_Month}='july' 

                                          or {vOIForecast.yofo_Month}='August'

                                          or {vOIForecast.yofo_Month}='September'

                                          or {vOIForecast.yofo_Month}='October'

                                          or {vOIForecast.yofo_Month}='November'

                                          or {vOIForecast.yofo_Month}='December') 

or

({vOIForecast.yofo_Year} = cstr(tonumber({?Yofo_Year})+ 1)) and ({vOIForecast.yofo_Month}='January'))

Open in new window

0
 
LVL 100

Expert Comment

by:mlmcc
ID: 22668896
What about February and March?

mlmcc
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 17

Expert Comment

by:MIKE
ID: 22669503
Mohammed:

Did you try the formula I provided? If so, what more are you trying to do. My formula should give the Fiscal Year breakdown that you are seeking.

M
0
 
LVL 34

Expert Comment

by:James0628
ID: 22674436
Mohammed_Triad,

 The formula you posted looks OK for the most part, but you have some misplaced ().

({vOIForecast.yofo_Year} = {?Yofo_Year} and ({vOIForecast.yofo_Month}='April'
                                          or {vOIForecast.yofo_Month}='May'
                                          or {vOIForecast.yofo_Month}='june'
                                          or {vOIForecast.yofo_Month}='july'
                                          or {vOIForecast.yofo_Month}='August'
                                          or {vOIForecast.yofo_Month}='September'
                                          or {vOIForecast.yofo_Month}='October'
                                          or {vOIForecast.yofo_Month}='November'
                                          or {vOIForecast.yofo_Month}='December')
)  // Add a right parentheses here
or
({vOIForecast.yofo_Year} = cstr(tonumber({?Yofo_Year})+ 1)
// )  Remove this right parentheses
 and ({vOIForecast.yofo_Month}='January'))


 Two other things:

 As mlmcc said, you need to add February and March to the second part of the formula.

 I noticed that you're checking for 'june' and 'july' (with lowercase "j"), while the other months start with uppercase letters.  Do June and July really start with lowercase letters?  Obviously you need to make sure that you're looking for the right values.  If there is any doubt, I'd just use UpperCase () on the field, so you know what it will look like.  For example:
 UpperCase ({vOIForecast.yofo_Month}) = 'APRIL'


 Also, while your formula should be OK (with the modifications that I mentioned), FWIW, I'd probably use something like:

({vOIForecast.yofo_Year} = {?Yofo_Year} and
 UpperCase ({vOIForecast.yofo_Month}) in
 [ "APRIL", "MAY", "JUNE", "JULY", "AUGUST", "SEPTEMBER",
 "OCTOBER", "NOVEMBER", "DECEMBER" ])
 or
({vOIForecast.yofo_Year} = CStr (ToNumber ({?Yofo_Year}) + 1) and
 UpperCase ({vOIForecast.yofo_Month}) in
 [ "JANUARY", "FEBRUARY", "MARCH" ])


 James
0
 

Author Comment

by:Mohammed_Triad
ID: 22701290
I thank you all for defining the formula for me, i got the result by modifying the view it self i added the following lines in my view and it took care of everything.
In the above mentioned codes i havent used Feb and march as i thought that once january comes in i can bring feb and march.

Thanks.

 CASE y.yofo_Month 

				when 'January' then cast(cast(y.yofo_Year as int)-1 as varchar(4)) 

				when 'February' then cast(cast(y.yofo_Year as int)-1 as varchar(4)) 

				when 'March' then cast(cast(y.yofo_Year as int)-1 as varchar(4)) 

				else y.yofo_Year end as yofo_Year

Open in new window

0
 
LVL 34

Expert Comment

by:James0628
ID: 22701457
So, you changed the year in the data.  Well, I guess that's one approach.  :-)  As long as you remember that that is the fiscal year and not the calendar year.  Otherwise, it could cause some confusion down the line.

 James
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 22967526
PAQed with points refunded (250)

Computer101
EE Admin
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now