[Expand]General Information
[Collapse]WinForms Controls
  .NET Core Support
 [Expand]What's Installed
 [Expand]Build an Application
 [Collapse]Controls and Libraries
  [Expand]Forms and User Controls
  [Expand]Messages, Notifications, and Dialogs
  [Expand]Editors and Simple Controls
  [Expand]Ribbon, Bars and Menu
  [Expand]Application UI Manager
  [Expand]Docking Library
  [Expand]Data Grid
  [Expand]Vertical Grid
  [Expand]Property Grid
  [Expand]Pivot Grid
  [Expand]Tree List
  [Expand]Gantt Control
  [Expand]Chart Control
  [Expand]Map Control
   [Expand]Product Information
    Product Structure
    Getting Started
   [Expand]Spreadsheet Document
    Supported Formats
   [Expand]Cell Basics
    Defined Names
    Data Binding
    Data Validation
   [Expand]Data Presentation
    Data Grouping
   [Expand]Pivot Tables
   [Expand]Charts and Graphics
   [Expand]Mail Merge
    Operation Restrictions
    Find and Replace
   [Expand]Visual Elements
   [Expand]Keyboard Shortcuts
    [Expand]Rows and Columns
    [Expand]Import and Export Data
    [Expand]Data Binding
    [Collapse]Mail Merge
      How to: Perform a Mail Merge
      How to: Create a Master-Detail Report
      How to: Sort Data
      How to: Group Data
      How to: Filter Data
      How to: Create a Master-Detail Report Without Coding
    [Expand]Formatting Cells
    [Expand]Conditional Formatting
    [Expand]Group Data
    [Expand]Filter Data
    [Expand]Pivot Tables
  [Expand]Rich Text Editor
  [Expand]Spell Checker
  [Expand]Form Layout Managers
  [Expand]Navigation Controls
  [Expand]PDF Viewer
  [Expand]TreeMap Control
  [Expand]Sunburst Control
 [Expand]Common Features
  Get More Help
 [Expand]API Reference
[Expand]ASP.NET Controls and MVC Extensions
[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]Report and Dashboard Server
[Expand]eXpressApp Framework
[Expand]eXpress Persistent Objects
[Expand]CodeRush Classic
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation
View this topic on docs.devexpress.com (Learn more)

How to: Create a Master-Detail Report

This tutorial illustrates how to use the SpreadsheetControl's mail merge functionality to construct a master-detail spreadsheet report and bind it to a two-level Categories-Products hierarchical data source.

It is recommended that you review the How to: Perform a Mail Merge topic first. It will help you learn how to create a mail merge template and bind it to data.

To create the Categories-Products master-detail report, follow the steps below.

  1. Add a Detail Data Table
  2. Prepare a Master-Detail Template
  3. Get the Result

Expanded Add a Detail Data Table

  1. Open the application that was created in the How to: Perform a Mail Merge example of the current Mail Merge section. A mail merge template in this application is already bound to the Categories table of the Northwind database. To create a master-detail report, add the Products table to the nwindDataSet's schema. To do this, follow the steps below.
  2. Open the nwindDataSet.xsd designer and expand the Server Explorer pane, which shows all data connections that are currently available in Visual Studio. For this, in the View menu, choose the Server Explorer option or press Ctrl+Alt+S.
  3. In the list of available Data Connections, locate the one that points to the nwind.mdb file, then select the Products data table and drop it onto the designer. This will add the Products table to nwindDataSet.

  4. When the Products table is added to the dataset schema, the appropriate master-detail relationship between the Categories and Products tables is automatically created by Visual Studio. To apply these changes, click the Save icon on the toolbar.

  5. Populate the ProductsDataTable table with data from the database using the Fill method of the ProductsTableAdapter object.

Expanded Prepare a Master-Detail Template

  1. Run the application. The mail merge template created in the How to: Perform a Mail Merge example will automatically be loaded into the SpreadsheetControl.
  2. Activate the Mail Merge Design View. Extend the existing detail range (marked Detail in the template) three rows down by dragging its bottom right corner.

  3. An existing detail range contains fields for the top-level data from the Categories parent table. Add a nested level to the detail range to display the detail-level data from the CategoriesProducts child table.

    Select the second row from the bottom in the Detail range, click the Master-Detail button, and then click Detail Level in the invoked submenu. The DetailLevel0 range will be created.

  4. Set the CategoriesProducts table as the data member for this detail level. To do this, select any cell within the DetailLevel0 range, and click Data Member in the Master-Detail submenu. In the invoked dialog, select the CategoriesProducts table for DETAILLEVEL0.

    Each DetailLevel range created in the mail merge template is stored as a "DETAILLEVEL(n)" defined name, where (n) is a zero-based nesting level. A data member assigned to a detail level is saved as a "DETAILDATAMEMBER(n)" defined name for a string constant specifying a data member name.

    For example, after you have created a nested level within the detail range as described above, the following defined names are added to the Worksheet.DefinedNames collection of the "Sheet1" worksheet.

    Name RefersTo
    DETAILLEVEL0 Sheet1!$B$9:$D$9
    DETAILDATAMEMBER0 "CategoriesProducts"
  5. Add the ProductName, UnitsInStock and UnitPrice data fields from the CategoriesProducts table into the created detail level range cells.


    Note that these fields must be taken from the CategoriesProducts relation, and not from the Products table. Otherwise, the report will display only the first record of the Products table for each category, as many times as there are records in each category.

  6. Enter column names in the last row of the detail range and format it as a header for detail-level data.

  7. Finally, select the bottommost row of the detail range and specify the top border for its cells, to visually separate each category in the resulting document.

The master-detail template is now ready. Save it to the MasterDetailTemplate.xlsx file in the Documents folder.

Add the following code to automatically load the created template to the SpreadsheetControl when invoking the application.

Expanded Get the Result

Run the application. Click the Mail Merge Preview button to display the merged document in a preview window, or click the Result button to automatically save the document to a file.


A complete sample project is available in the DevExpress Code Examples database at http://www.devexpress.com/example=T109795.

Expanded See Also

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