Duplication of rows in Report builder

Business Central/NAV
1

report 50007 “Stock At Hand Report”
{
ApplicationArea = All;
UsageCategory = ReportsAndAnalysis;
RDLCLayout = ‘stock.rdl’;
Caption = ‘Stock At Hand Report’;

dataset
{
    dataitem(ItemLedgerEntry; "Item Ledger Entry")
    {
        RequestFilterFields = "Location Code", "Inventory Posting Group","Item No.";
        DataItemTableView = where(Open = filter(true)); // Filter only open ledger entries

        column(ItemNo; "Item No.") { }
        column(LocationCode; "Location Code") { }
        column(RemainingQuantity; "Remaining Quantity") { }
        column(CurrentQty; CurrentQty) {  } 
        column(Item_Name; "Item Name") {  } 
        column(Inventory_Posting_Group; "Inventory Posting Group") { }

        trigger OnAfterGetRecord()
        var
            ItemLedgerEntrySum: Record "Item Ledger Entry";
        begin
            // Reset CurrentQty for each record
            CurrentQty := 0;

            // Set up filters to sum the quantities for the current item
            ItemLedgerEntrySum.SetRange("Item No.", "Item No.");
            ItemLedgerEntrySum.SetRange("Location Code", "Location Code");
            ItemLedgerEntrySum.SetRange("Inventory Posting Group", "Inventory Posting Group");
            ItemLedgerEntrySum.SetRange(Open, true); // Ensure it's filtered for open entries

            // Summing Remaining Quantity
            if ItemLedgerEntrySum.FindSet() then begin
                repeat
                    CurrentQty += ItemLedgerEntrySum."Remaining Quantity";
                until ItemLedgerEntrySum.Next() = 0;
            end;
        end;
    }
}

requestpage
{
    layout
    {
        area(Content)
        {
            group(GroupName)
            {
            }
        }
    }

    actions
    {
        area(Processing)
        {
        }
    }
}

var
    CurrentQty: Decimal; // Variable for storing the total remaining quantity

}
thats is my code but the rows repeat them selves like no 1 can be the same as 7 same as 89 like that how can i solve this

2

Hi @Winy_Kateregga,

Looking at the code, it looks like you want to group data, but you’re not actually grouping anything.

The loop in this dataitem is on “Item Ledger Entry” table, and, in this table, the same “Item No.” and “Location Code” are on several rows this is because you see the same information in more than 1 row.

First you need to order the dataitem by “Item No.” and “Location Code”, then in OnAfterRecord, you can filter own values on these fields, and do a “findlast()”, remove the filters and let the system take the next iteration of “Item No.” and “Location Code”.

trigger OnAfterGetRecord()
begin
SetRange(“Item No.”, “Item No.”);
SetRange(“Location Code”, “Location Code”);
FindLast();
SetRange(“Item No.”);
SetRange(“Location Code”);
…

I think this way you will get what you want.

3

You can do it in other way… you have to remove all the code in OnAfterGetRecord and remove the column CurrentQty (you already have “Remaining Quantity” as a column), and do the grouping in the report layout using the report designer.

In the previous, you can remove the column “Remaining Quantity”.

4

thanks @pjllaneras i will try that