Only show records for the specified FY 1

[Roscoe307]

I have a report where the user enters a fiscal year (by typing it in a parameter in the following format YYYY). I need to only show data for the specified fiscal year depending on the dates of two fields ({PERF_START_DATE} and {PERF_END_DATE}). These fields should be used to determine the fiscal years that the record should display for. For example, if the user enters '2007' for the fiscal year then the only records that should display in the report should have a {PERF_START_DATE} OR {PERF_END_DATE} that falls in the 2007 FY or it includes the 2007 FY.

Any help is greatly appreciated!

 

[lbass]

You need to identify your FY start and end dates.

-LB

 

[Roscoe307]

Where should the FY dates be defined? Just in a formula that's included in my record selection that's used to filter the records?

Sorry, sometimes I tend to make things harder than they really are.

Thanks lbass for your help!

 

[lbass]

Please tell us what your fiscal year is.

-LB

 

[Roscoe307]

Our fiscal year is Oct 1 - Sept 30.

 

[Roscoe307]

Our fiscal year is from Oct 1 - Sept 30.

 

[lbass]

Let's assume that your fiscal year is identified by the ending year, so that FY 2007 = October 1, 2006 to September 30, 2007.

First create a parameter {?fiscalyear} and then to report->selection formula->record and enter:

{table.perf_start_date} < date({?fiscalyear},10,1) and
(
isnull({table.perf_end_date}) or
{table.perf_end_date} >= date({?fiscalyear}-1,10,1)
)

This assumes that you want to include any record that overlaps with the fiscal year, including records that start before the beginning of the fiscal year and either have no end date or have an end date within the fiscal year.

-LB

 

[Roscoe307]

Thanks! That worked perfectly. =)