A New Twist to Pivoting Data - The XamPivotGridGrouping Control

Pivoting can be a very useful mechanism for summarizing data.  Among other functions, pivoting data can allow you to sort, count, total or give the average of various data points.   Pivoting data allows users to recognize patterns in that data, and can provide a way to visualize complex modeling schemas.

Traditionally, pivoting data has meant using a spreadsheet program such as Excel, and learning how to create a Pivot Table.  Infragistics has a control, the XamPivotGrid, which is an awesome too for data summarization and information visualization.  It allows you to build complex business analysis applications using the OLAP (Online Analytical Processing) approach to present the results of multi-dimensional queries in an easy to comprehend manner.

The XamPivotGrid control can take a number of data sources and build extensive reports based on the input data.  Users can navigate swiftly through the results, and change the perspective of the data on the fly to get a different view.  This ultimately results in a more effective use of the data.

The XamPivotGrid control can slice, dice, drill down or up, roll-up and pivot data taken from an OLAP cube. It can handle Flat Data too.

Here is a quick look at the XamPivotGrid:

Now, as great a control as this, I’ll be the first to admit that sometimes pivoted data can be a bit confusing.  For example, take a look at the Columns Area:

Reading this can be a bit difficult at times, and depending on the depth of each field, it can become rather confusing to look at.  Basically we have a hierarchical column relationship where we have data which is categorized by first “Season”, then “Team” and lastly “Game”.    Additionally, in this visual, we don’t see a “Total” anywhere.  For example, what if we wanted to know The Total number of completions for the season?  Well, as it stands, we would need to pull out the Game and Team Fields from the data selector to get the right display, as shown here:

Another limitation of the base pivot grid has to do with groups.  What if we wanted all Quarterbacks from all teams grouped together?  Right now the only way we can do that is by filtering out all the other positions like this:

This allows us to show just the QB data:

Now, wouldn’t it be kind of cool if we could display our columns in a horizontal hierarchy instead of a vertical one?  Wouldn’t it be a handy to be able to just add Groups rather than having to filter out what we don’t want to see?

Meet the XamPivotGridGrouping Control. 

 

The XamPivotGridGrouping control allows us to do even more to help visualize this summarized data. 

Expanding Horizontal Column Hierarchy

Let’s start by looking at the columns area.  Notice that we have 3 fields in the columns area (Season, Team and Game)

But we are only seeing a single column, which equates to Season.  Look closely at the column header:

There is an expansion indicator at the top of the Column.  When we expand the columns, look what happens:

WOW!  We now see very clearly all the associated “children” of the parent.  Additionally, notice how our data has re-visualized itself:

The “Season” parent now has an empty set of cells, where-as our first child has the current values listed.  If we expand out to the next level –

Look at how our data has become granulized to the specific children.  It’s very clear what numbers are associated with what child.

The columns in the XamPivotGridGrouping have a number of settings that allow you to determine if you want the parent columns in front of or behind the children, to determine if zeros will display in the “empty” cells, and more.  The effect here is that as you collapse each column “level” you collapse the data, and the grid handles all of the calculations internally, giving you a total value based on the current depth of expansion.

Rows Groups Grouping

What about grouping you ask?   The XamPivotGridGrouping adds an additional collection area in the Data Selector called Rows Groups.  Using the Rows Groups area we can now, to go back to my earlier suggestion, group our players by position to see all QB’s together, etc., as shown here:

The XamPivotGridGrouping control expands on the Infragistics XamPivotGrid control and gives it even more power and flexibility than ever before.  It allows for extremely unique ways to group and visualize your pivoted data in ways that frankly nothing else can do.  Try pivoting things like this in Excel! 

The XamPivotGridGrouping control is, as you might imagine, complex, and contains an incredible number of different moving parts.  Over the next few weeks, I will be expanding on this introduction by examining how we can implement the XamPivotGridGrouping in a project, how we can manipulate it to do even more, and how we can customize it so we can end up with something that might look a little bit like this:

As you can very clearly see, the XamPivotGridGrouping control is a pivot grid like no other, and most definitely puts a new twist on pivoting data!

Over the course of the next several blog posts, we'll build out a Football Statistics Analysis app (very basic) that incorporates the XamPivotGridGrouping as it's main view of the data.   We'll try and manage the following requirements:

The Data will track player stats broken down into 3 main Categories (PASS, RUSH, SPEC TEAMS)
Each category will have potential sub categories (YARDS, SCORE, TURNOVER, PUNT, KICK, RETURN).  The data will be granularized to quarter based stats and will allow us to pivot the data totals in a variety of ways.

We will additionally need to manage a few special case statistics (Passer Rating, Avg Yard Per Carry, Avg Pass Per Attempt, Avg Pass Per Completion, FG Percent, Completion Percent, etc).  These fields will behave a little differently than do the other pivot fields, because the will need to be calculated based on the visible statistics which yield their results.  (I.E., Comp % = Completions/Attempts).  Additionally, we'll highlight these fields with some explicit styling to place emphasis on them.

We'll start the process in the next post by buildilng out our DataSource.