Log In
Home
Support
Demos
Documentation
Blogs
Training
Webinars
[Expand]General Information
[Collapse]WinForms Controls
  Prerequisites
 [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]Pivot Grid
  [Expand]Tree List
  [Expand]Chart Control
  [Expand]Diagrams
  [Expand]Gauges
  [Expand]Map Control
  [Expand]Scheduler
  [Collapse]Spreadsheet
   [Expand]Product Information
    Product Structure
    Getting Started
   [Expand]Spreadsheet Document
    Supported Formats
   [Expand]Cell Basics
   [Expand]Spreadsheet Formulas
    Defined Names
    Data Binding
    Data Validation
   [Expand]Data Presentation
    Data Grouping
   [Expand]Pivot Table Overview
   [Expand]Charting Overview
    Printing
    Events
   [Expand]Mail Merge Overview
    Protection
    Find and Replace
   [Expand]Visual Elements
    Services
   [Expand]Shortcuts
   [Collapse]Examples
    [Expand]Files
    [Expand]Workbooks
    [Expand]Worksheets
    [Expand]Rows and Columns
    [Expand]Cells
    [Expand]Formulas
    [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]Tables
    [Expand]Pivot Tables
    [Expand]Printing
    [Expand]Pictures
    [Expand]Charts
    [Expand]Protection
    [Expand]Customization
  [Expand]Rich Text Editor
  [Expand]Spell Checker
  [Expand]Form Layout Managers
  [Expand]Navigation Controls
  [Expand]Printing-Exporting
  [Expand]PDF Viewer
   Reporting
  [Expand]Snap
  [Expand]TreeMap Control
 [Expand]Common Features
  Get More Help
 [Expand]API Reference
[Expand]ASP.NET Controls and MVC Extensions
[Expand]ASP.NET Bootstrap Controls
[Expand]WPF Controls
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Document Server
[Expand]Reporting
[Expand]Report Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Expand]CodeRush
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation

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

    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.

Show Me

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

Expanded See Also

How would you rate this topic?​​​​​​​