Handling null data points

Contents[Hide]

1. Overview

This article demonstrates how to handle null data points in the data visualizations. If there are rows that have no data, you could make a rule to those values in the table or chart.

Using "Missing data rule" option in the "Configure metric set element" panel window would allow you to display the data points that were hidden. By default, it is set to "None", thus, the null data points are not displayed.

2. Getting started

In this example, we will use the Sales.SalesPerson table from the AdventureWorks database. First, drop the SalesPerson.BusinessEntityID on the dashboard canvas to show the rows:

Table with Business Entity ID
Table with Business Entity ID

Then, drop SalesQuota in the Column area:

Adding SalesQuota in the Table
Adding SalesQuota in the Table

Notice that BusinessEntityID 274 is missing in the list after the SalesQuota was dropped in the table.

3. Using a missing data rule

To view the missing BusinessEntityID, open the Data Analysis Panel, and edit the measure (in this case, SalesQuota).

Edit Measure
Edit Measure

On the Configure Metric Set Element panel, under Metric Set Default Values, go to Missing Data Input Rule. In this drop-down, you will find options for which missing data points are used.

Missing Data Input Rule
Missing Data Input Rule

Here are the options:

  • Ignore Nulls (default) – does not include any null values. 
  • Source Nulls – includes only the null values present in the database.
  • Full Nulls – includes all null values. This includes null values that were generated by combining the involved hierarchy members.

Note
Working with Full Nulls consumes a lot of memory when working with multiple hierarchies.

Select the Source Nulls option. BusinessEntityID 274 shows an empty cell for SalesQuota. Go to Missing Data Output Rule. In this area, you will find options for how to display the missing data points.

Missing Data Output Rule
Missing Data Output Rule

Here are the options:

  • None – does not display the data point.
  • Empty – displays nothing on the data point.
  • Zero – displays zero on the data point.
  • Average – displays the average between the previous and next data points.
  • Average Non-Missing Data – displays the average between the previous and next non-empty data points.
  • Rolling Average – displays the average of the previous data points.

Select the Empty option. In the Empty Value Text field, indicate N/A.

Indicate N/A for Empty Value Text
Indicate N/A for Empty Value Text

BusinessEntityID 274 shows N/A for SalesQuota.

Table with N/A values
Table with N/A values

4. Styling missing data cells

You can style cells with missing data to look different from actual data cells. Open the Properties tab for the table.

In the Look section, select Missing Data Style.

Select Missing Data Style
Select Missing Data Style

Set the Cell Color and Font Color field to distinguish cells showing data calculated by the missing data rule.

Set the cell and font colors
Set the cell and font colors

5. See also

 

Dundas Data Visualization, Inc.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours: 7am-6pm, ET, Mon-Fri