Pivot Grid — An Overview of Latest Enhancements

In this post, I’ll summarize Pivot Grid-related enhancements we’ve introduced over our last few release cycles (specifically, API changes designed to simplify product use for those targeting WinForms, WPF, and web platforms).

New Unified Binding API

High on our list of objectives was to deliver a single/unified binding mechanism and to simplify overall Pivot Grid setup.

Pivot Grid fields use our new unified binding mechanism in the following modes:

• OLAP
• Server
• In-Memory (with the Optimized data processing engine enabled)

Our updated API is built upon our fastest engine to date (we call it our Optimized Calculation Engine). As such, our unified API allow you to bind your Pivot Grid to Server Mode/OLAP data sources or to an in-memory source (our new in-memory engine is significantly faster than our older Legacy and LegacyOptimized engines).

If you’ve yet to explore capabilities of our new Pivot Grid data processing engine and want to compare performance to older processing modes, refer to the following help section for more information: Pivot Grid Data Processing Modes.

With that introduction, let’s now look at each available data binding type…

DataSourceColumnBinding

Use the DataSourceColumnBinding class to bind Pivot Grid fields to columns within a data source or to measures/dimensions in an OLAP cube. In previous versions, you had to specify the PivotGridFieldBase.FieldName property to bind Pivot Grid fields to columns within a data source.

PivotGridField statusField = new PivotGridField() {
FieldName = "[Measures].[Internet Revenue Status]",
Area = PivotArea.DataArea, AreaIndex = 2, Caption = "Status", Name = "fieldStatus" };

PivotGridField quarterField = new PivotGridField() {
FieldName = "[Date].[Fiscal].[Fiscal Quarter]",
Area = PivotArea.RowArea, AreaIndex = 2, Caption = "Fiscal Quarter",
Name = "pivotGridField" };

pivotGridControl.Fields.AddRange(new[] { quarterField, statusField });

You can now use the PivotGridField.DataBinding property that is able to receive an instance of this type, along with others I will describe later.

PivotGridField statusField = new PivotGridField() { DataBinding =
new DataSourceColumnBinding("[Measures].[Internet Revenue Status]"),
Area = PivotArea.DataArea, AreaIndex = 2, Caption = "Status", Name = "fieldStatus" };

PivotGridField quarterField = new PivotGridField() { DataBinding =
new DataSourceColumnBinding("[Date].[Fiscal].[Fiscal Quarter]"),
Area = PivotArea.RowArea, AreaIndex = 2, Caption = "Fiscal Quarter",
Name = "pivotGridField" };

pivotGridControl.Fields.AddRange(new[] { quarterField, statusField });

The code snippet above is from our “OLAP KPI” technical demo. To run the demo, you must have our WinForms distribution installed on your machine: OLAP KPI.

ExpressionDataBinding and OlapExpressionBinding

ExpressionDataBinding and OlapExpressionBinding allow you to bind Pivot Grid fields to calculated expressions or MDX expressions (OLAP) respectively. In addition, ExpressionDataBinding can be used to implement the following Pivot Grid features:

• Custom Grouping
• Custom Summary
• Custom Sorting

For instance, the following code snippet from the Alphabetical Grouping sample app groups ProductName field values into three ranges: A-E, F-S, and T-Z (based on the initial characters of product names).

// Create fieldProductName and bind it to the ProductName column in the data source.

PivotGridField fieldProductName = new PivotGridField();
fieldProductName.Area = PivotArea.RowArea;
fieldProductName.Caption = "Product";
fieldProductName.DataBinding = new DataSourceColumnBinding("ProductName");
fieldProductName.Name = "fieldProductName";
pivotGridControl.Fields.Add("ProductName");

// Create fieldProductGroup, specify the expression, and bind it to the created field.

