Lesson 3 - Using a Pivot Grid with RIA Services

• May 22, 2020
• 4 minutes to read

DXPivotGrid can be bound to data using a number of ways. One of the methods is to use the WCF RIA Services to create a model and load data from a SQL Server database. In this example, an ADO.NET Entity Data Model is created and PivotGridControl is bound to a local SQL Server Express Database.

Steps 1-3. Creating a Project

1. Create a new Silverlight web application project.

2. Enable WCF RIA Services and proceed.

   

3. A new solution consists of two projects:

   • DXPivotGrid_RIAServices - contains the Silverlight code;
   • DXPivotGrid_RIAServices.Web - contains the ASP.NET web application code.

Steps 4-8. Accessing Data from a Database

4. Select the DXPivotGrid_RIAServices.Web project and add a new item.

   

5. Select ADO.NET Entity Data Model.

   

6. Choose model contents.

   

7. Select a data connection to the Northwind database.

   

   NOTE

   If there is no appropriate connection in the list, click the New Connection button and create a connection to the Northwind database using the invoked dialog.

8. Select the Invoices view and finish.

   

   The image below shows the result.

   

Steps 9-11. Creating a DomainService

A DomainService is a class that exposes entities and operations for a specific data domain.

9. Rebuild the solution. Select the DXPivotGrid_RIAServices.Web project and add a new domain service.

   

10. Select the NWINDEntities model and expose the Invoices entity.

    

11. Finally, rebuild the solution again.

Steps 12-14. Adding a Pivot Grid

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

    

13. 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.

    

14. 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

Step 15. Specifying Pivot Grid Data Source

Add the following code to the MainPage constructor.

C#

using System.Windows.Controls;
using System.ServiceModel.DomainServices.Client;
using DXPivotGrid_RIAServices.Web;

namespace DXPivotGrid_RIAServices {
    public partial class MainPage : UserControl {
        private nwDomainContext _invoicesContext = new nwDomainContext();
        public MainPage() {
            InitializeComponent();
            LoadOperation<Invoices> loadOperation = 
                _invoicesContext.Load(_invoicesContext.GetInvoicesQuery(),
                                      loadOperation_Completed,
                                      false);
            pivotGridControl1.DataSource = loadOperation.Entities;
        }
        private void loadOperation_Completed(LoadOperation<Invoices> loadOperation) {
            pivotGridControl1.ReloadData();
        }
    }
}

Step 16. Creating Pivot Grid Fields and Binding 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

The image below shows the result.