Clever Geek Handbook

Crystal Reports If true then return number else return NULL

In Crystal Reports, is it possible to have a function that returns a numeric value if the if statement evaluates to true and returns NULL otherwise?

I currently have

IF ({INDICATOR} = 'Y') Then
(
    {numeric value}
)
Else
(
    0
);

But since 0 is a possible value {numeric value}, it doesn't make sense. Rather, I would prefer this field to look empty unless the indicator is "Y", but when I replace 0 with NULL it gives me a type mismatch error.

Is there a way to show only the value when the indicator is "Y"?

+3


source to share


3 answers


you cannot return two different data types in the same expression if

. If if

is a number, then there should still be a number .. instead try to split the operators and try .. something like below.



 IF ({INDICATOR} = 'Y') Then
    (
        ToText({numeric value})
    )

   Else if ({INDICATOR} <> 'Y') Then
    (
       ""
    );
+1


source


If you really want a null value and not empty try this

create a NULL formula, then save and close it without entering any data into the formula area. Then in your formula above try



If {INDICATOR} = 'Y' then {numeric value}
else tonumber({@NULL})
+5


source 


If {INDICATOR} = 'Y' then {numeric value}
else {Command.NULLCOL}

The setting in Database Expert is to use the Add command from sql:

select null as nullcol
from dual

Then left join with it.

The returned null value can be very powerful, so your need for a null value shouldn't be questioned. Zero values ​​are automatically displayed in different ways to stand out. Compared to 0 or "", null values ​​work correctly with the DistinctCount function. Zero values ​​also work correctly with section summaries and crosstabs, which can save you a lot of work, which is the whole point of using a crystal.

0


source






More articles:


All Articles