Multiple Group Totals in Report

DHAMILTON5 · 2006-02-04

Got a report I need to create for an auditor and wondering if anyone has created anything similar or has any ideas on the best way to approach.

The report will be based off of the Item and Item Ledger Entry tables. For each item I'll want to report the total quantity on hand by Variant, Location and Bin. Anytime Item No., Variant, Location or Bin has a change in value I'll need to show the total quantity for that group. Would look something like this....

Item No.........Variant.........Location...........Bin.........Qty.
12345............XYZ................01..................A............10
12345............XYZ................01..................B............12
12345............XYZ................02..................A............20
12345............ABC................01..................A............15
TOTALS...................................................................57

Never tried writing a report with this many group totals before and am wondering if I can accomplish this easily by adding some keys and using the GroupTotals property on the item ledger entry data item, or am I better off just writing my own code to achieve this.

Any thoughts or suggestions are much appreciated.

kriki · 2006-02-04

I would read the Open Item Ledger Entries, put them as totals in a temptable, then print the temtable: to create the temptable, the code is something like this:

recItemLedgerEntries.RESET;
recItemLedgerEntries.SETCURRENTKEY(Open);
IF recItemLedgerEntries.FIND('-') THEN
  REPEAT
    tmpItemLedgerEntries.RESET;
    tmpItemLedgerEntries.SETCURRENTKEY("Item No.","Variant Code"); // this is an index I am sure exists and if location and bin are not in it, no problem
    tmpItemLedgerEntries.SETRANGE("Item No.",
      recItemLedgerEntries."Item No.");
    tmpItemLedgerEntries.SETRANGE("Variant Code",
       recItemLedgerEntries."Variant Code");
    tmpItemLedgerEntries.SETRANGE("Location Code",
       recItemLedgerEntries."Location Code");
    tmpItemLedgerEntries.SETRANGE("Bin Code",
       recItemLedgerEntries."Bin Code");
    IF NOT tmpItemLedgerEntries.FIND('-') THEN BEGIN
      tmpItemLedgerEntries := recItemLedgerEntries;
      tmpItemLedgerEntries.Quantity := 0;
      tmpItemLedgerEntries.INSERT(FALSE);
    END;
    tmpItemLedgerEntries.Quantity += recItemLedgerEntries."Remaining Quantity"; // total per item+variant+location+bin
    tmpItemLedgerEntries.MODIFY(FALSE);
    IF NOT tmpItem.GET(tmpItemLedgerEntries."Item No.") THEN BEGIN
      CLEAR(tmpItem);
      tmpItem."No." := tmpItemLedgerEntries."Item No.";
      tmpItem.INSERT(FALSE);
    END;
    tmpItem."Reorder Quantity" += recItemLedgerEntries."Remaining Quantity"; // total per item
    tmpItem.MODIFY(FALSE);
  UNTIL recItemLedgerEntries.NEXT = 0;

After this, all is in the temptable. And you can run your report on the temptable. How:
2 dataitems on integer: 1 for the items and 1 for the item ledger entries. The one for the item ledger entries must be indented by 1.

Item - OnPreDataItem()

tmpItem.RESET;
SETRANGE(Number,1,tmpItem.COUNT);

Item - OnAfterGetRecord()

IF Number = 1 THEN
  tmpItem.FIND('-')
ELSE
  tmpItem.NEXT;

Item Ledger Entry - OnPreDataItem()

tmpItemLedgerEntries.RESET;
tmpItemLedgerEntries.SETCURRENTKEY("Item No.");
tmpItemLedgerEntries.SETRANGE("Item No.",tmpItem."No:");
SETRANGE(Number,1,tmpItemLedgerEntries.COUNT);

Item Ledger Entry - OnAfterGetRecord()

IF Number = 1 THEN
  tmpItemLedgerEntries.FIND('-')
ELSE
  tmpItemLedgerEntries.NEXT;

DHAMILTON5 · 2006-02-04

Thank you, that's very helpful.

Just so I'm clear, how are you defining these?

