Crystal Reports Optional Date Parameter

[former_member594221]

I have created a (Sybase) SQL query that allows for null parameter values in the where clause. I setup {dateParm} as a parameter in Crystal's Database Expert (Command) and embedded it in the SQL, like so:

select someColumn from someTable where (isnull({dateParm},getdate())=getdate() OR dt_column={dateParm})

I've made the parameter dynamic (provides all possible dates from the table) and OPTIONAL but when we leave the field blank zero records are returned. (Is there a better way to make parameters optional?)

If the parameter were a number my where clause would look like this:

...where (isnull({numParm},0)=0 OR {numParm}=0 OR num_column={numParm})
--assumes zero means nothing selected

If the parameter were a string my where clause would look like this

...where (isnull('{strParm}','zzz')='zzz' OR len(trim('{strParm}'))=0 OR str_column={strParm})

How do I make this work for a date?

PS Crystal Reports record selection formula is NOT an option here because there's a ton of data to pull then filter, better to pull only what we need from the DB

[vitaly_izmaylov]

What is the version of your Crystal Reports?

In the latest versions of Crystal Reports Designer "HasValue" function in Records Selection formula introduced to work with Optional Parameters.

Is there a reason to have Command Object connection?

[abhilash_kumar · Accepted solution]

Hi Kerrie,

When using a Command SQL, I've had success with "string" prompts being left blank to mimic the "Optional" prompt functionality.

However, this hasn't worked with Date prompts for me.

You could, however, add a "default" date value for the parameter and adjust the SQL to account for this default value like so:

select someColumn from someTable where({?dateParm} = '1989/01/01' OR dt_column= {?dateParm})

Having said that, is there a reason why you wouldn't create the report against the "Table" and use the "Optional prompt" feature that is available for parameters created via the "Field Explorer"?

The Record Selection formula does generate a 'where clause' therefore, only pulling what you need from the DB!

The Section formula, if you create an Optional Prompt inside the report, would look like this:

Not(HasValue({?DatePrompt})) OR {Date field} = {?DatePrompt}

P.S: When you create a report against a Command SQL and use the Record Selection formula, the report first pulls everything per the SQL and THEN applies the filter "locally" thereby slowing down the report.

-Abhilash

[former_member594221 · Accepted solution]

Thank you, Abhilash, for clearing up how record selection works as a where clause and not a filter, I misunderstood and am very glad for your assistance. Also, choosing a default date that indicates a null (optional) is very clever, thank you for that.

[former_member594221]

Crystal Reports 2016 Support Pack 5

[former_member594221]

You ask, Is there a reason to have Command Object connection?

There's a ton of data to pull then filter via record selection formula, better to pull ONLY what we need from the DB