Crystal Reports formula with NULL values

Question

I am using Crystal Reports 2008.

I am having issues getting the Syntax correct for a formula.

The report is grouped by customers. I have 2 commands. The first one will always have a value but the second one has a value for some of the customers but not all.

What I need to do is write a formula that will use just a value of the first command if the second command does not have a value for that customer. If the second command does have a value then it should take the sum of command one and two.

I have tried it like this:

IF ISNULL({Command_2.fin_bal_60}) THEN

  {Command_1.inv_bal_60}

ELSE

  {Command_1.inv_bal_60} + {Command_2.fin_bal_60}

That is not working because when I put it into the report it only shows the records that have a value in command_2.

I am trying to figure out how to write this so even if command_2 does not return a value it just uses command_1.

Any help on this would be great.

Thanks in advance.

Stupid question: why are you using two Command objects? How are you joining the two Commands? Wouldn't it be easier to build the query in a single Command object? — craig

Unknown Unknown · Accepted Answer · 2012-07-25T07:19:38

The formula seems to be correct, so you don't have to look there.

I think the problem is in the way you have linked the table "Command_2" (menu [Database, Database Expert]). Assuming it's an outer table, you may have a good chance to solve this when you change the link type from "INNER JOIN" to "LEFT OUTER JOIN" (double-click on the link line to change the setting).

If you don't use field from a table then Crystal Reports ignores the table and doesn't use it in the underlying SQL query. So, in that case this problem won't occur. That's explains why it only occurs when you put the formula field in the report as then you use the "Command_2" table.

Crystal Reports formula with NULL values

1 Answers