How to: Create a Master-Detail Report

• Nov 28, 2023
• 4 minutes to read

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

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.

   C#

   nwindDataSetTableAdapters.ProductsTableAdapter productsTableAdapter = new nwindDataSetTableAdapters.ProductsTableAdapter();
   productsTableAdapter.Fill(dataSet.Products);
   

   VB.NET

   Dim productsTableAdapter As nwindDataSetTableAdapters.ProductsTableAdapter = New nwindDataSetTableAdapters.ProductsTableAdapter()
   productsTableAdapter.Fill(dataSet.Products)
   

Warning

The SpreadsheetControl cannot create a master-detail report with side-by-side detail levels. All subsequent non-nested details, which start in the same row (vertical reports) or in the same column (horizontal reports), are ignored.

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.

C#

private void Form1_Load(object sender, System.EventArgs e) {
    spreadsheetControl1.LoadDocument("Documents\\MasterDetailTemplate.xlsx");
}

VB.NET

Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
    SpreadsheetControl1.LoadDocument("Documents\MasterDetailTemplate.xlsx")
End Sub

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.