Sorting and Totalling Groups

Sometimes, you might wish to subtotal based on groups of matters, and sort that subtotal descending. A good example might be a Top Clients report which totals up the bills raised or paid by client, and sorts descending so your top client is on page 1.

Grouping your report and adding a Total field

To add a total field to a report, you must first decide whether this will be a grand total for the whole report, or a sub total for a fee earner/work type/client etc.

To add a group total, we first need to tell the report writer which field it is going to group by. The below shows a simple bills paid report:

When run, as it has no totals, the report looks like this:

We can group the report by client, add subtotals, and order the report descending, so that the top billed client appears on the first page.

To group by Client No, click the Add a Group button at the bottom of the report writer, select Client No, and ensure that Header and Footer are both ticked. If you’re not using the header, you can resize it to save space:

Now, in the footer, copy in the field you want to total, and when selected, click the > button in the top right corner. Click the … button alongside Summary.

Now, select the type of summary you require, and select the Group radio button as this is a Group subtotal. In the example below, as we’re grouping by Client No, we’re totalling the Amount Paid for each client. Once we save this we will see that the field has changed from [Amount Paid] to SUM([Amount Paid]):

When run, the report now has subtotals of Amount Paid per client:

If you wish, you can also add a Grand Total for the report. Repeat the steps above, but putting the totalling field into the Report Footer band, and selecting the Report radio button in the Summary section:

Sorting the Totals

Now that you have totals on your report, you can order the group according to the group totals. In the example below, we will order the report so that the highest bill paying clients will appear on page 1 of the report.

Select the Group Header band for the group which contains the total you want to sort by, locate and click the little chevron > button near the left hand edge. Select the … button on Group Sorting Summary, and click Enable.

Now, in the Field box, select the field you want to use for the values to sort. In the Summary Function box, select the type of summary you want to use (this would be Count for a simple count of matters, or Sum to calculate the total of a monetary field for example). In the Sort Order box, choose Ascending or Descending. Click OK.

The report will now total up the Amount Paid for each client, showing us the highest paying client at the start of the report:

Displaying the Totals Only

You can also choose to hide the detail line so that only the group totals for a report are shown. To do this, select the Detail band on your report, and from the Report Explorer in the bottom right hand corner, set Visible to No:

Only your totals will be displayed now, in descending order:

More Report Writer Links