Unique Items Report - Shows only 1 instance of item purchase

emulsified · 2009-01-20

I need to create a report that will allow me to choose a Customer, Enter Posting Dates, and output a report that shows every item they every purchased. There is a catch though, if they purchased for example:

Customer: C001235
Posting Dates: 12/28/2008..01/01/2009

12/28/2008 ITEM123
12/29/2008 ITEM123
01/01/2009 ITEM456

When the report outputs I only want 1 instance for each item purchased regardless of the date like this:

Unique Items Report
Customer: C001235
Posting Dates: 12/28/2008..01/01/2009

ITEM123
ITEM456

I assume I might use one of the "Lines" tables like "Posted Sales Invoice Lines" or such but I would probably need to use a temporary table at some point which I'm not very good at creating reports based on temporary tables so I'm a bit stuck here.

Can anyone show me some sample report code to get me started?

kapamarou · 2009-01-20

I would use the following:

First Data Item: Customer
Second Data Item: Item Ledger Entry (Using a key that starts with Item No. and contains Customer No.)

Then I would output my Item details in a Group footer causing section record for each Item (and you can utilize also the totals).

If you have a Key that starts with Customer, Item No, Posting Date then you'll need only One dataitem. (Item ledger entry is an example. You could use also sales invoice line, return receipt line etc...)

You can do this also with many dataitems if you don't have a suitable key and you want to avoid creating one...

kapamarou · 2009-01-20

Sorry... You could look at report 313 Vendor/Item Purchases and 113 Customer/Item Sales

emulsified · 2009-01-20

I don't have reports 313 or 113 unfortunately.

kapamarou · 2009-01-20

Which version of Nav do you use?

emulsified · 2009-01-20

NAV 4.0 SP3

You have to forgive me but I'm a bit slower in the morning, it's 9:00am here in Pennsylvania and a big day in history.

I put together a report how you suggested but can't seem to get all the pieces right.

Here is my report so far:

OBJECT Report 50047 Customer Unique Items Report
{
  OBJECT-PROPERTIES
  {
    Date=01/20/09;
    Time=[ 8:59:48 AM];
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
  }
  DATAITEMS
  {
    { PROPERTIES
      {
        DataItemTable=Table18;
        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=Customer;
                                                                    ESM=Cliente;
                                                                    FRC=Client;
                                                                    ENC=Customer] }
            { 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    ;1500 ;846  ;ParentControl=1000000011;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
            { 1000000015;Label  ;1650 ;0    ;4500 ;846  ;ParentControl=1000000014;
                                                         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 = Customer.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    ;1500 ;423  ;HorzAlign=Left;
                                                         SourceExpr="No." }
            { 1000000014;TextBox;1650 ;0    ;4500 ;423  ;HorzAlign=Left;
                                                         SourceExpr=Name }
          }
           }
        { PROPERTIES
          {
            SectionType=GroupFooter;
            SectionWidth=18150;
            SectionHeight=0;
            OnPreSection=BEGIN
                           IF NOT FooterPrinted THEN
                             LastFieldNo := CurrReport.TOTALSCAUSEDBY;
                           CurrReport.SHOWOUTPUT := NOT FooterPrinted;
                           FooterPrinted := TRUE;
                         END;

          }
          CONTROLS
          {
          }
           }
      }
       }
    { PROPERTIES
      {
        DataItemIndent=1;
        DataItemTable=Table32;
      }
      SECTIONS
      {
        { PROPERTIES
          {
            SectionType=Body;
            SectionWidth=18150;
            SectionHeight=846;
          }
          CONTROLS
          {
          }
           }
        { PROPERTIES
          {
            SectionType=GroupFooter;
            SectionWidth=18150;
            SectionHeight=846;
          }
          CONTROLS
          {
            { 1000000000;TextBox;0    ;0    ;3150 ;423  ;SourceExpr="Item Ledger Entry"."Item No." }
          }
           }
      }
       }
  }
  REQUESTFORM
  {
    PROPERTIES
    {
      Width=9020;
      Height=3410;
    }
    CONTROLS
    {
    }
  }
  CODE
  {
    VAR
      LastFieldNo@1000000000 : Integer;
      FooterPrinted@1000000001 : Boolean;

    BEGIN
    END.
  }
}

kapamarou · 2009-01-20

ok...

Go to the Item Ledger Entry DataItem properties and add:

DataItemTableView -> SORTING(Source Type,Source No.,Item No.,Variant Code,Posting Date) WHERE(Source Type=CONST(Customer))

DataItemLink-> Source No.=FIELD(No.)
GroupTotalFields -> Item No.

In your Item Ledger Entry Body add the Item No. with simple font.

In your Item Ledger Entry Group Footer add the Item No. with Bold font.

You'll see the body repeating the items for all entries and the footer showing once for each item.

then remove the body and you are ok....

It's the main picture....

emulsified · 2009-01-20

\:D/ Perfect!

Thanks for your help.

kapamarou · 2009-01-20

You're welcome...

Keep in mind one thing though.

By using a certain key for sorting, you can create reports like this using a single dataitem. But try to use existing keys instead of creating new keys, for performance reasons... Many times I personally find it better to create a report that has several dataitems and might be a bit "heavy" than creating new keys in large tables like "Item ledger Entry"

Just some additional info...

Unique Items Report - Shows only 1 instance of item purchase

Answers

Categories