Small and medium business | Business Central, N...

Get the Total Amount by calculating the Amount column from Cust. Ledger Entry to create a report.

Like (0) Share

Report

Posted on 25 Aug 2020 05:48:32 by Lino Asmolo Jr

Hi All,

Im new to Business Central al programming and i was tasked to make a report. In the report i was able to list down the invoices of a customer. So my problem is how sum the Amount column to get the total amount. Also, i am able to sum all the amount, but then it ended up having 1 record of invoice. So, im doing it wrong, can you please help to me setup this correctly. Thanks. Here is how i do it:

This results:

To sum all the the Amount, i add this,

And i get the result:

I guess, i was able to sum the Amount, but i ended having one record.

So, how i set this up so I can get all records as well as the Total Amount? or if this is another way to achieve this, i need guide

Thank you.

All responses (13)

Answers (0)

Suggested answer

Lino Asmolo Jr 80 on 10 Sep 2020 at 08:19:32

Like (0)

Report

RE: Get the Total Amount by calculating the Amount column from Cust. Ledger Entry to create a report.

Hi Mohsin Abbas,

I managed to solve my problem. I did not use "repeat" and "Until", only the trigger OnAfterGetRecord().

I just added another dataitem to hold the TotalAmount like these:

trigger OnAfterGetRecord()

begin

TotalAmount += Amount;

end;

dataitem(Total; Integer)

{

MaxIteration = 1;

column(TotalAmount; TotalAmount)

{

}

}
Suggested answer

Mohsin Abbas 525 on 30 Aug 2020 at 07:51:17

Like (0)

Report

RE: Get the Total Amount by calculating the Amount column from Cust. Ledger Entry to create a report.

Your dataset is okay but needs a little tweak for working with Word Layout. The issue is, when you put the TotalAmount field in layout, it takes the first value only. So you need to manipulate the dataset in such a way that the all the rows contain the overall total rather than the running total. You can include an Integer dataitem as the 1st datitem and Calculate the total by code. In this way, all of your rows will have the same value for TotalAmount i.e. the overall total.
See the below screenshot and let me know if this helps.

.
Lino Asmolo Jr 80 on 30 Aug 2020 at 05:44:46

Like (0)

Report

RE: Get the Total Amount by calculating the Amount column from Cust. Ledger Entry to create a report.

Actually, i wanted to use RDLC layout since it is much easier there i guess, but then im using Word layout.
Suggested answer

Mohsin Abbas 525 on 30 Aug 2020 at 05:29:20

Like (0)

Report

RE: Get the Total Amount by calculating the Amount column from Cust. Ledger Entry to create a report.

There are multiple ways to do that:

1. CurrReport.CreateTotals in OnPreDataItem Trigger(docs.microsoft.com/.../createtotals-function--report-)

2. In RDLC Layout, simply add a field for sum(Amount) it will give you the total.
Lino Asmolo Jr 80 on 30 Aug 2020 at 05:05:49

Like (0)

Report

RE: Get the Total Amount by calculating the Amount column from Cust. Ledger Entry to create a report.

Hi Mohsin Abbas,

This code triggered in report object.

This is my code.

I got this result, i just snipped the last page of the report. With this it just get the first Amount record.

I just wanted to sum all the Amount column of the report. How do i acheive it? How do i get the total Amount?
Mohsin Abbas 525 on 27 Aug 2020 at 05:53:43

Like (0)

Report

RE: Get the Total Amount by calculating the Amount column from Cust. Ledger Entry to create a report.

In which trigger are you writing this code?
Lino Asmolo Jr 80 on 26 Aug 2020 at 08:50:13

Like (0)

Report

RE: Get the Total Amount by calculating the Amount column from Cust. Ledger Entry to create a report.

Hi Mohsin,

I tried it, and it just get the first row Amount of the MainItem.
Mohsin Abbas 525 on 26 Aug 2020 at 08:01:08

Like (0)

Report

RE: Get the Total Amount by calculating the Amount column from Cust. Ledger Entry to create a report.

Hi Lino

Please note that Amount is a FlowField so you'll need to do CalcFields(Amount) to get the value of amount. Otherwise it'll always come as 0.
Suggested answer

Mohsin Abbas 525 on 26 Aug 2020 at 08:00:57

Like (0)

Report

RE: Get the Total Amount by calculating the Amount column from Cust. Ledger Entry to create a report.

Hi Lino

Please note that Amount is a flowField flowField so you'll need to do CalcFields(Amount) to get the value of amount. Otherwise it'll always come as 0.
Lino Asmolo Jr 80 on 26 Aug 2020 at 01:21:18

Like (0)

Report

RE: Get the Total Amount by calculating the Amount column from Cust. Ledger Entry to create a report.

Hi @Duilio Tacconi,

This one doesn't solve my problem, i tried it and the result is, it listed the rows but then the Total Amount field is 0 again.