Optional Date Parameters

[Paults]

Optional Date Parameters

I want to create a report where the users have the option to enter a one to many different parameters; or in other words - we are using the report as a search facility. For example ... I user can enter a state, a business code, a filing date, or an analysts name in order to search for the filings that fit the criteria. I've been able to bypass most parameters by using Trim, Len , and or clauses in the record selection.

For example:
I have a Formula name MissingGAC with the syntax:

if isnull({?GAC}[1]) then
1
else if len(trim({?GAC}[1])) = 0 then
1
else
0

Then in my Record Selection I enter the clause

({@MissingGAC} = 1 OR {REPORT_SUBMISSION.C_GAC} in {?GAC})
//parameter was set so go do what you want to do with the parameter

However I've run into a problem with Date Parameters; I can't leave then at null or use Trim and Len on them
.I'd like to use Date Parms (to verify dates, and use the calendar functions built into crystal, however I don't know how to make an optional date parameter without Strings.

Also, even with Strings as dates - Its tricky to convert strings to dates and include all the logic to verify the date all within crystal.

Can anyone offer me a solution.

Thanks

 

[SmpTraining]

Several solutions:

1. Default the start and end dates to some unlikely range and in the user prompt tell them to leave the dates untouched if date selection is not required, then test for the unchanged dates in the formula.

2. Don't set a default, tell the user to leave as is for ALL dates. Test to see whether the start and end dates are todays date and if so, don't select based on dates.

Also, using formulas in your selection criteria is not very efficient. Instead, try putting the formula code in your selection criteria. E.g.

Record Seclection Criteria:

(if (isnull({?GAC}) or {?GAC} = "&quot then
true
else
{your_field} = {?GAC})


Steve Phillips, Crystal Consultant

 

[Paults]

I can make the record selection everydate however what about null date.

For example. If i have a date completed. It won't get filled in until late. It wouldn't get picked up in the criteria.

 

[SmpTraining]

To select a range of dates AND nulls use this in the criteria:

( isnull({date_field}) or {date_field} in {?startDate} to {?EndDate})

However, your earlier post suggested you want the users to optionally supply a date range. The way to achieve this is to use one of the suggestions I made earlier and then use that in the selection criteria.

For example. Say you decide that if users leave the date prompts to todays date then all dates should be selected (including nulls) then you will need something like this in the selection formula:

(if {?startdate} = currentdate and {?enddate} = current date then 
   true
else
   {your_data_date_field} in {startdate} to {?enddate})

Crystal doesn't mind you using 'if' statements in your selection formula. Notice how using the 'true' statment means if the user leaves both dates as todays date, crystal never actually applys any filter on the date field in the database, therefore you even get the nulls returned. Steve Phillips, Crystal Consultant