web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / Inventory Availibity P...
Small and medium business | Business Central, N...
Suggested Answer

Inventory Availibity Plan Report

(4) ShareShare
ReportReport
Posted on by CU04021004-0 45

Hello Everyone,

For the past month, I've been working on the Inventory Availability Plan report and the Planning Worksheet in Business Central. The Inventory Availability Plan report currently has a fixed number of interval columns, but I need to make them dynamic.

For example, I want to display data in 1-week intervals over a 3-month period, meaning the number of columns should adjust accordingly.

Is there a way to achieve this in an RDLC layout?

 
Categories:
Dynamics 365 Business Central
I have the same question (0)
  • Suggested answer
    Steven Renders Profile Picture
    Steven Renders 5,672 Moderator on at
    In RDLC you can use a Matrix control. However it would seem that Power BI might be a better solution here, imho.
  • Suggested answer
    Nitin Verma Profile Picture
    Nitin Verma 21,708 Moderator on at
    Hi,
     
    I have tried to create that report something like this:
    report 69000 "Inventory Availability Plan"
{
    DefaultLayout = RDLC;
    RDLCLayout = './InventoryAvailabilityPlan.rdlc';
    dataset
    {
        dataitem(Item; Item)
        {
            column(No; "No.") { }
            column(Description; Description) { }

            dataitem("Dynamic Periods"; Integer)
            {
                DataItemTableView = SORTING(Number) WHERE(Number = CONST(1));
                column(PeriodStartDate; TempPeriodBuffer.StartDate) { }
                column(PeriodQty; TempPeriodBuffer.Quantity) { }

                trigger OnPreDataItem()
                begin
                    TempPeriodBuffer.Reset();
                    TempPeriodBuffer.SetRange("Record No.", Item."No.");
                    SetRange(Number, 1, TempPeriodBuffer.Count());
                end;

                trigger OnAfterGetRecord()
                begin
                    if TempPeriodBuffer.Get(Item."No.", Number) then;
                end;
            }

            trigger OnAfterGetRecord()
            var
                StartDate: Date;
                EndDate: Date;
                Interval: DateFormula;
                i: Integer;
            begin
                // Define the period range and interval
                StartDate := WorkDate(); // Or any user-defined start date
                EndDate := CalcDate('<3M>', StartDate); // 3 months
                Evaluate(Interval, '<1W>'); // 1-week intervals

                // Populate temporary buffer with dynamic periods
                TempPeriodBuffer.Reset();
                TempPeriodBuffer.DeleteAll();
                i := 1;
                while StartDate <= EndDate do begin
                    TempPeriodBuffer.Init();
                    TempPeriodBuffer."Record No." := "No.";
                    TempPeriodBuffer."Period No." := i;
                    TempPeriodBuffer.StartDate := StartDate;
                    TempPeriodBuffer.Quantity := CalculateInventoryQty("No.", StartDate); // Your logic here
                    TempPeriodBuffer.Insert();
                    StartDate := CalcDate(Interval, StartDate);
                    i += 1;
                end;
            end;
        }
    }

    requestpage
    {
        layout
        {
            area(content)
            {
                field("Start Date"; StartDate)
                {
                    ApplicationArea = All;
                }
                field("Period Length"; PeriodLength)
                {
                    ApplicationArea = All;
                    Caption = 'Period Length (e.g., 1W, 1M)';
                }
                field("Duration"; Duration)
                {
                    ApplicationArea = All;
                    Caption = 'Duration (e.g., 3M, 6M)';
                }
            }
        }
    }

    var
        TempPeriodBuffer: Record "Period Buffer" temporary;
        StartDate: Date;
        PeriodLength: Text;
        Duration: Text;

    local procedure CalculateInventoryQty(ItemNo: Code[20]; PeriodStart: Date): Decimal
    var
        ItemLedgerEntry: Record "Item Ledger Entry";
    begin
        // Example logic: Calculate inventory qty for the period
        ItemLedgerEntry.SetRange("Item No.", ItemNo);
        ItemLedgerEntry.SetRange("Posting Date", PeriodStart, CalcDate('<1W>', PeriodStart));
        ItemLedgerEntry.CalcSums(Quantity);
        exit(ItemLedgerEntry.Quantity);
    end;
}

table 69002 "Period Buffer"
{
    TableType = Temporary;
    fields
    {
        field(1; "Record No."; Code[20]) { }
        field(2; "Period No."; Integer) { }
        field(3; StartDate; Date) { }
        field(4; Quantity; Decimal) { }
    }
}
    You can create RDLC report layout after putting Matrix table in the Layout. and do something like below.
     
  • Suggested answer
    Ramesh Kumar Profile Picture
    Ramesh Kumar 7,527 Super User 2025 Season 2 on at

    Interesting question! I know everyone loves the convenience of Excel for Inventory Availability Planning or the Planning Worksheet. In your case, I would suggest using import/export with Excel or a paginated report in Power BI.

    To answer your question, yes, it can be done in an RDLC layout.

     
    Thanks!
    Ramesh
     
    If this helped you, please check the box Does this answer your question?
  • Suggested answer
    YUN ZHU Profile Picture
    YUN ZHU 95,548 Super User 2025 Season 2 on at
    It seems that the report extension cannot do this, and you can only create a new report.
    Perhaps it would be easier to use analysis mode or Power Bi.
     
    Thanks.
    ZHU
  • Suggested answer
    Khushbu Rajvi. Profile Picture
    Khushbu Rajvi. 20,439 Super User 2025 Season 2 on at
    Yes, you can achieve dynamic columns in an RDLC report
  • Suggested answer
    Valentin Castravet Profile Picture
    Valentin Castravet 31,387 Super User 2025 Season 2 on at
    I agree with the other users that it would be easier to do this using a third party reporting tool such as Power BI or Jet Reports
     

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Small and medium business | Business Central, NAV, RMS

#1
OussamaSabbouh Profile Picture

OussamaSabbouh 3,151

#2
Jainam M. Kothari Profile Picture

Jainam M. Kothari 1,443 Super User 2025 Season 2

#3
YUN ZHU Profile Picture

YUN ZHU 1,092 Super User 2025 Season 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans