Integrate the Pivot Grid with the Chart Control
- 7 minutes to read
Tip
You can use DevExpress BI Dashboard to build data visualization and analysis UIs that include Charts, Grids, Maps, Pivot Grids, Cards, Range Selectors, and other elements. This cross-platform product is available as a part of the DevExpress Universal Subscription.
Review the following help topic to learn the basics: Get Started with the DevExpress Dashboard.
The Pivot Grid displays multidimensional data in a cross-tabular form, allowing users to perform various data shaping operations. They can customize the report to reshape data and get a layout that fits their specific data analysis needs.
Pivot Grid control allows end-users to go further and visualize data displayed in the pivot grid using the Chart Control.
This topic contains the following sections.
Binding Overview
The ChartControl retrieves data from the Pivot Grid control in the following manner.
- The series data member values are generated based on Pivot Grid control columns.
- The argument data member values are generated based on Pivot Grid control rows.
- The Value data member contains summary values.
Series and argument data members are created based on column/row headers (e.g., ‘1994’, ‘Produce Total’, ‘Grand Total’). If a column/row is identified by multiple fields, the corresponding data member value is constructed from the respective field values connected by the ‘|’ sign (e.g., ‘1995 | January’, ‘Beverages | Chai’).
Implementation
To set a Pivot Grid control as the Chart control data source, do one of the following:
- At design-time
- Click the Chart control smart tag and select the PivotGridControl instance from the Choose Data Source drop-down list:
- In code
- Assign the PivotGridControl instance to the ChartControl.DataSource property.
The ChartControl visualizes data that is selected in the Pivot Grid control. To display all data in the Pivot Grid control, set the PivotGridOptionsChartDataSource.SelectionOnly property to false.
To change default behavior in order to create a series data member based on rows and an argument data member based on columns, set the PivotGridOptionsChartDataSourceBase.ProvideDataByColumns property to false.
Note
Use the PivotGridOptionsChartDataSourceBase.DataProvideMode property to control whether or not Pivot Grid control should pass to Chart control only values from the lowest detail level.
Binding and Layout Options
When the PivotGridControl instance is assigned to the ChartControl.DataSource property, chart bindings and layout are auto-adjusted. If necessary, you can manually specify ChartControl binding settings that define how to create a diagram from supplied data.
There are two modes of providing binding settings to ChartControl: automatic and manual. Use the ChartControl.AutoBindingSettingsEnabled property to switch between these modes.
- Automatic mode
- In this mode (the ChartControl.AutoBindingSettingsEnabled property is set to true), binding settings are automatically adjusted. In this instance, the Chart control uses data source series, argument and value data members to create series, arguments and values, respectively. You can handle the ChartControl.CustomizeAutoBindingSettings event to manually adjust automatically applied binding settings. This event occurs after automatic settings have been applied to a chart data source.
- Manual mode
- In this mode (when the ChartControl.AutoBindingSettingsEnabled property is set to false), you need to use the ChartControl.SeriesDataMember property to specify which data member should be used to create series. Then, use the SeriesBase.ArgumentDataMember and SeriesBase.ValueDataMembers properties of the ChartControl.SeriesTemplate object to specify which data members should be used to create arguments and values, respectively. Use the ‘Series’, ‘Arguments’, and ‘Values’ strings to refer to corresponding data members stored in the data source.
When binding settings are applied, you can manually specify layout settings that define how to display a diagram. As with binding settings, you can let ChartControl adjust these settings automatically (in this instance, you can manually customize them by handling various events) or specify them manually. Use the ChartControl.AutoLayoutSettingsEnabled property to specify whether or not layout settings are automatically adjusted.
For a list of binding and layout settings, see the Pivot Charting topic in the XtraCharts Suite documentation.
Data Types Specifics
If a Pivot Grid control contains multiple data fields with different data types (PivotGridField.DataType), cell values are converted to Decimal (if possible). Use the PivotGridOptionsChartDataSourceBase.ProvideCellValuesAsType property to override this behavior by explicitly specifying a data type to which cell values should be converted.
Chart control supports only Numeric and DateTime series point values (see Series Scale Types to learn more). To convert cell values into an appropriate type in a custom manner, handle the PivotGridControl.CustomChartDataSourceData event.
When choosing a Chart type, note that only Series with a single value per Series point are supported.
Export
Refer to the following example for information on how to print a document that contains a Pivot Grid control with an integrated Chart: How to: Print a Document that Contains Pivot and Chart Controls
API
The Pivot Grid control has multiple options you can use to specify how the ChartControl interprets data. You can use the PivotGridControl.OptionsChartDataSource property to access these settings stored in a PivotGridOptionsChartDataSource object.
- PivotGridOptionsChartDataSourceBase.ProvideDataByColumns
- Gets or sets whether series in a chart control are created for PivotGrid columns or rows.
- PivotGridOptionsChartDataSource.SelectionOnly
- Gets or sets whether a chart control must display selected cells or all the data of the PivotGrid control.
- PivotGridOptionsChartDataSourceBase.ProvideEmptyCells
- Gets or sets whether empty cells should be processed by the Chart control.
- PivotGridOptionsChartDataSourceBase.DataProvideMode
- Gets or sets what data the pivot grid should pass to a bound chart control.
- PivotGridOptionsChartDataSourceBase.ProvideColumnCustomTotals
- Gets or sets whether column custom totals should be passed to a chart control.
- PivotGridOptionsChartDataSourceBase.ProvideColumnGrandTotals
- Gets or sets whether column grand totals are passed to a chart control.
- PivotGridOptionsChartDataSourceBase.ProvideRowCustomTotals
- Gets or sets whether row custom totals should be displayed in a chart control.
- PivotGridOptionsChartDataSourceBase.ProvideRowGrandTotals
- Gets or sets whether row grand totals are passed to a chart control.
- PivotGridOptionsChartDataSourceBase.ProvideRowTotals
- Gets or sets whether row totals are passed to a chart control.
- PivotGridOptionsChartDataSourceBase.FieldValuesProvideMode
- Gets or sets how field values are passed to the chart control.
- PivotGridOptionsChartDataSourceBase.ProvideCellValuesAsType
- Gets or sets the type to which cell values are converted, before they are exported to the Chart Control.
- PivotGridOptionsChartDataSourceBase.ProvideColumnFieldValuesAsType
- Gets or sets the type to which the column field values are converted before they are exported to the chart control.
- PivotGridOptionsChartDataSourceBase.ProvideRowFieldValuesAsType
- Gets or sets the type to which the row field values are converted, before they are exported to the chart control.
- PivotGridOptionsChartDataSourceBase.MaxAllowedPointCountInSeries
- Gets or sets the maximum number of points allowed in a series.
- PivotGridOptionsChartDataSourceBase.MaxAllowedSeriesCount
- Gets or sets the maximum number of series allowed in the chart data source.
- PivotGridOptionsChartDataSource.UpdateDelay
- Gets or sets the delay between changing the cell focus or selection, and updating the bound Chart control.
Note
Note that PivotGridOptionsChartDataSourceBase.Provide…Totals properties are in effect when the PivotGridOptionsChartDataSourceBase.DataProvideMode is set to the PivotChartDataProvideMode.UseCustomSettings.
Example
The following example shows how to bind a ChartControl to a PivotGridControl to visualize data.
In this example, after a Pivot Grid Control and ChartControl are created, the Pivot Grid Control instance is assigned to the ChartControl.DataSource property. By default, the ChartControl.AutoBindingSettingsEnabled property is set to true, so that binding settings are automatically adjusted, and no further customization is needed. Once the ChartControl is bound to the PivotGridControl, Pivot Grid Control data is immediately displayed in the chart.
The main form of this sample application contains the ‘Transpose Data Source’ check box that defines the pivot grid’s PivotGridOptionsChartDataSourceBase.ProvideDataByColumns property value. If the check box is checked, the property is set to false, and series are created based on pivot grid rows (instead of columns, which is the default behavior).
using System;
using System.Windows.Forms;
namespace XtraPivotGrid_ChartsIntegration {
public partial class Form1 : Form {
public Form1() {
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e) {
// Populates the pivot grid's data source with data.
this.salesPersonTableAdapter.Fill(this.nwindDataSet.SalesPerson);
// Sets the PivotGridControl as a data source for the ChartControl.
chartControl1.DataSource = pivotGridControl1;
}
}
}