How to treat null values in static parameter

[MegOBytes]

Crystal XI
SQL Server

I tryiny to write a report where i ask the user for record selection based on values in the database field. The field can have several different values including nothing.
In my prompt i list the values but i am having a problem of how to represent blank value - I have a row in the prompt section that just has Blank for the description and no value in the value column. the user can select one of the values or they can select a combination of values which could include the blank value.

in my select formula i have:

if {?OrderType} in ("WO", "PO", "") then
{OrdType} = {?OrderType}

This will only return records that have a value (WO, PO) in the {OrdType} field even if the user picked the Blank parameter.

Where am i going wrong?



Thanks

Meg

 

[Turkbear]

Hi,
If you really want the user to select records where that field is blank then depending on your database you need to handle it separately from the other values like ;

If Trim({?OrderType}) = "" 
then
 IsNull({OrdType})
Else
 {OrdType} = {?OrderType}

If , however, you mean to have NO selection criteria applied when the user select the blank param then you should
add 'Any' to the choices ( delete the blank) and use this selection formula :

if {?OrderType} = 'Any' 
 then
   True
Else
 {OrdType} = {?OrderType}

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[MegOBytes]

thanks for your input.

i think i need to treat the blank value as a null because the record (inventory transaction) may or may not have a order type, so blank is a valid value. 2 issues arise.

1. the code produced an error message stating the "this array must be subscripted" . I have tried entering [1 to 6]
(the number of values in my prompt list including the blank) but that did not take.

2. the user can pick the 'blank' as well as any of the other values. i could not see how that gets incorporated into the code.

Thanks

Meg

 

[Turkbear]

Hi,
Trying to allow a blank as well as other values can present a real problem in the record selection formula, especially if the field in the database is actually NULL and not a blank( what database, by the way? some treat blanks as NULL while some differentiate between them).

Also, Crystal will stop searching for records if it finds a NULL in the data and that possibility has not been dealt with in the formula.

Let us know about the database type and maybe some a formula can be developed that would allow mixing NULLs with other values in a multiple value parameter, if needed.




To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[MegOBytes]

Thanks,

The databse is MS SQL SERVER 2005
ERP System: JD Edwards
Table: F4111

I used query analyzer to query the field in question:
Select * from testdta.f4111 where ILDCTO = ''

It returned all of the records with blank values. Changing the '' to ' ' (space betwwen the ') returned the same records. my conclusion then is that the field has blanks not nulls.

I was wondering of there are other JDE users on this forum who are faced with this problem. It is quite common in jde to select blank as a valid value (along with other non-blank values) in a record selection.

 

[lbass]

If you add a parameter option that is one space (add a description so you know what you've done), and then use a formula like this:

trim({table.field}) = {?parm}

...it might work. Or try it without the trim().

-LB

 

[MegOBytes]

Thanks for your reply.

This solution seems to be holding up- with trim. I was using "in {?parm}"

most grateful

Meg

Thanks

Meg