Null (Missing) Values in Calculations and Summaries

The data source that you use determines how null (or missing) values are handled in calculations, summaries, and other expressions.

Null values represent missing data values. For example, a new eyewear product, called Abby, is introduced in 2012. Therefore there are no sales in 2011. If you create a report with the years 2011 and 2012, the values for sales of Abby for the year 2011 are null (missing).

By default, null values appear as blank cells in a crosstab or list report. You can change the default to specify the display of other characters, such as the word null, by changing the data format of the Missing Value Characters property for the cells or report. For more information, see Set the Default Data Formats.

Arithmetic Operations

Null values in arithmetic operations yield different results depending on the type of data source.

In Microsoft SQL Server Analysis Services (SSAS) and Cubing Services data sources, a null value in arithmetic operations is treated as follows:

In operations such as addition (+), a null value yields the same result as zero (9 + NULL = 9)
In operations such as multiplication (*), a null value yields a null result (9 * NULL = NULL)

You can accommodate this behavior by using more complex expressions such as the following:

if ([M] is not NULL, then ([M]) else (0)
if ([M] is not NULL, then (<expression involving M> else NULL)

In relational and other OLAP data sources the result of an operation that includes a null value is always null. For example, 9 + NULL = NULL.

Equality Comparisons

Equality comparisons, such as equal to (=) and not equal to (<>), compare two values and return a Boolean value of true or false.

For all data sources, equality comparisons are treated as tests for missing data. Inequality comparisons are treated as tests for non-missing data.

For example, NULL=NULL is true and NULL=0 is false.

Ordered Comparisons

In ordered comparisons, such as rank and quantile functions, null values are handled as follows.

For relational data sources, the result of the comparison is always false. For example, NULL < 1 is false.

For Microsoft SSAS, Cubing Services, and other OLAP data sources, the null value is treated as zero. For example, NULL < 1 is true.

Boolean Functions

In Boolean operations, such as or, null values are handled as follows.

For relational and dimensionally-modeled relational data sources, the result of a Boolean operation is always null. Boolean functions are used in conditions and the null result is treated as a false condition. For example, NULL OR TRUE = NULL (FALSE)

For Microsoft SSAS, Cubing Services, and other OLAP data sources, the null value is treated as if it is false. For example, NULL OR TRUE = TRUE.

Summary Functions

All summary functions, such as aggregate, average, and maximum, ignore null values. For all functions except count, if all values in the expression are null, the result is null. For count, if all values in the expression are null, the result is zero.

For example, if the value for Quantity for 2013 is 10 and the values for 2011 and 2012 are null, then the average is as follows:

average ([Quantity] within set set([2012], [2013] ) ) = 10
average ([Quantity] within set set([2012], [2011] ) ) = NULL

The count is as follows:

count ([Quantity] within set set([2012], [2013] ) ) = 1
count ([Quantity] within set set([2012], [2011] ) ) = 0

String Operations

For all data sources, the result of string operations, such as concatenation (||), that include a null value is always null.

For example, 'A' || NULL = NULL and NULL || NULL = NULL.