Preparing Data for Display in a Chart Data Region (Report Builder 2.0)

Charts provide a summary view of your data. However, with large datasets, the information on a chart can become obscured or unreadable. Missing or null data points, data types ill-suited to the type of chart, and advanced applications such as combining charts with tables can all affect the readability of a chart. Before designing a chart, you should carefully prepare and understand your data so that you can design your charts quickly and efficiently.

Dataset Considerations Before Using the Chart

A chart, like any other data region such as a matrix or table, is bound to a single dataset. If you want to display multiple datasets on the same chart, you can create an additional dataset that uses a JOIN or UNION statement in your SQL query before adding data to the chart. For more information about the JOIN and UNION statement, see Books Online or another SQL reference. You can have as many charts in your report as you want.

Consider pre-aggregating data in the dataset query if detail data is not necessary or useful. To display each data point more clearly, reduce the number of categories in your dataset. You can filter the dataset or add a condition to your query that reduces the number of rows returned.

Most chart types require numeric values along the value axis, which is typically the y-axis, in order to draw correctly. If the data type of your value field is String, the chart will be unable to display a numeric value, even if there are numerals in the fields. Instead, the chart will display a count of the total number of rows that contain a value in that field. To avoid this behavior, make sure that the fields that you use for value series have numeric data types, as opposed to strings that contain formatted numbers.

Best Practices when Displaying Data on a Chart

Charts are most effective when the number of elements that are displayed presents a clear image of the underlying information. Some charts, like scatter graphs, benefit from numerous data points, while others, like pie charts, are more effective with fewer data points. You should carefully choose a chart type based on the values in your dataset and how you want this information to be shown. For more information, see Chart Types (Report Builder 2.0).

There are several ways you can consolidate data on a chart:

  • When using a pie chart, collect small slices into one slice called "Other". This will reduce the number of slices on your pie chart. For more information, see How to: Collect Small Slices on a Pie Chart (Report Builder 2.0).

  • Avoid using data point labels when there are numerous data points. Data point labels are most effective when there are only a few points on the chart.

  • Filter unwanted or irrelevant data. This helps you highlight the key data that you are trying to show on the chart. For more information about how to filter data in Reporting Services, seeĀ How to: Add a Filter (Report Builder 2.0).

  • If you want to add a bar graph to display ratio data in a table or matrix template, consider using a linear gauge instead. Gauges are better suited for showing a single value inside a cell. For more information, see Nesting Data Regions (Report Builder 2.0).

Empty or Null Values in a Field in a Dataset

The chart processes 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 How to: Add Empty Points to the Chart (Report Builder 2.0).

  • 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.

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 How to: Add a Filter (Report Builder 2.0).

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 Understanding Report Datasets (Report Builder 2.0).