Crystal Reports If true then return number else return NULL

6
votes

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 of {numeric value} it doesn't make sense. Rather, I would rather have that field come up blank if the indicator isn't 'Y', but when I replace the 0 with NULL it gives me a type mismatch error.

Is there a way for me to only show the value when the indicator is 'Y'?

3
crystal-reports

3 Answers

10
votes

If you truly want a null value and not empty try the following

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

If {INDICATOR} = 'Y' then {numeric value}
else tonumber({@NULL})
1
votes
If {INDICATOR} = 'Y' then {numeric value}
else {Command.NULLCOL}

The setup in Database Expert is to use Add Command with sql:

select null as nullcol
from dual

Then left join to it.

A returned null value can be very powerful, so your need for a null value should not be questioned. Null values automatically display differently to stand out. Compared to 0 or "", null values work correctly with DistinctCount function. Null values also work correctly with section summaries and crosstabs, which can save you a lot of work which is the whole point of using crystal.

2
votes

you can't return two different datatypes in a single if statement..If if is number then else should also be number.. instead try to split the statements and try.. something like below.

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

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