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


Gmail, Spreadsheets and Google App Script

If you have to administer an email list, creating a Google App Script to process email bounces and send the relevant data to a spreadsheet is not only useful but also a good example of using scripts.

 [ ... ]



Dealing With Forum & Mailing List Spam

Forum spam is a big problem and very difficult to combat. There are some solutions, none perfect, but this one might help reduce the burden on spam in your forum or mailing list.


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.