Lesson 2 - Binding a Pivot Grid to an Embedded XML File

• May 22, 2020
• 5 minutes to read

This tutorial will help you learn how to bind a pivot grid to an embedded XML file that contains data from the Northwind database. We will use data from the Orders table.

This will require eleven simple steps.

• Steps 1-5. Create a New Project and Add PivotGridControl
• Steps 6-7. Embed an XML File into the Application
• Steps 8-9. Create Data Classes
• Step 10. Create Pivot Grid Data Source
• Step 11. Create Pivot Grid Fields and Bind Them to Database Fields
• Result

Steps 1-5. Create a New Project and Add PivotGridControl

1. Run MS Visual Studio 2010.
2. Create a new Silverlight Application project.

3. Add PivotGridControl to your project. You can do this by dragging the PivotGridControl item from the DX.14.2: Data & Analytics toolbox tab.

   

4. Right-click the pivot grid and choose the Reset Layout | All option in the context menu. This will stretch the control to fill the whole window.

   

5. After this, your XAML may look like the following. If it does not, you can overwrite your code with:

   XAML

   
   <UserControl xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
                xmlns:dxpg="http://schemas.devexpress.com/winfx/2008/xaml/pivotgrid"
                xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
                xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
                xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
                x:Class="SilverlightApplication1.MainPage"
                d:DesignHeight="300" d:DesignWidth="400" 
                mc:Ignorable="d">
       <Grid x:Name="LayoutRoot" Background="White">
           <dxpg:PivotGridControl Name="pivotGridControl1" />
       </Grid>
   </UserControl>
   

   Note that you can add PivotGridControl by overwriting your MainPage.xaml file with this code without dragging PivotGridControl to the page. However, in this case, you need to manually add references to the following libraries.

   DevExpress.Data.v14.2, DevExpress.Xpf.Core.v14.2, DevExpress.PivotGrid.v14.2.Core, DevExpress.Xpf.PivotGrid.v14.2.

   

   NOTE

   Normally, when adding references to these assemblies, you should choose them from the Global Assembly Cache (GAC). However, if you prefer to copy them locally, or need to include them later into your product's installation, you can find their copies in the following directory.

   C:\Program Files (x86)\DevExpress 14.2\Components\Bin\Silverlight

Steps 6-7. Embed an XML File into the Application

6. Add an XML data file to the project. To do this, right-click the project in the Solution Explorer and choose Add | Existing Item from the context menu.

   

   This invokes the Add Existing Item dialog. In this dialog, locate the following file:

   C:\Users\Public\Documents\DevExpress Demos 14.2\Components\Data\nwind.xml

   

   Click Add to close the dialog.

7. Embed this file into the Silverlight application. To do this, right-click the XML file in the Solution Explorer and select Properties from the context menu.

    

   

    

   This invokes the Properties window. In this window, set the Build Action property to Embedded Resource.

   

    

   Now the XML file is embedded into the Silverlight application.

Steps 8-9. Create Data Classes

8. Add a reference to the System.Xml.Serialization.dll assembly that will be used to parse an XML file. To do this, right-click References in the Solution Explorer and choose Add Reference from the context menu.

    

   

    

   In the Add Reference dialog, select the System.Xml.Serialization assembly and click OK to close the dialog.

   

9. Then implement data classes. We will need two classes:

   • the Orders class that defines a data item (a row from the Orders table);
   • the OrderData class that defines a collection of data items.

   To do this, right-click the project in the Solution Explorer and choose Add | New Item from the context menu.

   

   This invokes the Add New Item dialog. In this dialog, select the Class item, enter its name (OrderData.cs) and click Add.

   

   Finally, put the following class declarations into the OrderData.cs file.

   C#

   
   using System;
   using System.Collections.Generic;
   using System.Xml.Serialization;
   
   //...
   
   [XmlRoot("NewDataSet")]
   public class OrderData : List<Orders> {
       public OrderData() { }
   }
   public class Orders {
       public int OrderID { get; set; }
       public string CustomerID { get; set; }
       public int EmployeeID { get; set; }
       public DateTime OrderDate { get; set; }
       public DateTime RequiredDate { get; set; }
       public DateTime ShippedDate { get; set; }
       public int ShipVia { get; set; }
       public Decimal Freight { get; set; }
       public string ShipName { get; set; }
       public string ShipAddress { get; set; }
       public string ShipCity { get; set; }
       public string ShipRegion { get; set; }
       public string ShipPostalCode { get; set; }
       public string ShipCountry { get; set; }
   }
   

   Note that the name and member set of a data item class must match the declaration of the corresponding data element in the XML schema. The following is the declaration of the Orders XML element.

   XML

   
   <xs:element name="Orders">
     <xs:complexType>
       <xs:sequence>
         <xs:element name="OrderID" type="xs:int" minOccurs="0" />
         <xs:element name="CustomerID" type="xs:string" minOccurs="0" />
         <xs:element name="EmployeeID" type="xs:int" minOccurs="0" />
         <xs:element name="OrderDate" type="xs:dateTime" minOccurs="0" />
         <xs:element name="RequiredDate" type="xs:dateTime" minOccurs="0" />
         <xs:element name="ShippedDate" type="xs:dateTime" minOccurs="0" />
         <xs:element name="ShipVia" type="xs:int" minOccurs="0" />
         <xs:element name="Freight" type="xs:decimal" minOccurs="0" />
         <xs:element name="ShipName" type="xs:string" minOccurs="0" />
         <xs:element name="ShipAddress" type="xs:string" minOccurs="0" />
         <xs:element name="ShipCity" type="xs:string" minOccurs="0" />
         <xs:element name="ShipRegion" type="xs:string" minOccurs="0" />
         <xs:element name="ShipPostalCode" type="xs:string" minOccurs="0" />
         <xs:element name="ShipCountry" type="xs:string" minOccurs="0" />
       </xs:sequence>
     </xs:complexType>
   </xs:element>
   

Step 10. Create Pivot Grid Data Source

Now add code that will parse an XML file, create an OrderData collection and bind PivotGridControl to this collection.

To do this, rewrite the MainPage constructor in the following way.

C#

using System.Reflection;
using System.IO;
using System.Xml.Serialization;

//...

public MainPage() {
    InitializeComponent();
    Stream stream = Assembly.GetExecutingAssembly()
        .GetManifestResourceStream("SilverlightApplication1.nwind.xml");
    XmlSerializer s = new XmlSerializer(typeof(OrderData));
    pivotGridControl1.DataSource = s.Deserialize(stream);
}

Step 11. Create Pivot Grid Fields and Bind Them to Database Fields

Finally, you should create the required pivot grid fields and map them to the corresponding data fields to display bound data.

To do this, open the Properties window and click the ellipsis button in the Fields row to edit the PivotGridControl.Fields collection.

This opens the Field Collection Editor dialog that allows you to add fields to PivotGridControl. To add a field, click the Add button. Use the Properties pane to adjust properties of the created field.

In this dialog, add five fields and set their properties to the following values.

	Area	Caption	FieldName	GroupInterval	ValueFormat
1	RowArea	Country	ShipCountry	-	-
2	RowArea	Customer	ShipName	-	-
3	ColumnArea	Quarter	ShippedDate	DateQuarter	Qtr {0}
4	ColumnArea	Month	ShippedDate	DateMonth	-
5	DataArea	-	Freight	-	-

Click OK to close the dialog.

Result

Run the project and see the result.