Take full advantage of SQL Server Reporting services diagrams

This article applies to Microsoft SQL Server Reporting Services

Brief introduction

This white paper describes how to design a chart in a Microsoft SQL Server Reporting Services report. This article is divided into several sections and refers to specific report examples, which are included in the sample project download.

The first part is data preparation, which mainly includes specific information, techniques and insights on the preparation of data. The second part is the chart label, which describes how to apply the label settings to improve the chart and control the visual appearance and effect.

The sample charts and reports section covers specific examples of how to take full advantage of the SQL Server Reporting Services built-in charting feature, and sometimes advanced examples. Some of these examples need to be carefully studied based on the step-by-step instructions provided. For convenience, it also includes a full-featured sample report. These sample reports are based on the SQL Server AdventureWorks sample database and the Northwind sample database.

Information about data preparation and chart labels can help you better understand these examples. When you look at these examples, you'll find it useful to skip back to the specific chart label topics that are described in the first section occasionally.

Data preparation

Charts provide a way to visualize data. Charts can deliver information more effectively than a lengthy list of data. Taking the time to prepare and understand data carefully before you create a chart will help you design your diagram quickly and efficiently. Reporting Services Chart data is divided into three areas: values, category groups, and series groups. For more information, see Using the chart data area in the SQL Server Reporting Services section of SQL Server Books Online.

Charts are very similar to matrices:

The category group of the chart corresponds to the column group of the Matrix.

The series group of the chart corresponds to the row group of the Matrix.

The value of the chart corresponds to the row group of the static matrix.

The data or data points of a chart correspond to the elements of a matrix.

Keep the following in mind when you prepare a DataSet query for a chart:

Chart values are displayed along the number Y axis. Make sure that the field you use as a value is a numeric data type (different from the string that contains the formatted number).

The value of the x-axis is determined according to the value of the chart category group or the group label (if the group label is explicitly defined). The x-axis supports two modes, which are discussed in detail in the X-axis category mode and scalar mode. If you want to use the X-axis scalar mode, make sure that the field and/or expression used for the category group expression evaluates to a numeric data type or DateTime object.

You can use any number of charts at your discretion. A chart is bound to a particular dataset, like a matrix or table, with other data regions. You can use connections and unions in a dataset query to contain all the required data in the dataset.

If the chart is placed in the end of a group header or group, or in a matrix cell, the data passed to the chart control is limited to a subset of the data that makes up the group. The chart cannot be placed in the detail row of the table because only one row of data is referenced.

If you have too much data in your chart (such as thousands of data points), it can be difficult to explain unless you use a scatter chart to display the values of the data points and the steps of the cluster. If the level of detail data granularity is not required or useful, you can consider aggregating data in a dataset query in advance.

Chart labels

This section contains the following chart label topics. When you look at the examples in the next section, you may find it useful to skip back to the topics described in this section occasionally.

X axis category mode and scalar mode

This section describes the significant differences between the two X-axis patterns. You can use the Categoryaxissettings sample report as the starting point for your experiment.

Axis label

The Axis tab section discusses in depth the application of label settings and how these settings affect the visual appearance of the chart at run time.

Data point labels and legend labels

This section describes how to improve the chart by adding data point labels and legend labels.

X axis category mode and scalar mode

There are two modes of X axis. You can use the value or timescale value option on the X Axis tab in the Chart Properties dialog box to set the mode.

Category mode

The value of the category group expression determines each category of the x-axis. The label is only displayed for the actual category in the data. In category mode, the sort order and explicit sort expressions within a group are important because the chart control will no longer reorder categories. The format code defined for the x-axis is applied only if the group expression or group label expression, if the definition is displayed, evaluates to a string-only object.

If there are multilevel category groupings, the grouping spans of categories are displayed.