Solved

Fiscal Year Report (formulas)

Posted on 2008-09-29
10
1,768 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
On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

 
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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

628 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