Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# Fiscal Year Report (formulas)

Posted on 2008-09-29
Medium Priority
1,794 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
Question by:Mohammed_Triad
• 3
• 2
• 2
• +2
10 Comments

LVL 17

Expert Comment

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

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

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'))
``````
0

LVL 101

Expert Comment

ID: 22668896
What about February and March?

mlmcc
0

LVL 17

Expert Comment

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

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

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

LVL 35

Expert Comment

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

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

Computer101
EE Admin
0

## Featured Post

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â€¦
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â€¦
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper cornâ€¦
###### Suggested Courses
Course of the Month13 days, 1 hour left to enroll

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

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