PivotGridField fieldProductGroup = new PivotGridField();
fieldProductGroup.Area = PivotArea.RowArea;
fieldProductGroup.Caption = "Product Group";
fieldProductGroup.AreaIndex = 0;
fieldProductGroup.DataBinding = new ExpressionDataBinding(
	"iif(Substring([ProductName], 0, 1) < 'F', 'A-E',
		Substring([ProductName], 0, 1) < 'T', 'F-S', 'T-Z')");
pivotGridControl.Fields.Add(fieldProductGroup);

The benefit here is that you do not need to handle the CustomGroupInterval event as you did in previous versions:

pivotGridControl.CustomGroupInterval += (s, e) => {
	if(!object.Equals(e.Field, fieldProductGroup)) return;
	if(Convert.ToChar(e.Value.ToString()[0]) < 'F') {
		e.GroupValue = "A-E";
	return;
	}
	if(Convert.ToChar(e.Value.ToString()[0]) > 'E' &x;&x;
	Convert.ToChar(e.Value.ToString()[0]) < 'T') {
	e.GroupValue = "F-S";
	return;
	}
	if(Convert.ToChar(e.Value.ToString()[0]) > 'S')
	e.GroupValue = "T-Z";
};

pivotGridControl.RefreshData();

Note: If you use our updated Binding API, previous grouping\sorting\summary methods that involved the following events are no longer supported:

• CustomSummary
• CustomUnboundFieldData
• CustomGroupInterval
• CustomFieldSort

New CriteriaOperator Functions

Our Binding API uses expressions for custom grouping. You can also calculate custom summaries, or sort data.

We added a few more expression functions to expand the range of supported usage scenarios:

IsTotal(Dimension1, Dimension2, ...) — Determines whether a total summary value is being calculated. This function helps calculate different total, grand total or last level values in a different manner.

FirstValue(Value) — Returns the first value from rows used in an aggregate value calculation.

WindowFirstValue(SummaryExpression, StartOffset, EndOffset) — Returns the first value of the expression within the window.

Custom Aggregates

We also added the ability to create and register custom aggregate functions within the Expression Editor. Registered functions can be used alongside predefined Pivot Grid methods.

Example: Pivot Grid for WPF - How to Create a Custom Summary to Display the Distinct Value Count

OLAP ADOMD.NET NuGet Package Support

The Pivot Grid now supports the OLAP ADOMD.NET NuGet package for both .NET Framework and .NET. With ADOMD.NET you can read multidimensional schema (more than two axes), initiate queries on cubes, and retrieve results as needs dictate.

Asynchronous Mode

We enhanced the Pivot Grid’s asynchronous mode and made it both more usable and stable.

• Pivot Grid can now invoke multiple asynchronous operations simultaneously.
• Pivot Grid linked to Filtering UI Context now works in asynchronous mode.
• The asynchronous mode API methods now return the Task<> object. This simplifies usage syntax with the await keyword.

The following code snippet from the OLAP Drill Down technical demo creates a drill-down data source for the selected Pivot Grid cell and displays the resulting data source within the DrillDownForm dialog in asynchronous mode.

pivotGridControl.CellDoubleClick += async (s, e) => {
	try {
		pivotGridControl.LoadingPanelVisible = true;
		PivotDrillDownDataSource ds = await e.CreateDrillDownDataSourceAsync();
		pivotGridControl.LoadingPanelVisible = false;
		using(DrillDownForm form = new DrillDownForm(ds))
		form.ShowDialog();
	} catch(Exception ex) {
	pivotGridControl.LoadingPanelVisible = false;
	XtraMessageBox.Show(ex.Message);
	}
};

Documentation

Refer to the help topics below for more information about Pivot Grid enhancements and code examples. Some topics are platform-independent, while others are specific to the WinForms Pivot Grid (Note that a similar set of topics is available for all supported platforms). Of course, feel free to use the search bar in the documentation section of our website to review the API for your platform of choice.

• Data Binding API
• Pivot Grid Data Processing Modes
• Custom Aggregate Functions
• Custom Grouping
• Custom Summary
• Asynchronous Mode
• Custom Grouping

Your Feedback Matters

Free DevExpress Products - Get Your Copy Today