Master-Detail Report using Subreport

When you want to separate the details from the master report, you can create two separate reports and embed the detail report in the master report using Subreport control. A parameter is passed from the master report to detail report to filter the details.

What You Will Learn

This tutorial guides you through the steps to create a Master-detail report using subreport in Wyn Enterprise Report Designer.

Note: This tutorial uses sample SalesByRegion data set.

After you complete this tutorial, you will have a report that looks similar to the following.

(Parameter1 = Canada West)

Create Master Report Layout

To create a master report, we will use List data region. The List data region will contain TextBox controls to display the data, and a Subreport control to display detail report. The List data region repeats any report control it contains for every record in the dataset.

  1. Drag and drop List data region onto the design area of the report.
  2. Drag and drop two TextBox controls, TextBox1 onto the list and TextBox2 above the list.
  3. Drag and drop the Subreport control onto the list. Arrange the controls such that the report now looks like the following.

Verify that the hierarchy of the controls added in the report is as follows. This can be viewed in Explorer by clicking .
         

Add Parameter to the Master Report

Let us add the parameter that will be passed to the detail report to display the details accordingly.

  1. From the top right corner of Report Designer, click the Data icon .
  2. Click Add next to Parameters. A parameter named Parameter1 is added.
  3. Click Parameter1 to edit parameter properties. You can change the Prompt to a more understandable text, such as 'Enter region name'. The following image shows how these properties are set:

  4. Go to From Query tab under Available Values and set the following properties.

| Property | Value |
| -------- | ----- |
| Data Set Name | SalesByRegion |
| Value Field | Region |
| Label Field | Region |      

The following image shows how these properties are set:
         

Note: The name of the parameter in master report and detail report (subreport) should be same.

Bind Data to the Report Controls

  1. Select TextBox1 and enter the expression ="Store ID: "& Fields!StoreID.Value.
  2. Select TextBox2 and enter the expression ="Details for region: "&Parameters!Parameter1.Value.
  3. Set the Data Set Name property of the List data region to the SalesByRegion data set.

Configure Subreport

Let us embed the parameterized report created in Create Parameterized Report as a detail report and pass the parameter as filter to the detail report data from the master report.

To embed the detail report to master report,

  1. Select Subreport control.
  2. From the Properties pane, set the Report Name property to the detail report's name, that is, 'Detail Report'.

Note: As you select the Report Name property of Subreport control, you see two Report parameters. It is because the report passed in the Subreport control has two parameters. We will set these parameters later.

To use the parameter (Parameter1) passed from the master report to the detail report as a filter, set the parameter properties of Subreport control as follows.

  1. For Parameter 1, enter the following expression as the value of the parameter: =Parameters!Parameter1.Value.
  2. For Parameter 2, enter the following expression as the value of the parameter: =Fields!StoreID.Value.

Add Title

  1. Drag and drop a TextBox control above the List data region. If required, relocate the table to create space for the text box.
  2. Click inside the text box and enter the text 'Customer Orders (Master Detail Report)'.

Customize the Appearance of Master Report

Note: You may need to resize and reposition the controls on the report to accommodate data, and for a cleaner look.

To customize the text boxes with information on store id and details for region,

  1. Select TextBox1 and TextBox2, one at a time.
  2. From the Properties pane, set TEXT - Text Align property to Center.
  3. Set BACKGROUND - Color to Plum from the colors available on Web color pallet.

To customize the title,

  1. Select the text box with title.
  2. From the Properties pane, set TEXT - Text Align property to Center.
  3. Set BACKGROUND - Color property to Plum from the colors available on Web color pallet.
  4. Set TEXT - Font Weight property to Bold.

Preview and Save Report

  1. Click Preview to view your report in the final output. You will be prompted to select the region name.
  2. Select a region and again click Preview .
    See how the parameter passed from master report obtains the details using the subreport control.
  3. Exit the preview mode by clicking Back on the left side of the designer.
  4. Click Save to open the Save dialog box. Enter the report name as 'Master Report' and click Save Report.