[Expand]General Information
[Expand]WinForms Controls
[Collapse]ASP.NET Controls and MVC Extensions
 [Expand]Prerequisites
 [Expand]What's Installed
 [Expand]Common Concepts
 [Expand]ASP.NET WebForms Controls
 [Collapse]ASP.NET MVC Extensions
  [Expand]Product Information
  [Expand]Getting Started
  [Expand]Common Concepts
  [Expand]Grid View
  [Expand]Tree List
  [Expand]Card View
  [Expand]Reporting
  [Expand]Charting
  [Collapse]Pivot Grid
   [Collapse]Getting Started
     Lesson 1 - Bind MVCxPivotGrid to Microsoft SQL Server Database File
     Lesson 2 - Bind a Pivot Grid to an OLAP Data Source
     Tutorial Videos
    Integrate the PivotGrid Extension into a Project
   [Expand]Binding to Data
    Main Features - PivotGrid
   [Expand]Export
    Client-Side Functionality
   [Expand]UI Elements
   [Expand]Examples
  [Expand]Spreadsheet
  [Expand]Rich Text Editor
  [Expand]Scheduler
  [Expand]Site Navigation and Layout
  [Expand]HTML Editor
  [Expand]Vertical Grid
  [Expand]Data Editors Extensions
  [Expand]Docking and Popups
  [Expand]File Management
  [Expand]Data and Image Navigation
  [Expand]Multi-Use Site Extensions
  [Expand]SpellChecker
  [Expand]Query Builder
 [Expand]Localization
 [Expand]Redistribution and Deployment
  Get More Help
 [Expand]API Reference
[Expand]ASP.NET Bootstrap Controls
[Expand]ASP.NET Core Bootstrap Controls
[Expand]WPF Controls
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Office File API
[Expand]Reporting
[Expand]Report and Dashboard Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Expand]CodeRush
[Expand]CodeRush Classic
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation

Lesson 1 - Bind MVCxPivotGrid to Microsoft SQL Server Database File

This topic describes how to add the ASP.NET MVC PivotGrid extension to a Web application, bind the extension to a data source and map pivot grid fields to data fields. This example gets its data from a Microsoft SQL Server Database File created in Visual Studio Designer using the query included in the project files.

Note

The complete sample project How to Bind a Dashboard to Microsoft SQL Server Database File is available in the DevExpress Examples repository

Follow the steps below to create an ASP.NET MVC application with the PivotGrid extension.

Expanded Steps 1-6. Create an ASP.NET MVC Application

  1. Run Visual Studio.
  2. Select File | New | Project… to create a new project. Open the Web section in the New Project dialog and choose the DevExpress v18.1 Template Gallery.

  3. Navigate to the ASP.NET category and select Empty Web Application.

  4. Navigate to the Choose Layout page in the DevExpress ASP.NET MVC Project Wizard and select Standard.

  5. Select the Metropolis theme.

  6. Click Create Project to create an MVC application.

Expanded Steps 7-8. Create a Microsoft SQL Server Database File

  1. Click the App_Data folder and press Ctrl-Shift-A to create an empty database file and add a new data item. Select SQL Server Database and name it Nwind_SalesPerson.mdf.

  2. Create a table and populate it with data. To do this, double-click the newly created database file in Solution Explorer to open the Server Explorer window. Right-click the Nwind_SalesPerson.mdf database and select New Query to invoke the SQLQuery1.sql window. Open the App_Data/NWind_SalesPerson.sql file, copy its content to the query window. Execute the query (select Execute in the context menu). After successful execution, close the SQLQuery1.sql query window without saving the changes.

Expanded Steps 9-15. Create a Data Model

  1. Right-click the Models folder and select Add -> New Item.... In the item list, select a new ADO.NET Entity Data Model and name it nwindDataContext.

    Click Add to invoke the Entity Data Model Wizard.

  2. Select the model created in the EF Designer based on an existing database.

  3. The wizard prompts for a data connection. Click New Connection and select the Microsoft SQL Server Database File data source.

  4. Select the file Nwind_SalesPerson.mdf created in Step 7. Leave the Windows Authentication box checked.

    The resulting data connection string is shown in the picture below.

  5. Select the data table to include in the model - the SalesPerson table.

  6. A Microsoft securty warning popup about the T4 Text Template is displayed. Click OK.

  7. The image below shows the created nwindDataContext data model used in this tutorial.

    Rebuild the solution before proceeding to the next step.

Expanded Steps 16-18. Add the PivotGrid Extension to the MVC Application

  1. Switch to Views | Home | Index.cshtml, set the cursor to the new line below the ViewBag.Title assignment, right-click and select Insert DevExpress MVC Extension... in the context menu.

  2. The Insert DevExpress Extension wizard appears. Switch to the Data tab , select PivotGrid and specify the following settings:
    • In the Data context class combo box, select the DXWebApplication1.Models.Nwind_SalesDataEntities class.
    • In the Model class combo box, select the DXWebApplication1.Models.SalesPerson data model.
    • In the Columns combo box, select the Country, ProductName, CategoryName, ExtendedPrice, and SalesPerson1 fields to add them to the PivotGrid.

    Click Insert. The Insert Extension Wizard generates a partial view with PivotGrid settings and inserts the code into the controller class.

  3. Modify the Views | Shared | _Layout.cshtml file. Attach stylesheets and scripts as shown in the code snippet below.

You now have a functional MVC PivotGrid bound to a data source.

Expanded Step 19. Configure Pivot Grid Fields

  1. To configure a PivotGrid layout, open the automatically generated Views | Home | _PivotGridPartial.cshtml file containing PivotGrid settings. Use the field's PivotGridFieldBase.Area and PivotGridFieldBase.AreaIndex properties to specify a field's location and order, and the PivotGridFieldBase.Caption property to change its caption.

    The code snippet below shows the _PivotGridPartial.cshtml file containing recommended settings.

Run the project to see the result.

Is this topic helpful?​​​​​​​