Adding to Formulas

[wanzek1]

I am having an issue when I add to formulas together. One of the formulas is a summary and the other is a formula that is adding 2 fields together. If the formula that is adding 2 fields togethers is zero than the result of teh entire formula keeps coming up as zero. Any ideas?

 

[pmax9999]

You will need to test for nulls. Change the formula that adds the 2 fields together to this:

If	Isnull({Table.Field1}) and
	Isnull({Table.Field2}) 
Then 	0
Else
If	Isnull({Table.Field1})
Then 	{Table.Field2}
Else
If	Isnull({Table.Field2})
Then 	{Table.Field1}
Else	{Table.Field1} + {Table.Field2}

Hope this helps.

Cheers
Pete.

 

[CoSpringsGuy]

pmax - I understand this solution and use it often. I just as often, however,get lazy and use the drop down box in the formula editor and select default value for Null..

What are the drawbacks of using this method rather than testing for null?

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[pmax9999]

Hi CoSpringsGuy

Your approach will or course work, as will setting the Convert NULL Values to Default under Options.

I don't think one way is necessarilly better than the other - I just stick with the 'manual' approach for consistency as it gives me less places to check when stuff isn't working as expected.

If I have lots of nulls to deal with in complex formulas, I often use SQL Expressions to convert the nulls to specific values (using Coalesce in SQL Server, or NVL in Oracle) at the database server level so as to avoid the complications that arise because of the need to to deal with nulls first.

As you know, most of the time there is at least a couple of ways to do things in Crystal. Often there are good reasons but not always.

It would be interesting to see hear the opinions of others.

Cheers
Pete

 

[Madawc]

CoSpringsGuy, nulls often mean something different from space or zeros, at least on the database I work with.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[CoSpringsGuy]

Thanks for the responses... I think you and i feel about the same way Pmax...

Madawc .... Elaborate if you get some time. Not saying I disagree with you at all but do you have an example? I feel pretty strongly that is not the case in my database but would love to know a scenario where that is the case so I can keep that in the back of my mind from here on out.

The only oddity I ran into in my database regarding null values was an instance where a field which contained a null value sometimes, could not be read at all in Crystal formulas or summaries until converted using an SQL expression //CONVERT( Call_Logging_Audit . ACCOUNT_NUM , "STRING")//. I never figured out why Crystal couldnt read it...

Anyway .. thanks for the discussion and my apologies to wanzek for tying up his forum question


_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[lbass]

I never use the "convert nulls to default values", although there might be cases when it makes sense. The value of leaving nulls is that inserted summaries won't count them--an approach you may sometimes want to use. In the case here, I would just change each formula to set the null to zero, as in:

if isnull({table.field}) then
0 else
{table.field}

Then reference the new formulas in the formula that combines them.

-LB

 

[Madawc]

I've found datafields that could be either space or null, an SQL database that is a daily copy of a mainframe.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP