“Custom Report Layout was locked”

Have you ever run into “The operation could not complete because a record in the Custom Report Layout table was locked by another user. Please retry the activity”?

Sadly, we have! And today is the day when I was digging into this issue and tried to find a root cause.

The Problem

Let’s start with creating a simple Report which needs 20 seconds to be executed:

report 90000 "longer running report"
{
    UsageCategory = ReportsAndAnalysis;
    ApplicationArea = All;

    dataset
    {
        dataitem(DataItemName; Integer)
        {
            DataItemTableView = where(Number = const(1));

            column(ColumnName; "Number")
            {
            }

            trigger OnPreDataItem();
            begin
                Sleep(20*1000);
            end;
        }
    }
}

Go to “report layout selection” and change the layout to “Custom Layout” and create an empty layout.

Let’s start 2 sessions and run the report in both of them. Oops… you got the lovely locking message in one of the sessions.

=> Whenever you run a report with a custom layout, the system will do a row lock on custom layout record.

Now you think “yeah but the report runs 20 seconds… does not happen on my.”.
Let’s say your report “only” runs around 3 seconds and 5 users click at the exact same time on print.
As default settings the locktimeout will pop up after 10 seconds. That means in the given example 1 of the users will get the lock message. This is not scalable!

Building a workaround!

First let us figure out how we can change a report layout by code
There is a Single Instance Codeunit “9654 – Design-time Report Selection” where the “selected custom layout” is set and taken from.

We creating a second layout and add something in that we see its the second layout.

DesignTimeReportSelection.SetSelectedCustomLayout('90000-00002');
Report.Run(90000);

if we now run this code we see the new layout in our report.
But wait if you run a different report it’s raising an error now.
of course, you need to reset the layout again

DesignTimeReportSelection.SetSelectedCustomLayout('90000-00002');
Report.Run(90000);
DesignTimeReportSelection.SetSelectedCustomLayout('');

Now it looks good!

But how does this help us now?
We know that there is a rowlock on the custom report layout which we are using.
What happens if we create a new “custom report layout” entry whenever we run a report?!



procedure Duplicate(ReportId: Integer);
var
    RepLayoutSelection: Record "Report Layout Selection";
    CustomReportLayout: Record "Custom Report Layout";
    DesignTimeReportSelection: Codeunit "Design-time Report Selection";
begin
    CleanupDuplicate();
    if not RepLayoutSelection.Get(ReportId, CompanyName()) then
        if not RepLayoutSelection.Get(ReportId, '') then
            exit; 

    if RepLayoutSelection.Type <> RepLayoutSelection.Type::"Custom Layout" then
        exit;

    if RepLayoutSelection."Custom Report Layout Code" = '' then
        exit;

    CustomReportLayout.SetAutoCalcFields("Custom XML Part", Layout);
    if CustomReportLayout.Get(RepLayoutSelection."Custom Report Layout Code") then begin
        CustomReportLayout.Code := GetTempCustomReportLayoutCode();
        CustomReportLayout.Insert(false);
        DesignTimeReportSelection.SetSelectedCustomLayout(GetTempCustomReportLayoutCode());
    end;
end;

procedure CleanupDuplicate()
var
    CustomReportLayout: Record "Custom Report Layout";
    DesignTimeReportSelection: Codeunit "Design-time Report Selection";
begin
    if CustomReportLayout.Get(GetTempCustomReportLayoutCode()) then
        CustomReportLayout.Delete(false);

    DesignTimeReportSelection.SetSelectedCustomLayout('');
end;

local procedure GetTempCustomReportLayoutCode() CustomReportLayoutCode: Code[20]
begin
    CustomReportLayoutCode := CopyStr(StrSubstNo('S-%1', SessionId()), 1, 20);
end;

We can use the SessionId() as Unique Identifier and this works because every session can only generate 1 report at the same time.

If we now running the report like this

Duplicate(90000);
Commit(); // is needed because we insert a record before we run a report
Report.Run(90000);
CleanupDuplicate(90000);

we can run the report in as many sessions as we want. But…

we have a commit
right now it does not help us on standard reports

Lets adjust the initial report

report 90000 "longer running report"
{
    UsageCategory = ReportsAndAnalysis;
    ApplicationArea = All;

    dataset
    {
        dataitem(DataItemName; Integer)
        {
            DataItemTableView = where(Number = const(1));

            column(ColumnName; "Number")
            {
            }

            trigger OnPreDataItem();
            begin
                Sleep(20*1000);
            end;
        }

    }

    trigger OnPreReport();
    var
        CustomReportLayoutLockHelper: Codeunit "CRL Lock Helper";
    begin
        CustomReportLayoutLockHelper.Duplicate(90000);
    end;

    trigger OnPostReport();
    var
        CustomReportLayoutLockHelper: Codeunit "CRL Lock Helper";
    begin
        CustomReportLayoutLockHelper.CleanupDuplicate();
    end;
}

You can implement this in every report where the company may change the layout to a custom layout.

Greate side effect of this implementation is that repots can now be generated in parallel. If the same layout is generated in different session, they do not need to wait till the report is generated in other sessions. In our given example all 5 users will get the output after 3 seconds.

* This is build and tested in NAV 2018

“Custom Report Layout was locked”