Convert Budget Period to Fiscal Year

[Hawkmek]

Formula assistance needed.
Using Crystal reports 7 (Damn State Government!)

I am trying to use field Budget Period in this format: MM/YYYY to determine Fiscal Year: YYYY. Our Fiscal Year runs Sep 1 thru August 31.

I can get this done in SQL, but am having syntax issues in Crystal.
I know what I WANT to do, I just don't know HOW to do it.

So 08/2005 would be Fiscal Year 2005,
09/2005 would be Fiscal Year 2006, etc.

Something like this:
If MM > 08 add one to YYYY
end

Thank you for the help, it's always a life-saver.

Hawkmek

 

[satinsilhouette]

if {datefield} = 08/2005 then 2005 else
if {datefield} = 09/2005 then 2006 else
if {datefield} = 10/2006 then 2006 else
if {datefield} = 11/2006 then 2006 else

etc.

If this were in a current version you could cut your code down by doing a case statement.

Is there a way for you to put a table in the database you are reporting off of that will do this conversion for you and then you just add this into your report? That would be much better than trying to keep up with this code year after year.

 

[synapsevampire]

So do you intend to use a parameter to filter data, or do you just want to isolate dates?

If you create the following formula you can determine the year:

if val(left({table.field},2)) > 8 then
val(right({table.field},4)) +1
else
val(right({table.field},4))

To filter for a given year, use Report->Edit Selection Formula->Record and place:

(
if val(left({table.field},2)) > 8 then
val(right({table.field},4)) +1 = {?MyDateParm}
else
val(right({table.field},4)) = {?MyDateParm}
)

This won't pass to the database though.

I would suggest building out a view which applies the proper year in anotehr field.

There's also the option of using a SQL Expression (not sure if CR 7 supported them) to build out the field, unfortunately you didn't post your database type so I can't give specifics, but a SQL Esxspression is processed on the database and would look something like:

if cast(substr(table.field,1,2) as numeric) > 8 then
cast(substr(table.field,4,4) as numeric)+1
else
cast(substr(table.field,4,4) as numeric)

Sounds like you know SQL, so this should be simple for you.

Then you can use the SQL Expression as a database field for whatever you need.

-k

 

[Hawkmek]

Thanks for the quick replies.

We use an Oracle database thru PeopleSoft Financials 7.52

I did use a CASE statement in my PeopleSoft query manager. It works great and I will use that to verify the report.
I don't want to change the query because I have to make this change to 5 different reports that incorrectly sum the amounts by Fiscal Year.

I want only to migrate the Crystal layouts. I don't want to have to change and migrate 5 queries. I know this can be done in Crystal and with the replies I have already received I will get busy updating the layouts(tomorrow morning). Time to head home now, though!

I'll keep you posted, and thanks again,

Hawkmek

 

[satinsilhouette]

Hawkmek,
If you are coming through pplsft, could you define this in a bizview then, there4 u would not have to create and set up 4 crystal layouts.

 

[synapsevampire]

I shouldn't have wasted time guessing at the more efficient means when you didn't bother to take the time to post basic technical informsation such as the database/connectivity.

Resolving on the client side may seem easier now, but I would correct the datasource instead.

-k