recItemLedgerEntries
tmpItemLedgerEntries
tmpItem

kriki · 2006-02-04

recItemLedgerEntries : Table 32
tmpItemLedgerEntries : Table 32 but temptable
tmpItem : table 27 but temptable

mstevens_1964 · 2006-02-07

I need to write a report very similar to this, and have to admit I'm a complete novice to Navision development. I think I understand the code in this thread, but I’m really curious (and confused) how you set this all up. I apologize up front if I ask anything dumb here.

First, where it shows the code to create the temptable. How are you setting up the dataitems for this report? Is this report setup with Integer as the main dataitem, and then recItemLedgerEntries, tmpItemLedgerEntries and tmpItem are setup as global variables (with the property on the later two set to temporary)? I guess in short, what is the correct way to set this up when I’m looking at the dataitem page? And is this code in the PreDataItem or OnAfterGetRecord trigger?

Second, where it discusses running the report on the temptable, and how that’s done I’m a little confused. Is this done is this done in the same report that the temptables are created? If so, again I’m not quite understanding how the DataItems would be setup for that.

Sorry for the newbie questions, but any clarification that can be provided is appreciated.

Mark

kriki · 2006-02-07

Hello mstevens_1964.
I created a small example that I saved as text and put it under here. You can copy the code, put it in a text-file and import it into Navision. So you can easily see how it works. I hope it will make things clearer.

