When adding Nested Report Fields to a report, the data will likely come back with repeating values in the Detail section. As an example let us make a report with three Report Fields from the INVOICE file: Invoice Number, Invoice Balance, and Product Number. Notice that the Product Number is marked as Nested.
When running the report the Invoice Number and Invoice Balance are repeated for every Product Number. We can address this by making a custom layout for the report (uncheck Generate Layout in the Definition tab and then go to the Layout tab).
First, insert a Group Header/Footer section and set the DataField property of the Group Header to the distinct Report Field that you will group on (in this case set it to Invoice Number).
Then move the Invoice Number and Invoice Balance TextBoxes from the Detail to inside that Group Header; save and run the report. The Invoice Number and Invoice Balance should appear once for each invoice.
Now, what if we wanted to find the sum of all the invoice balances? Let us insert a Report Header/Footer and add a TextBox to the Report Footer.
In this new TextBox set the DataField property to Invoice Balance. In the Summary section for this TextBox there are several properties that we will use.
First set the SummaryType property to GrandTotal because we want to sum all the invoice balances.
Next, because we have added the Nested Report Field Product Number to the report, we must set a DistinctField that the report can sum on (in this case it is the Report Field we are grouping on: Invoice Number) and use the SummaryFunc DSum instead of Sum. If you now run the report you should have a correct grand total of all the invoice balances on the last page.
Another useful SummaryFunc for this example is DCount instead of Count. Setting the Report Footer’s TextBox DataField property to Invoice Number and using DCount for the SummaryFunc will give you the count of invoices.