In this walkthrough, you will learn how design a report with grouping and totaling. How you group and total data is a crucial element of creating useful reports. By grouping and totaling data, your reports can contain information that is not otherwise readily available. You can use grouping and totaling to enhance reports in several ways.

Prerequisites

To complete this walkthrough, you will need:

Microsoft Dynamics NAV installed with a developer license.
The CRONUS International Ltd. demo data company installed.
To complete Walkthrough: Designing a More Advanced Report.

Defining the Data Model

First, you will define the data model for the report. For each record in the Customer data item, the records in the Sales Line data item that are related to this customer are selected. Records from the Sales Line data item are grouped according to the item number and totals are maintained for the Quantity and Amount fields of the Sales Line data item.

To define the data model

In the Classic client, open Object Designer.
Under Reports, locate the report that you created in the Walkthrough: Designing a More Advanced Report and click the Design button.
Open the Properties window for the Sales Line data item.
In the Value field of the GroupTotalFields property, enter the name of the field that you want to use to group the records. You can use the AssistButton to select the field. In this example, use the No. field.

In the Value field of the DataItemTableView property, use the AssistButton to select a key. You must select a key that contains the field that you want to group by.

Note
If the key you select is a composite key, the grouping can fail if there are other fields in the key before the grouping field, and the contents of one of these field changes. You may have to create a distinct key for reports that access data in ways other than those used by your application.

In the Value field of the TotalFields property, enter the names of the fields for which totals should be calculated. Use the AssistButton to select the fields. In this example, select the Quantity and Amount fields.

Designing the Header and Body Sections

Now that the data model is in place, there is a hierarchy of data items, where Customer is the data item at the highest level and Sales Line is an indented data item. The Sales Line records are grouped on the No. field, and totals are calculated for the Amount and Quantity fields. Until now, only the Header and Body sections have been used. To print totals, you will need to use some new sections, for more information, see Types of Report Sections.

To design the header and body sections

In Report Designer, open the report that you are working on.
Click View, and then click Sections. When you open Section Designer, a body section for each data item has been inserted.
Select the Customer data item, click Edit, and then click New to open the Insert New Section window.
Add a Header section. This section will be used to print headings for the columns in the report.
Select the Sales Line data item
Click Edit, click New, and then add a GroupFooter section. This section will be used to print the summary information about each item.
Click Edit, click New, and then add a Footer section for the Sales Line data item. This section will be used to print the summary information about each customer.
Select the section for the Body section of the Sales Line data item. Click Edit, and then click Delete.

Note

You need to delete this section because nothing will be printed in the Body section of the Sales Line data item.
You are prompted to confirm that you want to delete the section.
Select the labels for the Sales Line that you added to the Customer Header earlier and delete them.
Adjust the vertical size of the labels.

Note
You need to delete this section because nothing will be printed in the Body section of the Sales Line data item.

Designing the Footer Sections

Now you need to design the Footer sections and ensure that they display the information that you want.

To design the footer sections

Select the GroupFooter section of the Sales Line data item by clicking the section bar. Click View, and then click Field Menu to open the Field Menu window.
In the Field Menu window, select the No., Description, Quantity, and Amount fields.
Click twice in the GroupFooter section of the Sales Line data item to insert these fields. Delete the labels, and resize the section vertically.
Select the Footer section of the Sales Line data item.
In the Field Menu window, select the Amount field and insert it in the footer section directly below the Amount field in the GroupFooter section. Select the label and delete it. Keep the default size for the section so there is empty space before each new customer.
Select the No. label in the Customer Header. Open the Properties window and set the value of the HorzAlign property to Right.
Save, close, and run the report.