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. 

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

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.

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

In the Tablix group window, select the following options:

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.

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

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.

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.

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

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

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

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

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

Inserting Order Footer

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

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

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

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

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.

Specify the following parameters:

Press OK to add the group.

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

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

In the row, type the following:

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

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.

Replace the expression with the following:

=Sum(Fields!ExtendedPrice.Value)

Press OK to save.

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

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.