Displaying the months on my report

asembereng · 2008-01-18

Hey guys i need help on a report that i am redesigning.. What this report does is searches a a table called contribution, there is a field called "date to" and the date format is of "31/01/1997" the report converts this to "January 1997" but if there is no contribution for a particular month it won't be entered on the contribution table but yet still the report is suppose to display that report and a zero amount as contribution. for example

Date to contributions

10/01/1997 105
11/02/1997 455
23/04/1997 89
12/12/1997 600

Assuming that is the contribution table.. the report is suppose to display the following below.

January 1997 105
February 1997 455
March 1997 0
April 1997 89
May 1997 0
June 1997 0
July 1997 0
August 1997 0
September 1997 0
October 1997 0
November 1997 0
December 1997 600

Even though the months with zeros were not found on the contribution table it still has to show on the report. The months with contributions not zero are working perfect on the report but those with the zeros i need help on. Please help me on this. Its urgent.
Thanks

Savatage · 2008-01-18

here's a little kicka$$ report that breakdown a customer sales by month using array.

Cust#123 jansales decsales novsales octsales sepsales etc.

you can easily change this report to use the tables you are using
http://www.mibuso.com/forum/viewtopic.php?t=10082

See it (Cust Name removed for privacy) *sample*
http://savatage99.googlepages.com/Custo ... yMonth.pdf

Try it
http://savatage99.googlepages.com/Repor ... yMonth.fob

Alex_Chow · 2008-01-18

<EDIT>

Alex_Chow · 2008-01-18

You mean like this?

