Paul Kimmel's Blog

UPDATE: Please check out our recent post regarding enhancements to pivot grid and charts integration

The XtraPivotGrid is a great control for end-user composability. You can associate an XtraPivotGrid with a datasource and let the user figure out how the data should be organized. The XtraPivotGrid let’s you (the developer) and the user to pick which fields are row fields, which are columns, and which are displayed in the data area. You can create hierarchies of columns and rows, for example, if you have categories of products and products then you could place both of the categories and the products along the rows-side of the XtraPivotGrid and all of the products would be associated with it’s category.

The XtraPivotGrid works sort of like a spreadsheet. At the end of the day an XtraPivotGrid is going to display text and numeric data and is capable of display totals—a default option. And, text and numeric data is great but users often have to examine the numbers to derive meaning. If you combine a chart with the numbers—a pie or bar chart—then the chart ala a picture is a thousand words quickly conveys information, like biggest and smallest. For example, if you display product sales in the XtraPivotGrid then you could combine a chart with the grid to create a visualization of the data, and users can quickly figure out things like top sellers. The best part is that you can use an XtraPivotGrid as the data source for an XtraChart, then whatever the user does in the grid is automagically reflected in the chart—select specific rows, sort, filter, re-organize the data and the chart reflects the numeric and text data the user sees.

You can create a form with an XtraPivotGrid and XtraChart pairing without writing a stitch of code. The following steps will walk you through creating a WinForms application that uses the Northwind Product Sales view. The XtraPivotGrid gets its data from the view, and the XtraChart gets its data from the XtraPivotGrid. Move fields around in the XtraPivotGrid and the XtraChart is updated to reflect the data it “sees” in the XtraPivotGrid. There are oodles of settings for the XtraPivotGrid and the XtraChart, but we’ll take a fairly direct route to get the result shown in Figure 1.

 
Figure 1: An XtraPivotGrid and XtraChart; the XtraPivotGrid is the data source for the XtraChart.

1. Add an XtraForm to your project so you can use the LookAndFeel features of the DevExpress suite
2. Drop an XtraPivotGrid on the left side of the XtraForm
3. Set the PivotGridControl’s Anchor property to Top, Bottom, and Left
4. Drop an XtraChart control on the form; the ChartControl’s designer will be displayed; pick the bar chart type—see Figure 2—and click Finish
5. Position the ChartControl to be to the right of the PivotGridControl and set the ChartControl’s Anchor property to Top, Bottom, Left, Right
6. Click on the PivotGridControl’s smart tag and click Choose Data Source|Add Project Data Source
7. Use the Data Source Configuration Wizard and select the Northwind database and the Product Sales for 1997 view
8. This will set the PivotGridControl’s DataSource to the productSalesFor1997BindingSource
9. Run the PivotGridControl’s designer from the grid’s context menu—accessed by right-clicking over the grid
10. In the PivotGridControl’s designer (Property Editor) click the Show Field List button >>
11. Drag and drop the ProductName and ProductSales fields from the Field List to the PivotGrid Fields list-see Figure 3
12. Close the Property Editor
13. In the PivotGridControl drag and drop the ProductName field (represented by a button-looking control) to the location that reads Drop Row Fields Here
14. Drag the ProductSales field to the location on the PivotGridControl that reads Drop Data Items—see Figure 4
15. Switch to the Properties window for the PivotGridControl, expand OptionsChartDataSource and set ShowRowGrandTotals to False
16. Click on the ChartControl; change the ChartControl’s DataSource property to pivotGridControl1 by expanding the dropdown, clicking Other Data Sources, Form1 List Instances, pivotGridControl1—see Figure 5
17. Set the ChartControl’s DataAdapter property to (none)
18. Scroll down to the ChartControl’s SeriesDataMember and from the drop down list select Series
19. Scroll down to the ChartControl’s SeriesTemplate property and set the sub-property ArgumentDataMember to Arguments and ValueDataMembers Value sub-property to Values
20. Expand the SeriesTemplate PointOptions sub-property; Expand the nested ValueNumericOptions and change the Format property to Currency
21. Scroll up to the ChartControl’s Legend property and set the Visible sub-property to False
22. Click on the bar chart part of the ChartControl to select the DevExpressXtraCharts.XYDiagram (part) of the ChartControl; change the Rotated property to True
23. Click on the X-Axis which is now along the vertical edge of the chart to select the DevExpress.XtraCharts.AxisX part of the control; set the Reverse property to True in the Properties window
24. Click on the Y-Axis of the ChartControl which is now along the horizontal axis to focus the DevExpress.XtraCharts.AxisY part of the control; expand the Label property and change the Angle sub-property to –45 to make the Y-Axis text diagonal
25. Change the Label’s Antialiasing sub-property to True to smooth out the appearance of the axis text
26. Expand the NumericOptions property for the AxisY and change the Format sub-property to Currency and Precision sub-property to 0
27. In the Properties window select the XtraForm and change the LookAndFeel UseDefaultLookAndFeel to False and SkinName to Blue
28. Run the demo

When the demo is running drag the Product Name field to the Rows area and the Product Sales field to the data area. Experiment with the context menus for the fields and sort or filter the data, change the number of selected rows, and consider adding the Category Name to the Rows area to see how the chart is effected.

 
Figure 2: Choose the Bar chart type in the Chart Wizard.

  
Figure 3: Move the fields you want from the Field List to the PivotGrid Fields list.

 
Figure 4: Drop the fields in the row, columns or filter area to create the XtraPivotGrid view.

 
Figure 5: Select the XtraPivotGrid control for the XtraChart’s DataSource.

If you add the CategoryName field to the Rows area then a hierarchy of products by category is created. To select all of the products for a given category just click on the category. See Figure 6 for an updated view of the form with the category added to the Rows area and the Beverages category selected.

Figure 6: Add the Category Name to the Row area and select the Beverage category to update the chart.