Skip to main content

Notifications

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

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

Posted on by 80

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:

4807.PNG

This results:
2526.PNG

To sum all the the Amount, i add this,

3884.PNG

And i get the result:

5241.PNG

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.

  • Suggested answer
    Lino Asmolo Jr Profile Picture
    Lino Asmolo Jr 80 on at
    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 Profile Picture
    Mohsin Abbas 525 on at
    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.

    .Screenshot_5F00_12.png

  • Lino Asmolo Jr Profile Picture
    Lino Asmolo Jr 80 on at
    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 Profile Picture
    Mohsin Abbas 525 on at
    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.
    6036.Untitled.png

  • Lino Asmolo Jr Profile Picture
    Lino Asmolo Jr 80 on at
    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.

    23.PNG

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

    6558.Capture1.PNG

    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 Profile Picture
    Mohsin Abbas 525 on at
    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 Profile Picture
    Lino Asmolo Jr 80 on at
    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 Profile Picture
    Mohsin Abbas 525 on at
    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 Profile Picture
    Mohsin Abbas 525 on at
    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 Profile Picture
    Lino Asmolo Jr 80 on at
    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

November Spotlight Star - Khushbu Rajvi

Congratulations to a top community star!

Forum Structure Changes Complete!

🔔 Be sure to subscribe to the new forums you are interested in to stay up to date! 🔔

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,900 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,297 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans