Question Re: Null Parameter Values - Crystal 9 in Technology Q&A

Null Parameter Values - Crystal 9

Former Member — Tue, 01 Sep 2009 17:42:35 GMT

I have several reports that require values be entered into a number of parameters by the end user running the report from the Crystal IDE.

1. If no value is entered in a parameter with a string data type, no records are returned. I check in the record selection formula for a null parameter value as follows:

(If IsNull(?Parameter 1) Then

True //Show all records

Else

(database field)=(?Parameter 1)

And

(If IsNull(?Parameter 2) Then

True //Show all records for this condition

Else

(database field)=(?Parameter 2)

And

So on... until all my parameters

It appears as though the record selection criteria sees a null in the or any parameter parmeter, and returns no records.

I would like for the end user to be able to not enter any parameters, and have the record selection criteria return the records based on the defaults in the record selection criteria.

I have over 20 reports for 12 regions (20*12=240) 240 reports that have a minimum of 12 parameters each. The end users have to at least click on the parameter field when prompted so the record selection criteria will return the correct records..

2. Another issue is I have a date parameter that the end user enters a to and from range.

I want to select the records that fall in the user entered date range and also include records that have a null as the value for this date field in our table.

If I use as an example:

{Table.Date_Field_Name}={?Status_End_Date} AND IsNull({Table.Date_Field_Name})

This does not work as you would expect. If I remember correctly, It returned no records, but

IsNull({Table.Date_Field_Name}) AND {Table.Date_Field_Name}={?Status_End_Date}

This seemed to work. Can you tell me why one way would work and the other will not?

Thank you in Advance for your comments.

Re: Null Parameter Values - Crystal 9

Former Member — Tue, 01 Sep 2009 17:52:10 GMT

Sorry, Here is a correction:

If I use as an example:

{Table.Date_Field_Name}={?Status_End_Date} OR IsNull({Table.Date_Field_Name})

This does not work as you would expect. If I remember correctly, It returned no records, but

IsNull({Table.Date_Field_Name}) OR {Table.Date_Field_Name}={?Status_End_Date}

This seemed to work. Can you tell me why one way would work and the other will not?

Re: Null Parameter Values - Crystal 9

Former Member — Wed, 02 Sep 2009 04:39:15 GMT

With the "OR" condition..if the 1st condition is true it will not check for the next condition.

In your case IsNull({Table.Date_Field_Name}) is true thats why it returned records when IsNull({Table.Date_Field_Name}) OR {Table.Date_Field_Name}={?Status_End_Date}.

HTH,

Jyothi

Re: Null Parameter Values - Crystal 9

Former Member — Wed, 02 Sep 2009 17:35:00 GMT

... and as soon as Crystal "sees" a null field outside of an IsNull() function it stops processing the formula. (IMHO, this makes no sense, but that's the way it works.) So by using IsNull() first in the OR, it evaluates the OR as True, whereas putting it second causes Crystal to see the null parameter and halt evaluation.

HTH,

Carl

Re: Null Parameter Values - Crystal 9

Former Member — Wed, 02 Sep 2009 18:25:00 GMT

Thank you both for your comments. What you both say does make sense. I will always test for IsNull() first and I should be OK.

Any idea about my empty or NULL parameter issue I mentioned at the top of this message?

Regards,

Cort Collins

Re: Null Parameter Values - Crystal 9

Former Member — Wed, 02 Sep 2009 18:47:48 GMT

Create the formula (basic syntax):


if IsNull({?Parameter 1}) then
  formula = "<NULL>"
else
  if {?Parameter 1} = "" then
    formula = "<EMPTY>"
  else
    formula = {?Parameter 1}
  end if
end if

and place it on the report header. Run the report without entering {Parameter 1} and see what displays for the formula. (I.e., is the parameter NULL or is it just empty? If it's seen as empty, then your selection formula as written will not match any records.)

HTH,

Carl