Export (0) Print
Expand All

Adding Data to a Gauge Data Region (Report Builder 2.0)

SQL Server 2008

After you add a gauge to the design surface, simply drag a dataset field to the gauge and the field will appear in the data drop-zone. By default, when a field is added to the gauge, the field is aggregated, and the resulting value is attached to the pointer via the Value property. By default, the gauge aggregates all of the values in your field into one value that is displayed on the gauge. You can add grouping to the gauge in order to view individual groups or individual rows on the gauge. When grouping and filtering is applied, the gauge uses the pointer value to display the last group or row in the returned dataset.

You can add multiple values to an individual gauge by adding another pointer. This pointer can belong to the same scale, or you can add another scale and then associate the pointer with that scale.

Like all other data regions, the Gauge data region can be bound to only one dataset. If you have multiple datasets, consider using a JOIN or UNION to create one dataset, or use separate gauges for each dataset.

Numeric data types are aggregated with the SUM function. Non-numeric data types are aggregated with the COUNT function, which counts the number of instances for a particular value or field within the dataset or group.

After data is added, when you right-click on the pointer, you will get Clear Pointer Value and Delete Pointer options. The Clear Pointer Value option will remove the field attached to the gauge, but the pointer will still appear on the gauge. The Delete Pointer option will remove the field from the gauge and delete the pointer from view. If you re-add a field to the gauge, the default pointer will reappear. After you have added the field to the gauge, you must set the maximum and minimum values on the corresponding scale in order to give context to the value on the gauge. You also have the option of setting the minimum and maximum values on a range, which shows a critical area on the scale. The gauge will not automatically set the minimum or maximum values on the scale or the range because it cannot determine how the value should be perceived.

After you have defined a dataset for your report, you can add a data field to the gauge by using one of the following approaches:

  • Drag a field from your dataset into the drop-zone. Open the data drop-zone and drag a field to it. You can open the drop-zone by clicking on the gauge or placing a field on the gauge. If a pointer is not already on the gauge, a pointer is added to the gauge and bound to the field you have added.

  • Display the data drop-zone and point to the field placeholder. A field selector button appears inside the placeholder that you can click to select any field in the dataset. If there is a field already selected, click the field to display the button, and then select a different field.

    NoteNote

    This approach is not applicable when there is no pointer on the gauge or the report contains more than one dataset and the gauge panel is not associated with a dataset. For more information, see How to: Associate a Data Region with a Dataset (Report Builder 2.0).

  • Right-click on the gauge pointer and select Pointer Properties. For Value, select a field from the drop-down list or define a field expression by clicking the Expression (fx) button.

When a field is added to a gauge, Reporting Services calculates an aggregate for the field by default. Numeric data types are aggregated with the SUM function. Non-numeric data types are aggregated with the COUNT function, which counts the number of instances for a particular value or field within the dataset or group. If the data type of your value field is String, the gauge cannot display a numeric value, even if there are numerals in the fields. Instead, the gauge aggregates string fields using the COUNT function. To avoid this behavior, make sure that the fields you use have numeric data types, as opposed to 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 expression converts a string field called MyField to numeric values.

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

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

After you have added a field to the gauge, you can add one data group. The Gauge differs from all other data regions in Reporting Services, which can display multiple groups in one data region. When you add a group by defining a group expression on the gauge, it is the same as when you add a row group on the Tablix data region. However, when the group is added, only the value of the last group is displayed as the pointer value on the gauge. For example, if you add a grouping expression on Year, the pointer will point to the value that represents the aggregate sales value for the last year in the dataset. For more information about groups, see Understanding Groups (Report Builder 2.0).

You might want to add a group on the gauge if, for example, you are displaying multiple gauges in a table or list and you want to display data aggregated by group. For more information, see How to: Add or Delete a Group in a Data Region (Report Builder 2.0).

Community Additions

ADD
Show:
© 2014 Microsoft