Issue on Item Age Composition Qty Report

user123
user123 Member Posts: 22
edited 2010-08-03 in NAV/Navision Classic Client
Hi,

My client needs to run the item ageing report for a back date, then they compare that with the standard inventory valuation report. There is no issue in running the valuation report for a back date, but an issue arises when the standard item age composition quantity report is run for a back date #-o It does not calculate correct values for age buckets. This is because it considers applied entries until the current date, regardless of the given back date!!! :(

We have developed a report using item ledger entry and item application entry tables, but that was not so successful..The client urgently needs this report done :( and we have tried every possible way..All items should be issued on FIFO basis..

Do you have any suggestions of resolving this issue?? Would appreciate if someone can provide guidance on how to develop this report..

Thanks

Comments

  • Alex_Chow
    Alex_Chow Member Posts: 5,063
    You will need to reuse the way inventory valuation is calculated and use that for each timeframe bucket. Needless to say, the report will take a looong time to run...
    Confessions of a Dynamics NAV Consultant = my blog
    AP Commerce, Inc. = where I work

    Getting Started with Dynamics NAV 2013 Application Development = my book
    Implementing Microsoft Dynamics NAV - 3rd Edition = my 2nd book
  • user123
    user123 Member Posts: 22
    Thanks a lot for the reply!!

    Pls clarify how the method used in inventory valuation can be used for the ageing report. According to the client requirement, items should be issued on FIFO basis.

    Pls consider the scenario below;

    04/01/09- Purchased 100 units
    04/30/09- Consumed 50 units

    05/01/09- Purchased 50 units
    05/20/09- Consumed 10 units
    05/30/09- Consumed 10 units

    06/01/09- Purchased 20 units
    06/30/09- Comsumed 10 units

    If the work date is 06/30/09 and the end date is provided as 05/30/09, the item ageing report should calculate the values as follows;
    April= 30 (100-50-10-10)
    May= 50

    If we use the valuation report method, should that provide the above results???
  • Alex_Chow
    Alex_Chow Member Posts: 5,063
    Check the standard inventory valuation report. It does calculate item costs based on whatever costing method you use.
    Confessions of a Dynamics NAV Consultant = my blog
    AP Commerce, Inc. = where I work

    Getting Started with Dynamics NAV 2013 Application Development = my book
    Implementing Microsoft Dynamics NAV - 3rd Edition = my 2nd book
  • user123
    user123 Member Posts: 22
    Would the valuation report method provide correct quantities (as mentioned in the scenario) for each time bucket in the item ageing report?

    Thanks for the reply!!

    Does any one have other suggestions to resolve this??
  • Alex_Chow
    Alex_Chow Member Posts: 5,063
    not sure about the WW version. But the US version, it gives the correct quantities and the cost based on what you entered on the As of Date.
    Confessions of a Dynamics NAV Consultant = my blog
    AP Commerce, Inc. = where I work

    Getting Started with Dynamics NAV 2013 Application Development = my book
    Implementing Microsoft Dynamics NAV - 3rd Edition = my 2nd book
  • ayhan06
    ayhan06 Member Posts: 210
    I have changed standard report..It has "Export to Excel" functionality as well..
    OBJECT Report 5808 Item Age Composition - Value
{
  OBJECT-PROPERTIES
  {
    Date=24.12.09;
    Time=14:10:47;
    Version List=6GENCore1.8;
  }
  PROPERTIES
  {
    CaptionML=[ENU=Item Age Composition - Value;
               TRK=Madde YaŸ Kompozisyonu - De§er];
    LeftMargin=1000;
    RightMargin=0;
    OnPreReport=BEGIN
                  ItemFilter := Item.GETFILTERS;

                  PeriodStartDate[1] := 01011900D;
                  PeriodStartDate[7] := 31129999D;
                  FOR i := 1 TO 4 DO
                    PeriodStartDate[6 - i] := CALCDATE('-' + FORMAT(PeriodLength),PeriodStartDate[7 - i]);

                  IF PrintToExcel THEN MakeExcelInfo;
                END;

    Orientation=Landscape;
  }
  DATAITEMS
  {
    { PROPERTIES
      {
        DataItemTable=Table27;
        DataItemTableView=SORTING(No.);
        OnPreDataItem=BEGIN
                        GLSetup.GET;
                        IF ShowACY THEN BEGIN
                          GLSetup."LCY Code" := GLSetup."Additional Reporting Currency";
                          Currency.GET(GLSetup."Additional Reporting Currency");
                        END ELSE BEGIN
                          Currency.INIT;
                        END;
                        CurrReport.CREATETOTALS(InvtValue,TotalInvtValue);
                      END;

        OnAfterGetRecord=BEGIN
                           IF Item."Costing Method" = Item."Costing Method"::Average THEN BEGIN
                             ItemCostMgt.CalculateAverageCost(Item,AverageCost,AverageCostACY);
                             IF ShowACY THEN
                               AverageCost := AverageCostACY;
                           END;

                           PrintLine := FALSE;

                           FOR i:=1 TO 5 DO BEGIN
                             InvtValue[i] := 0;
                             InvtQty[i] := 0;
                           END;
                         END;

        OnPostDataItem=BEGIN
                         IF PrintToExcel THEN CreateExcelbook;
                       END;

        ReqFilterFields=No.,Inventory Posting Group,Statistics Group,Location Filter;
      }
      SECTIONS
      {
        { PROPERTIES
          {
            SectionType=Header;
            PrintOnEveryPage=Yes;
            SectionWidth=28350;
            SectionHeight=1692;
          }
          CONTROLS
          {
            { 1   ;Label        ;0    ;0    ;7500 ;423  ;FontSize=8;
                                                         FontBold=Yes;
                                                         CaptionML=[ENU=Item Age Composition - Value;
                                                                    TRK=Madde YaŸ Kompozisyonu - De§er] }
            { 2   ;TextBox      ;25200;0    ;3150 ;423  ;HorzAlign=Right;
                                                         SourceExpr=FORMAT(TODAY,0,4) }
            { 4   ;Label        ;27150;423  ;750  ;423  ;ParentControl=5 }
            { 5   ;TextBox      ;27900;423  ;450  ;423  ;CaptionML=[ENU=Page;
                                                                    TRK=Sayfa];
                                                         SourceExpr=CurrReport.PAGENO }
            { 6   ;TextBox      ;0    ;423  ;7500 ;423  ;SourceExpr=COMPANYNAME }
            { 7   ;TextBox      ;26100;846  ;2250 ;423  ;HorzAlign=Right;
                                                         SourceExpr=USERID }
          }
           }
        { PROPERTIES
          {
            SectionType=Header;
            PrintOnEveryPage=Yes;
            SectionWidth=28350;
            SectionHeight=846;
            OnPreSection=BEGIN
                           CurrReport.SHOWOUTPUT(CurrReport.PAGENO = 1);
                         END;

          }
          CONTROLS
          {
            { 8   ;TextBox      ;0    ;0    ;18150;423  ;SourceExpr=Item.TABLECAPTION + ': ' + ItemFilter }
            { 82800;TextBox     ;0    ;423  ;18150;423  ;SourceExpr=STRSUBSTNO(Text003,GLSetup."LCY Code") }
          }
           }
        { PROPERTIES
          {
            SectionType=Header;
            PrintOnEveryPage=Yes;
            SectionWidth=28350;
            SectionHeight=1269;
          }
          CONTROLS
          {
            { 33  ;Label        ;7050 ;0    ;3000 ;846  ;HorzAlign=Right;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes;
                                                         LeaderDots=Yes;
                                                         CaptionML=[ENU=...before;
                                                                    TRK=...”nce] }
            { 22  ;Label        ;25200;0    ;3150 ;846  ;HorzAlign=Right;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes;
                                                         LeaderDots=Yes;
                                                         CaptionML=[ENU=Inventory Value;
                                                                    TRK=Stok De§eri] }
            { 12  ;Label        ;1650 ;0    ;4950 ;846  ;ParentControl=11;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
            { 39  ;TextBox      ;10650;0    ;3000 ;423  ;VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         SourceExpr=PeriodStartDate[2] + 1 }
            { 40  ;TextBox      ;10650;423  ;3000 ;423  ;VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         LeaderDots=Yes;
                                                         SourceExpr=[PeriodStartDate[3]]  ] }
            { 41  ;TextBox      ;14100;0    ;3300 ;423  ;VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         SourceExpr=PeriodStartDate[3] + 1 }
            { 42  ;TextBox      ;14100;423  ;3300 ;423  ;VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         LeaderDots=Yes;
                                                         SourceExpr=PeriodStartDate[4] }
            { 43  ;TextBox      ;18000;0    ;3150 ;423  ;VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         SourceExpr=PeriodStartDate[4]  + 1 }
            { 44  ;TextBox      ;18000;423  ;3150 ;423  ;VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         LeaderDots=Yes;
                                                         SourceExpr=[PeriodStartDate[5]]  ] }
            { 10  ;Label        ;0    ;0    ;1500 ;846  ;ParentControl=9;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
            { 1000000000;TextBox;21750;423  ;3000 ;423  ;VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         LeaderDots=Yes;
                                                         SourceExpr=PeriodStartDate[6] }
            { 1000000001;TextBox;21750;0    ;3000 ;423  ;VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         SourceExpr=PeriodStartDate[5]  + 1 }
          }
           }
        { PROPERTIES
          {
            SectionType=TransHeader;
            SectionWidth=28350;
            SectionHeight=846;
          }
          CONTROLS
          {
            { 82827;TextBox     ;26550;0    ;1800 ;423  ;CaptionML=[ENU=Inventory Value;
                                                                    TRK=Stok De§eri];
                                                         SourceExpr=TotalInvtValue;
                                                         AutoFormatType=1;
                                                         AutoFormatExpr=Currency.Code }
            { 82828;TextBox     ;22950;0    ;1800 ;423  ;CaptionML=[ENU=after...;
                                                                    TRK=sonra...];
                                                         SourceExpr=InvtValue[5];
                                                         AutoFormatType=1;
                                                         AutoFormatExpr=Currency.Code }
            { 82829;TextBox     ;19350;0    ;1800 ;423  ;CaptionML=[ENU=...before;
                                                                    TRK=...”nce];
                                                         SourceExpr=InvtValue[4];
                                                         AutoFormatType=1;
                                                         AutoFormatExpr=Currency.Code }
            { 82830;TextBox     ;15600;0    ;1800 ;423  ;CaptionML=[ENU=...before;
                                                                    TRK=...”nce];
                                                         SourceExpr=InvtValue[3];
                                                         AutoFormatType=1;
                                                         AutoFormatExpr=Currency.Code }
            { 82831;TextBox     ;11850;0    ;1800 ;423  ;CaptionML=[ENU=...before;
                                                                    TRK=...”nce];
                                                         SourceExpr=InvtValue[2];
                                                         AutoFormatType=1;
                                                         AutoFormatExpr=Currency.Code }
            { 82832;TextBox     ;8250 ;0    ;1800 ;423  ;CaptionML=[ENU=Continued (LCY);
                                                                    TRK=Devam (LPB)];
                                                         SourceExpr=InvtValue[1];
                                                         AutoFormatType=1;
                                                         AutoFormatExpr=Currency.Code }
            { 82801;TextBox     ;1650 ;0    ;5850 ;423  ;LeaderDots=Yes;
                                                         SourceExpr=STRSUBSTNO(Text004,GLSetup."LCY Code");
                                                         AutoFormatType=1 }
          }
           }
        { PROPERTIES
          {
            SectionType=TransFooter;
            SectionWidth=28350;
            SectionHeight=846;
          }
          CONTROLS
          {
            { 82803;TextBox     ;2250 ;423  ;5850 ;423  ;LeaderDots=Yes;
                                                         SourceExpr=STRSUBSTNO(Text004,GLSetup."LCY Code");
                                                         AutoFormatType=1 }
            { 82818;TextBox     ;8250 ;423  ;1800 ;423  ;CaptionML=[ENU=Continued (LCY);
                                                                    TRK=Devam (LPB)];
                                                         SourceExpr=InvtValue[1];
                                                         AutoFormatType=1;
                                                         AutoFormatExpr=Currency.Code }
            { 82819;TextBox     ;11850;423  ;1800 ;423  ;CaptionML=[ENU=...before;
                                                                    TRK=...”nce];
                                                         SourceExpr=InvtValue[2];
                                                         AutoFormatType=1;
                                                         AutoFormatExpr=Currency.Code }
            { 82820;TextBox     ;15600;423  ;1800 ;423  ;CaptionML=[ENU=...before;
                                                                    TRK=...”nce];
                                                         SourceExpr=InvtValue[3];
                                                         AutoFormatType=1;
                                                         AutoFormatExpr=Currency.Code }
            { 82821;TextBox     ;19350;423  ;1800 ;423  ;CaptionML=[ENU=...before;
                                                                    TRK=...”nce];
                                                         SourceExpr=InvtValue[4];
                                                         AutoFormatType=1;
                                                         AutoFormatExpr=Currency.Code }
            { 82822;TextBox     ;22950;423  ;1800 ;423  ;CaptionML=[ENU=after...;
                                                                    TRK=sonra...];
                                                         SourceExpr=InvtValue[5];
                                                         AutoFormatType=1;
                                                         AutoFormatExpr=Currency.Code }
            { 82823;TextBox     ;26550;423  ;1800 ;423  ;CaptionML=[ENU=Inventory Value;
                                                                    TRK=Stok De§eri];
                                                         SourceExpr=TotalInvtValue;
                                                         AutoFormatType=1;
                                                         AutoFormatExpr=Currency.Code }
          }
           }
        { PROPERTIES
          {
            SectionType=Footer;
            SectionWidth=28350;
            SectionHeight=846;
          }
          CONTROLS
          {
            { 21  ;Label        ;0    ;423  ;1500 ;423  ;FontBold=Yes;
                                                         CaptionML=[ENU=Total;
                                                                    TRK=Toplam] }
          }
           }
      }
       }
    { PROPERTIES
      {
        DataItemIndent=1;
        DataItemTable=Table32;
        DataItemTableView=SORTING(Item No.,Posting Date);
        OnPreDataItem=BEGIN
                        SETRANGE("Posting Date",0D,PeriodStartDate[6]);
                        CurrReport.CREATETOTALS(InvtValue,TotalInvtValue,InvtQty,TotalInvtQty);
                        PrintLine := FALSE;
                      END;

        OnAfterGetRecord=BEGIN
                           IsPositive := GetSign;
                           CalcRemainingQty(PeriodStartDate[6]);
                           IF RemainingQty <> 0 THEN BEGIN
                             FOR i:=1 TO 5 DO BEGIN
                               IF ("Item Ledger Entry"."Posting Date" >= PeriodStartDate[i] + 1) AND
                                 ("Item Ledger Entry"."Posting Date" <= PeriodStartDate[i+1]) THEN BEGIN
                                 InvtQty[i] := RemainingQty;
                                 TotalInvtQty := InvtQty[i];
                                 IF Item."Costing Method" = Item."Costing Method"::Average THEN BEGIN
                                   TotalInvtValue := AverageCost * TotalInvtQty;
                                   InvtValue[i] := AverageCost * InvtQty[i];
                                 END ELSE BEGIN
                                   CalcUnitCost;
                                   TotalInvtValue := UnitCost * TotalInvtQty;
                                   InvtValue[i] := UnitCost * InvtQty[i];
                                 END;
                                 IF NOT PrintLine THEN
                                   PrintLine := (InvtValue[i] <> 0) OR (InvtQty[i] <> 0);
                               END;
                             END;
                           END;
                         END;

        DataItemLink=Item No.=FIELD(No.),
                     Location Code=FIELD(Location Filter),
                     Variant Code=FIELD(Variant Filter),
                     Global Dimension 1 Code=FIELD(Global Dimension 1 Filter),
                     Global Dimension 2 Code=FIELD(Global Dimension 2 Filter);
      }
      SECTIONS
      {
      }
       }
    { PROPERTIES
      {
        DataItemIndent=1;
        DataItemTable=Table2000000026;
        DataItemTableView=SORTING(Number)
                          WHERE(Number=CONST(1));
      }
      SECTIONS
      {
        { PROPERTIES
          {
            SectionType=Body;
            SectionWidth=28350;
            SectionHeight=423;
            OnPreSection=BEGIN
                           CurrReport.SHOWOUTPUT(PrintLine);

                           IF PrintLine AND PrintToExcel THEN MakeExcelDataBody;
                         END;

          }
          CONTROLS
          {
            { 11  ;TextBox      ;1650 ;0    ;4800 ;423  ;CaptionML=[ENU=Description;
                                                                    TRK=A‡klama];
                                                         SourceExpr=Item.Description + Item."Description 2" }
            { 9   ;TextBox      ;0    ;0    ;1500 ;423  ;CaptionML=[ENU=Item No.;
                                                                    TRK=Madde No];
                                                         SourceExpr=Item."No." }
            { 1000000002;TextBox;13800;0    ;1650 ;423  ;CaptionML=[ENU=...before;
                                                                    TRK=...”nce];
                                                         SourceExpr=InvtQty[3];
                                                         AutoFormatType=1 }
            { 1000000003;TextBox;10200;0    ;1500 ;423  ;CaptionML=[ENU=...before;
                                                                    TRK=...”nce];
                                                         SourceExpr=InvtQty[2];
                                                         AutoFormatType=1 }
            { 1000000004;TextBox;6600 ;0    ;1500 ;423  ;CaptionML=[ENU=...before;
                                                                    TRK=...”nce];
                                                         SourceExpr=InvtQty[1];
                                                         AutoFormatType=1 }
            { 1000000005;TextBox;17550;0    ;1650 ;423  ;CaptionML=[ENU=...before;
                                                                    TRK=...”nce];
                                                         SourceExpr=InvtQty[4];
                                                         AutoFormatType=1 }
            { 1000000006;TextBox;21300;0    ;1500 ;423  ;CaptionML=[ENU=...before;
                                                                    TRK=...”nce];
                                                         SourceExpr=InvtQty[5];
                                                         AutoFormatType=1 }
            { 82802;TextBox     ;24900;0    ;1500 ;423  ;CaptionML=[ENU=...before;
                                                                    TRK=...”nce];
                                                         SourceExpr=TotalInvtQty;
                                                         AutoFormatType=1 }
            { 82804;TextBox     ;8250 ;0    ;1800 ;423  ;CaptionML=[ENU=Continued (LCY);
                                                                    TRK=Devam (LPB)];
                                                         SourceExpr=InvtValue[1];
                                                         AutoFormatType=1;
                                                         AutoFormatExpr=Currency.Code }
            { 82805;TextBox     ;11850;0    ;1800 ;423  ;CaptionML=[ENU=...before;
                                                                    TRK=...”nce];
                                                         SourceExpr=InvtValue[2];
                                                         AutoFormatType=1;
                                                         AutoFormatExpr=Currency.Code }
            { 82806;TextBox     ;15600;0    ;1800 ;423  ;CaptionML=[ENU=...before;
                                                                    TRK=...”nce];
                                                         SourceExpr=InvtValue[3];
                                                         AutoFormatType=1;
                                                         AutoFormatExpr=Currency.Code }
            { 82807;TextBox     ;19350;0    ;1800 ;423  ;CaptionML=[ENU=...before;
                                                                    TRK=...”nce];
                                                         SourceExpr=InvtValue[4];
                                                         AutoFormatType=1;
                                                         AutoFormatExpr=Currency.Code }
            { 82808;TextBox     ;22950;0    ;1800 ;423  ;CaptionML=[ENU=after...;
                                                                    TRK=sonra...];
                                                         SourceExpr=InvtValue[5];
                                                         AutoFormatType=1;
                                                         AutoFormatExpr=Currency.Code }
            { 82809;TextBox     ;26550;0    ;1800 ;423  ;CaptionML=[ENU=Inventory Value;
                                                                    TRK=Stok De§eri];
                                                         SourceExpr=TotalInvtValue;
                                                         AutoFormatType=1;
                                                         AutoFormatExpr=Currency.Code }
          }
           }
      }
       }
  }
  REQUESTFORM
  {
    PROPERTIES
    {
      Width=5990;
      Height=2640;
      SaveValues=Yes;
      OnOpenForm=BEGIN
                   IF PeriodStartDate[6] = 0D THEN
                     PeriodStartDate[6] := CALCDATE('<CM>',WORKDATE);
                   IF FORMAT(PeriodLength) = '' THEN
                     EVALUATE(PeriodLength,'<1M>');
                 END;

    }
    CONTROLS
    {
      { 1   ;TextBox      ;3410 ;0    ;1650 ;440  ;InPage=-1;
                                                   CaptionML=[ENU=Ending Date;
                                                              TRK=BitiŸ Tarihi];
                                                   SourceExpr=PeriodStartDate[6];
                                                   OnValidate=BEGIN
                                                                IF PeriodStartDate[6] = 0D THEN
                                                                  ERROR(Text002);
                                                              END;
                                                               }
      { 21  ;Label        ;0    ;0    ;3300 ;440  ;ParentControl=1;
                                                   InPage=-1 }
      { 2   ;TextBox      ;3410 ;550  ;1650 ;440  ;InPage=-1;
                                                   CaptionML=[ENU=Period Length;
                                                              TRK=D”nem Uzunlu§u];
                                                   SourceExpr=PeriodLength;
                                                   OnValidate=BEGIN
                                                                IF FORMAT(PeriodLength) = '' THEN
                                                                  EVALUATE(PeriodLength,'<0D>');
                                                              END;
                                                               }
      { 3   ;Label        ;0    ;550  ;3300 ;440  ;ParentControl=2;
                                                   InPage=-1 }
      { 1000000001;CheckBox;3410;1100 ;440  ;440  ;InPage=-1;
                                                   ShowCaption=No;
                                                   CaptionML=[ENU=Show ACY;
                                                              TRK=˜PB Kullan];
                                                   SourceExpr=ShowACY }
      { 1000000002;Label  ;0    ;1100 ;3300 ;440  ;ParentControl=1000000001;
                                                   InPage=-1 }
      { 85750;CheckBox    ;3410 ;1650 ;440  ;440  ;ShowCaption=No;
                                                   CaptionML=[ENU=Print to Excel;
                                                              TRK=Excel'e Yazdr];
                                                   SourceExpr=PrintToExcel }
      { 85751;Label       ;0    ;1650 ;3300 ;440  ;ParentControl=85750 }
    }
  }
  CODE
  {
    VAR
      Text002@1001 : TextConst 'ENU=Enter the ending date;TRK=BitiŸ tarihini gir';
      ItemCostMgt@1004 : Codeunit 5804;
      ItemFilter@1005 : Text[250];
      InvtValue@1006 : ARRAY [7] OF Decimal;
      ExpectedInvtValue@1018 : ARRAY [7] OF Decimal;
      InvtQty@1007 : ARRAY [7] OF Decimal;
      UnitCost@1009 : Decimal;
      ExpectedUnitCost@1019 : Decimal;
      PeriodStartDate@1010 : ARRAY [7] OF Date;
      PeriodLength@1011 : DateFormula;
      i@1012 : Integer;
      TotalInvtValue@1013 : Decimal;
      TotalInvtQty@1014 : Decimal;
      PrintLine@1015 : Boolean;
      AverageCost@1016 : Decimal;
      AverageCostACY@1017 : Decimal;
      RemainingQty@1000000000 : Decimal;
      IsPositive@1000000001 : Boolean;
      PosQty@1000000002 : Decimal;
      ShowACY@1000000003 : Boolean;
      Text003@82800 : TextConst 'ENU=This report shows amount in %1;TRK=Bu rapor,de§erleri %1 g”sterir.';
      GLSetup@82801 : Record 98;
      Text004@82802 : TextConst 'ENU=Continued (%1);TRK=Devam (%1)';
      Currency@82803 : Record 4;
      ExcelBuf@85751 : TEMPORARY Record 370;
      PrintToExcel@85750 : Boolean;
      Text100@85768 : TextConst 'ENU=Company Name;TRK=žirket Ad';
      Text101@85767 : TextConst 'ENU=Data;TRK=Veri';
      Text102@85766 : TextConst 'ENU=Item Age Composition;TRK=Madde YaŸ Kompozisyonu';
      Text103@85765 : TextConst 'ENU=Period: %1;TRK=D”nem: %1';
      Text104@85764 : TextConst 'ENU=Report No.;TRK=Rapor No.';
      Text105@85763 : TextConst 'ENU=Report Name;TRK=Rapor Ad';
      Text106@85762 : TextConst 'ENU=User ID;TRK=Kullanc Kimli§i';
      Text107@85761 : TextConst 'ENU=Date;TRK=Tarih';
      Text108@85760 : TextConst 'ENU=Filters;TRK=Filtreler';
      Text109@85759 : TextConst 'ENU=Item no.;TRK=Madde No';
      Text110@85758 : TextConst 'ENU=Description;TRK=A‡klama';
      Text111@85757 : TextConst 'ENU=...Before;TRK=...™nce';
      Text113@85754 : TextConst 'ENU="Total ";TRK="Toplam "';
      Text112@85753 : TextConst 'ENU=Inventory Value;TRK=Stok De§eri';
      Text114@85752 : TextConst 'ENU=Period Start Date;TRK=D”nem BaŸlang‡ Tarihi';
      Text115@85756 : TextConst 'ENU=Quantity;TRK=Miktar';
      Text116@85755 : TextConst 'ENU=Value;TRK=De§er';

    PROCEDURE CalcUnitCost@2();
    BEGIN
      WITH "Item Ledger Entry" DO BEGIN
        CALCFIELDS("Cost Amount (Expected)","Cost Amount (Actual)","Cost Amount (Expected) (ACY)","Cost Amount (Actual) (ACY)");
        IF ShowACY THEN
          UnitCost := ("Cost Amount (Actual) (ACY)" + "Cost Amount (Expected) (ACY)") / Quantity
        ELSE
          UnitCost := ("Cost Amount (Actual)" + "Cost Amount (Expected)") / Quantity;
      END;
    END;

    PROCEDURE CalcRemainingQty@1000000001(ValuationDate@1000000000 : Date);
    VAR
      ItemApplnEntry@1001 : Record 339;
    BEGIN
      RemainingQty := "Item Ledger Entry".Quantity;
      IF IsPositive THEN
        PosQty := "Item Ledger Entry".Quantity;

      WITH ItemApplnEntry DO BEGIN
        IF "Item Ledger Entry".Positive THEN BEGIN
          RESET;
          SETCURRENTKEY("Inbound Item Entry No.","Outbound Item Entry No.","Cost Application");
          SETRANGE("Inbound Item Entry No.","Item Ledger Entry"."Entry No.");
          SETFILTER("Outbound Item Entry No.",'<>%1',0);
          SETRANGE("Cost Application",TRUE);
          SETRANGE("Posting Date",0D,ValuationDate);
          IF FIND('-') THEN
            REPEAT
              SumQty(RemainingQty,PosQty,"Outbound Item Entry No.",Quantity,ValuationDate);
            UNTIL NEXT = 0;
        END ELSE BEGIN
          RESET;
          SETCURRENTKEY("Outbound Item Entry No.","Item Ledger Entry No.","Cost Application");
          SETRANGE("Outbound Item Entry No.","Item Ledger Entry"."Entry No.");
          SETRANGE("Item Ledger Entry No.","Item Ledger Entry"."Entry No.");
          SETRANGE("Posting Date",0D,ValuationDate);
          SETRANGE("Cost Application",TRUE);
          IF FIND('-') THEN
            REPEAT
              SumQty(RemainingQty,PosQty,"Inbound Item Entry No.",-Quantity,ValuationDate);
            UNTIL NEXT = 0;
        END;
      END;

      IF IsPositive THEN
        PosQty := RemainingQty;
    END;

    PROCEDURE SumQty@3(VAR RemainingQty@1003 : Decimal;VAR PosQty@1005 : Decimal;EntryNo@1001 : Integer;AppliedQty@1000 : Decimal;ValuationDate@1000000000 : Date) : Decimal;
    VAR
      ItemLedgEntry@1002 : Record 32;
    BEGIN
      ItemLedgEntry.GET(EntryNo);
      IF (ItemLedgEntry.Quantity * AppliedQty < 0) OR
         (ItemLedgEntry."Posting Date" > ValuationDate)
      THEN
        EXIT;

      RemainingQty := RemainingQty + AppliedQty;
      IF IsPositive THEN
        PosQty := PosQty + AppliedQty;
    END;

    PROCEDURE GetSign@4() : Boolean;
    VAR
      ItemLedgEntry@1000 : Record 32;
    BEGIN
      WITH "Item Ledger Entry" DO
        CASE "Entry Type" OF
          "Entry Type"::Purchase,
          "Entry Type"::"Positive Adjmt.",
          "Entry Type"::Output:
            EXIT(TRUE);
          "Entry Type"::Transfer:
            BEGIN
              IF NOT Positive THEN
                EXIT(FALSE);
              ItemLedgEntry.COPYFILTERS("Item Ledger Entry");
              ItemLedgEntry."Entry No." := "Item Ledger Entry"."Entry No." - 1;
              EXIT(NOT ItemLedgEntry.FIND);
            END;
          ELSE
            EXIT(FALSE)
        END;
    END;

    PROCEDURE MakeExcelInfo@5();
    BEGIN
      ExcelBuf.SetUseInfoSheed;
      ExcelBuf.AddInfoColumn(FORMAT(Text100),FALSE,'',TRUE,FALSE,FALSE,'');
      ExcelBuf.AddInfoColumn(COMPANYNAME,FALSE,'',FALSE,FALSE,FALSE,'');
      ExcelBuf.NewRow;
      ExcelBuf.AddInfoColumn(FORMAT(Text105),FALSE,'',TRUE,FALSE,FALSE,'');
      ExcelBuf.AddInfoColumn(FORMAT(Text102),FALSE,'',FALSE,FALSE,FALSE,'');
      ExcelBuf.NewRow;
      ExcelBuf.AddInfoColumn(FORMAT(Text104),FALSE,'',TRUE,FALSE,FALSE,'');
      ExcelBuf.AddInfoColumn(REPORT::"Item Age Composition - Value",FALSE,'',FALSE,FALSE,FALSE,'');
      ExcelBuf.NewRow;
      ExcelBuf.AddInfoColumn(FORMAT(Text106),FALSE,'',TRUE,FALSE,FALSE,'');
      ExcelBuf.AddInfoColumn(USERID,FALSE,'',FALSE,FALSE,FALSE,'');
      ExcelBuf.NewRow;
      ExcelBuf.AddInfoColumn(FORMAT(Text107),FALSE,'',TRUE,FALSE,FALSE,'');
      ExcelBuf.AddInfoColumn(TODAY,FALSE,'',FALSE,FALSE,FALSE,'');
      ExcelBuf.NewRow;
      ExcelBuf.AddInfoColumn(FORMAT(Text108),FALSE,'',TRUE,FALSE,FALSE,'');
      ExcelBuf.AddInfoColumn(Item.GETFILTERS,FALSE,'',FALSE,FALSE,FALSE,'');
      ExcelBuf.NewRow;
      ExcelBuf.AddInfoColumn(FORMAT(Text114),FALSE,'',TRUE,FALSE,FALSE,'');
      ExcelBuf.AddInfoColumn(PeriodStartDate[2],FALSE,'',FALSE,FALSE,FALSE,'');
      ExcelBuf.NewRow;

      ExcelBuf.ClearNewRow;
      MakeExcelDataHeader;
    END;

    LOCAL PROCEDURE MakeExcelDataHeader@85753();
    BEGIN
      ExcelBuf.NewRow;
      ExcelBuf.AddColumn(FORMAT(' '),FALSE,'',TRUE,FALSE,TRUE,'@');
      ExcelBuf.AddColumn(FORMAT(' '),FALSE,'',TRUE,FALSE,TRUE,'@');
      ExcelBuf.AddColumn(FORMAT(' '),FALSE,'',TRUE,FALSE,TRUE,'@');
      ExcelBuf.AddColumn(FORMAT(' '),FALSE,'',TRUE,FALSE,TRUE,'@');
      ExcelBuf.AddColumn(FORMAT(' '),FALSE,'',TRUE,FALSE,TRUE,'@');
      ExcelBuf.AddColumn(FORMAT(PeriodStartDate[2]+1),FALSE,'',TRUE,FALSE,TRUE,'');
      ExcelBuf.AddColumn(FORMAT(' '),FALSE,'',TRUE,FALSE,TRUE,'@');
      ExcelBuf.AddColumn(FORMAT(PeriodStartDate[3]+1),FALSE,'',TRUE,FALSE,TRUE,'');
      ExcelBuf.AddColumn(FORMAT(' '),FALSE,'',TRUE,FALSE,TRUE,'@');
      ExcelBuf.AddColumn(FORMAT(PeriodStartDate[4]+1),FALSE,'',TRUE,FALSE,TRUE,'');
      ExcelBuf.AddColumn(FORMAT(' '),FALSE,'',TRUE,FALSE,TRUE,'@');
      ExcelBuf.AddColumn(FORMAT(PeriodStartDate[5]+1),FALSE,'',TRUE,FALSE,TRUE,'');
      ExcelBuf.AddColumn(FORMAT(' '),FALSE,'',TRUE,FALSE,TRUE,'@');
      ExcelBuf.AddColumn(FORMAT(' '),FALSE,'',TRUE,FALSE,TRUE,'@');


      ExcelBuf.NewRow;
      ExcelBuf.AddColumn(FORMAT(Text109),FALSE,'',TRUE,FALSE,TRUE,'@');
      ExcelBuf.AddColumn(FORMAT(Text110),FALSE,'',TRUE,FALSE,TRUE,'@');
      ExcelBuf.AddColumn(FORMAT(' '),FALSE,'',TRUE,FALSE,TRUE,'@');
      ExcelBuf.AddColumn(FORMAT(Text111),FALSE,'',TRUE,FALSE,TRUE,'@');
      ExcelBuf.AddColumn(FORMAT(' '),FALSE,'',TRUE,FALSE,TRUE,'@');
      ExcelBuf.AddColumn(FORMAT(PeriodStartDate[3]),FALSE,'',TRUE,FALSE,TRUE,'');
      ExcelBuf.AddColumn(FORMAT(' '),FALSE,'',TRUE,FALSE,TRUE,'@');
      ExcelBuf.AddColumn(FORMAT(PeriodStartDate[4]),FALSE,'',TRUE,FALSE,TRUE,'');
      ExcelBuf.AddColumn(FORMAT(' '),FALSE,'',TRUE,FALSE,TRUE,'@');
      ExcelBuf.AddColumn(FORMAT(PeriodStartDate[5]),FALSE,'',TRUE,FALSE,TRUE,'');
      ExcelBuf.AddColumn(FORMAT(' '),FALSE,'',TRUE,FALSE,TRUE,'@');
      ExcelBuf.AddColumn(FORMAT(PeriodStartDate[6]),FALSE,'',TRUE,FALSE,TRUE,'');
      ExcelBuf.AddColumn(FORMAT(' '),FALSE,'',TRUE,FALSE,TRUE,'@');
      ExcelBuf.AddColumn(FORMAT(Text112),FALSE,'',TRUE,FALSE,TRUE,'@');

      ExcelBuf.NewRow;
      ExcelBuf.AddColumn(FORMAT(' '),FALSE,'',TRUE,FALSE,TRUE,'@');
      ExcelBuf.AddColumn(FORMAT(' '),FALSE,'',TRUE,FALSE,TRUE,'@');
      ExcelBuf.AddColumn(FORMAT(Text115),FALSE,'',FALSE,FALSE,TRUE,'@');
      ExcelBuf.AddColumn(FORMAT(Text116),FALSE,'',FALSE,FALSE,TRUE,'@');
      ExcelBuf.AddColumn(FORMAT(Text115),FALSE,'',FALSE,FALSE,TRUE,'@');
      ExcelBuf.AddColumn(FORMAT(Text116),FALSE,'',FALSE,FALSE,TRUE,'@');
      ExcelBuf.AddColumn(FORMAT(Text115),FALSE,'',FALSE,FALSE,TRUE,'@');
      ExcelBuf.AddColumn(FORMAT(Text116),FALSE,'',FALSE,FALSE,TRUE,'@');
      ExcelBuf.AddColumn(FORMAT(Text115),FALSE,'',FALSE,FALSE,TRUE,'@');
      ExcelBuf.AddColumn(FORMAT(Text116),FALSE,'',FALSE,FALSE,TRUE,'@');
      ExcelBuf.AddColumn(FORMAT(Text115),FALSE,'',FALSE,FALSE,TRUE,'@');
      ExcelBuf.AddColumn(FORMAT(Text116),FALSE,'',FALSE,FALSE,TRUE,'@');
      ExcelBuf.AddColumn(FORMAT(Text115),FALSE,'',FALSE,FALSE,TRUE,'@');
      ExcelBuf.AddColumn(FORMAT(Text116),FALSE,'',FALSE,FALSE,TRUE,'@');
    END;

    PROCEDURE MakeExcelDataBody@85752();
    BEGIN
      ExcelBuf.NewRow;
      ExcelBuf.AddColumn(Item."No.",FALSE,'',FALSE,FALSE,FALSE,'@');
      ExcelBuf.AddColumn(Item.Description + Item."Description 2",FALSE,'',FALSE,FALSE,FALSE,'');
      ExcelBuf.AddColumn(InvtQty[1],FALSE,'',FALSE,FALSE,FALSE,'');
      ExcelBuf.AddColumn(InvtValue[1],FALSE,'',FALSE,FALSE,FALSE,'');
      ExcelBuf.AddColumn(InvtQty[2],FALSE,'',FALSE,FALSE,FALSE,'');
      ExcelBuf.AddColumn(InvtValue[2],FALSE,'',FALSE,FALSE,FALSE,'');
      ExcelBuf.AddColumn(InvtQty[3],FALSE,'',FALSE,FALSE,FALSE,'');
      ExcelBuf.AddColumn(InvtValue[3],FALSE,'',FALSE,FALSE,FALSE,'');
      ExcelBuf.AddColumn(InvtQty[4],FALSE,'',FALSE,FALSE,FALSE,'');
      ExcelBuf.AddColumn(InvtValue[4],FALSE,'',FALSE,FALSE,FALSE,'');
      ExcelBuf.AddColumn(InvtQty[5],FALSE,'',FALSE,FALSE,FALSE,'');
      ExcelBuf.AddColumn(InvtValue[5],FALSE,'',FALSE,FALSE,FALSE,'');
      ExcelBuf.AddColumn(TotalInvtQty,FALSE,'',FALSE,FALSE,FALSE,'');
      ExcelBuf.AddColumn(TotalInvtValue,FALSE,'',FALSE,FALSE,FALSE,'');
    END;

    PROCEDURE CreateExcelbook@85751();
    BEGIN
      ExcelBuf.CreateBook;
      ExcelBuf.CreateSheet(Text101,Text102,COMPANYNAME,USERID);
      ExcelBuf.GiveUserControl;
      ERROR('');
    END;

    PROCEDURE MakeExcelDataBodyTotal@85750();
    BEGIN
    END;

    BEGIN
    {
    }
    END.
  }
}

    hope this helps.
  • user123
    user123 Member Posts: 22
    Thanks a lot for the code!!! :D

    Will be trying this and would let you know whether that solves my issue..
  • user123
    user123 Member Posts: 22
    Alex,from where can I download the U.S version of the item age report??
  • Alex_Chow
    Alex_Chow Member Posts: 5,063
    My guess would be Partnersource. Download the NA database and get the Inventory Valuation report.
    Confessions of a Dynamics NAV Consultant = my blog
    AP Commerce, Inc. = where I work

    Getting Started with Dynamics NAV 2013 Application Development = my book
    Implementing Microsoft Dynamics NAV - 3rd Edition = my 2nd book
  • user123
    user123 Member Posts: 22
    Have searched the Partnersource and every other possible way..Still unable to download the U.S version of the item ageing report..

    Any suggestions would be greatly appreciated!! :)
  • Alex_Chow
    Alex_Chow Member Posts: 5,063
    Contact your local Microsoft office. They should direct you in the proper place.
    Confessions of a Dynamics NAV Consultant = my blog
    AP Commerce, Inc. = where I work

    Getting Started with Dynamics NAV 2013 Application Development = my book
    Implementing Microsoft Dynamics NAV - 3rd Edition = my 2nd book
  • user123
    user123 Member Posts: 22
    Hi,

    The U.S version of the item age composition report did not meet the expected results..

    The main issue with the standard item age composition report (WW version) is, it does not generate correct values if run for a back date.

    Believe most of the experienced have faced the same issue with this report..Would appreciate if anyone can share your knowledge on modifications done to overcome this issue..Or any suggestions for workarounds?

    Thanks

Categories