Working with null values

Learn how null values work in Reports, and use different strategies to fix issues related to null values.

How it works

All aggregation functions ignore null values. This occurs because Reports cannot add a null value to a number. Null values display when a field in Projects, Results, or Strategy is not required.

Example calculations

  • COUNT(1, 2 , 2 , 3 , null ,null) = 4
  • COUNT DISTINCT (1, 2, 2, 3, null, null) = 3
  • SUM (1, 2, 2, 3, null, null) = 8
  • AVG (1, 2, 2, 3, null, null) = 2
  • MIN (1, 2, 2, 3, null, null) = 1
  • MAX (1, 2, 2, 3, null, null) = 3

How do I fix null values?

You can fix null values by doing any of the following:

  • creating a calculated field
  • creating a filter
  • specifying a value for each record in the field

Creating a calculated field

You can fix null values by creating a calculated field that displays 0 if the value is null and apply an aggregation function on the calculated field:

Example

  1. Create the following calculated field:
    • Calculated Field Name c_Days_To_Remediate
    • Formula Type Simple
    • SyntaxCASE WHEN Days To Remediate IS NULL THEN 0 ELSE Days To Remediate END
  2. Apply the appropriate aggregation function to the calculated field.

Result The aggregation is applied correctly.

Creating a filter or creating a calculated field

If you are reporting on timesheets data, you may need to either filter out time entries that belong to the organization, or create a calculated field to replace the Project Name value with the name of your organization.

Note

Using the Timesheets feature in Projects, you can add time entries for administrative tasks that do not belong to any particular project. If these entries exist, the Project Name field contains null values.

Examples

Option 1: Create a filter

Create a Project Name filter to filter out any entry where Project Name Is Not Null.

By adding this filter, you are filtering out time entries that are associated with the organization, rather than with a particular project.

Option 2: Create a calculated field

Create the following calculated field:

  • Calculated Field Name c_Time_Entries
  • Formula Type Simple
  • SyntaxCASE WHEN Project Name IS NULL THEN 'organization_name' ELSE Project Name END

Result The aggregation is applied correctly.

Specifying a value for each record in the field

Example

The table below shows null values in the Days to Remediate field.

If you applied a Sum aggregation on Days to Remediate, the value would be null.

Project Folder Days to Remediate
Compliance 100
Risk Management  
SOX  

The table below does not have null values in the Days to Remediate field.

If you applied a Sum aggregation on Days to Remediate, the aggregation will apply correctly.

Project Folder Days to Remediate
Compliance 100
Risk Management 40
SOX 25