By default the XtraPivotGrid (PivotGridControl) sorts field values (row and column headers) in ascending or descending order. OLAP cubes can define their own ordering. If you want to maintain the cube’s ordering then you can disable field ordering. When working with an OLAP cube if you set a pivot grid field’s SortMode to None then default ordering is turned off. New in v2009 volume 3 is the Clear Sorting feature. Clear Sorting, as well as the A-Z and Z-A, ordering let you re-order the data or clear ordering altogether.
When you turn automatic sorting off by setting a field’s SortMode to None the PivotGridControl’s header context menus will show these additional header context menu items (see Figure 1). The following steps walk you through constructing the sample solution.
Figure 1: The new Clear Sorting context menu item shows up when automatic filtering is turned off for a field by setting SortMode to None.
Constructing the XtraPivotGrid (PivotGridControl) Demo
I am using the XtraPivotGrid version 2009 volume 3 for the demo. The data comes from the Northwind database and a .cub file was created using Microsoft Excel’s export cube feature. (DevExpress’ book Professional DevExpress ASP.NET Controls chapter 8 talks about how to export a cube file.) To create the demo follow these steps:
1. Create WinForms project
2. Add a PivotGridControl to the form and dock it
3. Place the cube file in your project, somewhere that it is accessible
4. Define a connection string using the Smart tags’ Choose OLAP Data Source wizard, or simply define the connection string in the PivotGridControl’s OLAPConnectionString property, similar to the following:
Provider=msolap;Initial Catalog=Northwind;Cube Name=Northwind;Data Source=c:\devexpress\winforms\olapdemo\OlapDemoWinForms\Northwind.cub
(If you define an OLAPConnectionString then the DataSource property is cleared and vice versa.)
5. Right click on the PivotGridControl (or the Smart tags menu) and click on Run Designer to run the field’s designer (see Figure 2)
6. Add six fields in the designer using the Add button. (The fields for the demo are City, Category name, Products, Country, Quantity, and Discount)
7. You will need to set the field properties based on where you want the data to appear in the pivot grid. To create the result shown in Figure 3 use these settings:
City – Caption is City, Area is FilterArea, FieldName is [Customers].[City].[City], and SortMode is None
Category Name – Caption is Category Name, Area is RowArea, FieldName is [Categories].[Category Name].[Category Name], and SortMode is None
Products – Caption is Products, Area is RowArea, FieldName is [Products].[Products].[Products], and SortMode is None
Country – Caption is Country, Area is ColumnArea, FieldName is [Customers].[Country].[Country], and SortMode is None
Quantity – Caption is Quantity, Area is DataArea, FieldName is [Measures].[Quantity], and SortMode is None
Discount – Caption is Discount, Area is DataArea, FieldName is [Measures].[Quantity], and SortMode is None
8. Close the designer. (The design-time view is shown in Figure 3.)
9. Run the demo. The results at runtime (Figure 4) with the Clear Sorting context menu item is shown in Figure 1.
The XtraPivotGrid is a great tool for exploring and arranging your data to figure out what it all means, collectively referred to as data mining.
The Clear Sorting feature is also supported for the ASP.NET ASPxPivotGrid (and you can use the same .cub file, if you don’t have Microsoft Analysis Services handy.) If you are having trouble getting your hands on a cube file or following Excel’s instructions for creating one drop me a line and I will email you the one for the demo.
Figure 2: Add the fields using the designer (shown above).
Figure 3: The design-time results based on the designer settings.
Figure 4: The PivotGridControl at runtime with the configured data.