FY VARIABLE

[herrld]

I don't know if this is possible, but if so, can someone help.
I would like to include a parameter (?Fiscal Year) in my record selection, such as...
Table.Date in ?Fiscal Year

I don't want the user to have to enter actual dates in the parameter because I only want to pull one fiscal year at a time and I can't trust the user to enter the dates correctly.

I've created default values for the ?Fiscal Year parameter.
They are FY2001, FY2002, FY2003, etc.
(our FY is July01-June30)
Can I create a variable that will identify FY2001 as the date range 07/01/01 to 06/30/02, and so on. So that when the user chooses FY2001 (or whatever) the parameter will use the correct date range?

If I'm doing this totally wrong, and there's an easier way, I'm open to that!!
Linda

 

[synapsevampire]

Sure, create the parm as a string and prepopulate it, then create 2 formulas as in the following:

@Start Date
If {?FY Choice} = "FY2001" then
cdate(2001,1,1)
else if...

@End Date
If {?FY Choice} = "FY2001" then
cdate(2001,12,31)
else if...

Now in the record selection formula place:
(
{table.datefield} >= @start date
and
{table.datefield} <= @end date

This will create the SQL and pass it to the database for optimal performance.

If your table has datetime types, use cdatetime and fully qualify the values, as in:

cdatetime(2001,1,1,0,0,0)
and
cdatetime(2001,12,31,11,59,59)

-k

 

[herrld]

Thanks so much. This report I've been working on has turned my brain to mush! Linda