Log in to like this post! Elements of a Pivot Grid - XamPivotGridGrouping 3 Brian E. Roach / Thursday, April 25, 2013 XamPivotGridGrouping 3 – Elements of a Pivot Grid In my last blog, we built out a GroupingFlatDataSource which we can use to supply the data to our XamPivotGridGrouping control. Before we start to look at exactly how to go about that, let’s review the requirements of our project, and then take a look at the control itself, and the accompanying XamDataSelectorGrouping control, by examining some of the key elements in the control. PROJECT REQUIREMENTS: For our project, we are building a “Statistics” review tool to allow us to examinble NFL player stats in a variety of different ways. Our focus, of course, is on the XamPivotGridGrouping. The basics of what we are being asked to do with the control are as follows: The Player Label must show both the Player name and an image of the player. Player name will be bold and font-size 16pts. The Team Label must show the team logo. The Position Label must show an image (silhouette) and abbreviated text regarding player position and will be font-size 24pts. Type (Pass, Rush, Def), Category (Yards, Score, TO) and Statistic columns must be static in their position and cannot be moved, or removed from the row listing. As a result, they should not be visible in the Rows section of the DataSelector nor should they be listed in the Field Selection area. Name, Team, Position must all be sortable (A-Z, Z-A) across the entire data set. The statistic results for Passer Rating, Avg Yards Passing, Avg Yards Rushing, Comp %, FG %, and XP% must be calculated based on the visible values relating to their formulas. These same fields will be highlighted with unique color schemes for the Row Headers and the Data Rows. All other statistical results will be white/white. The Data Selector Field Selection area will not list hierarchal data fields, but rather the specific field (dimension/measure) that is available, and will display a custom icon. The “Measures” area will be hidden, because for the purposes of this application, the only measure will be Statistic Total, and it should not be removable. The Control will be styled based on a specific scheme. Before we start to dig into these requirements, let’s take a closer look at the control and its elements, so we start to get an understanding of what we will need to do to fulfill the stated requirements. CONTROL ELEMENTS There are two specific controls we need to examine to fulfill our required specs, the XamPivotGridGrouping control and the XamDataSelectorGrouping control. We’ve looked at what the XamPivotGridGrouping does in general, so let’s briefly overview the DataSelector as well. The XamDataSelectorGrouping can greatly aid in the manipulation of your data at runtime. Its purpose is to allow users to select data. Given a DataSource (such as the one we built in the last blog), the XamDataSelectorGrouping can give you an interactive UI component to select a database to connect to (if you are using database data), a choice of which cube to extract data from (if you have multiple cubes), and a set of measures. When you have selected those a tree will be loaded with all the available dimensions with their respective hierarchies along with a list with all the available measures. Figure 1 - XamDataSelectorGrouping The XamDataSelectorGrouping along with the XamPivotGridGrouping controls are both required for us to effectively satisfy the project’s requirements. Let’s take a more detailed look at the elements of each one, starting with the XamPivotGridGrouping XAMPIVOTGRIDGROUPING CONTROL The image below divides the XamPivotGridGrouping into 4 sectors: Measures & Filters Row Headers Column Headers Data Cells Figure 2 - XamPivotGridGrouping Sectors Let’s examine these areas, and their function, briefly. Measures & Filters In this area you would normally see the measures (Total in this sample is the Measure) and any hierarchies which had been placed in the filter area, but which are not in any of the other sectors. Any hierarchy can only be placed within a single collection (Rows, RowsGroups, Columns, Filters, Measures) at any given time. Measures can be any numeric dimension of the data. When measures are added the XamPivotGridGrouping will show aggregate computation data for that specific slice. If no rows or columns are specified, the grid will display the sum of the dimension for all the items in the input data. For example, if we were to remove all the columns, our resultant grid would look like this: Column Headers When you add a hierarchy to the Columns area, a new slice with the current rows will be created. Adding more hierarchies to the same area dices the data. Unlike in the XamPivotGrid, the XamPivotGridGrouping shows the hierarchy tree in a horizontal structure. When you expand or collapse the columns you can drill down or up into the data. <![if vml=""> Figure 3 - Column Headers Notice that the Parent columns for 2012 and Week 1 are now empty. This means that all data items are assigned to either the First Half or Second Half hierarchies (or their child hierarchies). If there was a Pass Yards statistic that was set for Week 1, but was not assigned a value for any further hierarchies, that remainder would be displayed in Week 1. Row Headers Row headers, similar to Column headers, slice the data. The difference now is the data is being sliced based on the current columns. Adding more hierarchies to Rows dices the data. The hierarchy tree for rows is display horizontally as well, and can be confusing because people sometimes fail to understand that these are not data cells, but rather is a grouping of hierarchical row headers. For example, in the image below, the row hierarchy is highlighted. <![if vml=""> As you can see clearly, the Team, Position, Type, Category and Statistic Row headers are all children of the Name hierarchy. Remember our requirement that states “Name, Team, Position must all be sortable (A-Z, Z-A) across the entire data set.”? How do records in a hierarchical set sort? They sort within the confines of the hierarchy. There is an issue here which we will need to resolve. Data Cells Our last sector is the Data Cell sector. This sector shows the data computed as a result of the requested slice(s). In other words, this is a query to the data source, which has been defined by the hierarchies in the rows and columns, filtered by those hierarchies in the filters area (if any), and computed based on the measure(s). You can place conditional formatting on the data cells by providing a template or style for each cell or you can allow the grid itself to display numeric format for the data. Digging Deeper - XamPivotGridGrouping The table below lists the key elements of each of these areas that we are going to need to concern ourselves with in order to meet the project requirements. As you can see, there are a lot of moving parts to concern ourselves with. Let’s finish up this post by looking a little closer at the XamDataSelectorGrouping control. XAMDATASELECTORGROUPING CONTROL The image below divides the XamDataSelectorGrouping into 6 sectors: Hierarchies Filters Columns Rows Measures Row Groups <![if vml=""> Figure 4 - Data Selector Sectors Let’s examine these areas, and their function, briefly. HIERARCHIES This area can provide both a Database selector, and cube selector if you are connected to a database for data. Additionally, it provides the measures and dimensions that the grid will use. Each item in the list is a hierarchical structure. It can be modified to limit the items which appear in the list. FILTERS This area is a drop target for adding dimensions into the filters collection. COLUMNS This area is a drop target for adding dimensions into the columns collection. ROWS This area is a drop target for adding dimensions into the rows collection. MEASURES This area is a drop target for adding dimensions into the measures collection. ROW GROUPS This area is a drop target for adding dimensions into the row groups collection. The Rows Groups area allows the pivoted data rows to be grouped by the associated dimension, as shown below: Figure 5 - Row Grouping Digging Deeper - XamDataSelectorGrouping The table below lists the key elements of each of these areas that we are going to need to concern ourselves with in order to meet the project requirements. Even More Elements In addition to those elements listed above, we have some other items which we will need to modify/style or behavorialize in order to satisfy the specifications. These are as follows: FIELD CHOOSER <![if vml=""> The Field Chooser is essentially a Popup control. It is sourced by a collection of AreaItemData objects and we will need to adapt this to fit some specific limitations. FILTERING CONTROL <![if vml=""> The filtering control which also is essentially a popup control manages the ability to filter the hierarchies. Based on our requirements, we’ll need to modify this control and add sorting features here for the ability to Sort the associated column/row from A=>Z or Z=>A. Before I close this post out, let’s take the data source we created in my last post, and apply it to the XamPivotGridGrouping, and the XamDataSelector. For demonstration purposes, my main window contains 3 user controls. The XAML is as follows: <local:PivotGridGroupingMain x:Name="CustomGroupingPivotGrid" DataContext="{Binding Path =DataContext, RelativeSource={RelativeSource AncestorType=Window}}" Grid.Column="1" Grid.Row="1" Visibility ="{Binding ElementName ShowCustomizedGroupingPivotGrid, Path=IsChecked, Converter={StaticResource boolToVisibilityConverter}}"/> <local:StandardPivotGrid x:Name="BasePivot" Grid.Column="1" Grid.Row="1" DataContext="{Binding Path=DataContext, RelativeSource={RelativeSource AncestorType=Window}}" Visibility="{Binding ElementName=ShowBasePivotGrid, Path=IsChecked, Converter={StaticResource boolToVisibilityConverter}}"/> <local:StandardGroupingView x:Name="GroupingViewPivot" Grid.Column="1" Grid.Row="1" DataContext="{Binding Path=DataContext, RelativeSource={RelativeSource AncestorType=Window}}" Visibility="{Binding ElementName=ShowGroupingPivotGrid, Path=IsChecked, Converter={StaticResource boolToVisibilityConverter}}"/> As you can see here, I am passing the datacontext of the main window ( an object called GroupingMainViewModel) to the StandardGroupingView.DataContext via a binding. There are several ways I could do this, but this seemed the most effective for this sample. Inside the StandardGroupingView there is a XamPivotGridGrouping item and a XamDataSelectorGrouping item. The XAML for these is also shown here: <ig:XamPivotGridGrouping x:Name="BaseGroupingPivot" Grid.Row="1" Grid.Column="1" DataSource="{Binding StatisticResultsDataSource}"> <ig:XamPivotGridGrouping.RowGroupBySettings> <ig:RowGroupBySettings Indent="10"> ig:RowGroupBySettings> <ig:XamPivotGridGrouping.RowGroupBySettings> <ig:XamPivotGridGrouping.FieldChooser> <ig:FieldChooser RowsItemsSource="{Binding RowsChooseList}" MeasuresItemsSource="{Binding MeasuresChooseList}" DisplayMemberPath="Caption" MeasuresGroupHeader="Measures" RowsGroupHeader="Columns" FieldUniqueNameMemberPath="ItemUniqueName" FieldOrdinalMemberPath="Index" MaxHeight="500" Width="22"/> <ig:FieldChooser.GroupHeaderTemplate> <DataTemplate> <Grid> <ContentControl Margin="10, 2" Content="{Binding}" BorderThickness="0" FontWeight="Bold" FontStyle="Italic" /> Grid> DataTemplate> ig:FieldChooser.GroupHeaderTemplate> ig:FieldChooser><ig:XamPivotGridGrouping.FieldChooser>ig:XamPivotGridGrouping> <ig:Expander Grid.Column="2" Grid.Row="1" > <ig:XamDataSelectorGrouping x:Name="dataSelector" DataSource="{Binding DataSelectorFlatDataSource}"><ig:XamDataSelectorGrouping> Both the DataSelector and the PivotGrid are bound to a property of the GroupingMainViewModel: public GroupingFlatDataSource StatisticResultsDataSource { get { if(DataConstructor.OffensiveStaticstAllTeamsData != null) { return DataConstructor.OffensiveStaticstAllTeamsData; } DataConstructor.OffensiveStatisticsAllTeams(); return DataConstructor.OffensiveStaticstAllTeamsData; } } public GroupingFlatDataSource DataSelectorFlatDataSource { get { return DataConstructor.OffensiveStaticstAllTeamsData; }} These properties both return the OffensiveStaticstAllTeamsData property from the DataConstructorViewModel, which is itself a property of the GroupingMainViewModel: private readonly DataConstructorViewModel m_data;public DataConstructorViewModel DataConstructor { get { return m_data; }} So you can see, both the grid and the data selector are bound to the same data source. Without making any further changes, if we run the sample, here is what our XamPivotGridGrouping will look like: <![if vml=""> Figure 6 - Standard View of XamPivotGridGrouping Where are our expanding group columns? This looks exactly like the XamPivotGrid! What’s going on!?! There are a couple more settings we need to apply to the XamPivotGridGrouping to get us closer to what we expect. In the XAML we need to add the following to our declaration of the grid: <ig:XamPivotGridGrouping x:Name="BaseGroupingPivot" Grid.Row="1" Grid.Column="1" DataSource="{Binding StatisticResultsDataSource}" ParentInFrontForColumns="True" UseColumnGroupByView="True"> UseColumnGroupByView must be set to true to enable the style of column grouping we want. And I am also setting ParentInFrontForColumns to assure that my parent column is at the front of my column hierarchy. Now when we run it we get a little closer to our desired result: <![if vml=""> Figure 7 - XamPivotGridGrouping View This gives us a pretty solid idea of how much work we have in front of us. As I mentioned before, there are a lot of moving parts in the XamPivotGridGrouping and quite a few in the XamDataSelectorGrouping as well. Now that we have an idea of at least some of what’s involved, we can start the process of implementing our requirements and modifying the XamPivotGridGrouping control to fit what has been spec’d out. In my next post we’ll look at how we can modify the XamDataSelectorGrouping items so that we can meet the specification of no hierarchies there, and we’ll also look at how to handle adding custom RowHeader Templates to add images and change font sizes for the Name, Team and Position Row Headers.