Streamlining Report Substitution in Business Central: A Dynamic Alternative to OnAfterSubstituteReport Code Changes

 

In Microsoft Dynamics 365 Business Central, the OnAfterSubstituteReport event is commonly used to substitute one report for another at runtime. While this event handler is powerful, it can become cumbersome when you frequently update report substitutions. For each report change, you must modify the code, which can be time-consuming, and you must wait to move the changes to the production environment.

It can grow big like the example below:

    [EventSubscriber(ObjectType::Codeunit, Codeunit::ReportManagement, 'OnAfterSubstituteReport', '', false, false)]

    local procedure OnAfterSubstituteReport(ReportId: Integer; RunMode: Option; RequestPageXml: Text; RecordRef: RecordRef; var NewReportId: Integer);

begin

        case ReportId of

            1305:

                NewReportId := 10075;

            299:

                NewReportId := 50107;

            393:

                NewReportId := 50108;

            120:

                NewReportId := 50140;

            10040:

                NewReportId := 50140;

            108:

                NewReportId := 50154;

            10048:

                NewReportId := 50141;

            5802:

                NewReportId := 50147;

            10049:

                NewReportId := 50142;

        end;

end;

An alternative approach to managing report substitutions dynamically involves creating a custom table that stores the mappings between old report IDs and new report IDs. By leveraging this table in conjunction with the OnAfterSubstituteReport event, you can simplify the process of updating report substitutions without modifying the underlying code each time.

Step 1: Create a Custom Table and Page for Report Mappings

First, create a custom table to store the mappings between old and new report IDs. This table should have at least two fields: one for the old report ID and another for the new report ID. Here’s an example:

 

table 88100 "MPA Dynamic Rep. Subs. Mapping"

{

    Caption = 'Dynamic Report Substitution Mapping';

    DataClassification = CustomerContent;

 

    fields

    {

        field(1; ReportId; Integer)

        {

            DataClassification = CustomerContent;

            TableRelation = AllObjWithCaption."Object ID" WHERE("Object Type" = CONST(Report));

            trigger OnValidate()

            begin

                Calcfields("Report Caption");

            end;

        }

        field(2; NewReportId; Integer)

        {

            DataClassification = CustomerContent;

            TableRelation = AllObjWithCaption."Object ID" WHERE("Object Type" = CONST(Report));

            trigger OnValidate()

            begin

                Calcfields("New Report Caption");

            end;

        }

        field(4; "Report Caption"; Text[250])

        {

            CalcFormula = lookup("Report Metadata".Caption where(ID = field(ReportId)));

            Caption = 'Report Name';

            Editable = false;

            FieldClass = Flowfield;

        }

        field(5; "New Report Caption"; Text[250])

        {

            CalcFormula = lookup("Report Metadata".Caption where(ID = field(NewReportId)));

            Caption = 'New Report Name';

            Editable = false;

            FieldClass = Flowfield;

        }

    }

 

    keys

    {

        key(Key1; ReportId)

        {

            Clustered = true;

        }

    }

 

}

This page will allow you to maintain a list of report substitutions that can be easily updated through the UI, without needing to touch any code.

page 88100 "MPA Dynamic Rep. Subs. Mapping"

{

    Caption = 'Dynamic Report Substitution Mappings';

    PageType = List;

    UsageCategory = Lists;

    ApplicationArea = All;

    SourceTable = "MPA Dynamic Rep. Subs. Mapping";

 

    layout

    {

        area(Content)

        {

            repeater(Group)

            {

                field(ReportId; Rec.ReportId)

                {

                    ApplicationArea = All;

                }

                field("Report Caption"; Rec."Report Caption")

                {

                    ApplicationArea = All;

                }

                field(NewReportId; Rec.NewReportId)

                {

                    ApplicationArea = All;

                }

                field("New Report Caption"; Rec."New Report Caption")

                {

                    ApplicationArea = All;

 

                }

            }

 

        }

    }

}

 

Step 2: Populate the Table with Mappings

Once the table is created, you can populate it with your report mappings. For example, if you want to replace Report 100 with Report 200, simply add an entry with Old Report ID = 100 and New Report ID = 200.

Step 3: Modify the OnAfterSubstituteReport Event

Now, modify the OnAfterSubstituteReport event to reference the custom table you created. This will enable the event to dynamically substitute reports based on the mappings stored in the table:

 

codeunit 88100 "MPA Report Management Subs"

{

 

    [EventSubscriber(ObjectType::Codeunit, Codeunit::ReportManagement, 'OnAfterSubstituteReport', '', false, false)]

    local procedure OnAfterSubstituteReport(ReportId: Integer; RunMode: Option; RequestPageXml: Text; RecordRef: RecordRef; var NewReportId: Integer);

    var

        SubstituteReports: Record "MPA Dynamic Rep. Subs. Mapping";

    begin

        if SubstituteReports.Get(ReportId) then

            NewReportId := SubstituteReports.NewReportId;

    end;

 

}

This code checks the custom table for a matching Old Report ID and, if found, assigns the corresponding New Report ID to the NewReportID parameter. This substitution happens without the need to hard-code any report IDs, making your solution much more flexible and easier to maintain.

Step 4: Manage Substitutions Dynamically

With this setup, managing report substitutions becomes straightforward. Whenever you need to substitute a report, simply update the mapping in the custom table—no code changes are required. This approach allows for quick adjustments in a production environment without the need for a new deployment.

Conclusion

Using a custom table to manage report substitutions in Business Central is a powerful alternative to hard-coding substitutions in the OnAfterSubstituteReport event. By separating the logic from the code and storing it in a table, you gain flexibility, reduce maintenance overhead, and simplify the process of updating report mappings.

 

You can find the code here

pmohanakrishna/DynamicReportSubstitutions (github.com)