Report - Horizontal Columns using arrays

TFCrowtherTFCrowther Member Posts: 35
edited 2013-12-05 in NAV Tips & Tricks
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

Comments

  • ara3nara3n Member Posts: 9,255
    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;
    
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • SavatageSavatage Member Posts: 7,142
    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?
  • ara3nara3n Member Posts: 9,255
    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.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • SavatageSavatage Member Posts: 7,142
    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;
    
  • gulamdastagirgulamdastagir Member Posts: 411
    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 ](*,)
    Regards,

    GD
  • SavatageSavatage Member Posts: 7,142
    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.
  • divyesh10divyesh10 Member Posts: 71
    i m Doing the Same report but i want the user to enter Startdate and EndDate can i get some help
Sign In or Register to comment.