x
Wyn 4.0 launches with more extensibility, enhanced embedded business intelligence, and an improved end-user experience. Learn More Wyn 4.0 launches, enhanced embedded business intelligence, improved end-user experience. Learn More Wyn 4.0 launches

Master-Detail Report using Groups

A master detail report can be created by grouping the details in the Details row of Table data region. For each associated row from the master record, the grouped details are repeated. Consider that you want to create a report that shows titles and corresponding values of quantity, price, and total price. You also want these details to be categorized according to the customer name. In this case, the details go to the details row of the table, and these details are repeated for each customer.

What You Will Learn

This tutorial guides you through the steps to create a master detail report by grouping a part of data in a Table data region.

Note: This tutorial uses sample CustomerOrders data set.

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

Report without Grouping

Create Report Layout

  1. Drag and drop Table data region onto the design area of the report. By default, you have a table with three rows and three columns.

  2. To add one more column to the table, click inside the table to display the column handle, and then click the plus sign.

  3. Now we have a table with three rows and four columns.

Bind Data to Table Data Region

Let us now populate the Table data region with data. The detail part of the report will consist of titles and corresponding values of quantity, price, and total price.

To fill the data in details row of the table,

  1. Click the dropdown next to the CustomerOrders data set to view fields.
  2. Drag and drop the Title field on the first column, Quantity field on the second column, and Price on the third column.
  3. To add total field in the fourth column of the details row, select the last text box in the details row.
  4. Go to the Properties pane, and select the radio button next to Value property.
  5. Select Expression... from the drop down and enter the following expression in the Expression Editor: =Fields!Quantity.Value * Fields!Price.Value.

The table with all labels filled-in should look like the following image.
         

Preview Report

  1. Click Preview to view your report in the final output. This is a simple tabular report with details and label for each column. The labels for the details are displayed on the first page of the report.
  2. Exit the preview mode by clicking Back on the top left side of the designer.

Report with Grouping (Master - Detail Report)

Let us add more meaning to the report by adding groups. We will group the details according to the customer names, and will have the column labels repeated for each group. So, the master part of the table will consist of last name of the customer and the static text for the labels in the detail part. To do so, let us add two row groups, as described in the following sections.

First of all, delete the Header and Footer rows (these are not required in our report),

  1. Click inside the Table data region to make the row handle appear.
  2. Right-click the row handle and select Remove Header to remove the header row from the table.

  3. Again, right-click the row handle and select Remove Footer to remove the footer row from the table.

Add Groups and Bind data to the Groups in Table Data Region

To add a group that contains the customer name,

  1. Right-click any text box (cell) in the details row of the table to view options in the context menu.
  2. Go to Group and select Insert Group.

This adds a row group above and below the details row.
         

  1. In the first text box of the added row group, drag and drop the LastName field as shown.

  2. Right-click a text box in the added row group (above the details row) to view options in the context menu.

  3. Go to Row and select Below. Now your table looks similar to the following.

Note that the table groups are also displayed in the adorner on the right side of the Table data region.

  1. In the row group above the details row, enter the labels for the details as shown.

To group the details for each customer,

  1. From the Explorer , select the Table data region.
  2. From the adorner on the right side of the Table data region, select Table1_TableGroup1 row group, and go to the Properties pane.
  3. In the Group Expressions property, click Show Items and then select Add Item.
  4. Click the radio button to display the fields and select the CustomerID field.

To obtain the sum of prices for each group,

  1. Go to the text box below the text box in the row group with expression '=Fields!Quantity.Value * Fields!Price.Value'.

  2. Enter the expression =Sum(Fields!Price.Value*Fields!Quantity.Value in the same way as we added expression in the Bind Data to Table Data Region section. The table with bound fields looks as shown.

Add Report Title

  1. Drag and drop a TextBox control above the table. 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 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 field with the last name,

  1. Select the LastName field.
  2. In the Properties pane, set Font Size property to 12pt.
  3. Set BACKGROUND - Color to Plum from the colors available in Web colors.

To span the last name field over the table,

  1. Select the cells in the top row group using mouse drag or simply using Ctrl+Click combination.
  2. Right-click the selected cells to view the options in the context menu, go to Cells and select Merge Cells.

To align the labels,

  1. Select the label Title and from the Properties pane, set the TEXT - Text Align property to Left.
  2. Set the TEXT - Text Align property of Quantity, Price, and Total Price text boxes to Center.

To make the labels bold,

  1. Go to the Properties pane.
  2. Set the Font Weight property of Title, Quantity, Price, and Total Price text boxes to Bold.

To add background colors for the detail part,

  1. Select the text boxes of the details row, and the Sum field in the row group below detail row.
  2. From the Properties pane, set the BACKGROUND - Color to Moccasin from the colors available on Web color pallet.

Preview and Save Report

  1. Click Preview to view your report in the final output.
  2. Exit the preview mode by clicking Back on the top left side of the designer.
  3. Click Save to open the Save dialog box. Enter the report name and click Save Report.