This article will demonstrate how to create a grouping in a report design and show subtotals for each group. A video demonstration is presented at the end of the article.
We will be using the Alias 1 column for this example. Alias 1 is a common column used for tagging and sorting purposes. If you wish to use another column instead, such as Alias 2, Alias 3, Building, Floor, Department, Person, or Category, you can follow the same steps. Before starting, make sure your Worksheet file is tagged in the appropriate column.
- From the Reports menu, choose Apply Report Design, and apply the report to the worksheet you wish to modify to show subtotals. In this example, I have applied the standard report named Sell Price Report.
- After the report has generated, go back to the Reports menu and choose Edit Report Design... This will open the report you just applied in the Report Designer to make changes.
- From the Report menu in the editor, select Grouping... You may also click the grouping icon on the toolbar
- Click the Add button under Groups.
- Check the box for the column you wish to group and subtotal under the Group Details section. In this example, Alias 1.
- Check Show to insert a group header and group footer section in the report design.
- Click OK.
- From the Field Explorer tab, expand the + sign beside Group 1 Header.
- Drag and drop the Alias 1 field into the Group 1 Header section of the report design. Position it where you want it to appear.
- You may then double-click on the field to edit the display properties if desired.
- Resize the Group 1 Header section to remove any empty white space above and below the Alias 1 field. It should now look similar to this:
- From the Field Explorer tab, expand the + sign beside Group 1 Footer.
- Drag and drop the total field you wish to subtotal. In this example, we will drag the Ext Sell field to the right-hand side of the Group 1 Footer section of the report design.
- Double-click on the field to edit the display properties if desired.
- Now drag in a reusable text field from the field explorer tab and place it to the left of the Ext Sell field previously added. Double-click the text field to open the Field Properties dialog. Enter the text you wish it to display. In this example, Subtotal:
- Resize the Group 1 Footer section from the bottom to remove any empty white space. The group 1 footer should now look similar to this:
- We can now save our changes to the report. From the File menu, choose Save As. Name your file and save it to your User Reports folder (Documents\CAP\UserReports)
- Exit the Report Designer to return to your worksheet.
- You can now apply this user report to any Worksheet file. From the Reports menu in Worksheet, select Apply Report Design and select the report you just saved.
- Here is our completed example. Notice how the report groups the line items we had tagged by the Alias 1 column. It displays the alias tag used in the group header section, and then subtotals the Ext Sell value of those line items in the group footer section.