web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / To calculate sum of a ...
Small and medium business | Business Central, N...
Suggested Answer

To calculate sum of a column in Report

(0) ShareShare
ReportReport
Posted on by Dynamics 365 Busine... 700

Hi All,

In a report, I have to display the sum of Quantity field (Job Planning Line table) corresponding to a particular resource. I tried using CalcTotals but did not succeed.

Is this task possible to achieve ?

If yes, how ?

Please suggest.

Thanks in advance !

I have the same question (0)
  • Suggested answer
    Stefano Demiliani Profile Picture
    Stefano Demiliani 37,166 Most Valuable Professional on at

    With RDLC report there's the SUM operator to sum the value of a field in a dataset.

    Other way (without operators) it to use the dataset's OnAfterGetRecord trigger, calculate the sum here and assign its value to a global variable, then display the variable in the report layout.

  • Dynamics 365 Business Central Profile Picture
    Dynamics 365 Busine... 700 on at

    Hi Stefano,

    I used the same approach here. Sum is also getting calculated, but as I mentioned, I need to display the total sum on the basis of Resource No.

    For e.g. I have to display the sum of 'Quantity' field for a particular resource in a particular Job task.

  • Suggested answer
    Stefano Demiliani Profile Picture
    Stefano Demiliani 37,166 Most Valuable Professional on at

    Then calculate the value in OnAfterGetRecord trigger of your dataset by applying the criteria you need, assign this value to a global variable and then display this variable in the report layout.

  • Dynamics 365 Business Central Profile Picture
    Dynamics 365 Busine... 700 on at

    Can you please check if it is correct or not

    trigger OnAfterGetRecord()

                       var

                           myInt: Integer;

                       begin

                           //Message(' Resource %1 and title %2', Resource."No.", Resource."Job Title");

                           Rec_JobTask.Reset();

                           Rec_JobTask.SetRange("Job Task No.", "Job Planning Line"."Job Task No.");

                           if Rec_JobTask.Find('-') then

                               repeat

                                   Quantity_Total := Quantity_Total + "Job Planning Line".Quantity;

                               until

                               Rec_JobTask."Job Task No." = Rec_JobPlanningLine."Job Task No.";

  • Suggested answer
    Stefano Demiliani Profile Picture
    Stefano Demiliani 37,166 Most Valuable Professional on at

    The UNTIL clause is not correct. If you're looping through Job Tasks, you need to write:

    UNTIL Rec_JobTask.NEXT = 0;

  • Dynamics 365 Business Central Profile Picture
    Dynamics 365 Busine... 700 on at

    In that case, the sum is getting calculated on each row.

    The sum is needed on the basis of resource.

  • Suggested answer
    Stefano Demiliani Profile Picture
    Stefano Demiliani 37,166 Most Valuable Professional on at

    I don't know what's your business case. Here you're looping through Job Tasks (I see only this in your code) and so you're calculating the sum for every task.

    What I said before is that the sintax for repeat... until is:

    repeat

    until YourRecord.NEXT = 0;

    You've to handle your calculation here, I think it's quite simple.

  • Dynamics 365 Business Central Profile Picture
    Dynamics 365 Busine... 700 on at

    I have to display the sum of 'Quantity' spent by each resource on each Job Task. This is the requirement.

  • Suggested answer
    Stefano Demiliani Profile Picture
    Stefano Demiliani 37,166 Most Valuable Professional on at

    Ok so your calculation is totally wrong.

    You need to loop through Job Task table and for each task filter the Job Ledger Entry by Job No, Job Task No, Type = Resource and then sum the Quantity field.

  • Dynamics 365 Business Central Profile Picture
    Dynamics 365 Busine... 700 on at

    Means, I have to display the total no. of quantity spent by one resource on a particular job task

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Small and medium business | Business Central, NAV, RMS

#1
OussamaSabbouh Profile Picture

OussamaSabbouh 3,010

#2
Jainam M. Kothari Profile Picture

Jainam M. Kothari 1,270 Super User 2025 Season 2

#3
YUN ZHU Profile Picture

YUN ZHU 1,085 Super User 2025 Season 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans