Case Statement Formula

[Former Member]

Can someone please help? I am having difficulties lableing blank fields using a case statement formula.  Example:

select {Mtr_Test.Action_Type_Cd}

case   ' '   :    'No Action Taken'

case   '0'   :   'No Action Taken'

case   '1'   :   'Send to Manufacturer RMA'

case   '2'   :   'Repaired in Shop'

case   '3'   :   'Retired'

default : 'Not Found'

I would like to treat a blank field the same as if it where case '0'  :  'No Action Taken'.

[abhilash_kumar · Accepted solution]

Hi Steve,

Look for an option on the top in the Formula Editor called 'Exceptions for Nulls'; set that to 'Default Values for Nulls'.

This will convert any NULLs in the {Mtr_Test.Action_Type_Cd} column to blanks if it is a string field.

-Abhilash

[Former Member · Accepted solution]

Abhilash,

Awesome. That worked perfectly.  {Mtr_Test.Action_Type_Cd}'s that are 0 and blank, now are listed in my report as 'No Action Taken'.  Brian also suggested using the Report Options settings which accomplished the same thing.  However, that seems to be a more global setting.  I chose to use the 'Exception for Nulls' at the formula level.  In either case, this new found knowledge will prove quite useul. Thanks again to both you and Brian!

select {Mtr_Test.Action_Type_Cd}

case   ' '   :    'No Action Taken'

case   '0'   :   'No Action Taken'

case   '1'   :   'Send to Manufacturer RMA'

case   '2'   :   'Repaired in Shop'

case   '3'   :   'Retired'

default : 'Not Found'

[former_member292966]

Hi Steve,

Do you get an error when you run the formula or does it display Not Found? 

Is Action_Type_Cd a numeric field or is it a string? 

I suspect the empty value may actually be a null and not an empty string.  You can tell the report to change the null to the default value for the field.  When you open the report, go to File | Report Options.  There is an option for Convert Null Values to Default.  If the field is numeric, it will change the null to 0.  If the field is a string it will change the null to an empty string. 

Good luck,

Brian

[Former Member]

Brian,

As you can see in my responce to Abhilash, That worked perfectly.  I chose to use Abhilash solution as stated above. Both of you were on the mark.  Thanks again.