REPORT using Sales Invoice Line with Group By "No.&quot

emulsified

I know what I want.
A simple report based on Sales Invoice Line table that will show only a single instance of "No." in the report even if it shows up multiple times. Explained in simpler terms: I want to provide the Type=Item, Posting Date=Whatever, Quantity=0 and just show me what was invoiced at ZERO in a DISTINCT fashion no matter what the date range happens to be.

When I run this report I am setting the following on the request form:

Type = Item
Posting Date = 02/01/07..02/28/07
Quantity = 0

I'm pretty sure I know what steps need to be done just not how to do each step or so I think so anyway.

I believe I need to create a TEMPORARY TABLE and somewhere in a "OnPre" section I need to write some code that takes my input from the request form then somehow ONLY INSERT into the TEMPORARY TABLE when the value for "No." DOES NOT exist in the TEMPORARY TABLE. I believe this will leave me with a TEMPORARY TABLE full of lines/records that are DISTINCT.

If Navision Native DB C/SIDE had a SELECT DISTINCT type function for Native DB then I wouldn't be so confused.

I have already tried creating a KEY on the Sales Invoice Line table and grouping by it, that did not work at all.

Can someone please help me out? Explanation of how and I will be grateful. Some code or an actual object with ID set to 50111 and I will be forever grateful, heck I may even send a gift out.

Any help would be great. Thanks.

Savatage

I have already tried creating a KEY on the Sales Invoice Line table and grouping by it, that did not work at all.

How did that not work? If you added a new key of "No." you'll will be able to create a group for it using the report wizard. "Tabular type". I know my posted sales line table is very large adding another key would really make it huge

more info...
http://www.mibuso.com/forum/viewtopic.php?t=6773

i4tost

Create a key starting with "No.".
Sort this dataitem by using this key.
Then at the begining of OnAfterGetRecord triger put a code
SETRANGE("No.","No.");
FIND('+');
SETRANGE("No.");

emulsified

Forgot to use TABULAR type that's why I didn't see it.

Okay I have created a new report but it seems like it runs forever, it never actually stopped after about 5 minutes of letting it run with the following in the request form:

Posting Date = 02/02/07..02/26/07
Type = Item
Quantity = 0


Here is the code:

OBJECT Report 50111 Sales Inv. Line DISTINCT by No
{
  OBJECT-PROPERTIES
  {
    Date=06/09/07;
    Time=[ 2:54:46 PM];
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
  }
  DATAITEMS
  {
    { PROPERTIES
      {
        DataItemTable=Table113;
        DataItemTableView=SORTING(No.);
        OnPreDataItem=BEGIN
                        LastFieldNo := FIELDNO("No.");
                      END;

        ReqFilterFields=No.;
        GroupTotalFields=No.;
      }
      SECTIONS
      {
        { PROPERTIES
          {
            SectionType=Header;
            PrintOnEveryPage=Yes;
            SectionWidth=18150;
            SectionHeight=1692;
          }
          CONTROLS
          {
            { 1000000001;Label  ;0    ;0    ;7500 ;423  ;FontSize=8;
                                                         FontBold=Yes;
                                                         CaptionML=[ENU=Sales Invoice Line;
                                                                    ESM=Hist¢rico l¡n. factura venta;
                                                                    FRC=Ligne facture vente;
                                                                    ENC=Sales Invoice Line] }
            { 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=1269;
          }
          CONTROLS
          {
            { 1000000012;Label  ;0    ;0    ;300  ;846  ;ParentControl=1000000011;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
            { 1000000015;Label  ;450  ;0    ;1500 ;846  ;ParentControl=1000000014;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
            { 1000000018;Label  ;2100 ;0    ;4500 ;846  ;ParentControl=1000000017;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
          }
           }
        { PROPERTIES
          {
            SectionType=GroupHeader;
            SectionWidth=18150;
            SectionHeight=846;
            OnPreSection=BEGIN
                           CurrReport.SHOWOUTPUT := FooterPrinted;
                           FooterPrinted := FALSE;
                         END;

          }
          CONTROLS
          {
          }
           }
        { PROPERTIES
          {
            SectionType=GroupHeader;
            SectionWidth=18150;
            SectionHeight=423;
            OnPreSection=BEGIN
                           CurrReport.SHOWOUTPUT :=
                             CurrReport.TOTALSCAUSEDBY = "Sales Invoice Line".FIELDNO("No.");
                         END;

          }
          CONTROLS
          {
            { 1000000008;TextBox;3150 ;0    ;1500 ;423  ;HorzAlign=Left;
                                                         SourceExpr="No." }
            { 1000000009;Label  ;0    ;0    ;3000 ;423  ;ParentControl=1000000008 }
          }
           }
        { PROPERTIES
          {
            SectionType=GroupHeader;
            SectionWidth=18150;
            SectionHeight=423;
            OnPreSection=BEGIN
                           CurrReport.SHOWOUTPUT :=
                             CurrReport.TOTALSCAUSEDBY = LastFieldNo;
                         END;

          }
          CONTROLS
          {
          }
           }
        { PROPERTIES
          {
            SectionType=Body;
            SectionWidth=18150;
            SectionHeight=423;
          }
          CONTROLS
          {
            { 1000000011;TextBox;0    ;0    ;300  ;423  ;HorzAlign=Left;
                                                         SourceExpr=Type }
            { 1000000014;TextBox;450  ;0    ;1500 ;423  ;HorzAlign=Left;
                                                         SourceExpr="No." }
            { 1000000017;TextBox;2100 ;0    ;4500 ;423  ;HorzAlign=Left;
                                                         SourceExpr=Description }
          }
           }
        { PROPERTIES
          {
            SectionType=GroupFooter;
            SectionWidth=18150;
            SectionHeight=0;
            OnPreSection=BEGIN
                           IF NOT FooterPrinted THEN
                             LastFieldNo := CurrReport.TOTALSCAUSEDBY;
                           CurrReport.SHOWOUTPUT := NOT FooterPrinted;
                           FooterPrinted := TRUE;
                         END;

          }
          CONTROLS
          {
          }
           }
      }
       }
  }
  REQUESTFORM
  {
    PROPERTIES
    {
      Width=9020;
      Height=3410;
    }
    CONTROLS
    {
    }
  }
  CODE
  {
    VAR
      LastFieldNo@1000000000 : Integer;
      FooterPrinted@1000000001 : Boolean;

    BEGIN
    END.
  }
}

Savatage

if your Sales invoice Line is anything like mine
(4,207,215 Records) it's not gonna be a fast report.

did you make your new key
No.,Posting Date ?

emulsified

No. I made it just "No."

Would it be faster to set some keys, ranges, filters, etc.. loop through them extracting "No." = 0 writing it the TEMP table and then at the end showing it on the report? Wouldn't the Navision C/SIDE Native DB INSERT act just like a SQL "INSERT IGNORE" statement?

I wish this was faster. The only values I care to know about are the "No." and its Quantity by whatever filters I specify of course.

Savatage

I haven't tried this but if I were going the "add a new key" route.

I would add the fields that you would be using as your main filters.

Item , Posting Date , Type or maybe Type,Item No,Posting Date.

I'm assuming your trying to get something like

Item ABC
-2/12/2008 = 12 outs
-3/14/2008 = 10 outs
-4/15/2008 = 9 outs

You can always delete the key later if it doesn;y give you what you need.

This is the kind of report I love Crystal Reports for =P~

have you tried the report with just todays date to see it's speed before jumping right into feb 2007 n such?

Also that link to reports & temp tables is good.