Why NULL fields converted to zero in Crystal? 2

[hallux]

Hello,
I noticed that when I insert into details a number field object that I know has a null value, it displays as 0. Shouldn't it display as a blank?
Is this expected?

Environment: Using Developer 8.5, ODBC driver to Oracle 9.

-Hallux

 

[bdreed35]

Look under File, Options, then the Reporting Tab.
Do you have "Convert Database NULL values to default" set?

Also, check that the property isn't set at the report level under File, Report Options.

~Brian

 

[synapsevampire]

Which Oracle ODBC driver, Oracle or Crystal?

You should be using the Crystal supplied one, but keep in mind that Oracle 9 wasn't out when CR 8.5 came out, so you may have some problems.

You consider using the Crystal native connectivity as it's even faster.

If Brian's post doesn't resolve (that's likely the culprit), try changing connectivity.

-k

 

[Ngolem]

a lot of people like converting numeric nulls to zero's. This allows them to blissfully create reports without having to worry about null's interfering with their report formulas.

Personally I do not like this approach since a null usually indicates a data problem , while a zero value can be a valid result.

By automatically converting nulls to zeros for numerics and string nulls to whatever, you eliminate the ability to diagnoze your results.

I would much rather have all my convert Nulls switched off and deal with real data.....sounds like you do too....follow Brian's advice

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[synapsevampire]

Jim: Nulls don't necessarily represent a problem, they generally identify that an entity has never existed, and are very useful as such.

If the design of the database allows for nulls, yet null has no unique value with respect to business rules, then it makes sense for people to turn them off.

If you differentiate a null from a blank/zero as an error, I'd suggest a redesign which would change that to null as the default (takes less space), and treat a blank/zero as the error (which is more often the case for databases I work on).

-k

 

[Madawc]

If you are going to allow nulls, remember that you must test for them first in any formula field. Thus:

if isnull ({yourfield}) then "No data"
else if {yourfield} = 0 then "Zero"
else "Value of " & ToText({yourfield})

Whereas

if {yourfield} = 0 then "Zero"
else if isnull ({yourfield}) then "No data"
else "Value of " & ToText({yourfield})

would remain blank when it finds a null. I made much this error when I first started using Crystal.

Madawc Williams
East Anglia, Great Britain

 

[hallux]

Thanks for the replies:

No, "Convert Database NULL values to default" is not set.

Am using Oracle ODBC Driver, SQORA32.dll 8.01.57.00, I will speak with the vendor about an upgrade recommendation for the driver.

Yes, I prefer it to have the null simply blank, not 0. In Oracle I can use the NVL function to separate out the nulls when analyzing data. So, I would like to deal with a zero differently from a null. Having not designed databases, I don't know the ins and outs, but prefer to have a null (nothing entered) separated from an entered value 0 or other number.

I appreciate all of your input.
-Hallux

 

[Ngolem]

SV - your post confuses me a little...it seems like both agreement and disagreement. My personal position....and I admit it to be a personal position...is to treat nulls as they exist in the database. I do not automatically convert them to any particular value, since by doing so it I find it makes life simpler for me in maintaining my reports. I am not fooled by data that really doesn't exist.

I also as I am sure you are aware a fan of 3 formula sums rather than using summary functions...especially where averaging or other similar functions. When nulls are a fact of the database, I avoid all errors of factoring in data that truly does not exist with respect to the reporting objective.

I agree you that ideally the database should be designed to avoid these null situations but BUT as a consultant whose SOLE function is to create reports from existing databases....I don't have the luxury of tailoring the database to my needs...in fact in most cases I am expressly forbidden from even developing a stored proceedure to make my life easier since I have no permissions and the the dba staff are too busy on other issues to worry about "trivial", in their mind, aspects of making my life easier, especially since they often believe that such changes will negatively affect their core operations.

I cannot tell you the number of times that through developing reports in this manner, I have found problems in their database...especially on in-house software.

So, those are the facts of my existance. Treating the database as it truly is and not artifically changing it is simpler for me...and frankly is a trivial problem to deal with...with experience.

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!