Solved

Fiscal Year Report (formulas)

Posted on 2008-09-29
10
1,764 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 35

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
Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

 
LVL 101

Expert Comment

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

mlmcc
0
 
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 35

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 35

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
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…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

728 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