OBJECT Report 99999 report on temptable
{
  OBJECT-PROPERTIES
  {
    Date=07/02/06;
    Time=19:12:10;
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
    OnPreReport=BEGIN
                  FillUpTheTemptable();
                END;

  }
  DATAITEMS
  {
    { PROPERTIES
      {
        DataItemTable=Table2000000026;
        DataItemTableView=SORTING(Number);
        DataItemVarName=Item;
        OnPreDataItem=BEGIN
                        tmpItem.RESET;

                        RESET;
                        SETRANGE(Number,1,tmpItem.COUNT);
                      END;

        OnAfterGetRecord=BEGIN
                           IF Number = 1 THEN
                             tmpItem.FIND('-')
                           ELSE
                             tmpItem.NEXT;
                         END;

      }
      SECTIONS
      {
        { PROPERTIES
          {
            SectionType=Header;
            PrintOnEveryPage=Yes;
            SectionWidth=18150;
            SectionHeight=1692;
          }
          CONTROLS
          {
            { 1000000001;Label  ;0    ;0    ;7500 ;423  ;FontSize=8;
                                                         FontBold=Yes;
                                                         CaptionML=ENU=Item Ledger Entry }
            { 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 }
          }
           }
        { PROPERTIES
          {
            SectionType=Header;
            PrintOnEveryPage=Yes;
            SectionWidth=18150;
            SectionHeight=846;
          }
          CONTROLS
          {
            { 1000000009;Label  ;0    ;0    ;1500 ;846  ;ParentControl=1000000008;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
            { 1000000013;Label  ;3300 ;0    ;1500 ;846  ;ParentControl=1000000010;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
            { 1000000012;Label  ;1650 ;0    ;1500 ;846  ;ParentControl=1000000011;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
          }
           }
      }
       }
    { PROPERTIES
      {
        DataItemIndent=1;
        DataItemTable=Table2000000026;
        DataItemTableView=SORTING(Number);
        DataItemVarName=ItemLedgerEntry;
        OnPreDataItem=BEGIN
                        tmpItemLedgerEntry.RESET;
                        tmpItemLedgerEntry.SETCURRENTKEY("Item No.");
                        tmpItemLedgerEntry.SETRANGE("Item No.",tmpItem."No.");

                        RESET;
                        SETRANGE(Number,1,tmpItemLedgerEntry.COUNT);
                      END;

        OnAfterGetRecord=BEGIN
                           IF Number = 1 THEN
                             tmpItemLedgerEntry.FIND('-')
                           ELSE
                             tmpItemLedgerEntry.NEXT;
                         END;

      }
      SECTIONS
      {
        { PROPERTIES
          {
            SectionType=Body;
            SectionWidth=18150;
            SectionHeight=423;
          }
          CONTROLS
          {
            { 1000000008;TextBox;0    ;0    ;1500 ;423  ;HorzAlign=Left;
                                                         CaptionML=ENU=No.;
                                                         SourceExpr=tmpItemLedgerEntry."Item No." }
            { 1000000010;TextBox;3300 ;0    ;1500 ;423  ;HorzAlign=Left;
                                                         CaptionML=ENU=Quantity;
                                                         SourceExpr=tmpItemLedgerEntry.Quantity }
            { 1000000011;TextBox;1650 ;0    ;1500 ;423  ;HorzAlign=Left;
                                                         CaptionML=ENU=Location Code;
                                                         SourceExpr=tmpItemLedgerEntry."Location Code" }
          }
           }
        { PROPERTIES
          {
            SectionType=Footer;
            SectionWidth=18150;
            SectionHeight=846;
          }
          CONTROLS
          {
            { 1000000007;Label  ;0    ;0    ;1500 ;423  ;CaptionML=ENU=TOTALS }
            { 1000000014;TextBox;3300 ;0    ;1500 ;423  ;HorzAlign=Left;
                                                         CaptionML=ENU=Quantity;
                                                         SourceExpr=tmpItem."Reorder Quantity" }
          }
           }
      }
       }
  }
  REQUESTFORM
  {
    PROPERTIES
    {
      Width=9020;
      Height=3410;
    }
    CONTROLS
    {
    }
  }
  CODE
  {
    VAR
      tmpItemLedgerEntry@1000000000 : TEMPORARY Record 32;
      tmpItem@1000000001 : TEMPORARY Record 27;

    PROCEDURE FillUpTheTemptable@1000000000();
    VAR
      LrecItemLedgerEntry@1000000000 : Record 32;
    BEGIN
      // FillUpTheTemptable
      // This procedure fills up the temptable
      // I am just making a total of "Remaining Quantity" per item no. per location

      LrecItemLedgerEntry.RESET;
      IF LrecItemLedgerEntry.SETCURRENTKEY(Open) THEN ; // in case no index exists, no error is generated
      IF LrecItemLedgerEntry.FIND('-') THEN
        REPEAT
          tmpItemLedgerEntry.RESET;
          tmpItemLedgerEntry.SETCURRENTKEY("Item No.");
          tmpItemLedgerEntry.SETRANGE("Item No.",LrecItemLedgerEntry."Item No.");
          tmpItemLedgerEntry.SETRANGE("Location Code",LrecItemLedgerEntry."Location Code");
          IF NOT tmpItemLedgerEntry.FIND('-') THEN BEGIN
            tmpItemLedgerEntry := LrecItemLedgerEntry;
            tmpItemLedgerEntry.Quantity := 0;
            tmpItemLedgerEntry.INSERT(FALSE);
          END;
          tmpItemLedgerEntry.Quantity += LrecItemLedgerEntry."Remaining Quantity"; // total per item+location
          tmpItemLedgerEntry.MODIFY(FALSE);

          IF NOT tmpItem.GET(tmpItemLedgerEntry."Item No.") THEN BEGIN
            CLEAR(tmpItem);
            tmpItem."No." := tmpItemLedgerEntry."Item No.";
            tmpItem.INSERT(FALSE);
          END;

          tmpItem."Reorder Quantity" += LrecItemLedgerEntry."Remaining Quantity"; // total per item
          tmpItem.MODIFY(FALSE);
        UNTIL LrecItemLedgerEntry.NEXT = 0;
    END;

    BEGIN
    END.
  }
}

ara3n · 2006-02-07

if you are in version 3.7 or 4.0 you can use warehouse entry table. And you can build this report using the wizard. No need for temporary table. Plus item ledger doesn't have bin in these versions.

Howdy, Stranger!

Quick Links

Categories

Multiple Group Totals in Report

Comments