Updated: Sun, 10/06/2024 - 10:30

From Saturday, Oct. 5 through Monday, Oct. 7, the Downtown and Macdonald Campuses will be open only to McGill students, employees and essential visitors. Many classes will be held online. Remote work required where possible. See Campus Public Safety website for details.

Du samedi 5 octobre au lundi 7 octobre, le campus du centre-ville et le campus Macdonald ne seront accessibles qu’aux étudiants et aux membres du personnel de l’Université McGill, ainsi qu’aux visiteurs essentiels. De nombreux cours auront lieu en ligne. Le personnel devra travailler à distance, si possible. Voir le site Web de la Direction de la protection et de la prévention pour plus de détails.

McGill.CA / Enrolment Services / SIS Data Warehouse / Crystal Reports / Null value problem

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