Export (0) Print
Expand All

Adding Data to a Chart Data Region

SQL Server 2008

After you add a Chart data region to the design surface, you can drag report dataset fields for numeric and non-numeric data to the drop zones of the chart. When you left-click the chart on the design surface, three drop zones—Series, Category and Data—will appear. When you have defined a dataset for your report, the resulting fields from your dataset appear in the Report Data pane. Drag your fields from the dataset into the appropriate drop zone. By default, when a field is added to one of the field drop zones of the chart, Reporting Services calculates an aggregate for the field. You can also use series grouping to dynamically generate series. The chart is also closely related to the matrix.

Note   When you add data at design time, the resulting chart looks different at design time than it looks when the report is processed. The data that is created at design time is not your real data. It is generated data that has been added so that you can design your chart with an idea of what the chart will look like.

By default, when a field is added to the data field drop zone of the chart, Reporting Services calculates an aggregate for the field. If you drag a field onto the chart without dropping the field into a specific drop zone, the chart will determine whether this field belongs on the category (x) axis or value (y) axis based on the data type of the field. Numeric fields that are dropped in the data field drop zone are aggregated using the SUM function. If the data type of your value field is String in the data field drop zone, the chart cannot display a numeric value, even if there are numbers in the fields, so the chart displays the COUNT function. To avoid this behavior, make sure that the fields that you use have numeric data types, instead of Strings that contain formatted numbers. You can use a Visual Basic expression to convert String values to a numeric data type using the CDbl or CInt constant. For example, the following complex expression converts a field that is named MyField that contains numeric values that are formatted as Strings.

=Sum(CDbl(Fields!MyField.Value))

For more information about aggregate expressions, see Using Built-in Report and Aggregate Functions in Expressions (Reporting Services).

Suppose you have a report that shows Sales by Name. You drop the Name field to the category fields drop zone and the Sales field to the data fields drop zone.

When you add the Sales field to the data fields drop zone, the text of the data field appears in the legend, and the data from this numeric field will be aggregated into one value. By default, the value is aggregated using the built-in function Sum. The chart drop zone will contain a simple expression for your field. In our example, [Sum(Sales)] will appear for the field expression =Sum(Fields!Sales.Value). If no groups are specified, the chart will only show one data point. In order to show multiple data points, you must group your data by adding a grouping field. When you add the Name field to the category fields drop zone, a grouping field of the same name as the name of the field is automatically added to the chart. When fields that define the values along the x and y axes are added, the chart has enough information to plot the data correctly.

When the series fields drop zone is left empty, the number of series is fixed at design time. In this example, Sales is the only series that appears on the chart.

In our earlier example, suppose you add a Year field to the series fields drop zone. The number of values in the Year field will determine how many series will appear on the chart. If you add a field to the series fields drop zone, the number of series depends on the data that is contained in that field. If the Year field contains the years 2004, 2005, and 2006, the chart will display three series for every field in the data drop zone.

The chart behaves identically to the matrix template of the Tablix control:

  • The Columns field drop zone on the matrix is identical to the category group drop zone on the chart.

  • The Rows field drop zone on the matrix is identical to the series group drop zone on the chart.

  • The Data field drop zone on the matrix is identical to the data field drop zone on the chart.

Community Additions

ADD
Show:
© 2014 Microsoft