(X) Hide this
    • Login
    • Join
      • Generate New Image
        By clicking 'Register' you accept the terms of use .

Creating a simple Pivot table using LINQ and Telerik RadTreeView for Silverlight

(3 votes)
Emil Stoychev
Emil Stoychev
Joined Oct 15, 2007
Articles:   23
Comments:   98
More Articles
4 comments   /   posted on Jul 16, 2008
Categories:   General , Controls

This article is compatible with the latest version of 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:

Group Name Quantity
Carbohydrates Total carbohydrates 27.3
Carbohydrates Total disaccharides 5.7
Carbohydrates Total polysaccharides 21.6
Minerals Calcium 147
Minerals Phosphorus 150
Minerals Potassium 201
Minerals Copper 0.13
Minerals Magnesium 19
Minerals Sodium 582
Minerals Selenium 4
Minerals Total iron 0.7
Minerals Zinc 1.07
Vitamins Beta-carotene 173.8
Vitamins Nicotinic 1.5
Vitamins Total vitamin B6 0.127
Vitamins Total vitamin D 0.3
Vitamins Total vitamin E 2.1
Vitamins Vitamin B1 0.1
Vitamins Vitamin B12 0.59
Vitamins Vitamin B2 0.16
Vitamins Vitamin C 10

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:

Carbohydrates   Minerals   Vitamins  
Total carbohydrates 27.3 Calcium 147 Beta-carotene 173.8
Total disaccharides 5.7 Phosphorus 150 Nicotinic 1.5
Total polysaccharides 21.6 Potassium 201 Total vitamin B6 0.127
    Copper 0.13 Total vitamin D 0.3
    Magnesium 19 Total vitamin E 2.1
    Sodium 582 Vitamin B1 0.1
    Selenium 4 Vitamin B12 0.59
    Total iron 0.7 Vitamin B2 0.16
    Zinc 1.07 Vitamin C 10

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:

<?xml version="1.0" encoding="utf-8" ?>
    <Nutrition Group="Carbohydrates" Name="Total carbohydrates" Quantity="27.3"></Nutrition>
    <Nutrition Group="Carbohydrates" Name="Total disaccharides" Quantity="5.7"></Nutrition>
    <Nutrition Group="Carbohydrates" Name="Total polysaccharides" Quantity="21.6"></Nutrition>
    <Nutrition Group="Minerals" Name="Calcium" Quantity="147"></Nutrition>
    <Nutrition Group="Minerals" Name="Phosphorus " Quantity="150"></Nutrition>

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

public class Nutrition
    public string Group { get; set; }
    public string Name { get; set; }
    public string Quantity { get; set; }

and NutritionGroup:

 public class NutritionGroup
    public string NutritionGroupHeader { get; set; }
    public Collection<Nutrition> Nutritions { get; set; }

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.

List data = ( from nutrition in nutritionsDoc.Descendants( "Nutrition" )
            select new Nutrition
                Group = nutrition.Attribute( "Group" ).Value,
                Name = nutrition.Attribute( "Name" ).Value,
                Quantity = nutrition.Attribute( "Quantity" ).Value
            } ).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.

 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 added to the nutritions in the corresponding root. When the ItemSource property is set to the collection of nutrition groups, the RadTreeView will be populated with the corresponding data:

Collection<NutritionGroup> nutritions = new Collection<NutritionGroup>();
foreach ( IGrouping<string, Nutrition> nutritionGroup in query )
    NutritionGroup group = new NutritionGroup()
        NutritionGroupHeader = nutritionGroup.Key,
    group.Nutritions = new Collection<Nutrition>();
    foreach ( Nutrition nutrition in nutritionGroup )
        group.Nutritions.Add( nutrition );
    nutritions.Add( group );
nutritionTree.ItemsSource = nutritions;

Ok, we are almost over. Let's take a look at the NutritionGroupTemplate and NutritionTemplate that we will use. First, we will need the following schema:

Having that, follows both the templates:


<telerik:HierarchicalDataTemplate x:Key="NutritionGroupTemplate"
          ItemsSource="{Binding Nutritions}" ItemTemplate="{StaticResource NutritionTemplate}">
    <Border BorderThickness="1" BorderBrush="#ececec" CornerRadius="4">
        <Border BorderThickness="1" BorderBrush="White" Padding="1" CornerRadius="4">
                <LinearGradientBrush EndPoint="0.5,1" StartPoint="0.5,0">
                    <GradientStop Color="#f8f8f8" />
                    <GradientStop Color="#ececec" Offset="1" />
            <TextBlock Text="{Binding NutritionGroupHeader}" FontWeight="Bold" />


<telerik:HierarchicalDataTemplate x:Key="NutritionTemplate">
    <StackPanel Orientation="Horizontal">
        <TextBlock Text="{Binding Name}" Width="150"></TextBlock>
        <TextBlock Text="{Binding Quantity}" FontWeight="Bold"></TextBlock>

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

<Style TargetType="telerik:RadTreeViewItem" x:Key="TreeViewItemStyle">
    <Setter Property="IsExpanded" Value="True"></Setter>
    <Setter Property="ItemsPanel">
                <StackPanel HorizontalAlignment="Center"
                            Margin="4,6" Orientation="Vertical" />

And the RadTreeView element:

<telerik:RadTreeView x:Name="nutritionTree"
                     ItemTemplate="{StaticResource NutritionGroupTemplate}"
                     ItemContainerStyle="{StaticResource TreeViewItemStyle}">
            <StackPanel VerticalAlignment="Top" Orientation="Horizontal" />

Ready! And here is the final result...

The source code of this article is available for download (you will need to have Telerik.Windows.Controls and Telerik.Windows.Controls.Navigation dlls installed in order to use the source code).


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.


Using LINQ to XML in Silverlight 2 by Martin Mihaylov

Telerik RadControls for Silverlight



  • -_-

    RE: Creating a simple Pivot table using LINQ and RadTreeView for Silverlight

    posted by Darek on Jun 30, 2009 18:54
    Unfortunatelly, this technique suffers from a Silverlight bug, that doesn't allow more than 200 TextBoxes on the same page. It generates "silverlight layout cycle detected" exception.
  • -_-

    RE: Creating a simple Pivot table using LINQ and RadTreeView for Silverlight

    posted by broogie on Jul 31, 2009 12:16
    Code Sample Not Working for some reason - even the preview in this page seems to be blank
  • -_-

    RE: Creating a simple Pivot table using LINQ and RadTreeView for Silverlight

    posted by franck on Dec 01, 2010 15:07
    You re application is not compatible with new version of silverlight .
  • lnikolov

    RE: Creating a simple Pivot table using LINQ and Telerik RadTreeView for Silverlight

    posted by lnikolov on Jan 12, 2011 17:14
    The article has been updated to the latest version of Silverlight and Visual Studio.

Add Comment

Login to comment:
  *      *