Hosting a WPF PivotGrid delivering advanced data analysis in your Windows Forms application

Kiril Matev / Friday, June 11, 2010

When it comes to data analysis, users have for too long relied heavily on spreadsheet software. We’ve all seen applications where users export the data shown in a grid control, and analyze it using spreadsheet software. Apart from slowing end-users down, this practice has in some places led to embarrassing information leakages and incompatible ways of computing of the same measure by different users, resulting in misleading data fed to decision-makers.

It’s time to give your users the opportunity to browse and analyze their data right within your application, rather than forcing them to export data and use Excel to analyze it. This would increase their productivity, as they wouldn’t have to switch applications, export and import data. Providing data analysis capabilities within the application would also ensure all users use the same way of computing the same measure, and allow the implementation of comprehensive information security policies.

You can meet your users’ data analysis needs using the XamPivotGrid, which is available as part of the NetAdvantage for Data Visualization products for Silverlight and WPF. It provides powerful interactive analysis capabilities - sorting, filtering, visual measure and dimension selector, conditional formatting, binding to OLAP, data sources implementing the IEnumerable interface, and Microsoft Excel workbook files.

If you’re working on a WPF or Silverlight application, you can directly benefit by using the XamPivotGrid. However, as many applications are based on Windows Forms, we will provide guidance on how to use the XamPivotGrid and other powerful data visualization components within your Windows Forms applications. We have already provided guidance on how to host the XamDataChart (our fast chart offering zooming and panning) into a Windows Forms application. This blogpost is a continuation on this theme - it demonstrates how to host the XamPivotGrid in a Windows Forms application using a sample project.

The sample project has been built using Visual Studio 2010, targeting .NET Framework 4. The sample project contains everything you need to run the sample – a pre-release version of the XamPivotGrid, a Microsoft Excel spreadsheet file, and a Northwind database, which is used to load Orders data into a DataSet. Please download the sample project, open it and refer to the source code as we go along.

Hosting a WPF Control in a Windows Forms applications

There has been extensive guidance on the topic of hosting WPF controls within Windows Forms applications, such as this walkthrough. Basically, the WPF control is built within a WPF UserControl project, which, once instantiated and  initialized, is set as a child to an ElementHost instance in a Windows Forms form instance.

How do I interact with the sample

The sample demonstrates how to load data from a Microsoft Excel spreadsheet file, a Windows Forms dataset, and an OLAP cube by pressing the buttons on top. Click on each of the buttons above to bind to the corresponding source of data. Please browse the project code to see how to bind to each of these. 

Once you’ve loaded the data, you can use the data selector which appears next to the pivot grid to select the measures and dimensions to be used using drag and drop. If you would like to present your users with a pivot grid whose dimensions and measures they cannot change, do not use the data selector together with the pivot grid in the WPF UserControl project. In this application, you can hide it at runtime using the checkbox in the upper-right part of the application window.

Excel Sheet Selector

The 10.2 version of the XamPivotGrid, does not include a form allowing your users to choose which the worksheet from a workbook to load data from into the pivot grid. We recognize the importance of such a feature, and have planned it for future releases. However, for the time being there’s a substitute you can use – there’s a simple Excel Sheet Selector form included in the sample project. You can reuse it in your own projects and extend it to fit your needs. It will allow your users to easily select the sheet to use, without you having to write much additional code.


We looked at hosting the XamPivotGrid (available as part of our Silverlight and WPF Data Visualization products) within a Windows Forms application. The sample application demonstrates how to bind different types of datasets to the XamPivotGrid from within your Windows Forms applications. Further guidance on how to use the XamPivotGrid excel export capabilities, conditional formatting, among others will also be available as samples.

Now that you have the tools to provide interactive data analysis capabilities to your users within your existing Windows Forms applications, try and find out what are their data analysis needs, what data sources they use and what functionality they expect you to provide.

By all means, let us know how your projects are coming along using the XamPivotGrid. What features do you and your users find most valuable, what would you like to see in future releases?

If you have any questions, do not hesitate to email me at