Empty and Null Data Points in Charts (Report Builder and SSRS)

  • Article

If you are displaying fields with empty or null values in your chart, the chart may not look as you expect. Charts process empty values differently depending on the specified chart type:

  • If the chart type is a linear chart type (bar, column, scatter, line, area, range), empty values are displayed as empty spaces or "gaps" in the chart. If you want to indicate empty points, you must add empty point placeholders. For more information, see Add Empty Points to the Chart (Report Builder and SSRS).

  • If the chart type is a contiguous, linear chart type (area, bar, column, line, scatter), empty data points are added to the chart to maintain continuity in the series.

  • If the chart type is a nonlinear chart type (polar, pie, doughnut, funnel or pyramid), empty values are omitted from display on the chart.

  • In shape chart types, null values are omitted.

An example of a chart with empty data points is available as a sample report. For more information about downloading this sample report and others, see SQL Server 2014Report Builder and Report Designer sample reports.

Note

You can create and modify report definitions (.rdl) in Report Builder and in Report Designer in SQL Server Data Tools. Each authoring environment provides different ways to create, open, and save reports and related items. For more information, see Designing Reports in Report Designer and Report Builder (SSRS) on the Web at microsoft.com.

Removing Empty or Null Values

To avoid obscuring important data, consider removing empty values from your dataset. To filter nulls, you can use the NOT IS NULL clause in your query. Alternatively, you can add a filtering expression that specifies that you only want to display values not equal to zero. For more information, see Add Dataset Filters, Data Region Filters, and Group Filters (Report Builder and SSRS).

Fields with No Values in a Chart

If a field does not contain any values in the returned dataset, the chart displays an empty chart with no data points, but the series name (typically the field name) is added as a legend item.

This behavior differs from the case where there are zero rows of data in the returned dataset, which can occur when the report is parameterized and the selected value returns an empty result set. If your dataset query returns zero rows of data, a message is displayed at run time to indicate that no data can be shown. You can customize this message by modifying the NoDataMessage caption for the report in the Properties pane. For more information, see Report Embedded Datasets and Shared Datasets (Report Builder and SSRS).

See Also

Charts (Report Builder and SSRS)
Formatting a Chart (Report Builder and SSRS)
Add a Chart to a Report (Report Builder and SSRS)
Troubleshoot Charts (Report Builder and SSRS)