Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics NAV forum
Unanswered

Calculation of a field in report

Posted on by 700

Hi All,

In a report, I have two filters - Starting Date and Ending Date which correspond to the Return Date Time field present on table 'Job'.

Report is generated per employee wise.

For each Employee, 'Total Available Hours' has to be calculated and displayed on the report.

These available hours are getting calculated from the Time Sheet of that employee. (Time Sheet is created for 7 days, starting from Monday)

A filter has been put to pull the time sheets of that Employee i.e. the Time Sheet Starting Date should fall between the applied filters Starting Date and Ending Date on the report.

Now the issue is that whenever I put date range which includes Time Sheet Starting Date then correct data is displayed but when the date range doesn't include Time Sheet Starting Date, then incorrect data is displayed.

pastedimage1581579542448v1.png

Available Hours are calculated from Total(HH:MM) field displayed in above screenshot.

For Ex. Time Sheet for an Emp is created from 10-02-2019 to 16-02-2019 and for 10 and 11, Total(HH:MM) is entered as 7 hrs and 8 hrs. Now when I put a filter including 10-02-2019 data shown is correct but when I want to see the Available Hours of 11-02-2019 only i.e. 8 hrs, report displays 0.

Code is also attached.

  trigger OnAfterGetRecord()
            var
                int: Integer;
                days_time_text: array[32] of Text[10];
                days_time_temp: array[32] of Text[10];
                days_time_num: array[32] of Decimal;
                jobtsakInner: Record "Job Task";
                jobPLanningLineInner: Record "Job Planning Line";
                productiveHoursInner: Decimal;

            begin

                Clear(Available_Hours);
                TimesheetHeader.Reset();
                TimesheetHeader.SetRange("Resource No.", Employee."Resource No.");
                IF TimesheetHeader.FindSet() then begin
                    repeat
                        TimesheetLine.Reset();
                        TimesheetLine.SetRange("Time Sheet No.", TimesheetHeader."No.");
                        TimesheetLine.SetRange("Time Sheet Starting Date", DT2DATE(StartingDate), DT2DATE(EndingDate));
                        if TimesheetLine.FindSet() then begin

                            TimeSheetOfficeSchedule.Reset();
                            TimeSheetOfficeSchedule.SetRange("Time sheet No.", TimesheetLine."Time Sheet No.");
                            TimeSheetOfficeSchedule.SetRange("Office Schedule Type", TimeSheetOfficeSchedule."Office Schedule Type"::"Total (HH:MM)");
                            if TimeSheetOfficeSchedule.FindSet() then begin
                                //Available_Hours_text_Mon := TimeSheetOfficeSchedule.Mon;
                                days_time_text[1] := TimeSheetOfficeSchedule.Mon;
                                days_time_text[2] := TimeSheetOfficeSchedule.Tue;
                                days_time_text[3] := TimeSheetOfficeSchedule.Wed;
                                days_time_text[4] := TimeSheetOfficeSchedule.Thus;
                                days_time_text[5] := TimeSheetOfficeSchedule.Fri;
                                days_time_text[6] := TimeSheetOfficeSchedule.Sat;
                                days_time_text[7] := TimeSheetOfficeSchedule.Sun;

                                for int := 1 to 7 do begin
                                    days_time_temp[int] := ConvertStr(days_time_text[int], ':', '.');
                                    Evaluate(days_time_num[int], days_time_temp[int]);
                                end;

                                for int := 1 to 7 do begin
                                    Available_Hours  = days_time_num[int];
                                end;
                            end;
                        end;
                    until TimesheetHeader.Next() = 0;
                end;

How can I overcome this issue, please suggest!

  • RE: Calculation of a field in report

    Could anyone please respond !

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

Anton Venter – Community Spotlight

Kudos to our October Community Star of the month!

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 228,552 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans