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

Banner

 

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

flatDataSource.Cube = 
DataSourceBase.GenerateInitialCube(
"ExcelData");
flatDataSource.CubesSettings.Add(
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:

xamPivotGrid1.DataSource=flatDataSource;
stream.Close();

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:

InfragisticsWPF4.Controls.Grids.
XamPivotGrid.v10.2
InfragisticsWPF4.Controls.Menus.
XamTree.v10.2
InfragisticsWPF4.DragDrop.v10.2
InfragisticsWPF4.Olap.Excel.v10.2
InfragisticsWPF4.Olap.FlatData.v10.2
InfragisticsWPF4.Olap.v10.2
InfragisticsWPF4.v10.2

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:

grid1

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 = 
DataSourceBase.GenerateInitialItems(
"[Type],[Region]");

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

grid2

If you change the Rows property instead to read:

flatDataSource.Rows = 
DataSourceBase.GenerateInitialItems(
"[Store],[Region]");

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

grid3

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 = 
DataSourceBase.GenerateInitialItems(
"[Type,Region]");

leaves only the Store category to cross tabulate:

grid4

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.

Banner


Getting Started with Google Earth

Google Earth is more capable than Google Maps, but to use it you have to download a plug-in and learn a new API. This article explains that it's not so difficult and there are some easy to understand  [ ... ]



Getting started with Windows 7 Gadgets

Given the very real possibility that Windows 8 will have an HTML5/JavaScript framework and an app store, now is a very good time to look at the existing support for these technologies in the form of S [ ... ]


Other Projects

<ASIN:1590594320>

<ASIN:0764516612>

<ASIN:0470104872>

<ASIN:1590599209>

<ASIN:0789736012>



Last Updated ( Friday, 20 August 2010 )
 
 

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