Hi All,
I have a report named 'Employee Productivity' in which it is supposed to display the data on 'Per Employee/Resource' basis.
The data-item structure used is as follows :-
dataitem(Employee; Employee)
{
dataitem(Job; Job)
{
dataitem(Job Planning Line; Job Planning Line)
}
}
Report should contain a filter of 'Return Date Time' from Job table, in range format as Starting Date and Ending Date.
Data of Employees linked to Jobs falling under the above date range are needed to be displayed on the report.
I have linked the Job data-item to the Employee data-item using Person Responsible field present on Job DI as there was no other way available.
But, it is showing incorrect result in the case when the Person Responsible field value does not match with the value coming from Employee DI.
Is there any other way available to achieve the functionality ?
Attaching the source code of the report.
report 50205 EmpProductivity
{
Caption = 'Employee Productivity';
DefaultLayout = RDLC;
RDLCLayout = 'Emp-Productivity.rdl';
UsageCategory = ReportsAndAnalysis;
ApplicationArea = All;
dataset
{
dataitem(Employee; Employee)
{
PrintOnlyIfDetail = true;
RequestFilterFields = "No.";
column(COMPANYNAME; COMPANYPROPERTY.DisplayName)
{
}
column(No_Employee; Employee."No.")
{
}
column(Office_Location; "Office Location")
{
}
column(Resource_No_; "Resource No.")
{
}
column(Department; Department)
{
}
column(First_Name; "First Name")
{
}
column(Last_Name; "Last Name")
{
}
column(Job_Title; "Job Title")
{
}
dataitem(Job_DI; Job)
{
DataItemLink = "Person Responsible" = field("No.");
dataitem("Job Planning Line"; "Job Planning Line")
{
DataItemLink = "No." = FIELD("Person Responsible");
//DataItemLink = "Job No." = field("No.");
//DataItemTableView = WHERE(Type = FILTER(Resource));
column(Productive_Hours; Productive_Hours)
{
}
column(Type_JobPlanningLine; "Job Planning Line".Type)
{
}
column(JobNo_JobPlanningLine; "Job Planning Line"."Job No.")
{
}
column(PlanningDate_JobPlanningLine; "Job Planning Line"."Planning Date")
{
}
//dataitem("Job Task"; "Job Task")
//{
// DataItemLink = "Job No." = field("Job No."), "Job Task No." = field("Job Task No.");
column(Audio_Length; Audio_Length)
{
}
column(No_Of_pages; No_Of_pages)
{
}
column(CustCode; CustCode)
{
}
column(Available_Hours; Available_Hours)
{
}
column(ClientAdmin; ClientAdmin)
{
}
column(ClientAdmin_Percent; ClientAdmin_Percent)
{
}
column(ChargeableToClient; ChargeableToClient)
{
}
column(ExigentAdminHours; ExigentAdminHours)
{
}
column(ExigentAdminPercent; ExigentAdminPercent)
{
}
column(UnAccounted_Hours; UnAccounted_Hours)
{
}
column(UnAccounted_Percent; UnAccounted_Percent)
{
}
column(Total_Admin_Hours; Total_Admin_Hours)
{
}
column(Total_Admin_Percent; Total_Admin_Percent)
{
}
column(Hours_Accounted_Percent; Hours_Accounted_Percent)
{
}
column(ChargeableToClientPErcent; ChargeableToClientPErcent)
{
}
column(Productive_Percent; Productive_Percent)
{
}
column(Processing_Ratio; Processing_Ratio) //Processing Ratio
{
}
trigger OnAfterGetRecord()
var
AudioLength_Temp: Integer;
NoOfPages_Temp: Integer;
jobtsakInner: Record "Job Task";
jobPLanningLineInner: Record "Job Planning Line";
productiveHoursInner: Decimal;
begin
Clear(AudioLength_Temp);
Clear(NoOfPages_Temp);
Clear(Audio_Length);
Clear(No_Of_pages);
JobPlanningLine.Reset();
JobPlanningLine.SetRange("No.", Employee."Resource No.");
if JobPlanningLine.FindSet()
then begin
repeat
JobTask.Reset();
JobTask.SetRange("Job Task No.", JobPlanningLine."Job Task No.");
JobTask.SetRange("Job No.", JobPlanningLine."Job No.");
if JobTask.FindFirst() then begin
repeat
AudioLength_Temp = JobTask."Audio Length";
NoOfPages_Temp = JobTask."No. of Pages";
until JobTask.Next() = 0;
end;
until JobPlanningLine.Next() = 0;
Audio_Length := AudioLength_Temp;
No_Of_pages := NoOfPages_Temp;
if (Audio_Length No_Of_pages) <> 0 then begin
Processing_Ratio := (Productive_Hours * 60) / (Audio_Length No_Of_pages);
end;
end;
end;
}
trigger OnPreDataItem()
var
myInt: Integer;
begin
SetFilter(ReturnDateTime, '%1..%2', StartingDate, EndingDate);
end;
}
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", StartingDate, 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;
Clear(Productive_Hours);
Clear(Productive_Percent);
JobPlanningLine.Reset();
JobPlanningLine.SetRange("No.", Employee."Resource No.");
if JobPlanningLine.FindSet() then begin
repeat
if ConsiderPlanningLineForCalculation_Produtive(JobPlanningLine) then begin
Productive_Hours = JobPlanningLine."Quantity from time tracker";
end;
until JobPlanningLine.Next() = 0;
if Available_Hours <> 0 then begin
Productive_Percent := Productive_Hours / Available_Hours;
end;
end;
Clear(ClientAdmin);
Clear(ClientAdmin_Percent);
JobPlanningLine.Reset();
JobPlanningLine.SetRange("No.", Employee."Resource No.");
if JobPlanningLine.FindSet() then begin
repeat
if ConsiderPlanningLineForCalculation_ClientAdmin(JobPlanningLine) then begin
ClientAdmin = JobPlanningLine."Quantity from time tracker";
end;
until JobPlanningLine.Next() = 0;
if Available_Hours <> 0 then begin
ClientAdmin_Percent := ClientAdmin / Available_Hours;
end;
end;
Clear(ExigentAdminHours);
Clear(ExigentAdminPercent);
JobPlanningLine.Reset();
JobPlanningLine.SetRange("No.", Employee."Resource No.");
if JobPlanningLine.FindSet() then begin
repeat
if ConsiderPlanningLineForCalculation_ExigentAdmin(JobPlanningLine) then begin
ExigentAdminHours = JobPlanningLine."Quantity from time tracker";
end;
until JobPlanningLine.Next() = 0;
if Available_Hours <> 0 then begin
ExigentAdminPercent := ExigentAdminHours / Available_Hours;
end;
end;
ChargeableToClient := Productive_Hours ClientAdmin;
if Available_Hours <> 0 then begin
ChargeableToClientPErcent := ChargeableToClient / Available_Hours;
end;
UnAccounted_Hours := ((Available_Hours - Productive_Hours) - ClientAdmin) - ExigentAdminHours;
if Available_Hours <> 0 then begin
UnAccounted_Percent := UnAccounted_Hours / Available_Hours;
end;
Total_Admin_Hours := ClientAdmin ExigentAdminHours;
if Available_Hours <> 0 then begin
Total_Admin_Percent := Total_Admin_Hours / Available_Hours;
end;
if Available_Hours <> 0 then begin
Hours_Accounted_Percent := (ChargeableToClient ExigentAdminHours) / Available_Hours;
end;
end;
}
}
//}
requestpage
{
// SaveValues = true;
//ContextSensitiveHelpPage = 'my-feature';
layout
{
area(content)
{
//group(control)
//{
field("Starting Date"; StartingDate)
{
Caption = 'Starting Date';
Visible = true;
ApplicationArea = All;
ShowMandatory = true;
}
field("Ending Date"; EndingDate)
{
Caption = 'Ending Date';
Visible = true;
ApplicationArea = all;
ShowMandatory = true;
}
}
}
actions
{
}
}
labels
{
}
var
StartingDate: DateTime;
EndingDate: DateTime;
Dur: Time;
Job: Record "Job";
//Department: Text[100];
CustCode: Code[20];
Productive_Hours: Decimal;
Processing_Ratio: Decimal;
Available_Hours: Decimal;
Productive_Percent: Decimal;
JobPlanningLine: Record "Job Planning Line";
JobTask: Record "Job Task";
TimesheetLine: Record "Time Sheet Line";
TimesheetHeader: Record "Time Sheet Header";
TimesheetDetail: Record "Time Sheet Detail";
OfficeScheduleType: Record "Standard Office Schedule";
ClientAdmin: Decimal;
ClientAdmin_Percent: Decimal;
ChargeableToClient: Decimal;
ChargeableToClientPErcent: Decimal;
ExigentAdminHours: Decimal;
ExigentAdminPercent: Decimal;
UnAccounted_Hours: Decimal;
UnAccounted_Percent: Decimal;
Total_Admin_Hours: Decimal;
Total_Admin_Percent: Decimal;
Hours_Accounted_Percent: Decimal;
Audio_Length: Integer;
Audio_Length_temp: Integer;
No_Of_pages: Integer;
No_Of_pages_Temp: Integer;
TimeSheetOfficeSchedule: Record TimesheetOfficeSchedule;
trigger OnPreReport()
begin
end;
//procedure to calculate Productive Hours
local procedure ConsiderPlanningLineForCalculation_Produtive(JobPlanningLine_P: Record "Job Planning Line"): Boolean
var
Rec_Job: Record Job;
Rec_JobTask: Record "Job Task";
begin
Rec_Job.SetRange("No.", JobPlanningLine_P."Job No.");
Rec_Job.SetFilter(ReturnDateTime, '%1..%2', StartingDate, EndingDate);
if Rec_Job.FindFirst() then begin
Rec_JobTask.SetRange("Job No.", Rec_Job."No.");
Rec_JobTask.SetRange("Job Task No.", JobPlanningLine_P."Job Task No.");
Rec_JobTask.SetRange("Task Classification", Rec_JobTask."Task Classification"::Productive);
if Rec_JobTask.FindFirst() then begin
exit(true);
end;
end;
end;
//procedure to calculate Client Admin Hours
local procedure ConsiderPlanningLineForCalculation_ClientAdmin(JobPlanningLine_P: Record "Job Planning Line"): Boolean
var
Rec_Job: Record Job;
Rec_JobTask: Record "Job Task";
begin
Rec_Job.SetRange("No.", JobPlanningLine_P."Job No.");
Rec_Job.SetFilter(ReturnDateTime, '%1..%2', StartingDate, EndingDate);
if Rec_Job.FindFirst() then begin
Rec_JobTask.SetRange("Job No.", Rec_Job."No.");
Rec_JobTask.SetRange("Job Task No.", JobPlanningLine_P."Job Task No.");
Rec_JobTask.SetRange("Task Classification", Rec_JobTask."Task Classification"::"Client Admin");
if Rec_JobTask.FindFirst() then begin
exit(true);
end;
end;
end;
//procedure to calculate Exigent(Internal) Admin Hours
local procedure ConsiderPlanningLineForCalculation_ExigentAdmin(JobPlanningLine_P: Record "Job Planning Line"): Boolean
var
Rec_Job: Record Job;
Rec_JobTask: Record "Job Task";
begin
Rec_Job.SetRange("No.", JobPlanningLine_P."Job No.");
Rec_Job.SetFilter(ReturnDateTime, '%1..%2', StartingDate, EndingDate);
if Rec_Job.FindFirst() then begin
Rec_JobTask.SetRange("Job No.", Rec_Job."No.");
Rec_JobTask.SetRange("Job Task No.", JobPlanningLine_P."Job Task No.");
Rec_JobTask.SetRange("Task Classification", Rec_JobTask."Task Classification"::"Internal Admin");
if Rec_JobTask.FindFirst() then begin
exit(true);
end;
end;
end;
}
Please suggest as it is urgent.
Thanks in advance
