Question Re: Case Statement Formula in Technology Q&A

Case Statement Formula

Former Member — Thu, 05 Mar 2015 16:09:48 GMT

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'.

Re: Case Statement Formula

abhilash_kumar — Thu, 05 Mar 2015 19:08:53 GMT

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

Re: Case Statement Formula

former_member292966 — Thu, 05 Mar 2015 19:12:25 GMT

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

Re: Case Statement Formula

Former Member — Thu, 05 Mar 2015 22:07:24 GMT

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'

Re: Case Statement Formula

Former Member — Thu, 05 Mar 2015 22:10:39 GMT

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.