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

Number of rows

Crystal Reports does not perform set arithmetic correctly. The columns in an 'OR' statement are not selected independently as they should before the results are merged. Crystal Reports performs the selection on all records in the first column of the 'OR' statement that are not null. It then assumes that all other columns in the 'OR' statement have the same null records and ignores those rows in the subsequent comparisons. This leads to missed records that have a first column that is null but meet another condition in the 'OR' statement.

For the example:

({SR_ACADEMIC_FAC.PRIM_MINOR1_CODE} = "ECN8" or
{SR_ACADEMIC_FAC.PRIM_MINOR2_CODE} = "ECN8"

What Crystal does is gathers all students with a PRIM_MINOR1_CODE that is not null. From this reduced set of students it first gathers all students with "ECN8" as a PRIM_MINOR1_CODE and next gathers all students with "ECN8" as a PRIM_MINOR2_CODE. It then merges both groups into the report. What is the problem? We are missing all students with a PRIM_MINOR1_CODE that is null, but have a PRIM_MINOR2_CODE of "ECN8". And as a result, an incorrect number of rows are returned.

In other words (those written in SQL):

What is wanted?

SELECT ID, NAME
FROM SR_ACADEMIC_FAC
WHERE PRIM_MINOR1_CODE = "ECN8" OR
PRIM_MINOR2_CODE = "ECN8"

What Crystal Reports returns?

SELECT ID, NAME
FROM SR_ACADEMIC_FAC
WHERE PRIM_MINOR1_CODE IS NOT NULL AND
(PRIM_MINOR1_CODE = "ECN8" OR
PRIM_MINOR2_CODE = "ECN8")

What is missing?

SELECT ID, NAME
FROM SR_ACADEMIC_FAC
WHERE PRIM_MINOR1_CODE IS NULL AND
PRIM_MINOR2_CODE = "ECN8"

How can the problem be corrected?

  1. Use a formula that checks for null values.

    By creating a formula that checks for null values, Crystal Reports is forced to check all records and therefore does not ignore any fields. For example:

    ((not isnull({SR_ACADEMIC_FAC.PRIM_MINOR1_CODE}) and
    {SR_ACADEMIC_FAC.PRIM_MINOR1_CODE} = "ECN8") or
    (not isnull({SR_ACADEMIC_FAC.PRIM_MINOR2_CODE}) and
    {SR_ACADEMIC_FAC.PRIM_MINOR2_CODE}="ECN8"))

  2. Use the 'Convert NULL Field Value to Default' option.

    Caution, this solution can solve the row count issue, but can cause problems with sums, totals and averages. Please read the next section for more information on when to use this solution.

  3. Use the Crystal SQL Designer.

    The Crystal SQL Designer does not have the same limitations that Crystal Reports has in regard to set arithmetic, however to use Crystal SQL Designer you must write your query separately from creating the report and you need to be more familiar with writing SQL. The Selection Expert in the Crystal SQL Designer is not as thorough as the Report Expert in Crystal Reports but allows for more control. Once you create a selection in the Crystal SQL Designer, you have open that selection in Crystal Reports to createa report or export your data to Excel.

Back to top