OBJECT Report 50035 Item Sales by Month
{
  OBJECT-PROPERTIES
  {
    Date=01/18/08;
    Time=10:42:29 AM;
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
    CaptionML=ENU=Item Sales by Month w/SBT;
    OnPreReport=BEGIN
                  FilterString := Item.GETFILTERS;
                  FilterString2 := "Accounting Period".GETFILTERS;
                END;

  }
  DATAITEMS
  {
    { PROPERTIES
      {
        DataItemTable=Table27;
        PrintOnlyIfDetail=Yes;
        OnPreDataItem=BEGIN
                        CurrReport.CREATETOTALS("Item Ledger Entry"."Sales Amount (Actual)","Item Ledger Entry".Quantity);
                      END;

        ReqFilterFields=No.;
      }
      SECTIONS
      {
        { PROPERTIES
          {
            SectionType=Header;
            PrintOnEveryPage=Yes;
            SectionWidth=18150;
            SectionHeight=1692;
          }
          CONTROLS
          {
            { 1000000001;Label  ;0    ;0    ;7500 ;423  ;FontSize=8;
                                                         FontBold=Yes;
                                                         CaptionML=ENU=Item Sales by Month }
            { 1000000002;TextBox;15000;0    ;3150 ;423  ;HorzAlign=Right;
                                                         SourceExpr=FORMAT(TODAY,0,4) }
            { 1000000003;TextBox;0    ;423  ;7500 ;423  ;SourceExpr=COMPANYNAME }
            { 1000000004;TextBox;17700;423  ;450  ;423  ;CaptionML=ENU=Page;
                                                         SourceExpr=CurrReport.PAGENO }
            { 1000000005;Label  ;16950;423  ;750  ;423  ;ParentControl=1000000004 }
            { 1000000006;TextBox;15900;846  ;2250 ;423  ;HorzAlign=Right;
                                                         SourceExpr=USERID }
            { 1000000035;Label  ;0    ;1269 ;11400;423  ;CaptionML=ENU=Sales figures include credit memos, SBT History, line discounts and invoice discounts }
          }
           }
        { PROPERTIES
          {
            SectionType=Header;
            SectionWidth=18150;
            SectionHeight=423;
            OnPreSection=BEGIN
                           CurrReport.SHOWOUTPUT(CurrReport.PAGENO = 1);
                         END;

          }
          CONTROLS
          {
            { 1000000034;Label  ;0    ;0    ;6300 ;423  ;CaptionML=ENU="Excl.: Quantity=0 .and. Sales Amount=0" }
          }
           }
        { PROPERTIES
          {
            SectionType=Header;
            SectionWidth=18150;
            SectionHeight=423;
            OnPreSection=BEGIN
                           CurrReport.SHOWOUTPUT((CurrReport.PAGENO = 1) AND (FilterString <> ''));
                         END;

          }
          CONTROLS
          {
            { 1000000032;TextBox;0    ;0    ;18150;423  ;SourceExpr=FilterString }
          }
           }
        { PROPERTIES
          {
            SectionType=Header;
            SectionWidth=18150;
            SectionHeight=423;
            OnPreSection=BEGIN
                           CurrReport.SHOWOUTPUT((CurrReport.PAGENO = 1) AND (FilterString2 <> ''));
                         END;

          }
          CONTROLS
          {
            { 1000000033;TextBox;0    ;0    ;18150;423  ;SourceExpr=FilterString2 }
          }
           }
        { PROPERTIES
          {
            SectionType=Header;
            PrintOnEveryPage=Yes;
            SectionWidth=18150;
            SectionHeight=846;
          }
          CONTROLS
          {
            { 1000000009;Label  ;0    ;0    ;1500 ;423  ;ParentControl=1000000008;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
            { 1000000012;Label  ;1650 ;0    ;4500 ;423  ;ParentControl=1000000011;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes;
                                                         CaptionML=ENU=Description }
            { 1000000019;Label  ;16050;0    ;2100 ;846  ;ParentControl=1000000018;
                                                         HorzAlign=Right;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes;
                                                         CaptionML=ENU=Sales Amount }
            { 1000000007;Label  ;7500 ;0    ;1200 ;846  ;ParentControl=1000000000;
                                                         HorzAlign=Right;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
            { 1000000013;Label  ;10500;0    ;1500 ;846  ;ParentControl=1000000010;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
            { 1000000017;Label  ;12150;0    ;1500 ;846  ;ParentControl=1000000016;
                                                         HorzAlign=Right;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes;
                                                         CaptionML=ENU=Quantity }
            { 1000000024;Label  ;13950;0    ;1800 ;846  ;ParentControl=1000000023;
                                                         HorzAlign=Right;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes;
                                                         CaptionML=ENU=Unit Price after All Discount }
            { 1000000026;Label  ;8850 ;0    ;1500 ;846  ;ParentControl=1000000015;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes;
                                                         CaptionML=ENU=Customer No. }
          }
           }
        { PROPERTIES
          {
            SectionType=Body;
            SectionWidth=18150;
            SectionHeight=423;
          }
          CONTROLS
          {
            { 1000000008;TextBox;0    ;0    ;1500 ;423  ;HorzAlign=Left;
                                                         SourceExpr="No." }
            { 1000000011;TextBox;1650 ;0    ;6600 ;423  ;HorzAlign=Left;
                                                         SourceExpr=Description + "Description 2" }
          }
           }
      }
       }
    { PROPERTIES
      {
        DataItemIndent=1;
        DataItemTable=Table50;
        DataItemTableView=SORTING(Starting Date);
        PrintOnlyIfDetail=Yes;
        OnPreDataItem=BEGIN
                        CurrReport.CREATETOTALS("Item Ledger Entry"."Sales Amount (Actual)","Item Ledger Entry".Quantity);
                      END;

        OnAfterGetRecord=BEGIN
                           BeginDate := "Starting Date";
                           EndDate := CALCDATE('+CM',"Starting Date");
                         END;

        ReqFilterFields=Starting Date;
        GroupTotalFields=Starting Date;
      }
      SECTIONS
      {
        { PROPERTIES
          {
            SectionType=GroupFooter;
            SectionWidth=18150;
            SectionHeight=846;
            OnPreSection=BEGIN
                           IF ("Item Ledger Entry"."Sales Amount (Actual)" = 0) AND
                              ("Item Ledger Entry".Quantity = 0) THEN
                             CurrReport.SHOWOUTPUT(FALSE);
                         END;

          }
          CONTROLS
          {
            { 1000000020;TextBox;10650;0    ;1200 ;423  ;SourceExpr=EndDate }
            { 1000000021;Label  ;8250 ;0    ;2400 ;423  ;ParentControl=1000000020;
                                                         HorzAlign=Left;
                                                         FontSize=8;
                                                         FontBold=Yes;
                                                         MultiLine=Yes;
                                                         LeaderDots=Yes;
                                                         CaptionML=ENU="Total For " }
            { 1000000022;TextBox;16050;0    ;2100 ;423  ;SourceExpr="Item Ledger Entry"."Sales Amount (Actual)" }
            { 1000000030;TextBox;12150;0    ;1500 ;423  ;DecimalPlaces=0:5;
                                                         SourceExpr="Item Ledger Entry"."Invoiced Quantity" }
          }
           }
      }
       }
    { PROPERTIES
      {
        DataItemIndent=2;
        DataItemTable=Table32;
        DataItemTableView=SORTING(Entry Type,Item No.,Variant Code,Drop Shipment,Location Code,Posting Date)
                          WHERE(Entry Type=CONST(Sale));
        OnPreDataItem=BEGIN
                        SETRANGE("Posting Date",BeginDate,EndDate);

                        CurrReport.CREATETOTALS("Sales Amount (Actual)","Invoiced Quantity");
                      END;

        OnAfterGetRecord=BEGIN
                           CALCFIELDS("Sales Amount (Actual)");
                           IF "Invoiced Quantity" <> 0 THEN
                             UnitPrice := "Sales Amount (Actual)" / "Invoiced Quantity"
                           ELSE
                             UnitPrice := 0;
                         END;

        DataItemLinkReference=Item;
        DataItemLink=Item No.=FIELD(No.);
      }
      SECTIONS
      {
        { PROPERTIES
          {
            SectionType=Body;
            SectionWidth=18150;
            SectionHeight=423;
          }
          CONTROLS
          {
            { 1000000000;TextBox;7500 ;0    ;1200 ;423  ;SourceExpr="Posting Date" }
            { 1000000010;TextBox;10500;0    ;1500 ;423  ;HorzAlign=Left;
                                                         SourceExpr="Document No." }
            { 1000000016;TextBox;12150;0    ;1500 ;423  ;DecimalPlaces=0:5;
                                                         SourceExpr="Invoiced Quantity" }
            { 1000000018;TextBox;16050;0    ;2100 ;423  ;SourceExpr="Sales Amount (Actual)" }
            { 1000000023;TextBox;13950;0    ;1800 ;423  ;SourceExpr=-UnitPrice }
            { 1000000015;TextBox;8850 ;0    ;1500 ;423  ;HorzAlign=Left;
                                                         SourceExpr="Source No." }
          }
           }
      }
       }
  }
  REQUESTFORM
  {
    PROPERTIES
    {
      Width=9020;
      Height=3410;
    }
    CONTROLS
    {
    }
  }
  CODE
  {
    VAR
      UnitPrice@1000000000 : Decimal;
      BeginDate@1000000001 : Date;
      EndDate@1000000002 : Date;
      FilterString@1000000005 : Text[150];
      FilterString2@1000000006 : Text[150];

    BEGIN
    END.
  }
}

asembereng · 2008-01-18

I did some modifications to it, it picks the contributions and repeating the values throughout for an employee when its only suppose to display lets say a value different from zero for months the employee contributed and zero for months he didn't contribute.. What do you think is my problem?

kolaboy · 2008-01-19

Hi, i have exactly the same problem. I have use you sample report and tested it. I want it do the same thing its doing for the years(Horizontally). But i don't know how to get the months run vertically. For example;

2007 2008
Jan. 105 105
Feb. 0 200
Mar 200 0
Apr. 0 200
May 108 0
Jun. 230 0
Jul. 0 205
Aug. 100 109
Sept. 300 200
Oct. 105 0
Nov. 0 300
Dec. 320 105

The ideas of the report is exactly the same as explain by asembereng initially. I need the format as above. Here is the report i modify.

OBJECT Report 50007 Customer 1Yr Sales By Month
{
  OBJECT-PROPERTIES
  {
    Date=19/01/08;
    Time=17:12:04;
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
    TopMargin=1000;
    BottomMargin=500;
    LeftMargin=500;
    RightMargin=500;
    Orientation=Landscape;
  }
  DATAITEMS
  {
    { PROPERTIES
      {
        DataItemTable=Table5207;
        OnPreDataItem=BEGIN
                        MonthLabel[1] := FORMAT(010107D,0,'<Year4>');

                        FOR x := 2 TO 13 DO BEGIN
                          MthFormula := '+' + FORMAT(x) + 'Y';
                          MonthLabel[x] := FORMAT(CALCDATE(MthFormula,010106D),0,'<Year4>');
                        END;

                        //CurrReport.CREATETOTALS(SalesMthTotal);
                        //CurrReport.CREATETOTALS("Balance (LCY)");
                      END;

        OnAfterGetRecord=BEGIN
                           CLEAR(SalesMthTotal);

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

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

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

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

                           END;
                         END;

      }
      SECTIONS
      {
        { PROPERTIES
          {
            SectionType=Header;
            PrintOnEveryPage=Yes;
            SectionWidth=27500;
            SectionHeight=2115;
          }
          CONTROLS
          {
            { 1000000019;TextBox;8550 ;1269 ;1500 ;423  ;HorzAlign=Center;
                                                         SourceExpr=MonthLabel[1] }
            { 1000000020;TextBox;10050;1269 ;1500 ;423  ;HorzAlign=Center;
                                                         SourceExpr=MonthLabel[2] }
            { 1000000021;TextBox;11550;1269 ;1500 ;423  ;HorzAlign=Center;
                                                         SourceExpr=MonthLabel[3] }
            { 1000000022;TextBox;13050;1269 ;1500 ;423  ;HorzAlign=Center;
                                                         SourceExpr=MonthLabel[4] }
            { 1000000023;TextBox;14550;1269 ;1500 ;423  ;HorzAlign=Center;
                                                         SourceExpr=MonthLabel[5] }
            { 1000000024;TextBox;16050;1269 ;1500 ;423  ;HorzAlign=Center;
                                                         SourceExpr=MonthLabel[6] }
            { 1000000025;TextBox;17550;1269 ;1500 ;423  ;HorzAlign=Center;
                                                         SourceExpr=MonthLabel[7] }
            { 1000000026;TextBox;19050;1269 ;1500 ;423  ;HorzAlign=Center;
                                                         SourceExpr=MonthLabel[8] }
            { 1000000027;TextBox;20550;1269 ;1500 ;423  ;HorzAlign=Center;
                                                         SourceExpr=MonthLabel[9] }
            { 1000000028;TextBox;22050;1269 ;1500 ;423  ;HorzAlign=Center;
                                                         SourceExpr=MonthLabel[10] }
            { 1000000029;TextBox;23550;1269 ;1500 ;423  ;HorzAlign=Center;
                                                         SourceExpr=MonthLabel[11] }
            { 1000000030;TextBox;25050;1269 ;1500 ;423  ;HorzAlign=Center;
                                                         SourceExpr=MonthLabel[12] }
            { 1000000032;Shape  ;0    ;1692 ;27450;423  ;BorderWidth=3pt;
                                                         ShapeStyle=HorzLine }
            { 1000000046;TextBox;25050;846  ;1500 ;423  ;HorzAlign=Right;
                                                         SourceExpr=USERID }
            { 1000000047;TextBox;25800;423  ;750  ;423  ;CaptionML=ENU=Page;
                                                         SourceExpr=CurrReport.PAGENO }
            { 1000000048;TextBox;25500;0    ;1050 ;423  ;HorzAlign=Right;
                                                         SourceExpr=TIME;
                                                         Format=<Hours12>:<Minutes,2> <AM/PM> }
            { 1000000049;Label  ;25050;423  ;750  ;423  ;ParentControl=1000000047;
                                                         HorzAlign=Right }
            { 1000000050;TextBox;22050;0    ;3300 ;423  ;HorzAlign=Right;
                                                         SourceExpr=FORMAT(TODAY,0,4) }
            { 1000000051;Label  ;0    ;0    ;7500 ;423  ;FontSize=8;
                                                         FontBold=Yes;
                                                         CaptionML=ENU=Customer 1Yr Sales By Month }
            { 1000000053;Label  ;0    ;423  ;7500 ;423  ;CaptionML=ENU=BI-LO DISTRIBUTORS }
          }
           }
        { PROPERTIES
          {
            SectionType=Body;
            SectionWidth=27500;
            SectionHeight=423;
          }
          CONTROLS
          {
            { 1000000000;TextBox;8550 ;0    ;1500 ;423  ;SourceExpr=SalesMthTotal[1] }
            { 1000000001;TextBox;10050;0    ;1500 ;423  ;SourceExpr=SalesMthTotal[2] }
            { 1000000002;TextBox;11550;0    ;1500 ;423  ;SourceExpr=SalesMthTotal[3] }
            { 1000000003;TextBox;13050;0    ;1500 ;423  ;SourceExpr=SalesMthTotal[4] }
            { 1000000004;TextBox;14550;0    ;1500 ;423  ;SourceExpr=SalesMthTotal[5] }
            { 1000000005;TextBox;16050;0    ;1500 ;423  ;SourceExpr=SalesMthTotal[6] }
            { 1000000006;TextBox;17550;0    ;1500 ;423  ;SourceExpr=SalesMthTotal[7] }
            { 1000000007;TextBox;19050;0    ;1500 ;423  ;SourceExpr=SalesMthTotal[8] }
            { 1000000008;TextBox;25050;0    ;1500 ;423  ;SourceExpr=SalesMthTotal[12] }
            { 1000000009;TextBox;23550;0    ;1500 ;423  ;SourceExpr=SalesMthTotal[11] }
            { 1000000010;TextBox;22050;0    ;1500 ;423  ;SourceExpr=SalesMthTotal[10] }
            { 1000000011;TextBox;20550;0    ;1500 ;423  ;SourceExpr=SalesMthTotal[9] }
          }
           }
        { PROPERTIES
          {
            SectionType=GroupFooter;
            SectionWidth=27500;
            SectionHeight=0;
          }
          CONTROLS
          {
          }
           }
        { PROPERTIES
          {
            SectionType=Footer;
            SectionWidth=27500;
            SectionHeight=846;
          }
          CONTROLS
          {
            { 1000000018;TextBox;8550 ;423  ;1500 ;423  ;SourceExpr=SalesMthTotal[1] }
            { 1000000031;TextBox;10050;423  ;1500 ;423  ;SourceExpr=SalesMthTotal[2] }
            { 1000000033;TextBox;11550;423  ;1500 ;423  ;SourceExpr=SalesMthTotal[3] }
            { 1000000034;TextBox;13050;423  ;1500 ;423  ;SourceExpr=SalesMthTotal[4] }
            { 1000000035;TextBox;14550;423  ;1500 ;423  ;SourceExpr=SalesMthTotal[5] }
            { 1000000036;TextBox;16050;423  ;1500 ;423  ;SourceExpr=SalesMthTotal[6] }
            { 1000000037;TextBox;17550;423  ;1500 ;423  ;SourceExpr=SalesMthTotal[7] }
            { 1000000038;TextBox;19050;423  ;1500 ;423  ;SourceExpr=SalesMthTotal[8] }
            { 1000000039;TextBox;20550;423  ;1500 ;423  ;SourceExpr=SalesMthTotal[9] }
            { 1000000040;TextBox;22050;423  ;1500 ;423  ;SourceExpr=SalesMthTotal[10] }
            { 1000000041;TextBox;23550;423  ;1500 ;423  ;SourceExpr=SalesMthTotal[11] }
            { 1000000042;TextBox;25050;423  ;1500 ;423  ;SourceExpr=SalesMthTotal[12] }
            { 1000000043;Shape  ;5850 ;0    ;21150;423  ;BorderWidth=1pt;
                                                         ShapeStyle=HorzLine }
          }
           }
      }
       }
  }
  REQUESTFORM
  {
    PROPERTIES
    {
      Width=9020;
      Height=3410;
    }
    CONTROLS
    {
    }
  }
  CODE
  {
    VAR
      MonthLabel@1000000000 : ARRAY [13] OF Text[30];
      x@1000000001 : Integer;
      MthFormula@1000000002 : Text[30];
      SalesMthTotal@1000000003 : ARRAY [12] OF Decimal;
      StartDate@1000000004 : Date;
      EndDate@1000000005 : Date;
      CustLedEntry@1000000006 : Record 21;
      CompanyInformation@1000000007 : Record 79;

    BEGIN
    END.
  }
}

The report format should be for every employee. When you run the report by employee No. We should have a report in the above format for each year. We can have up to 13 years on one page.
Any ideas. I need you help in this please.
Thanks.[/quote]

asembereng · 2008-01-20

I did the modifications and is working and i was always also able to solve the repeated values i was having initially but now the problem i am having is the size of the array on your report is fix, what is the possibility of it being dynamic, the size increases depending on the date rate? What i am trying to say is you might might have an employee you have contributed for more than a year, how can the report be redesigned to accommodate such?

And also is possible to write a different report but having the same functionality with the first. This report will have a date range, then query the date table then it queries the contribution table and iterate and obtain months that are not in the contribution table for a specific employee and list these months and display zero amount contribution next to it.... How possible is this?? Can someone help on this??

asembereng · 2008-01-21

I truly need help help on this please.. can some make my day??

Howdy, Stranger!

Quick Links

Categories

Displaying the months on my report

Comments