Creating a simple Pivot table using LINQ and RadTreeView for Silverlight

What is a Pivot table/grid? According to Wikipedia it is a data summarization tool found in spreadsheet applications. Still when I was a child I learned that people understand the best when they see an example.

Consider you have a table that contains the nutrition of given food, say a pizza:

In the data above you see that every nutrition is contained in a specific Group - 3 groups and 21 nutrition in total. To display the nutrition in a more meaningful way in most cases you need to group the nutrition (rows in the general case) by their Group attribute and display them in columns instead of in rows. Now we are closer to what we call a Pivot table - group and turn rows into columns.

So the above table displayed in a Pivot would look like that:

I searched for a 3rd party control that can help me achieve this goal out of the box, but I couldn't find one. Looking for a way to do it I finally made it work with the help of LINQ - for grouping and the Telerik RadTreeView for Silverlight - for displaying.

Loading the data with LINQ

First, consider we have the initial table of nutrition exported to XML. The output look like that:

   1: <?xml version="1.0" encoding="utf-8" ?>

   2: <Nutritions>

   3:     <Nutrition Group="Carbohydrates" Name="Total carbohydrates" Quantity="27.3"></Nutrition>

   4:     <Nutrition Group="Carbohydrates" Name="Total disaccharides" Quantity="5.7"></Nutrition>

   5:     <Nutrition Group="Carbohydrates" Name="Total polysaccharides" Quantity="21.6"></Nutrition>

   6:     <Nutrition Group="Minerals" Name="Calcium" Quantity="147"></Nutrition>

   7:     <Nutrition Group="Minerals" Name="Phosphorus " Quantity="150"></Nutrition>

Create a business object Nutrition that will be used later when loading the XML with LINQ.

   1: public class Nutrition

   2: {

   3:     public string Group { get; set; }

   4: 

   5:     public string Name { get; set; }

   6: 

   7:     public string Quantity { get; set; }

   8: }

Now, it's time to load the XML document with LINQ. Martin Mihaylov published a great article on using LINQ to XML in Silverlight so if you are not familiar you'd better go read it first.

   1: List data = ( from nutrition in nutritionsDoc.Descendants( "Nutrition" )

   2:                                select new Nutrition

   3:                                {

   4:                                    Group = nutrition.Attribute( "Group" ).Value,

   5:                                    Name = nutrition.Attribute( "Name" ).Value,

   6:                                    Quantity = nutrition.Attribute( "Quantity" ).Value

   7:                                } ).ToList();

Grouping the data with LINQ and building the tree

Ok, this is the core of this article. The ability to group is a great feature in LINQ. It really does simplify the code to minimum.

We need to group by the Group attribute.

   1: IEnumerable<string, Nutrition>> query = data.GroupBy( nutrition => nutrition.Group );

Now, each nutrition group should be a root node in the tree and each nutrition in this group should be a child in the corresponding root.

   1: foreach ( IGrouping<string, Nutrition> nutritionGroup in query )

   2: {

   3:     RadTreeViewItem root = new RadTreeViewItem();

   4:     root.HeaderTemplate = ( DataTemplate )this.Resources[ "NutritionGroupTemplate" ];

   5:     root.DataContext = nutritionGroup.Key;

   6: 

   7:     foreach ( Nutrition nutrition in nutritionGroup )

   8:     {

   9:         RadTreeViewItem child = new RadTreeViewItem();

  10:         child.HeaderTemplate = ( DataTemplate )this.Resources[ "NutritionTemplate" ];

  11:         child.DataContext = nutrition;

  12: 

  13:         root.Items.Add( child );

  14:     }

  15: 

  16:     nutritionTree.Items.Add( root );

  17: }

Basically, iterating through the groups we create a new RadTreeViewItem - root, then iterate through all nutrition in this group and create another RadTreeViewItem - child. Each child is added to the root. Setting the DataContext of the root to nutritionGroup.Key allows us to bind to the Group attribute. The DataContext of the child is set to nutrition which contains the other 2 attributes - Name and Quantity.

Ok, we are almost over. Let's take a look at the NutritionGroupTemplate and NutritionTemplate that we are using for HeaderTemplate in the child and the root.

NutritionGroupTemplate

   1: <DataTemplate x:Key="NutritionGroupTemplate">

   2:     <Border BorderThickness="1" BorderBrush="#ececec" CornerRadius="4">

   3:         <Border BorderThickness="1" BorderBrush="White" Padding="1" CornerRadius="4">

   4:             <Border.Background>

   5:                 <LinearGradientBrush EndPoint="0.5,1" StartPoint="0.5,0">

   6:                     <GradientStop Color="#f8f8f8"  />

   7:                     <GradientStop Color="#ececec" Offset="1"  />

   8:                 </LinearGradientBrush>

   9:             </Border.Background>

  10:             <TextBlock Text="{Binding}" FontWeight="Bold"></TextBlock>

  11:         </Border>

  12:     </Border>

  13: </DataTemplate>

NutritionTemplate

   1: <DataTemplate x:Key="NutritionTemplate">

   2:     <StackPanel Orientation="Horizontal">

   3:         <TextBlock Text="{Binding Name}" Width="150"></TextBlock>

   4:         <TextBlock Text="{Binding Quantity}" FontWeight="Bold"></TextBlock>

   5:     </StackPanel>

   6: </DataTemplate>

The default orientation of the nodes in the RadTreeView, as expected, is vertical. However in our case it makes more sense to arrange the nodes on the horizontal.

   1: <telerik:RadTreeView x:Name="nutritionTree" ItemsIndent="0">

   2:     <telerik:RadTreeView.ItemsPanel>

   3:         <ItemsPanelTemplate>

   4:             <StackPanel VerticalAlignment="Top" Orientation="Horizontal"   />

   5:         </ItemsPanelTemplate>

   6:     </telerik:RadTreeView.ItemsPanel>

   7: </telerik:RadTreeView>

Ready! The final result looks like that:

The source code of this article is available for download.

Summary

LINQ and RadTreeView make it easy to display a Pivot table. However, I would greatly appreciate a Pivot table control that can be bound to a data source. In our example we are building the tree manually which is not the best way showing data these days. I hope some of the component vendors will think more on this topic and release such control.

References

Using LINQ to XML in Silverlight 2 by Martin Mihaylov

Telerik RadControls for Silverlight