Group Header by month

twallace_sentinel · 2007-10-03

I have to create a report wherein I group ValueEntry records by month. To clarify, I need a January group, a February group, etc. for all entries within a date range. I will be summarizing quantities and amounts for items for each month.

I'm not certain where to start for creating the goup by month. Any pointers?

Tim

Alex_Chow · 2007-10-03

Use the Date table as your first dataitem, then Value Entry.

You can group by month using the Date table.

twallace_sentinel · 2007-10-03

Alex, thank you for your reply. I'm afraid I am new to Navision (not programming though, been doing this for over 12 years), so if you could give a little more step-by-step assistance, it would be greatly appreicated.

In the interim, I will begin to hack away with what you've provided.

Thanks,

Tim

Alex_Chow · 2007-10-03

Something like this:

OBJECT Report 50035 Item Sales by Month
{
  OBJECT-PROPERTIES
  {
    Date=10/03/07;
    Time=[ 1:40:06 PM];
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
    CaptionML=ENU=Item Sales by Month;
    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);
        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");
                           HisTotAmt := 0;
                           HisTotQty := 0;
                         END;

        ReqFilterFields=Starting Date;
        GroupTotalFields=Starting Date;
      }
      SECTIONS
      {
        { PROPERTIES
          {
            SectionType=GroupFooter;
            SectionWidth=18150;
            SectionHeight=846;
            OnPreSection=BEGIN
                           IF (("Item Ledger Entry"."Sales Amount (Actual)" + HisTotAmt) = 0) AND
                              ((HisTotQty - "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)" + 

HisTotAmt }
            { 1000000030;TextBox;12150;0    ;1500 ;423  ;DecimalPlaces=0:5;
                                                         SourceExpr=HisTotQty - "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;
      HisTotAmt@1000000003 : Decimal;
      HisTotQty@1000000004 : Decimal;
      FilterString@1000000005 : Text[150];
      FilterString2@1000000006 : Text[150];
      MiscFun@1000000007 : Codeunit 50009;

    BEGIN
    END.
  }
}

[/quote]

Howdy, Stranger!

Quick Links

Categories

Group Header by month

Comments