Sample Applications / Order Form / Report
Multi-Level Grouping

If multiple orders are included in the “order details” data set, then a simple filtered report will produce an incorrect output. This will happen if the Report action has a missing or incorrect filter in its Data property.

For example, the header of the report presented below displays the order information from the first “order details” data row. Order details from multiple orders are listed without any separation. The subtotal and total are calculated from the sum of Extended Price of all order details included in the data set. 

Simple report created from multiple orders.

In fact, order #10248 has only two “order detail” rows in the Northwind database.

SQL query to select order details from the specific order shows the inaccuracy of the report.

Let’s change the custom report template to group items by OrderID and CustomerID to ensure valid output even in situations when a report data set contains details of multiple orders.

Adding Grouping by Order

First, decrease the horizontal size of the  tablix by clicking and dragging on the dividers in the grey area above the column headers.

Decreasing the size of the tablix.

Right-click on the grey area to the left of the [ProductProductName] data cell. Select Add Group | Parent Group… option.

Adding a new parent row group to the tablix.

In the Tablix group window, select the following options:

Property Value
Group by: [OrderID]
Add group header true
Add group footer true

Press OK to save. A row group has been added to the tablix. Select the original header label cells by clicking on Product Name, then holding Shift key and clicking on Extended Price. Right-click, and press Cut.

Cutting the column headers from the first row.

Right-click on the cell below Product Name, and press Paste.

Pasting column headers into the second row inside the group.

The column headers will be inserted inside the group.

Right-click on the grey area to the left of the first row in the tablix. Press Delete Rows.

Deleting the first row.

Inserting Order Header

Let’s add another row above the headers to provide a placeholder for the order information. Right-click in the grey area to the left of the column headers, and press Insert Row | Inside Group – Above.

Inserting a row inside the group.

Select the entire row that was added. Right-click, and select Merge Cells option.

Merging the cells in the added row.

Delete the text in OrderID column. Use the divider in the grey area to “minimize” the OrderID column.

Minimizing the 'OrderID' column.

Drag a Rectangle element from the Toolbox, and drop it onto the header row of the group.

Adding a rectangle element to the row.

Select all elements in the header area of the page, right-click, and press Cut.

Cutting all elements from the header.

Right-click on the rectangle, and press Paste. The header elements for the order will be moved from the page header to the group.

Pasting the header elements inside the first row of the group.

Inserting Order Footer

Select the entire row below the data cells in the tablix. Right-click, and press Merge Cells.

Merging the cells in the footer row of the group

From the Toolbox window, drop a Rectangle element onto the row.

Dropping a rectangle into the footer row.

Select all elements in the report footer. Right-click and press Cut.

Cutting the report footer elements.

Right-click on the row underneath the data cells, and press Paste.

Pasting footer elements into the group footer.

The order footer elements will be pasted into the tablix row.

Adding Grouping By Customer

Right-click on the grey area to the left of the first row, and press Add Group | Parent Group… option.

Adding another parent row group.

Specify the following parameters:

Property Value
Group by: [OrderCustomerID]
Add group header true

Press OK to add the group.

Delete the text in the first column, and “minimize” the column.

Minimizing the Customer column.

Select all the cells in the first row. Right-click and press Merge Cells.

Merging the cells in the first row.

In the row, type the following:

[OrderCustomerCompanyName]

On the toolbar, set the font size to “24”, and set the text as bold, underlined, and center-aligned.

Changing the text properties of the first row.

Expand the first row using the handle of the grey area to the left of the row to make sure the text fits.

Fixing Subtotal and Total Formulas

The Subtotal and Total fields are currently displaying a sum for all order details in the returned dataset. Let’s fix the formulas to ensure the correct data is being displayed for each order.

Right-click on the Subtotal field, and press Expression.

Activating the Expression context menu option for 'Subtotal' field.

Replace the expression with the following:

=Sum(Fields!ExtendedPrice.Value)

Press OK to save.

Right-click on the Total field, and press Expression.

Activating the Expression context menu option for 'Total' field.

Use this expression:

=Sum(Fields!ExtendedPrice.Value) + First(Fields!OrderFreight.Value)

Press OK to save.

If a data set with “order details” from multiple orders is used with the new report template with “grouping”, then each order and its details will be rendered on a separate page.

Report created using grouped template shows accurate data.