Grouping and Summing (Reports)

oioi007 · 2008-07-10

Hi there..

I've done Group total fields with only DATE and amount, but now I want to add Salesperson Code there, how can I make it?

01/01/01

PS........ 1500
JR........ 3000

4500

Something like that...

Im using only 1 dataitem (Cust.Ledger.Entry)
please help

Revolution1210 · 2008-07-10

For grouping and group totals to work correctly you need to select a key (DataItemTableView) on the data item in question, which contains the fields you want to create group totals for.

oioi007 · 2008-07-10

DataItemTableView:SORTING(Salesperson Code,Posting Date)

I know that, but what I gonna do next?

Revolution1210 · 2008-07-10

oioi007 wrote:

DataItemTableView:SORTING(Salesperson Code,Posting Date)

I know that, but what I gonna do next?

Enter the field(s) you wish to group by in the GroupTotalFields property.

Enter the field(s) you wish to total, e.g. Remaining Amount, in the TotalFields property

Add group header and group footer sections with your total fields as appropriate.

Hope this helps

oioi007 · 2008-07-10

What I have now is that:

01/01/01 100
01/01/01 100
02/02/02 50

01/01/01 200
02/02/02 50

Now imagine

01/01/01 PS 100
01/01/01 PS 200
01/01/01 JR 50

in Result I want

01/01/01 PS 300
JR 50
group both: dates and Salespersons and ofcourse total sums.
Do you understand me?

oioi007 · 2008-07-10

Someone Please..

Sandeep_Prajapati · 2008-07-11

hi oioi007,

1) Create a key in table - Customer ledger Entry [Posting Date,Salesperson Code]
2) take the following Report object ... O:) .. and do the necessary formatting

OBJECT Report 90002 Grouping & Summing
{
  OBJECT-PROPERTIES
  {
    Date=07/11/08;
    Time=[ 1:32:51 PM];
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
  }
  DATAITEMS
  {
    { PROPERTIES
      {
        DataItemTable=Table21;
        DataItemTableView=SORTING(Posting Date,Salesperson Code)
                          ORDER(Ascending);
        OnPreDataItem=BEGIN
                        LastFieldNo := FIELDNO("Salesperson Code");
                      END;

        TotalFields=Amount;
        GroupTotalFields=Posting Date,Salesperson Code;
      }
      SECTIONS
      {
        { PROPERTIES
          {
            SectionType=Header;
            PrintOnEveryPage=Yes;
            SectionWidth=18150;
            SectionHeight=1692;
          }
          CONTROLS
          {
            { 1102753001;Label  ;0    ;0    ;7500 ;423  ;FontSize=8;
                                                         FontBold=Yes;
                                                         CaptionML=ENU=Cust. Ledger Entry }
            { 1102753002;TextBox;15000;0    ;3150 ;423  ;HorzAlign=Right;
                                                         SourceExpr=FORMAT(TODAY,0,4) }
            { 1102753003;TextBox;0    ;423  ;7500 ;423  ;SourceExpr=COMPANYNAME }
            { 1102753004;TextBox;17700;423  ;450  ;423  ;CaptionML=ENU=Page;
                                                         SourceExpr=CurrReport.PAGENO }
            { 1102753005;Label  ;16950;423  ;750  ;423  ;ParentControl=1102753004 }
            { 1102753006;TextBox;15900;846  ;2250 ;423  ;HorzAlign=Right;
                                                         SourceExpr=USERID }
          }
           }
        { PROPERTIES
          {
            SectionType=Header;
            PrintOnEveryPage=Yes;
            SectionWidth=18150;
            SectionHeight=1269;
          }
          CONTROLS
          {
            { 1102753015;Label  ;600  ;0    ;1650 ;1269 ;VertAlign=Bottom;
                                                         FontSize=9;
                                                         FontBold=Yes;
                                                         MultiLine=Yes;
                                                         CaptionML=ENU=Posting Date }
            { 1102753018;Label  ;2400 ;0    ;2250 ;1269 ;ParentControl=1102753017;
                                                         VertAlign=Bottom;
                                                         FontSize=9;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
            { 1102753021;Label  ;4950 ;0    ;1800 ;846  ;VertAlign=Bottom;
                                                         FontSize=9;
                                                         FontBold=Yes;
                                                         MultiLine=Yes;
                                                         CaptionML=ENU=Amount }
          }
           }
        { PROPERTIES
          {
            SectionType=GroupHeader;
            SectionWidth=18150;
            SectionHeight=0;
            OnPreSection=BEGIN
                           CurrReport.SHOWOUTPUT := FooterPrinted;
                           FooterPrinted := FALSE;
                         END;

          }
          CONTROLS
          {
          }
           }
        { PROPERTIES
          {
            SectionType=GroupHeader;
            SectionWidth=18150;
            SectionHeight=846;
            OnPreSection=BEGIN
                           CurrReport.SHOWOUTPUT :=
                             CurrReport.TOTALSCAUSEDBY = "Cust. Ledger Entry".FIELDNO("Posting Date");
                         END;

          }
          CONTROLS
          {
            { 1102753008;TextBox;750  ;423  ;1200 ;423  ;HorzAlign=Left;
                                                         SourceExpr="Posting Date" }
          }
           }
        { PROPERTIES
          {
            SectionType=GroupFooter;
            SectionWidth=18150;
            SectionHeight=423;
            OnPreSection=BEGIN
                           CurrReport.SHOWOUTPUT :=
                             CurrReport.TOTALSCAUSEDBY = "Cust. Ledger Entry".FIELDNO("Salesperson Code");
                         END;

          }
          CONTROLS
          {
            { 1102753023;TextBox;4500 ;0    ;1800 ;423  ;HorzAlign=Right;
                                                         SourceExpr=Amount }
            { 1102753017;TextBox;2550 ;0    ;1500 ;423  ;HorzAlign=Left;
                                                         SourceExpr="Salesperson Code" }
          }
           }
      }
       }
  }
  REQUESTFORM
  {
    PROPERTIES
    {
      Width=9020;
      Height=3410;
    }
    CONTROLS
    {
    }
  }
  CODE
  {
    VAR
      LastFieldNo@1102753000 : Integer;
      FooterPrinted@1102753001 : Boolean;
      TotalFor@1102753002 : TextConst 'ENU="Total for "';

    BEGIN
    END.
  }
}

oioi007 · 2008-07-11

Sandeep Prajapati

Thanks a lot.. I got it

TOTALSCAUSEDBY and SHOWOUTPUT solved the problem ;-)

bangswit · 2010-06-23

could you teach me how to summary per month?

Howdy, Stranger!

Quick Links

Categories

Grouping and Summing (Reports)

Answers