Share
Report
15Hello,
I am trying to merge two Reports, The Item Age Composition-Value and The Item Age Composition-Quantity.
But the values of the Item Age Composition-Quantity come incorrectly or inconsistently.
Kindly help me fix it.
Here is a screenshot of the Item Age Composition-Value
and a screenshot of The Item Age Composition-Quantity
And This is the Merged Report of the two
and below is the AL code behind it.
dataset
{
dataitem(Item; Item)
{
DataItemTableView = SORTING("No.") WHERE(Type = CONST(Inventory));
RequestFilterFields = "No.", "Inventory Posting Group", "Statistics Group", "Location Filter";
column(TodayFormatted; Format(Today, 0, 4))
{
}
column(CompanyName; COMPANYPROPERTY.DisplayName)
{
}
column(ItemTableCaptItemFilter; TableCaption + ': ' + ItemFilter)
{
}
column(ItemFilter; ItemFilter)
{
}
column(PeriodStartDate21; Format(PeriodStartDate[2] + 1))
{
}
column(PeriodStartDate3; Format(PeriodStartDate[3]))
{
}
column(PeriodStartDate31; Format(PeriodStartDate[3] + 1))
{
}
column(PeriodStartDate4; Format(PeriodStartDate[4]))
{
}
column(PeriodStartDate41; Format(PeriodStartDate[4] + 1))
{
}
column(PeriodStartDate5; Format(PeriodStartDate[5]))
{
}
column(Item2PeriodStartDate; Format(PeriodStartDate[2] + 1))
{
}
column(Item3PeriodStartDate; Format(PeriodStartDate[3]))
{
}
column(Item31PeriodStartDate; Format(PeriodStartDate[3] + 1))
{
}
column(Item4PeriodStartDate; Format(PeriodStartDate[4]))
{
}
column(Item41PeriodStartDate; Format(PeriodStartDate[4] + 1))
{
}
column(Item5PeriodStartDate; Format(PeriodStartDate[5]))
{
}
column(ItemAgeCompositionQtyCaption; ItemAgeCompositionQtyCaptionLbl)
{
}
column(PageNoCaption; PageNoCaptionLbl)
{
}
column(AfterCaption; AfterCaptionLbl)
{
}
column(BeforeCaption; BeforeCaptionLbl)
{
}
column(TotalInvtQtyCaption; TotalInvtQtyCaptionLbl)
{
}
column(ItemDescriptionCaption; ItemDescriptionCaptionLbl)
{
}
column(ItemNoCaption; ItemNoCaptionLbl)
{
}
column(InvtQty1_ItemLedgEntry; InvtQty[1])
{
DecimalPlaces = 0 : 2;
}
column(InvtQty2_ItemLedgEntry; InvtQty[2])
{
DecimalPlaces = 0 : 2;
}
column(InvtQty3_ItemLedgEntry; InvtQty[3])
{
DecimalPlaces = 0 : 2;
}
column(InvtQty4_ItemLedgEntry; InvtQty[4])
{
DecimalPlaces = 0 : 2;
}
column(InvtQty5_ItemLedgEntry; InvtQty[5])
{
DecimalPlaces = 0 : 2;
}
column(TotalInvtQty; TotalInvtQty)
{
DecimalPlaces = 0 : 2;
}
column(Desc_Item; Description)
{
}
column(PrintLine; PrintLine)
{
}
column(InvtValueRTC1; InvtValueRTC[1])
{
}
column(InvtValueRTC2; InvtValueRTC[2])
{
}
column(InvtValueRTC5; InvtValueRTC[5])
{
}
column(InvtValueRTC4; InvtValueRTC[4])
{
}
column(InvtValueRTC3; InvtValueRTC[3])
{
}
column(TotalInvtValueRTC; TotalInvtValueRTC)
{
}
column(InvtValue1_Item; InvtValue[1])
{
AutoFormatType = 1;
}
column(InvtValue2_Item; InvtValue[2])
{
AutoFormatType = 1;
}
column(InvtValue3_Item; InvtValue[3])
{
AutoFormatType = 1;
}
column(InvtValue4_Item; InvtValue[4])
{
AutoFormatType = 1;
}
column(InvtValue5_Item; InvtValue[5])
{
AutoFormatType = 1;
}
column(TotalInvtValue_Item; TotalInvtValue_Item)
{
AutoFormatType = 1;
}
column(ItemAgeCompositionValueCaption; ItemAgeCompositionValueCaptionLbl)
{
}
column(CurrReportPageNoCaption; CurrReportPageNoCaptionLbl)
{
}
column(InventoryValueCaption; InventoryValueCaptionLbl)
{
}
column(TotalCaption; TotalCaptionLbl)
{
}
dataitem("Item Ledger Entry"; "Item Ledger Entry")
{
DataItemLink = "Item No." = FIELD("No."), "Location Code" = FIELD("Location Filter"), "Variant Code" = FIELD("Variant Filter"), "Global Dimension 1 Code" = FIELD("Global Dimension 1 Filter"), "Global Dimension 2 Code" = FIELD("Global Dimension 2 Filter");
DataItemTableView = SORTING("Item No.", Open) WHERE(Open = CONST(true));
trigger OnAfterGetRecord()
var
ItemLedgEntry: Record "Item Ledger Entry";
begin
begin
PrintLine := false;
TotalInvtQty := 0;
for i := 1 to 5 do
InvtQty[i] := 0;
ItemLedgEntry.FilterLinesWithItemToPlan(Item, false);
if ItemLedgEntry.FindSet then
repeat
PrintLine := true;
TotalInvtQty := TotalInvtQty + ItemLedgEntry."Remaining Quantity";
for i := 1 to 5 do
if (ItemLedgEntry."Posting Date" > PeriodStartDate[i]) and (ItemLedgEntry."Posting Date" <= PeriodStartDate[i + 1]) then
InvtQty[i] := InvtQty[i] + ItemLedgEntry."Remaining Quantity";
until ItemLedgEntry.Next = 0;
end;
if "Remaining Quantity" = 0 then
CurrReport.Skip();
PrintLine := true;
CalcRemainingQty;
RemainingQty += TotalInvtQty;
if Item."Costing Method" = Item."Costing Method"::Average then begin
InvtValue[i] += AverageCost[i] * InvtQty[i];
InvtValueRTC[i] += AverageCost[i] * InvtQty[i];
end else begin
CalcUnitCost;
TotalInvtValue_Item += UnitCost * Abs(TotalInvtQty);
InvtValue[i] += UnitCost * Abs(InvtQty[i]);
TotalInvtValueRTC += UnitCost * Abs(TotalInvtQty);
InvtValueRTC[i] += UnitCost * Abs(InvtQty[i]);
end
end;
trigger OnPostDataItem()
var
AvgCostCurr: Decimal;
AvgCostCurrLCY: Decimal;
begin
if Item."Costing Method" = Item."Costing Method"::Average then begin
Item.SetRange("Date Filter");
ItemCostMgt.CalculateAverageCost(Item, AvgCostCurr, AvgCostCurrLCY);
TotalInvtValue_Item := AvgCostCurr * RemainingQty;
TotalInvtValueRTC += TotalInvtValue_Item;
end;
end;
trigger OnPreDataItem()
begin
TotalInvtValue_Item := 0;
for i := 1 to 5 do
InvtValue[i] := 0;
RemainingQty := 0;
end;
}
dataitem("Integer"; "Integer")
{
DataItemTableView = SORTING(Number) WHERE(Number = CONST(1));
column(TotalInvtValue_ItemLedgEntry; TotalInvtValue_Item)
{
AutoFormatType = 1;
}
column(InvtValue5_ItemLedgEntry; InvtValue[5])
{
AutoFormatType = 1;
}
column(InvtValue4_ItemLedgEntry; InvtValue[4])
{
AutoFormatType = 1;
}
column(InvtValue3_ItemLedgEntry; InvtValue[3])
{
AutoFormatType = 1;
}
column(InvtValue2_ItemLedgEntry; InvtValue[2])
{
AutoFormatType = 1;
}
column(InvtValue1_ItemLedgEntry; InvtValue[1])
{
AutoFormatType = 1;
}
column(Description_Item; Item.Description)
{
}
column(No_Item; Item."No.")
{
}
}
trigger OnAfterGetRecord()
begin
if "Costing Method" = "Costing Method"::Average then begin
for i := 2 to 5 do begin
SetRange("Date Filter", PeriodStartDate[i] + 1, PeriodStartDate[i + 1]);
ItemCostMgt.CalculateAverageCost(Item, AverageCost[i], AverageCostACY[i]);
end;
SetRange("Date Filter", 0D, PeriodStartDate[2]);
ItemCostMgt.CalculateAverageCost(Item, AverageCost[1], AverageCostACY[1]);
end;
PrintLine := false;
end;
trigger OnPreDataItem()
begin
Clear(InvtValue);
Clear(TotalInvtValue_Item);
end;
}
}
requestpage
{
SaveValues = true;
layout
{
area(content)
{
group(Options)
{
Caption = 'Options';
field(EndingDate; PeriodStartDate[5])
{
ApplicationArea = Basic, Suite;
Caption = 'Ending Date';
ToolTip = 'Specifies the end date of the report. The report calculates backwards from this date and sets up three periods of the length specified in the Period Length field.';
trigger OnValidate()
begin
if PeriodStartDate[5] = 0D then
Error(Text002);
end;
}
field(PeriodLength; PeriodLength)
{
ApplicationArea = Basic, Suite;
Caption = 'Period Length';
ToolTip = 'Specifies the length of the three periods in the report.';
trigger OnValidate()
begin
if Format(PeriodLength) = '' then
Evaluate(PeriodLength, '<0D>');
end;
}
}
}
}
actions
{
}
trigger OnOpenPage()
begin
if PeriodStartDate[5] = 0D then
PeriodStartDate[5] := CalcDate('<CM>', WorkDate);
if Format(PeriodLength) = '' then
Evaluate(PeriodLength, '<1M>');
end;
}
labels
{
}
trigger OnPreReport()
var
NegPeriodLength: DateFormula;
begin
ItemFilter := Item.GetFilters;
PeriodStartDate[6] := DMY2Date(31, 12, 9999);
Evaluate(NegPeriodLength, StrSubstNo('-%1', Format(PeriodLength)));
for i := 1 to 3 do
PeriodStartDate[5 - i] := CalcDate(NegPeriodLength, PeriodStartDate[6 - i]);
end;
var
Text002: Label 'Enter the ending date';
ItemCostMgt: Codeunit ItemCostManagement;
ItemFilter: Text;
ItemAgeCompositionQtyCaptionLbl: Label 'Item Age Composition - Quantity';
PageNoCaptionLbl: Label 'Page';
TotalInvtQtyCaptionLbl: Label 'Inventory';
InvtValue: array[6] of Decimal;
InvtValueRTC: array[6] of Decimal;
InvtQty: array[6] of Decimal;
UnitCost: Decimal;
PeriodStartDate: array[6] of Date;
PeriodLength: DateFormula;
i: Integer;
TotalInvtValue_Item: Decimal;
TotalInvtValueRTC: Decimal;
TotalInvtQty: Decimal;
PrintLine: Boolean;
AverageCost: array[5] of Decimal;
AverageCostACY: array[5] of Decimal;
ItemAgeCompositionValueCaptionLbl: Label 'Stock Ageing Analysis';
CurrReportPageNoCaptionLbl: Label 'Page';
AfterCaptionLbl: Label 'After...';
BeforeCaptionLbl: Label '...Before';
InventoryValueCaptionLbl: Label 'Inventory Value';
ItemDescriptionCaptionLbl: Label 'Description';
ItemNoCaptionLbl: Label 'Item No.';
TotalCaptionLbl: Label 'Total';
RemainingQty: Decimal;
local procedure CalcRemainingQty()
begin
with "Item Ledger Entry" do begin
for i := 1 to 5 do
InvtQty[i] := 0;
TotalInvtQty := "Remaining Quantity";
for i := 1 to 5 do
if ("Posting Date" > PeriodStartDate[i]) and
("Posting Date" <= PeriodStartDate[i + 1])
then
if "Remaining Quantity" <> 0 then begin
InvtQty[i] := "Remaining Quantity";
exit;
end;
end;
end;
local procedure CalcUnitCost()
var
ValueEntry: Record "Value Entry";
begin
with ValueEntry do begin
SetRange("Item Ledger Entry No.", "Item Ledger Entry"."Entry No.");
UnitCost := 0;
if Find('-') then
repeat
if "Partial Revaluation" then
SumUnitCost(UnitCost, "Cost Amount (Actual)" + "Cost Amount (Expected)", "Valued Quantity")
else
SumUnitCost(UnitCost, "Cost Amount (Actual)" + "Cost Amount (Expected)", "Item Ledger Entry".Quantity);
until Next = 0;
end;
end;
local procedure SumUnitCost(var UnitCost: Decimal; CostAmount: Decimal; Quantity: Decimal)
begin
UnitCost := UnitCost + CostAmount / Abs(Quantity);
end;
procedure InitializeRequest(NewEndingDate: Date; NewPeriodLength: DateFormula)
begin
PeriodStartDate[5] := NewEndingDate;
PeriodLength := NewPeriodLength;
end;
}
All responses (
Answers (
