Page 4 of 4
Next we specify how the cube is to be constructed and some minor formatting niceties:
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:
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:
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:
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:
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.