Generating Subtotals and Totals in Reports


I know this question has been asked many times before, but I can’t seem to get this to work no matter what I read. I know you can use CreateTotals and the group totals to create subtotals based on groups like posting date, customer, etc… but I’ve never been able to get this to work. Each time I try to enter the GroupTotal fields, place a group total section on the report and insert the field that I want to be calculated on the actual report it just ends up showing the value of the last record. I’ve tried a variety of tutorials, and I still end up with the same results (I’m feeling a little silly right now). The CreateTotals function yeilds the same results.

I have a sales header table connected to the sales line table. What I want to do is create two subtotals: one that sums up the total revenue of each invoice, and another subtotal that sums up the total revenue for each customer. I have the report sorted by customer, and invoice number. Solutions that deal with C\AL coding are welcome. I actually find it easier and more flexible to use code than to use built in NAV reporting functions.

I am using NAV 5.0 SP1.

Help is much appreciated.

Hi Courtney,

First do sorting of the dataitem, using the key that contains the field which you use for grouping (subtotals).

Then, in GROUPTOTALFIELDS, enter those fields for grouping n totaling,

create section (group footer)

place those fields there…

run the report, you will get your subtotals…!!!



Okay - here is what I tried:

I have my sales line data item sorted by the key “customer.” Then I entered “customer” as the group total field. I went to the report sections and addeda group total footer for sales line. Then I placed “Amount” in this group total footer. I received the same results; the group total line just repeats the last sales line so I have a double entry for the “amount” field.

I am not sure what I am doing wrong.

hi courtney [:)]

Please check report trail Balance…see how the total field works in it…similarly you can use the groupbytotal fields too dear.

All the best [Y]

Hi kashyap im modifying the report InventOnhand… (it shows the invents available) and i have this:

public boolean fetch()


QueryBuildDatasource source;

boolean ret;

source = this.query().dataSourceTable(tableNum(InventDim)); source.addOrderByField(fieldNum(InventDim, InventLocationId), SortOrder::Ascending);

// Im sorting the report by location, now i want to add a subtotal, that sum by location … i have the sum all and sum all the report but i want to add subtotal sum by location.

Thanks for your help