I’d produce a new report. Power BI would be the best place to do it. If you don’t have access then maybe AL is ok. A general approach to try with AL:
Main dataitem to be the customer
Var
DateHdr: array[12] of Text;
Amt: array[12] of decimal;
Dataset wise have a column for all 12 months:
column(DateHdr1; DateHdr[1]) { }
column(DateHdr2; DateHdr[2]) { }
column(DateHdr3; DateHdr[3]) { }
column(DateHdr4; DateHdr[4]) { }
column(DateHdr5; DateHdr[5]) { }
column(DateHdr6; DateHdr[6]) { }
column(DateHdr7; DateHdr[7]) { }
column(DateHdr8; DateHdr[8]) { }
column(DateHdr9; DateHdr[9]) { }
column(DateHdr10; DateHdr[10]) { }
column(DateHdr11; DateHdr[11]) { }
column(DateHdr12; DateHdr[12]) { }
column(Amt1; Amt[1]) { }
column(Amt2; Amt[2]) { }
column(Amt3; Amt[3]) { }
column(Amt4; Amt[4]) { }
column(Amt5; Amt[5]) { }
column(Amt6; Amt[6]) { }
column(Amt7; Amt[7]) { }
column(Amt8; Amt[8]) { }
column(Amt9; Amt[9]) { }
column(Amt10; Amt[10]) { }
column(Amt11; Amt[11]) { }
column(Amt12; Amt[12]) { }
Trigger wise:
trigger OnPreDataItem()
var
CurrDate: Date;
i: Integer;
Month: Integer;
Year: Integer;
begin
CurrDate := GetRangeMin("Start Date");
for i := 1 to ArrayLen(DateHdr) do begin
MnthArr[i] := Date2DMY(CurrDate, 2);
YearArr[i] := Date2DMY(CurrDate, 3);
DateHdr[i] := StrSubstNo('%1/%2', Date2DMY(CurrDate, 2), Date2DMY(CurrDate, 3));
CurrDate := CalcDate('<1M>', CurrDate);
end;
end;
OnAfterGetRecord have a procedure called to calculate the values.
for i := 1 to ArrayLen(DateHdr) do begin
CalcPeriodDate(i, StartDate, EndDate);
Amt[i] := whatever you need it to be
