Share
Report
700Hi All,
Below is the result of the report which I have created :-
In this report, I have applied for leave ('No. of Days Absent' field) two times. We have to display the total number of leaves taken by an employee. Although, this logic is working correct, but for the same employee, the record (row) is generated the number of times equal to the total applications of leaves. It is required to display the record only once for a particular employee.
Could anyone please help me out resolving the issue.
Below is the report code mentioned for better understanding :-
report 50130 "Emp-AbsencebyEntitlementPeriod"
{
RDLCLayout = './Employee - Absences by Entitlement Period.rdl';
ApplicationArea = All;
Caption = 'Absence By Entitlement Period';
UsageCategory = ReportsAndAnalysis;
dataset
{
dataitem(Employee1; Employee)
{
RequestFilterFields = Location, Status, "Department";
dataitem("Employee Absence"; "Employee Absence")
{
DataItemLink = "Employee No." = field("No.");
DataItemTableView = SORTING("Cause of Absence Code", "From Date");
RequestFilterFields = "Employee No.";
column(FORMAT_TODAY_0_4_; Format(Today, 0, 4))
{
}
column(COMPANYNAME; COMPANYPROPERTY.DisplayName)
{
}
// column(CurrReport_PAGENO; CurrReport.PageNo)
// {
// }
column(EmployeeAbsenceFilter; EmployeeAbsenceFilter)
{
}
column(Employee_Absence_Description; Description)
{
}
column(Employee_Absence__Cause_of_Absence_Code_; "Cause of Absence Code")
{
}
column(Employee_Absence__From_Date_; Format("From Date"))
{
}
column(Employee_Absence__To_Date_; Format("To Date"))
{
}
column(Employee_Absence__Quantity__Base__; "Quantity (Base)")
{
}
column(HumanResSetup__Base_Unit_of_Measure_; HumanResSetup."Base Unit of Measure")
{
}
column(Employee_Absence__Employee_No__; "Employee No.")
{
}
column(Employee_FullName; Employee.FullName)
{
}
column(Employee_FirstName; Employee."First Name")
{
}
column(Employee_Surname; Employee."Last Name")
{
}
column(TotalAbsence; TotalAbsence)
{
DecimalPlaces = 0 : 2;
}
column(Employee___Absences_by_CausesCaption; Employee___Absences_by_CausesCaptionLbl)
{
}
column(CurrReport_PAGENOCaption; CurrReport_PAGENOCaptionLbl)
{
}
column(Employee_Absence__From_Date_Caption; Employee_Absence__From_Date_CaptionLbl)
{
}
column(Employee_Absence__To_Date_Caption; Employee_Absence__To_Date_CaptionLbl)
{
}
column(Employee_Absence__Employee_No__Caption; FieldCaption("Employee No."))
{
}
column(Employee_Absence__Quantity__Base__Caption; FieldCaption("Quantity (Base)"))
{
}
column(Employee_EmpDate; Employee."Employment Date")
{
}
column(DaysAbsent; DaysAbsent)
{
}
column(Emp_Department; Emp_Department)
{
}
column(AbsenceDescription; AbsenceDescription)
{
}
dataitem(Entitlements; Entitlements)
{
DataItemLink = Employee_No = field("Employee No.");
RequestFilterFields = "Code";
column(Starting_Period; Starting_Period)
{
}
column(Ending_Period; Ending_Period)
{
}
column(Days; Days)
{
}
column(Additional_Entitlements; Additional_Entitlements)
{
}
column(DaysAbsentUpdated; DaysAbsent)
{
}
column(AvailableEnitilement; AvailableEnitilement)
{
}
column(EmployeeFilter; EmployeeFilter)
{
}
trigger OnPreDataItem()
begin
EmployeeFilter := Employee1.GetFilters();
EmployeeAbsenceFilter := "Employee Absence".GetFilters();
end;
trigger OnAfterGetRecord()
begin
Clear(DaysAbsent);
Clear(AvailableEnitilement);
EmpAbsenceRec.Reset();
EmpAbsenceRec.SetRange("Employee No.", Employee_No);
EmpAbsenceRec.SetRange("Entitlement Code", Code);
if EmpAbsenceRec.FindSet() then
repeat
DaysAbsent += EmpAbsenceRec."Quantity (Base)";
until EmpAbsenceRec.Next() = 0;
AvailableEnitilement := Additional_Entitlements + Days - DaysAbsent;
FindFirst();
end;
}
trigger OnAfterGetRecord()
begin
Clear(Emp_Department);
Clear(AbsenceDescription);
Employee.Get("Employee No.");
TotalAbsence := TotalAbsence + "Quantity (Base)";
DefaultDimension.Reset();
DefaultDimension.SetRange("Table ID", 5200);
DefaultDimension.SetRange("Dimension Code", 'DEPARTMENT');
DefaultDimension.SetRange("No.", "Employee No.");
IF DefaultDimension.Findset() then begin
Emp_Department := DefaultDimension."Dimension Value Code";
END;
CauseofAbsenceRec.Reset();
CauseofAbsenceRec.SetRange(Code, "Cause of Absence Code");
IF CauseofAbsenceRec.FindFirst() then begin
AbsenceDescription := CauseofAbsenceRec.Description;
end;
end;
}
}
}
requestpage
{
layout
{
}
trigger Onopenpage() //sets default value for the Status filter as 'Active'
var
myInt: Integer;
begin
Employee1.SetRange(Status, Employee1.Status::Active);
end;
// actions
// {
// }
}
labels
{
}
// trigger OnPreReport()
// begin
// LocationFilter := Employee.GetFilters;
// EmployeeNoFilter := "Employee Absence".GetFilters;
// EmployeeAbsenceFilter := "Employee Absence".GetFilters;
// HumanResSetup.Get;
// HumanResSetup.TestField("Base Unit of Measure");
// end;
var
Employee: Record Employee;
EmpAbsenceRec: Record "Employee Absence";
CauseofAbsenceRec: Record "Cause of Absence";
Emp_Department: Code[30];
AbsenceDescription: Text;
DefaultDimension: Record "Default Dimension";
HumanResSetup: Record "Human Resources Setup";
EmployeeAbsenceFilter: Text;
TotalAbsence: Decimal;
Employee___Absences_by_CausesCaptionLbl: Label 'Employee - Absences by Causes';
CurrReport_PAGENOCaptionLbl: Label 'Page';
Employee_Absence__From_Date_CaptionLbl: Label 'From Date';
Employee_Absence__To_Date_CaptionLbl: Label 'To Date';
Full_NameCaptionLbl: Label 'Full Name';
HumanResSetup__Base_Unit_of_Measure_CaptionLbl: Label 'Base Unit of Measure';
Total_AbsenceCaptionLbl: Label 'Total Absence';
DaysAbsent: Decimal;
AbsenceReg: Record "Absence";
AvailableEnitilement: Decimal;
EmployeeFilter: Text;
}
Please suggest a solution as earlier as possible.
Thanks in advance !
All responses (
Answers (
