FINANCIAL YEAR TO DATE 2

[kettie]

My report requires me to report on total absences for the current financial year. The Australian financial year runs from 1 July to 30 June.

Is there some way I can put a selection criteria in the report that will automatically return records pertaining to the current financial year only, without the need to visit the report at the end of each financial year and reset the date?

At present my selection criteria is :

{EMPIC.PIC_RUN_DATEC} in Date (2002, 07, 01) to Date (2003, 06, 30), but I don't want to have to change the year each end of financial year

 

[Reebo99]

Goto Selection formula, and change to the following:

{EMPIC.PIC_RUN_DATEC} in Date (year(currentdate), 07, 01) to Date (year(currentdate), 06, 30)

This should work, tell me how you get on. Reebo
Scotland (Going mad in the mist!)

 

[lbass]

There might be a better way, but something like this should at least work:

if Month(CurrentDate) in [7 to 12] then
{yourdatefield} in (Year(CurrentDate), 07, 01) to (Year(CurrentDate)+1, 06, 30) else
if Month(CurrentDate) in [1 to 6] then
{yourdatefield} in (Year(CurrentDate)-1, 07, 01) to (Year(CurrentDate), 06, 30)

-LB

 

[kettie]

Thanks Reebo & lbass

Alas, neither of your solutions worked.

Reebo - your formula returns nil rows
lBass - I receive an error message "The ) is missing" (the cursor then moves to before the comma after (Year(CurrentDate)

I have played around with adding commas and brackets but am getting myself in a tangle but I seem to be having a bit of a 'blonde' day!

Any other suggestions that may help

Kettie

 

[lbass]

Sorry, forgot to put in "Date"--perhaps because I AM blonde:

if Month(CurrentDate) in [7 to 12] then
{yourdatefield} in Date(Year(CurrentDate), 07, 01) to Date(Year(CurrentDate)+1, 06, 30) else
if Month(CurrentDate) in [1 to 6] then
{yourdatefield} in Date(Year(CurrentDate)-1, 07, 01) to Date(Year(CurrentDate), 06, 30)

Reebo's formula might be returning no rows because it translates to 7/1/03 to 6/30/03, i.e., from 7/1 to the day before.

I don't think my if/then formula will make it into the SQL Statement, so this approach might be slower than if you used a parameter. Using a parameter with a pick list that included a few fiscal years (as defined by the ending year), e.g., 2004, 2005, 2006, etc., you could replace years in all date formulas in your report with the parameter. Your select statement would look like:

{yourdatefield} in Date(?FiscalYear-1, 07, 01) to Date(?FiscalYear, 06, 30)

-LB

 

[kenhamady]

You could simply convert the date into the fiscal year using:

year (DateField + 184)

This will return a year number like 2003, 2002, etc. and you can then select by year. The number 184 represents the number of days from July 1 to Jan 1.

You do the US Federal year by using 92. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[kettie]

Thanks for everyone's assistance.

I now have the report working with a Year to Date formula.

Kettie