Lesson 6: Adding Grouping and Totals (Reporting Services)
Add grouping and totals to your report to organize and summarize your data.
To group data in a report
Click the Design tab.
From the Report Data pane, drag the Date field to the Row Groups pane. Place it above the row called Details.
Note that the row handle now has a bracket in it, to show a group. The table now also has two Date columns -- one on either side of a vertical dotted line.
.gif "Date field added to Row Groups pane")
From the Report Data pane, drag the Order field to the Row Groups pane. Place it below Date and above Details.
Note that the row handle now has two brackets in it, to show two groups. The table now has two Order columns, too.
Delete the original Date and Order columns to the right of the double line. This removes this individual record values so that only the group value is displayed. Select the column handles for the two columns, right-click and click Delete Columns.
.gif "Select columns to delete")
You can format the column headers and date again.
Switch to the Preview tab to preview the report. It should look similar to the following illustration:
.gif "Table grouped by date and then order")
To add totals to a report
Switch to Design view.
Right-click the data region cell that contains the field [LineTotal], and click Add Total.
This adds a row with a sum of the dollar amount for each order.
Right-click the cell that contains the field [Qty], and click Add Total.
This adds a sum of the quantity for each order to the totals row.
In the empty cell to the left of Sum[Qty], type the label "Order Total".
You can add a background color to the totals row. Select the two sum cells and the label cell.
On the Format menu, click Background Color and click Light Gray.
.gif "Design view: Basic table with order total")
To add a daily total to a report
Right-click the Order cell, point to Add Total, and click After.
This adds a new row containing sums of the quantity and dollar amount for each day, and the label "Total" in the Order column.
Type the word Daily before the word Total in the same cell, so it reads Daily Total.
Select the Daily Total cell, the two Sum cells and the empty cell between them.
On the Format menu, click Background Color and click Orange.
.gif "Design view: Daily total row in basic table")
To add a grand total to a report
Right-click the Date cell, point to Add Total, and click After.
This adds a new row containing sums of the quantity and dollar amount for the entire report, and the Total label in the Date column.
Type the word Grand before the word Total in the same cell, so it reads Grand Total.
Select the Grand Total cell, the two Sum cells and the empty cells between them.
On the Format menu, click Background Color and click Light Blue.
.gif "Design view: Grand total in basic table")
Click Preview.
The last page should look something like this, although your Order Total, Daily Total, and Grand Total may be on a second page:
.gif "Preview: Basic table with grand total")
Next Steps
You have successfully completed the Creating a Basic Table Report tutorial. You can use this basic table report as a basis for these other tutorials. See: