Report - Horizontal Columns using arrays

TFCrowther

REPORT USING ARRAYS TO CREATE HORIZONTAL COLUMNS

2 Arrays with Dimension 12:
MonthLabel
SalesMthTotal

** Customer down the left with Sales Total by month along the top **

Customer - OnPreDataItem()
//MESSAGE(FORMAT(CALCDATE('CM + 1D - 1M',150600D)));  //TESTING
//MESSAGE(FORMAT(CALCDATE('CM',TODAY)));              //TESTING

//Header Section insert Textboxes - MonthLabel[1], MonthLabel[2], MonthLabel[3] etc

//Prints the Month now ie Jan 06
MonthLabel[1] := FORMAT(TODAY,0,'<Month Text,3> <Year4>');

//Prints each month in descending order ie Dec 05, Nov 05, Oct 05 etc..
FOR x := 2 TO 12 DO BEGIN
  MthFormula := '-' + FORMAT(x) + 'M';
  MonthLabel[x] := FORMAT(CALCDATE(MthFormula,TODAY),0,'<Month Text,3> <Year4>');
END;

CurrReport.CREATETOTALS(SalesMthTotal);




Customer - OnAfterGetRecord()

//Body Section insert Textboxes SalesMthTotal[1], SalesMthTotal[2], SalesMthTotal[3], etc..

CLEAR(SalesMthTotal);

StartDate := CALCDATE('CM + 1D - 1M',TODAY);
EndDate := CALCDATE('CM',TODAY);

FOR x := 1 TO 12 DO BEGIN

  CustLedEntry.SETRANGE(CustLedEntry."Customer No.",Customer."No.");
  CustLedEntry.SETFILTER(CustLedEntry."Posting Date",'%1..%2',StartDate,EndDate);

  IF CustLedEntry.FIND('-') THEN
    REPEAT
        SalesMthTotal[x] := SalesMthTotal[x] + CustLedEntry."Sales (LCY)";
    UNTIL CustLedEntry.NEXT=0;

  StartDate := CALCDATE('-1M',StartDate);
  EndDate := CALCDATE('-1M',EndDate);

  StartDate := CALCDATE('CM + 1D - 1M',StartDate);
  EndDate := CALCDATE('CM',EndDate);

END;

Good Luck

Toby Crowther

ara3n

Hello onaftergetrecord section can improved for performance with the following code.

FOR x := 1 TO 12 DO BEGIN
  SETFILTER("Date Filter",'%1..%2',StartDate,EndDate);
  CALCFIELDS("Sales (LCY)");
  SalesMthTotal[x] := "Sales (LCY)";
  
  StartDate := CALCDATE('-1M',StartDate);
  EndDate := CALCDATE('-1M',EndDate);

  StartDate := CALCDATE('CM + 1D - 1M',StartDate);
  EndDate := CALCDATE('CM',EndDate);

END;

Savatage

I tried this report But I get

Feb 06 for MonthLabel[1]
Dec 05 for MonthLabel[2]
Nov 05 for MonthLabel[3]
so on

What Happend to Jan 06?

ara3n

I believe the following code is the reason

//Prints the Month now ie Jan 06
MonthLabel[1] := FORMAT(TODAY,0,'<Month Text,3> <Year4>');

Change everywhere where TODAY is used to first day of the year.

Savatage

I moved my clock ahead - the report does skip the previous month

It's Monthlabel[2] where it goes wrong

TFCrowther wrote:

MthFormula := '-' + FORMAT(x) + 'M';

MonthLabel[x] := FORMAT(CALCDATE(MthFormula,TODAY),0,'<Month Text,3> <Year4>');
END;

so I added a line inbetween to add a month so it works.

MthFormula := MthFormula + '+' +'1M';

I'm sure someone will pop in with a better code.
Where's Kriki the codemaster when you need him 8)

Now i'll go check the monthly totals to see if everything is peachy-keen!

& This drastically increases the reports performance

ara3n wrote:

Hello onaftergetrecord section can improved for performance with the following code.

FOR x := 1 TO 12 DO BEGIN
  SETFILTER("Date Filter",'%1..%2',StartDate,EndDate);
  CALCFIELDS("Sales (LCY)");
  SalesMthTotal[x] := "Sales (LCY)";
  
  StartDate := CALCDATE('-1M',StartDate);
  EndDate := CALCDATE('-1M',EndDate);

  StartDate := CALCDATE('CM + 1D - 1M',StartDate);
  EndDate := CALCDATE('CM',EndDate);

END;

gulamdastagir

how to do this for a Form This is really urgent

Considering a form doesnt have CREATETOTALS

i tried addding the code on ONAFTERGETRECORD TRIGGER BUT ITS ](*,)

Savatage

do you have form 351?

Another form that calculates using the cust ledger entry is form
10007 "Customer Credit Information"

It's splits into 4 periods but it can be easily changed to 12 months instead.
If i have some time later I'll try it myself. If you don't have that form I renamed it and you and try it yourself here
http://savatage99.googlepages.com/Form5 ... naform.fob

now that I look more into it I think 351 kicks more butt.
let me know if you don't have it.

divyesh10

i m Doing the Same report but i want the user to enter Startdate and EndDate can i get some help