Solved

Fiscal Year Report (formulas)

Posted on 2008-09-29
10
1,754 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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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.

 
LVL 100

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 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

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

Suggested Solutions

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…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

791 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