How to: Create a Master-Detail Report Without Coding
- 4 minutes to read
This tutorial illustrates how to use the SpreadsheetControl’s mail merge functionality to construct a master-detail spreadsheet report without a single line of code.
We have recorded a video available online that follows this step-by-step guide.
To create the Categories-Products master-detail report, follow the steps below.
- Create a New Spreadsheet Application
- Bind a Worksheet to Data using Data Source Wizard
- Specify Relationships
- Specify Template Ranges
- Insert Mail Merge Fields
- Preview the Result
Create a New Spreadsheet Application
To get started, create a new spreadsheet application with the help of the DevExpress Template Gallery .
Start a new project in Visual Studio, select DevExpress Template Gallery, and launch Project Wizard. Select required options for a Winforms Ribbon Form Spreadsheet application and click Create.
The application will be created automatically.
- When a new application is ready, click the SpreadsheetControl smart tag in the Visual Studio designer and select Create Mail Merge Bars to add a Mail Merge tab.
- Select Create Field List Panel to add the Field List Panel panel to the right of the SpreadsheetControl.
- Select Create Parameters Panel to add the Parameters Panel to the bottom right of the SpreadsheetControl.
- Run the application.
Bind a Worksheet to Data using Data Source Wizard
Create a new workbook. Modify a document by entering captions, changing cell fonts and backgrounds and formatting cell ranges as shown in the following picture.
- Activate the Mail Merge Design View to display the template structure. To do this, click the Mail Merge Design View button on the Mail Merge tab, in the Design group.
- Click Add Data Source to invoke Data Source Wizard.
Select Databaseas the data source type. Click Next.
- Select an option to create a new data connection. Click Next.
- Select the MS Access 97 data provider and locate the database file nwind.mdb in the Data subfolder of the DevExpress demos. Click Next.
- Select an option that does not save credentials. Click Next.
- Click Run Query Builder to invoke the Query Designer window that enables you to select data from the data source.
Add the Suppliers table and include all columns into the query.
Click OK to close Query Designer.
- Click Next to save the query.
- Click OK to select the newly created data source.
- Click Manage Relations -> Manage Queries to invoke Query Designer and create a new query for the Products table.
- Click Manage Relations -> Manage Queries to invoke Query Designer and create a new query for the Order Details table.
- Click Manage Relations to invoke the Master-Detail Relations Editor.
Add relations to the Suppliers and Products tables. The editor suggests appropriate relations automatically.
Specify Template Ranges
- Select the “B3:I13” cell range and click the Detail button on the Mail Merge tab, in the Template Ranges group. This cell range will be repeated for each record of the data source.
- Click Select Data Member to invoke a dialog to select Suppliers as the data member in the worksheet data source.
- Specify a header to display above all detail range instances in the resulting worksheet. Select the “B3:I3” range. Click the Header button on the Mail Merge tab.
- Add a nested level to the detail range to display the detail-level data from the SuppliersProducts child table. Select the “B9:I13” cell range, click the Master-Detail button, and then click Detail Level in the invoked submenu. The DetailLevel0 range will be created.
- Select Master-Detail->Data Member to specify SuppliersProducts nested data member for the Detail Level 0.
- Select the “D13:I13” cell range for the next level sub-detail area (Detail Level 1) and click Master-Detail->Detail Level to mark it up.
- Select Master-Detail->Data Member to specify ProductsOrder Details nested data member for the DetailLevel1.
The resulting template is shown below:
Insert Mail Merge Fields
Select a cell and double-click the field in the Field List panel. Make sure that the fields are selected from the nested data member that has been previously specified for the area to which a field is inserted. The resulting template will look as illustrated in the following image.
Preview the Result
- Select the Mail Merge mode. Click Multiple Sheets to place a report for each supplier into an individual worksheet.
Click the Mail Merge Preview button on the Mail Merge tab, in the Design group. A new window with a resulting document opened in a new SpreadsheetControl instance will be invoked.
You can save the resulting workbook to a file and edit it later.
- You can also save a data-bound template to a file in .xls or .xlsx format. All data bindings are saved and will be restored when the spreadsheet control loads a template again.