ÎÛÎÛ²ÝÝ®ÊÓƵ

Sums, totals, averages

There is a flaw in the logic that converting null values to their default values will solve all issues with the null value problem in Crystal Reports.

Null values are an important element in a database because they signify the absence of value in a field. When one uses the 'Convert NULL Field Value to Default' option, a number becomes a zero, a string becomes "" (an empty string), and a date becomes 0/0/0. Because each element now has a value, the row count problem is solved, however reports that contain sums, totals, and averages are potentially flawed.

For example:

Suppose that you are trying to average the number of credit hours for the students in the row count example (those with either a PRIM_MINOR1_CODE of "ECN8" or a PRIM_MINOR2_CODE of "ECN8"). With the 'Convert NULL Field Value to Default' option selected, we will get the complete list of students, however those with credit hours that were null will now have credit hours that are zero. The average calculation will now include the zero values (null values are ignored in a calculation) and the average reported therefore will be far less then correct.

Back to top