Share
Report
29Hello Everyone,
I have added the Opening Balance to the existing Dimensions – Detail report in Business Central.
Problem:
-
When I run the report for the first time, the Opening Balance shows 0
-
If I run the report again, it shows the correct value.
-
Similarly, if I change the report parameters, the value still shows the last run’s data, and only updates correctly when I run it again.
I suspect this might be related to data caching or initialization logic in the report dataset, but I am not sure how to fix it.
I have added my code below for reference:
reportextension 50800 DimensionDetailExtension extends "Dimensions - Detail"
{
dataset
{
add(Level2)
{
column(OpeningBalance; OpeningBalance)
{
}
}
modify(Level2)
{
trigger OnAfterAfterGetRecord()
var
OpeningBalanceMgt: Codeunit 50802;
begin
// Read the value prepared by the subscriber for this row’s combo
OpeningBalance := OpeningBalanceMgt.GetCurrent();
end;
}
}
var
OpeningBalance: Decimal;
}
codeunit 50801 DimDetailOpeningBalanceSub
{
[EventSubscriber(ObjectType::Report, Report::"Dimensions - Detail",
'OnPrintDetailOnAfterTempGLEntrySetFilters', '', false, false)]
local procedure OnAfterTempGLEntrySetFilters(var TempGLEntry: Record "G/L Entry" temporary)
var
TempBefore: Record "G/L Entry";
StartDate: Date;
OpeningBalanceMgt: Codeunit 50802;
CacheKey: Text;
CachedValue: Decimal;
Opening: Decimal;
begin
// Start date of the period that the report is currently processing
StartDate := TempGLEntry.GetRangeMin("Posting Date");
if StartDate = 0D then
exit;
// Build a unique key for this GL + Dim1 + Dim2 + StartDate combo
CacheKey := OpeningBalanceMgt.MakeKey(
TempGLEntry."G/L Account No.",
TempGLEntry."Global Dimension 1 Code",
TempGLEntry."Global Dimension 2 Code",
StartDate);
// If already computed, just set "current" and exit
if OpeningBalanceMgt.TryGetForKey(CacheKey, CachedValue) then begin
OpeningBalanceMgt.SetCurrentKey(CacheKey);
exit;
end;
// Compute opening balance (entries strictly before StartDate)
Opening := 0;
TempBefore.Reset();
TempBefore.SetRange("G/L Account No.", TempGLEntry."G/L Account No.");
TempBefore.SetRange("Global Dimension 1 Code", TempGLEntry."Global Dimension 1 Code");
TempBefore.SetRange("Global Dimension 2 Code", TempGLEntry."Global Dimension 2 Code");
TempBefore.SetFilter("Posting Date", '<%1', StartDate);
if TempBefore.FindSet() then
repeat
Opening += (TempBefore."Debit Amount" - TempBefore."Credit Amount");
until TempBefore.Next() = 0;
// Cache and mark as current (no accumulation across calls)
OpeningBalanceMgt.SetForKey(CacheKey, Opening);
OpeningBalanceMgt.SetCurrentKey(CacheKey);
end;
}
codeunit 50802 OpeningBalanceMgt
{
SingleInstance = true;
var
// Cache: <GL|Dim1|Dim2|StartDate> -> Opening Balance
OpeningByKey: Dictionary of [Text, Decimal];
// The key for the combination currently being printed by the report
CurrentCacheKey: Text;
procedure MakeKey(GLAcc: Code[20]; Dim1: Code[20]; Dim2: Code[20]; StartDate: Date): Text
begin
// Format(StartDate, 0, 9) gives an invariant text for the date
exit(StrSubstNo('%1|%2|%3|%4', GLAcc, Dim1, Dim2, Format(StartDate, 0, 9)));
end;
procedure SetForKey(CacheKey: Text; Value: Decimal)
begin
if OpeningByKey.ContainsKey(CacheKey) then
OpeningByKey.Set(CacheKey, Value)
else
OpeningByKey.Add(CacheKey, Value);
end;
procedure TryGetForKey(CacheKey: Text; var Value: Decimal): Boolean
begin
exit(OpeningByKey.Get(CacheKey, Value));
end;
procedure SetCurrentKey(CacheKey: Text)
begin
CurrentCacheKey := CacheKey;
end;
procedure GetCurrent(): Decimal
var
Value: Decimal;
begin
if (CurrentCacheKey <> '') and OpeningByKey.Get(CurrentCacheKey, Value) then
exit(Value);
exit(0);
end;
procedure ClearAll()
begin
// Use the global Clear() to avoid method-availability differences
Clear(OpeningByKey);
CurrentCacheKey := '';
end;
}
Any guidance or suggestions to resolve this issue would be highly appreciated.
Thank you!
Shabir
Categories:
I have the same question (0)
All responses (
Answers (
