Demystifying Pivot Tables
Demystifying Pivot Tables
Written by Janet Swift   
Friday, 20 August 2010
Article Index
Demystifying Pivot Tables
Pivot tables
Using the PivotGrid control
Running the program



Next we specify how the cube is to be constructed and some minor formatting niceties:

flatDataSource.Cube = 
new CubeMetadata
     DataTypeFullName = "ExcelData",
     DisplayName = "My Store Data"

Everything about the data is now set up and a cube can be generated and used we simply need to remember to close the stream:


If you now run the program you will discover it doesn't work because we need a set of using statements:

using System.IO;
using Infragistics.Controls.Grids;
using Infragistics.Olap.FlatData;
using Infragistics.Olap.Excel;
using Infragistics.Olap;

and you need to make sure to add references to:


Some of these assemblies are added automatically when you drop the control on the form but the ones relating to Excel and the FlatData object aren't.

If you now run the program you will see a very simple 2D crosstab:


You should be able to follow how the data maps from the spreadsheet to the crosstab. There are many more properties that you can tweet to specify the mapping more accurately - formatting for instance - but this is the simplest relationship you can work with. You can also see that the buttons in the top left-hand corner invite user interaction. If you try them you will discover that the program crashes - you haven't set everything up for this just yet.

Before moving on to consider interaction lets make the grid more than two-dimensional by adding the Region category. If you change the Columns specification to:

flatDataSource.Columns = 

this says that the columns are classified on Type and and sub-category Region. The result is a tree style pivot table:


If you change the Rows property instead to read:

flatDataSource.Rows = 

the result is the same but now the sub-category is listed as part of the row structure:


The idea is that you can specify a list of categories to be used to create either the rows or the columns using an obvious notation:

[cat1],[cat2],[cat3] and so on...

If you specify more than one set of categories between square brackets  e.g. [cat1,cat2] then the data is aggregated on those categories. For example:

flatDataSource.Columns = 

leaves only the Store category to cross tabulate:


This is a good start but there is so much more that we can do. In particular we can let the user select the categories and how they are displayed, we can add aggregating functions and more.

If you would like to be informed about new articles on I Programmer you can either follow us on Twitter, on Facebook or you can subscribe to our weekly newsletter.


A C# Oscilloscope Display In Windows Forms

If you need a real time stripchart or oscilloscope style display for a Windows forms project then the good news is that it can be done without having to move outside of C#.

The Minimum Spanning Tree - Prim's Algorithm

Finding the minimum spanning tree is one of the fundamental algorithms and it is important in computer science and practical programming. We take a look at the theory and the practice. 

Other Projects






Last Updated ( Friday, 20 August 2010 )

RSS feed of all content
I Programmer - full contents
Copyright © 2018 All Rights Reserved.
Joomla! is Free Software released under the GNU/GPL License.