Options

Report based on Salesperson/Customer/Value Entry acting odd

emulsifiedemulsified Member Posts: 139
I have a report with the following structure:

Salesperson
Customer
Value Entry

The report has always worked very well. Let me say that while running the report I specify the following criteria:

Salesperson TAB
Salesperson Code = AJ

Customer TAB
No. = C123456

Value Entry TAB
Posting Date = 03/01/12..03/31/12
Item No. = *@SALE*|A* (these are our sale items)

When I run the above the report is missing SALE items that appear on a Posted Sales Invoice posted on 03/21/12, not all of the items on that invoice are showing up on the report.

If I run the report for a shorter date range like 03/21/12 or 03/21/12..03/22/12 all of the SALE items that appear on the Posted Sales Invoice will also show on this report correctly.

If I run the report for the intended date range Posting Date = 03/01/12..03/31/12 and also specify the Document No. = 120321017 (Posted Invoice#) under the Value Entry TAB all of the items will show up like they're supposed to.

I have looked up the lines in the Item Ledger Entry and Value Entry tables just to verify they look normal and they are.

Now the only different thing that we did this month was back posting invoices using the WORK DATE setting in the Navision Clients. We took great care to make sure that Document Date, Posting Date, Order Date were the same when posting these invoices.

Is Navision having some sort of issue with applying it's filters? I'm not understanding this. Can anyone shed some light onto this. We are on the native database version 4.0 SP3.


Report:
OBJECT Report 10049 Cust./Item Stat. by Salespers.
{
  OBJECT-PROPERTIES
  {
    Date=07/07/10;
    Time=[ 3:36:51 PM];
    Modified=Yes;
    Version List=NAVUS3.70;
  }
  PROPERTIES
  {
    CaptionML=[ENU=Cust./Item Stat. by Salespers.;
               ESM=Estad. cliente/prod. por vendedor;
               FRC=Statistiques articles/client par repr‚sentant;
               ENC=Cust./Item Stat. by Salespers.];
    TopMargin=200;
    BottomMargin=200;
    LeftMargin=200;
    OnPreReport=BEGIN
                  CompanyInformation.GET;
                  FilterString := "Salesperson/Purchaser".GETFILTERS;
                  FilterString2 := Customer.GETFILTERS;
                  FilterString3 := "Value Entry".GETFILTERS;
                  CASE SalespersonToUse OF
                    SalespersonToUse::"Assigned To Customer":
                      SalespersonString := Text002;
                    SalespersonToUse::"Assigned To Sales Order":
                      SalespersonString := Text003;
                    ELSE
                      ERROR(Text001);
                  END;
                END;

  }
  DATAITEMS
  {
    { PROPERTIES
      {
        DataItemTable=Table13;
        DataItemTableView=SORTING(Code);
        PrintOnlyIfDetail=Yes;
        ReqFilterHeadingML=[ENU=Salesperson;
                            ESM=Vendedor;
                            FRC=Repr‚sentant;
                            ENC=Salesperson];
        OnPreDataItem=BEGIN
                        CurrReport.CREATETOTALS("Value Entry"."Sales Amount (Actual)",Profit,"Value Entry"."Discount Amount");
                      END;

        OnAfterGetRecord=BEGIN
                           CurrReport.NEWPAGEPERRECORD := OnlyOnePerPage;
                         END;

        ReqFilterFields=Code,Name;
      }
      SECTIONS
      {
        { PROPERTIES
          {
            SectionType=Header;
            PrintOnEveryPage=Yes;
            SectionWidth=18150;
            SectionHeight=1692;
          }
          CONTROLS
          {
            { 1001;Label        ;0    ;0    ;7500 ;423  ;FontSize=8;
                                                         FontBold=Yes;
                                                         CaptionML=[ENU=Customer/Item Statistics by Salesperson;
                                                                    ESM=Estad¡sticas cliente/producto por vendedor;
                                                                    FRC=Statistiques client/article par repr‚sentant;
                                                                    ENC=Customer/Item Statistics by Salesperson] }
            { 1002;TextBox      ;14850;0    ;2100 ;423  ;HorzAlign=Right;
                                                         SourceExpr=FORMAT(TODAY,0,4) }
            { 1003;TextBox      ;17100;0    ;1050 ;423  ;HorzAlign=Right;
                                                         SourceExpr=TIME;
                                                         Format=<Hours12>:<Minutes,2> <AM/PM> }
            { 1004;TextBox      ;0    ;423  ;7500 ;423  ;SourceExpr=CompanyInformation.Name }
            { 1005;Label        ;16950;423  ;750  ;423  ;ParentControl=1006;
                                                         HorzAlign=Right }
            { 1006;TextBox      ;17700;423  ;450  ;423  ;CaptionML=[ENU=Page;
                                                                    ESM=P gina;
                                                                    FRC=Page;
                                                                    ENC=Page];
                                                         SourceExpr=CurrReport.PAGENO }
            { 1007;TextBox      ;16650;846  ;1500 ;423  ;HorzAlign=Right;
                                                         SourceExpr=USERID }
          }
           }
        { PROPERTIES
          {
            SectionType=Header;
            PrintOnEveryPage=Yes;
            SectionWidth=18150;
            SectionHeight=423;
            OnPreSection=BEGIN
                           CurrReport.SHOWOUTPUT((CurrReport.PAGENO = 1) AND (FilterString <> ''));
                         END;

          }
          CONTROLS
          {
            { 9   ;TextBox      ;0    ;0    ;18150;423  ;FontBold=Yes;
                                                         SourceExpr="Salesperson/Purchaser".TABLECAPTION + ':  ' + FilterString }
          }
           }
        { PROPERTIES
          {
            SectionType=Header;
            PrintOnEveryPage=Yes;
            SectionWidth=18150;
            SectionHeight=423;
            OnPreSection=BEGIN
                           CurrReport.SHOWOUTPUT((CurrReport.PAGENO = 1) AND (FilterString2 <> ''));
                         END;

          }
          CONTROLS
          {
            { 10  ;TextBox      ;0    ;0    ;18150;423  ;SourceExpr=Customer.TABLECAPTION + ':  ' + FilterString2 }
          }
           }
        { PROPERTIES
          {
            SectionType=Header;
            PrintOnEveryPage=Yes;
            SectionWidth=18150;
            SectionHeight=423;
            OnPreSection=BEGIN
                           CurrReport.SHOWOUTPUT((CurrReport.PAGENO = 1) AND (FilterString3 <> ''));
                         END;

          }
          CONTROLS
          {
            { 11  ;TextBox      ;0    ;0    ;18150;423  ;SourceExpr="Value Entry".TABLECAPTION + ':  ' + FilterString3 }
          }
           }
        { PROPERTIES
          {
            SectionType=Header;
            PrintOnEveryPage=Yes;
            SectionWidth=18150;
            SectionHeight=846;
            OnPreSection=BEGIN
                           CurrReport.SHOWOUTPUT(CurrReport.PAGENO = 1);
                         END;

          }
          CONTROLS
          {
            { 1020000;TextBox   ;0    ;0    ;18150;423  ;SourceExpr=SalespersonString }
          }
           }
        { PROPERTIES
          {
            SectionType=Header;
            PrintOnEveryPage=Yes;
            SectionWidth=18150;
            SectionHeight=1692;
          }
          CONTROLS
          {
            { 12  ;Label        ;0    ;0    ;1650 ;423  ;ParentControl=27;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes }
            { 13  ;Label        ;3000 ;0    ;4500 ;423  ;ParentControl=28;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes }
            { 15  ;Label        ;300  ;423  ;1650 ;423  ;ParentControl=34;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes }
            { 16  ;Label        ;3300 ;423  ;4500 ;423  ;ParentControl=35;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes }
            { 17  ;Label        ;14400;423  ;1500 ;846  ;ParentControl=49;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes;
                                                         CaptionML=ENU=Price }
            { 20  ;Label        ;6150 ;846  ;2250 ;423  ;ParentControl=46;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes }
            { 21  ;Label        ;8550 ;846  ;3750 ;423  ;ParentControl=47;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes }
            { 22  ;Label        ;12750;423  ;1050 ;846  ;ParentControl=48;
                                                         HorzAlign=Right;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
            { 23  ;Label        ;16050;423  ;1800 ;846  ;ParentControl=50;
                                                         HorzAlign=Right;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes }
            { 1000000000;Label  ;450  ;846  ;1650 ;423  ;VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         CaptionML=ENU=Date }
            { 1000000003;Label  ;3450 ;846  ;2550 ;423  ;VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         CaptionML=ENU=Document Number }
          }
           }
        { PROPERTIES
          {
            SectionType=Body;
            SectionWidth=18150;
            SectionHeight=423;
          }
          CONTROLS
          {
            { 27  ;TextBox      ;0    ;0    ;1500 ;423  ;HorzAlign=Left;
                                                         FontSize=8;
                                                         FontBold=Yes;
                                                         CaptionML=[ENU=Salesperson;
                                                                    ESM=Vendedor;
                                                                    FRC=Repr‚sentant;
                                                                    ENC=Salesperson];
                                                         SourceExpr=Code }
            { 28  ;TextBox      ;3000 ;0    ;4500 ;423  ;FontSize=8;
                                                         FontBold=Yes;
                                                         CaptionML=[ENU=Salesperson Name;
                                                                    ESM=Nombre vendedor;
                                                                    FRC=Nom du repr‚sentant;
                                                                    ENC=Salesperson Name];
                                                         SourceExpr=Name }
          }
           }
        { PROPERTIES
          {
            SectionType=Footer;
            SectionWidth=18150;
            SectionHeight=1269;
            OnPreSection=BEGIN
                           "CalculateProfit%";
                         END;

          }
          CONTROLS
          {
            { 29  ;TextBox      ;15150;423  ;2700 ;423  ;FontSize=8;
                                                         FontBold=Yes;
                                                         SourceExpr="Value Entry"."Sales Amount (Actual)" }
            { 33  ;Label        ;3000 ;423  ;6600 ;423  ;FontSize=8;
                                                         FontBold=Yes;
                                                         PadChar=.;
                                                         CaptionML=[ENU=Report Totals;
                                                                    ESM=Informe totales;
                                                                    FRC=Rapport des totaux;
                                                                    ENC=Report Totals] }
          }
           }
      }
       }
    { PROPERTIES
      {
        DataItemIndent=1;
        DataItemTable=Table18;
        DataItemTableView=SORTING(Salesperson Code,No.);
        PrintOnlyIfDetail=Yes;
        OnPreDataItem=BEGIN
                        CASE SalespersonToUse OF
                          SalespersonToUse::"Assigned To Customer":
                            BEGIN
                              SETCURRENTKEY("Salesperson Code","No.");
                              SETRANGE("Salesperson Code","Salesperson/Purchaser".Code);
                            END;
                          SalespersonToUse::"Assigned To Sales Order":
                            BEGIN
                              SETCURRENTKEY("No.");
                              SETRANGE("Salesperson Code");
                            END;
                        END;

                        CurrReport.CREATETOTALS("Value Entry"."Sales Amount (Actual)",Profit,"Value Entry"."Discount Amount");
                      END;

        ReqFilterFields=No.,Search Name;
      }
      SECTIONS
      {
        { PROPERTIES
          {
            SectionType=Body;
            SectionWidth=18150;
            SectionHeight=1269;
          }
          CONTROLS
          {
            { 34  ;TextBox      ;300  ;423  ;1500 ;423  ;HorzAlign=Left;
                                                         CaptionML=[ENU=Customer No.;
                                                                    ESM=N§ cliente;
                                                                    FRC=Nø de client;
                                                                    ENC=Customer No.];
                                                         SourceExpr="No." }
            { 35  ;TextBox      ;3300 ;423  ;4500 ;423  ;CaptionML=[ENU=Customer Name;
                                                                    ESM=Nombre cliente;
                                                                    FRC=Nom du client;
                                                                    ENC=Customer Name];
                                                         SourceExpr=Name }
            { 36  ;TextBox      ;10050;423  ;2250 ;423  ;SourceExpr="Phone No." }
            { 37  ;TextBox      ;13950;423  ;3750 ;423  ;SourceExpr=Contact }
            { 38  ;Label        ;9000 ;423  ;900  ;423  ;FontBold=Yes;
                                                         CaptionML=[ENU=Phone:;
                                                                    ESM=Tel‚fono:;
                                                                    FRC=T‚l‚phone:;
                                                                    ENC=Phone:] }
            { 39  ;Label        ;12600;423  ;1200 ;423  ;FontBold=Yes;
                                                         CaptionML=[ENU=Contact:;
                                                                    ESM=Contacto:;
                                                                    FRC=Contact :;
                                                                    ENC=Contact:] }
            { 1000000004;Shape  ;0    ;423  ;18000;423  ;BorderColor=255;
                                                         BorderWidth=1pt }
          }
           }
        { PROPERTIES
          {
            SectionType=Footer;
            SectionWidth=18150;
            SectionHeight=1692;
            OnPreSection=BEGIN
                           "CalculateProfit%";
                         END;

          }
          CONTROLS
          {
            { 40  ;TextBox      ;0    ;423  ;1500 ;423  ;HorzAlign=Left;
                                                         SourceExpr="Salesperson/Purchaser".Code }
            { 41  ;TextBox      ;15150;423  ;2700 ;423  ;FontBold=Yes;
                                                         SourceExpr="Value Entry"."Sales Amount (Actual)" }
            { 45  ;Label        ;3000 ;423  ;5100 ;423  ;FontBold=Yes;
                                                         PadChar=.;
                                                         CaptionML=[ENU=Salesperson Totals;
                                                                    ESM=Totales vendedor;
                                                                    FRC=Totaux du repr‚sentant;
                                                                    ENC=Salesperson Totals] }
          }
           }
      }
       }
    { PROPERTIES
      {
        DataItemIndent=2;
        DataItemTable=Table5802;
        DataItemTableView=SORTING(Source Type,Source No.,Item Ledger Entry Type,Item No.,Posting Date)
                          WHERE(Source Type=CONST(Customer),
                                Item Ledger Entry Type=CONST(Sale),
                                Expected Cost=CONST(No));
        OnPreDataItem=BEGIN

                        CASE SalespersonToUse OF
                          SalespersonToUse::"Assigned To Customer":
                            SETRANGE("Salespers./Purch. Code");
                          SalespersonToUse::"Assigned To Sales Order":
                            SETRANGE("Salespers./Purch. Code","Salesperson/Purchaser".Code);
                        END;

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

        OnAfterGetRecord=BEGIN
                           // Added to show ONLY Items of chosen "Item Category Code" on Options form
                           Item.GET("Value Entry"."Item No.");

                           IF ItemCategoryCode <> '' THEN BEGIN
                             IF Item."Item Category Code" <> ItemCategoryCode THEN CurrReport.SKIP;
                           END;

                           Profit := "Sales Amount (Actual)" + "Cost Amount (Actual)";
                           "Discount Amount" := -"Discount Amount";
                         END;

        ReqFilterFields=Item No.,Posting Date,Sales Amount (Actual);
        GroupTotalFields=Item No.;
        DataItemLink=Source No.=FIELD(No.),
                     Global Dimension 1 Code=FIELD(Global Dimension 1 Filter),
                     Global Dimension 2 Code=FIELD(Global Dimension 2 Filter);
      }
      SECTIONS
      {
        { PROPERTIES
          {
            SectionType=GroupFooter;
            SectionWidth=18150;
            SectionHeight=423;
            OnPreSection=BEGIN
                           "CalculateProfit%";
                           IF NOT Item.GET("Item No.") THEN BEGIN
                             Item.Description  := 'Others';
                             Item."Base Unit of Measure" := '';
                           END;
                         END;

          }
          CONTROLS
          {
            { 46  ;TextBox      ;6150 ;0    ;2250 ;423  ;HorzAlign=Left;
                                                         CaptionML=[ENU=Item Number;
                                                                    ESM=N§ producto;
                                                                    FRC=Num‚ro d'article;
                                                                    ENC=Item Number];
                                                         SourceExpr="Item No." }
            { 47  ;TextBox      ;8550 ;0    ;3600 ;423  ;CaptionML=[ENU=Item Description;
                                                                    ESM=Descripci¢n producto;
                                                                    FRC=Description d'article;
                                                                    ENC=Item Description];
                                                         SourceExpr=Item.Description }
            { 48  ;TextBox      ;12750;0    ;1050 ;423  ;CaptionML=[ENU=Quantity;
                                                                    ESM=Cantidad;
                                                                    FRC=Quantit‚;
                                                                    ENC=Quantity];
                                                         SourceExpr=-"Invoiced Quantity" }
            { 49  ;TextBox      ;14400;0    ;1500 ;423  ;CaptionML=[ENU=Unit of Measure;
                                                                    ESM=Unidad medida;
                                                                    FRC=Unit‚ de mesure;
                                                                    ENC=Unit of Measure];
                                                         SourceExpr=Item."Unit Price" }
            { 50  ;TextBox      ;16050;0    ;1800 ;423  ;CaptionML=[ENU=Amount;
                                                                    ESM=Importe;
                                                                    FRC=Montant;
                                                                    ENC=Amount];
                                                         SourceExpr="Sales Amount (Actual)" }
            { 1000000001;TextBox;450  ;0    ;2250 ;423  ;HorzAlign=Left;
                                                         CaptionML=[ENU=Item Number;
                                                                    ESM=N§ producto;
                                                                    FRC=Num‚ro d'article;
                                                                    ENC=Item Number];
                                                         SourceExpr="Value Entry"."Posting Date" }
            { 1000000002;TextBox;3450 ;0    ;2100 ;423  ;HorzAlign=Left;
                                                         CaptionML=[ENU=Item Number;
                                                                    ESM=N§ producto;
                                                                    FRC=Num‚ro d'article;
                                                                    ENC=Item Number];
                                                         SourceExpr="Value Entry"."Document No." }
          }
           }
        { PROPERTIES
          {
            SectionType=Footer;
            SectionWidth=18150;
            SectionHeight=846;
            OnPreSection=BEGIN
                           "CalculateProfit%";
                         END;

          }
          CONTROLS
          {
            { 54  ;TextBox      ;300  ;423  ;1500 ;423  ;HorzAlign=Left;
                                                         SourceExpr=Customer."No." }
            { 55  ;TextBox      ;16050;423  ;1800 ;423  ;SourceExpr="Sales Amount (Actual)" }
            { 59  ;Label        ;3300 ;423  ;4800 ;423  ;MultiLine=No;
                                                         PadChar=.;
                                                         CaptionML=[ENU=Customer Totals;
                                                                    ESM=Totales cliente;
                                                                    FRC=Totaux du client;
                                                                    ENC=Customer Totals] }
          }
           }
      }
       }
  }
  REQUESTFORM
  {
    PROPERTIES
    {
      Width=7590;
      Height=2310;
      SaveValues=Yes;
    }
    CONTROLS
    {
      { 2   ;CheckBox     ;3410 ;550  ;330  ;440  ;ShowCaption=No;
                                                   SourceExpr=OnlyOnePerPage }
      { 3   ;Label        ;0    ;550  ;3300 ;440  ;ParentControl=2;
                                                   CaptionML=[ENU=New Page per Salesperson;
                                                              ESM=Nueva p g. por vendedor;
                                                              FRC=Nouvelle page par repr‚sentant;
                                                              ENC=New Page per Salesperson] }
      { 1020000;TextBox   ;3410 ;0    ;3740 ;440  ;CaptionML=[ENU=Salesperson To Use;
                                                              ESM=Vendedor que se utilizar ;
                                                              FRC=Repr‚sentant … utiliser;
                                                              ENC=Salesperson To Use];
                                                   OptionCaptionML=[ENU=Assigned To Customer,Assigned To Sales Order;
                                                                    ESM=Asignado a cliente,Asignado a pedido venta;
                                                                    FRC=Affect‚ … client,Affect‚ … document de vente;
                                                                    ENC=Assigned To Customer,Assigned To Sales Order];
                                                   OptionString=Assigned To Customer,Assigned To Sales Order;
                                                   SourceExpr=SalespersonToUse }
      { 1020001;Label     ;0    ;0    ;3300 ;440  ;ParentControl=1020000 }
      { 1000000000;Label  ;0    ;1100 ;3300 ;440  ;CaptionML=ENU=Item Category Code }
      { 1000000001;TextBox;3410 ;1100 ;3740 ;440  ;DropDown=No;
                                                   BlankZero=Yes;
                                                   SourceExpr=ItemCategoryCode;
                                                   TableRelation="Item Category".Code }
    }
  }
  CODE
  {
    VAR
      FilterString@1000 : Text[250];
      FilterString2@1001 : Text[250];
      FilterString3@1002 : Text[250];
      Profit@1003 : Decimal;
      "Profit%"@1004 : Decimal;
      OnlyOnePerPage@1005 : Boolean;
      CompanyInformation@1007 : Record 79;
      SalespersonToUse@1020000 : 'Assigned To Customer,Assigned To Sales Order';
      SalespersonString@1020001 : Text[250];
      Text001@1020002 : TextConst 'ENU=Invalid option chosen for Salesperson To Use.;ESM=Opci¢n no v lida para Vendedor que se utilizar .;FRC=Option invalide choisie pour le repr‚sentant … utiliser.;ENC=Invalid option chosen for Salesperson To Use.';
      Text002@1020003 : TextConst 'ENU=Individual sale shows under the Salesperson assigned to that Customer.;ESM=Cada venta se muestra en el vendedor asignado a ese cliente.;FRC=La vente individuelle s''affiche sous le repr‚sentant attribu‚ … ce client.;ENC=Individual sale shows under the Salesperson assigned to that Customer.';
      Text003@1020004 : TextConst 'ENU=Individual sale shows under the Salesperson assigned to that individual Sales Order.;ESM=Cada venta se muestra en el vendedor asignado al correspondiente pedido de venta.;FRC=La vente individuelle s''affiche sous le repr‚sentant attribu‚ … ce document de vente individuel.;ENC=Individual sale shows under the Salesperson assigned to that individual Sales Order.';
      Item@1000000000 : Record 27;
      ItemCategoryCode@1000000001 : Text[90];

    PROCEDURE "CalculateProfit%"@1();
    BEGIN
      IF "Value Entry"."Sales Amount (Actual)" <> 0 THEN
        "Profit%" := ROUND(100 * Profit / "Value Entry"."Sales Amount (Actual)",0.1)
      ELSE
        "Profit%" := 0;
    END;

    BEGIN
    END.
  }
}
Half-empy or half-full how do you view your database?

Thanks.

Comments

  • Options
    mohana_cse06mohana_cse06 Member Posts: 5,504
    the reason may be you are grouping the Value entry table on Item No. so 03/21/12 entries may be grouping with someother entries..
  • Options
    emulsifiedemulsified Member Posts: 139
    That doesn't make sense to me. To be clearer on what is happening:

    Lets say I have one invoice # 120321017 posted on 03/21/12 with these items:

    1204SALE
    1234SALE
    5678SALE
    9012SALE

    When running the report with the date range 03/01/12..03/31/12 only these would show up from the invoice 120321017:

    1204SALE
    5678SALE

    When running the report with the date range 03/21/12 all of these show up:

    1204SALE
    1234SALE
    5678SALE
    9012SALE

    Normally everything shows up. I have never had this happen before. How do I fix this?
    Half-empy or half-full how do you view your database?

    Thanks.
  • Options
    BernardJBernardJ Member Posts: 57
    For the Value Entry dataitem I see you are using this key:
    DataItemTableView=SORTING(Source Type,Source No.,Item Ledger Entry Type,Item No.,Posting Date)
    
    This does not include the field "Discount Amount".
    However, is it possible in any way that this field is added to the key later?

    In this case the change of the plus minus sign could give this effect.
  • Options
    emulsifiedemulsified Member Posts: 139
    Not that I am aware of. Where would it be added besides the report? I don't think that any of those tables being Item Ledger Entry, Item Entry, etc have a key with that field on it.

    Wouldn't the key need to be set implicitly for it to affect the output? It isn't set anywhere in the report itself that I can see.

    This report has always worked very well. The only reason I think this is happening has something to do with the fact that we posted invoices (back-dating) using the WORK DATE in Navision and every invoice posted has the same date for posting date as order date, and document date when they were posted.

    In someone's previous reply they mentioned being grouped with something else which I don't think is possible. If I run the same report with a single date then everything with "SALE" in the item number shows up how it is supposed to.

    I'm puzzled here. The only thing I can think of is that Navision isn't catching everything it should with the filter on the ITEM tab due to the back posting effecting it somehow.
    Half-empy or half-full how do you view your database?

    Thanks.
  • Options
    BernardJBernardJ Member Posts: 57
    This can happen though, let's say in the key window of table 5802 you have this key:
    Source Type,Source No.,Item Ledger Entry Type,Item No.,Posting Date
    
    It happened to be a suitable key for this report (or maybe it's just created for the purpose of it), so this key is used in the DataItemTableView.

    Now somebody modified this key later for other purposes, then it could look like this:
    Source Type,Source No.,Item Ledger Entry Type,Item No.,Posting Date,Discount Amount
    
    Now when you run the report, the dataitem behaves like it is using the new key, even though the old key is still mentioned in the dataitem properties. Even if you export the object in text format (like above post) the old key is still used there. Only in the case that there is no key at all that starts with the same fields as the one mentioned in the properties, the report will fail.

    Although it does not match with your statement that the report always worked very well, i cannot see why this report won't work apart from this reason; all records that match the filter 03/21/12 should match to 03/01/12..03/31/12 as well.
    emulsified wrote:
    In someone's previous reply they mentioned being grouped with something else which I don't think is possible. If I run the same report with a single date then everything with "SALE" in the item number shows up how it is supposed to.
    Did you try to add a body section to see what happens?
  • Options
    emulsifiedemulsified Member Posts: 139
    BernardJ wrote:
    For the Value Entry dataitem I see you are using this key:
    DataItemTableView=SORTING(Source Type,Source No.,Item Ledger Entry Type,Item No.,Posting Date)
    
    This does not include the field "Discount Amount".
    However, is it possible in any way that this field is added to the key later?

    In this case the change of the plus minus sign could give this effect.


    The problem does seem to be SORTING. Since I want the output from the Value Entry table to show the invoice numbers in order by Posting Date would it work if I added a new key:

    Posting Date,Source Type,Source No.,Item Ledger Entry Type,Item No.

    Then changed the SORTING to:
    DataItemTableView=SORTING(Posting Date,Source Type,Source No.,Item Ledger Entry Type,Item No.)
    
    Half-empy or half-full how do you view your database?

    Thanks.
  • Options
    emulsifiedemulsified Member Posts: 139
    I just added a new KEY with the Posting Date first and changed the SORT of Value Entry on the report to match the new key.

    I ran a few test reports vs the old version of the report. Guess what? The old version of the report that uses the key with the Posting Date last fails to get all entries per the user entered filter criteria under the Value Entry tab. In other words I enter "*@SALE|A*&quot; under the Value Entry tab whichs filter out our sale items and the old report is missing some of those items that were definitely invoiced and show up if you specify just a single Posting Date rather than a range. The new report with the new key catches every one of the lines and it's much faster.

    Why is this the case?
    Half-empy or half-full how do you view your database?

    Thanks.
  • Options
    BernardJBernardJ Member Posts: 57
    To find a definitive answer to give I need more info, eg all keys present in the Value Entry table. I would still say there is a key that starts with "Source Type,Source No.,Item Ledger Entry Type,Item No.,Posting Date" with more fields at the end of it.
    Regarding the speed is that the fields where you filter on just a few unique items should be at the beginning of the key. See this topic as well: viewtopic.php?f=23&t=44264

    I see the problem is solved with a new key, but in case you needed the first key for specific view of the report, you could also try to avoid altering the dataitem. To do this, create a global decimal gDecDiscountAmount, and replace
    "Discount Amount" := -"Discount Amount";
    
    with
    gDecDiscountAmount := -"Discount Amount";
    
    Besides, why is this field "Discount Amount" not on any section? If you don't need it, why (re)calculate it?
  • Options
    emulsifiedemulsified Member Posts: 139
    BernardJ wrote:
    To find a definitive answer to give I need more info, eg all keys present in the Value Entry table. I would still say there is a key that starts with "Source Type,Source No.,Item Ledger Entry Type,Item No.,Posting Date" with more fields at the end of it.
    Regarding the speed is that the fields where you filter on just a few unique items should be at the beginning of the key. See this topic as well: viewtopic.php?f=23&t=44264

    I see the problem is solved with a new key, but in case you needed the first key for specific view of the report, you could also try to avoid altering the dataitem. To do this, create a global decimal gDecDiscountAmount, and replace
    "Discount Amount" := -"Discount Amount";
    
    with
    gDecDiscountAmount := -"Discount Amount";
    
    Besides, why is this field "Discount Amount" not on any section? If you don't need it, why (re)calculate it?

    See attached image for a view of the keys on my Value Entry table.

    I added the last key with the Posting Date first and it fixed my report as I mentioned previously, it makes sense to me that this worked because my filtering criteria was in the following levels: Salesperson->Customer->Value Entry

    Salesperson Code specified = Data filtered to Salesperson level
    Customer = No filtering so all customers in Data filtered to Salesperson level above (untouched no filtering on Customer)
    Value Entry = Data filtered all Customers to Salesperson Assigned and SORTED by my new KEY which the first field is Posting Date of Value Entry

    Am I wrong in doing it this way? It works great. Any better way to modify this report?
    Half-empy or half-full how do you view your database?

    Thanks.
  • Options
    BernardJBernardJ Member Posts: 57
    Hi,

    tonight i did a thorough look at this report, and came to the conlusion that Mohana was right on the grouping. Looks like I got it wrong myself in previous posts...

    Let's work this out:
    Suppose you have these entries on 03/21/12
    item 1233SALE document A
    item 1234SALE document A
    and an entry on 03/22/12 for item 1234SALE, document B

    Now we run this report with both dates in the filter.
    Since the (initial) sorting of the report was first Item No. and then Posting Date, AND the grouptotalfield is set to Item No., item 1234SALE will show up only once on the groupfooter, showing a date of 03/22/12, and its corresponding document no (B). Its amount will be the total of 03/21 and 03/22 (more than one document). There is no reporting for item 1234SALE on 03/21/12.
    Item 1233SALE will show up once as well, but this time with a correct date.
    If you filter this report only on 03/21/12 then of course the corresponding entries will show up, neatly grouped on that one date. (but still showing wrong data if there are more documents on that date!)

    To correct this, and to show all entries of one document together, you need to modify the key so that Ddocument No comes before Item No, and also include document no in the GroupTotalFields. To avoid double groupfooters at the end of a document, add this code to the OnPreSection of the groupfooter:
    CurrReport.SHOWOUTPUT := CurrReport.TOTALSCAUSEDBY = "Value Entry".FIELDNO("Item No.");
    
    It can be assumed that all entries of one document are on the same posting date, so posting date doesn't need to be a grouptotalfield.

    There are more fields before Item No. in your first key, but all of them are filtered out on one value (Source Type=Customer, Source No=Customer No, Item Ledger Entry Type=Sale), so these fields are not causing any problems. Actually, for best performance it is best if these fields are kept at the beginning of the key.
    Since you are filtering on posting date, the most ideal key in my opinion would be SourceType,SourceNo,ItemLedgerEntryType,PostingDate,DocumentNo,ItemNo
Sign In or Register to comment.