Skip to main content
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / Sales Inv Num in Report
Small and medium business | Business Central, N...
Answered

Sales Inv Num in Report

(0) ShareShare
ReportReport
Posted on by CrackBC

Dear Experts

kindly request your help on add one field Sales Returns Invoice from (External Document -Sales Credit Memo) in our report. this report is run only in excel layout. when i check the report id 50093 in Report layout selection it was not listing there.

how can i modify add this field in below mentioned code and add it in the excel layout 

Output of the Report while Running & External Doc num is the Invoice number of particular Sales Return

sg1.jpg

CODE FOR THIS REPORT

report 50093 Register"
{
  dataset
  {
    dataitem("Sales Invoice Header"; "Sales Invoice Header")
    {
      dataitem("Sales Invoice Line"; "Sales Invoice Line")
      {
      }
    }
    dataitem("Sales Cr.Memo Header"; "Sales Cr.Memo Header")
    {
      dataitem("Sales Cr.Memo Line"; "Sales Cr.Memo Line")
      {
      }
    }
  }
  requestpage
  {
    layout
    {
      area(content)
      {
        field(txtDateFilter; txtDateFilter)
        {
          Caption = 'Date From';
        }
        field(dtDateFilterTo; dtDateFilterTo)
        {
          Caption = 'Date To';
        }
      }
    }
  }
  var CustDesc: Text[50];
  RecCust: Record Customer;
  TotCost: Decimal;
  ProfitPct: Decimal;
  SalesInvHdr: Record "Sales Invoice Header";
  Recitem: Record Item;
  SalesPerson: Record "Salesperson/Purchaser";
  TotalAdjCostLCY: Decimal;
  CostLCY: Decimal;
  CostCalcMgt: codeunit "Cost Calculation Management";
  UnitCost: Decimal;
  VendorCost: Decimal;
  RecVend: Record Vendor;
  ValueEntry: Record "Value Entry";
  intRowNo: Integer;
  txtData: array[255]of Text[250];
  ExcelBuff: Record "Excel Buffer" temporary;
  PrintToExcel: Boolean;
  RecShipmentHe: Record "Sales Shipment Header";
  txtOrderNo: Text[20];
  txtShipmentNo: Text[20];
  RecDimensionSet: Record "Dimension Set Entry";
  Dimensextion: Text[40];
  DefaultDIme: Record "Dimension Set Entry";
  Issuetype: Text[40];
  Recitem1: Record Item;
  RecCust1: Record Customer;
  recSalesInvLine: Record "Sales Invoice Line";
  txtSalesInfo: array[22]of Text[250];
  txtDocDate: Date;
  txtCustNo: Integer;
  txtCustName: Text[50];
  txtDateFilter: Date;
  dtDateFilterTo: Date;
  recGL: Record "G/L Account";
  CompanyInfo: Record "Company Information";
  compname: Text[100];
  DimensionValue: Record "Dimension Value";
  grade: Text;
  GLSetup: Record "General Ledger Setup";
  DimSetEntry: Record "Dimension Set Entry";
}
Categories:
Dynamics 365 Business Central
  • Verified answer
    pankaj.k Profile Picture
    pankaj.k 976 on at
    RE: Sales Inv Num in Report

    One more changes is required Please check the code or change as pr green color row:

    report 62017 "EPM Sales Register"
    {
    // DRGKK :: Shipment no. added
    ProcessingOnly = true;
    UsageCategory = ReportsAndAnalysis;
    ApplicationArea = Basic, Suite;

    dataset
    {
    dataitem("Sales Invoice Header"; "Sales Invoice Header")
    {
    RequestFilterFields = "No.";

    dataitem("Sales Invoice Line"; "Sales Invoice Line")
    {
    DataItemLink = "Document No." = FIELD("No.");
    DataItemTableView = SORTING("Document No.", "Line No.");

    trigger OnAfterGetRecord()
    begin
    txtShipmentNo := '';
    CostLCY := 0;
    TotalAdjCostLCY := 0;
    TotCost := 0;
    VendorCost := 0;
    ProfitPct := 0;
    IF RecCust.GET("Sell-to Customer No.") THEN;
    CostLCY := CostLCY + (Quantity * "Unit Cost (LCY)");
    //TotalAdjCostLCY := TotalAdjCostLCY + CostCalcMgt.CalcSalesInvLineCostLCY("Sales Invoice Line");
    //UNTIL NEXT = 0;
    //END;
    //TotCost := TotalAdjCostLCY - CostLCY;
    ValueEntry.RESET;
    ValueEntry.SETCURRENTKEY("Item No.", "Document No.", "Posting Date");
    ValueEntry.SETRANGE("Item No.", "No.");
    ValueEntry.SETRANGE("Document No.", "Document No.");
    ValueEntry.SETRANGE("Posting Date", "Posting Date");
    IF ValueEntry.FINDFIRST THEN
    REPEAT
    TotCost += ValueEntry."Cost Amount (Actual)";
    UNTIL ValueEntry.NEXT = 0;
    IF Quantity <> 0 THEN UnitCost := (TotCost) / Quantity;
    IF "Sales Invoice Line".Quantity = 0 THEN CurrReport.SKIP;
    IF "Sales Invoice Line".Amount <> 0 THEN ProfitPct := (("Sales Invoice Line".Amount - ABS(TotCost)));
    IF Quantity <> 0 THEN UnitCost := TotCost / Quantity;
    IF Recitem.GET("No.") THEN
    IF "Sales Invoice Line".Type = "Sales Invoice Line".Type::"G/L Account" THEN
    txtSalesInfo[17] := ''
    ELSE
    txtSalesInfo[17] := Recitem."Vendor No.";
    txtSalesInfo[1] := "Document No.";
    txtSalesInfo[2] := FORMAT("Posting Date");

    txtSalesInfo[23] := "Sales Invoice Header"."External Document No.";// add code

    txtSalesInfo[3] := "Sell-to Customer No.";
    txtSalesInfo[4] := "Sales Invoice Header"."Sell-to Customer Name";
    txtSalesInfo[5] := "No.";
    txtSalesInfo[6] := Description;
    txtSalesInfo[7] := FORMAT(Quantity);
    txtSalesInfo[8] := FORMAT("Unit Price");
    txtSalesInfo[9] := FORMAT("Line Amount");
    txtSalesInfo[10] := FORMAT("VAT %");
    txtSalesInfo[11] := FORMAT("VAT Base Amount" * ("VAT %") / 100);
    txtSalesInfo[16] := "Sales Invoice Line"."Location Code";
    txtSalesInfo[12] := Recitem."Inventory Posting Group";
    ///txtSalesInfo[13] := "Product Group Code";
    txtSalesInfo[14] := "Sales Invoice Header"."External Document No.";
    txtSalesInfo[19] := "Sales Invoice Header"."Currency Code";
    txtSalesInfo[20] := "Sales Invoice Header"."Order No.";
    //txtSalesInfo[22] := "Sales Invoice Header"."Posting Description";
    IF SalesPerson.GET("Sales Invoice Header"."Salesperson Code") THEN txtSalesInfo[15] := SalesPerson.Name;
    txtSalesInfo[18] := "Sales Invoice Line"."Shortcut Dimension 1 Code";
    //-----DRGKK, BEGIN-----//
    txtOrderNo := '';
    txtShipmentNo := '';
    SalesInvHdr.RESET;
    SalesInvHdr.SETRANGE("No.", "Sales Invoice Line"."Document No.");
    IF SalesInvHdr.FIND('-') THEN BEGIN
    txtOrderNo := SalesInvHdr."Order No."
    END;
    RecShipmentHe.RESET;
    RecShipmentHe.SETRANGE(RecShipmentHe."Order No.", txtOrderNo);
    IF RecShipmentHe.FIND('-') THEN BEGIN
    txtShipmentNo := RecShipmentHe."No.";
    END;
    txtSalesInfo[21] := "Shortcut Dimension 2 Code";
    DimSetEntry.reset;
    DimSetEntry.SetRange("Dimension Set ID", "Dimension Set ID");
    DimSetEntry.SetRange("Dimension Code", GLSetup."Shortcut Dimension 3 Code");
    if DimSetEntry.FindFirst() then begin
    DimSetEntry.CalcFields("Dimension Value Name");
    DimSetEntry.CalcFields("Dimension Value Name");
    txtSalesInfo[22] := DimSetEntry."Dimension Value Name";
    end;
    /*
    CLEAR(grade);
    StudentsStatus.RESET;
    StudentsStatus.SETRANGE("Student ID", "Sales Invoice Line"."Sell-to Customer No.");
    StudentsStatus.SETFILTER("Academic Year", "Sales Invoice Line"."Shortcut Dimension 2 Code");
    IF StudentsStatus.FINDLAST THEN
    grade := StudentsStatus.Grade;
    */
    /*
    DimensionValue.RESET;
    DimensionValue.SETFILTER("Dimension Code", 'GRADE');
    DimensionValue.SETRANGE(Code, grade);
    IF DimensionValue.FINDFIRST THEN
    txtSalesInfo[21] := DimensionValue.Name;
    //-----DRGKK, END-----//
    Issuetype := ' ';
    DefaultDIme.RESET;
    DefaultDIme.SETFILTER("Dimension Code", '%1', 'GRADE');
    DefaultDIme.SETRANGE("Dimension Set ID", "Dimension Set ID");
    //DefaultDIme.SETFILTER("Dimension Code",'%1','CUSTOMERSEGMENT');
    IF DefaultDIme.FIND('-') THEN BEGIN
    Issuetype := DefaultDIme."Dimension Value Code";
    END;
    */
    IF PrintToExcel THEN MakeExcelBody;
    end;
    }
    trigger OnPreDataItem()
    begin
    GLSetup.get();
    IF txtDateFilter <> 0D THEN "Sales Invoice Header".SETFILTER("Sales Invoice Header"."Posting Date", '%1..%2', txtDateFilter, dtDateFilterTo);
    end;
    }
    dataitem("Sales Cr.Memo Header"; "Sales Cr.Memo Header")
    {
    RequestFilterFields = "No.";

    dataitem("Sales Cr.Memo Line"; "Sales Cr.Memo Line")
    {
    DataItemLink = "Document No." = FIELD("No.");
    DataItemTableView = WHERE("No." = FILTER(<> ' '), Type = FILTER(<> ' '));

    trigger OnAfterGetRecord()
    begin
    //-----DRGKH0011----//BEGIN
    txtShipmentNo := '';
    TotCost := 0;
    ProfitPct := 0;
    IF Recitem.GET("No.") THEN;
    IF "Sales Cr.Memo Line".Type = "Sales Cr.Memo Line".Type::"G/L Account" THEN
    txtSalesInfo[17] := ''
    ELSE
    txtSalesInfo[17] := Recitem."Vendor No.";
    ValueEntry.RESET;
    ValueEntry.SETCURRENTKEY("Item No.", "Document No.", "Posting Date");
    ValueEntry.SETRANGE("Item No.", "No.");
    ValueEntry.SETRANGE("Document No.", "Document No.");
    ValueEntry.SETRANGE("Posting Date", "Posting Date");
    IF ValueEntry.FINDFIRST THEN
    REPEAT
    TotCost += ValueEntry."Cost Amount (Actual)";
    UNTIL ValueEntry.NEXT = 0;
    IF Quantity <> 0 THEN UnitCost := (TotCost) / Quantity;
    IF "Sales Cr.Memo Line".Amount <> 0 THEN ProfitPct := (("Sales Cr.Memo Line".Amount - ABS(TotCost)));
    /*
    CLEAR(grade);
    StudentsStatus.RESET;
    StudentsStatus.SETRANGE("Student ID", "Sales Cr.Memo Line"."Sell-to Customer No.");
    StudentsStatus.SETFILTER("Academic Year", "Sales Cr.Memo Line"."Shortcut Dimension 2 Code");
    IF StudentsStatus.FINDLAST THEN
    grade := StudentsStatus.Grade;
    */
    /*
    DimensionValue.RESET;
    DimensionValue.SETFILTER("Dimension Code", 'GRADE');
    DimensionValue.SETRANGE(Code, grade);
    IF DimensionValue.FINDFIRST THEN
    txtSalesInfo[21] := DimensionValue.Name;
    */
    DimSetEntry.reset;
    DimSetEntry.SetRange("Dimension Set ID", "Dimension Set ID");
    DimSetEntry.SetRange("Dimension Code", GLSetup."Shortcut Dimension 3 Code");
    if DimSetEntry.FindFirst() then begin
    txtSalesInfo[22] := DimSetEntry."Dimension Value Name";
    end;
    txtSalesInfo[21] := "Shortcut Dimension 2 Code";
    txtSalesInfo[1] := "Document No.";
    txtSalesInfo[2] := FORMAT("Posting Date");

    txtSalesInfo[23] := "Sales Cr.Memo Header"."External Document No.";// add code


    txtSalesInfo[3] := "Sell-to Customer No.";
    txtSalesInfo[4] := "Sales Cr.Memo Header"."Sell-to Customer Name";
    txtSalesInfo[5] := "No.";
    txtSalesInfo[6] := Description;
    txtSalesInfo[7] := FORMAT(-Quantity);
    txtSalesInfo[8] := FORMAT(-"Unit Price");
    txtSalesInfo[9] := FORMAT(-"Line Amount");
    txtSalesInfo[10] := FORMAT(-"VAT %");
    txtSalesInfo[11] := FORMAT(-"VAT Base Amount" * ("VAT %") / 100);
    txtSalesInfo[12] := Recitem."Inventory Posting Group";
    ///txtSalesInfo[13] := "Product Group Code";
    txtSalesInfo[14] := "Sales Cr.Memo Header"."External Document No.";
    IF SalesPerson.GET(SalesInvHdr."Salesperson Code") THEN txtSalesInfo[15] := SalesPerson.Name;
    txtSalesInfo[16] := "Location Code";
    txtSalesInfo[18] := "Shortcut Dimension 1 Code";
    txtSalesInfo[19] := "Sales Cr.Memo Header"."Currency Code";
    txtSalesInfo[20] := '';
    //txtSalesInfo[22] := "Sales Cr.Memo Header"."Posting Description";
    //-----DRGKH0011----//END
    IF PrintToExcel THEN MakeExcelBody;
    end;
    }
    trigger OnPreDataItem()
    begin
    //"Sales Cr.Memo Header".SETFILTER("Posting Date", txtDateFilter);
    "Sales Cr.Memo Header".SETFILTER("Sales Cr.Memo Header"."Posting Date", '%1..%2', txtDateFilter, dtDateFilterTo);
    end;
    }
    }
    requestpage
    {
    layout
    {
    area(content)
    {
    field(txtDateFilter; txtDateFilter)
    {
    ApplicationArea = all;
    Caption = 'Date From';
    }
    field(dtDateFilterTo; dtDateFilterTo)
    {
    ApplicationArea = all;
    Caption = 'Date To';
    }
    }
    }
    actions
    {
    }
    }
    labels
    {
    }
    trigger OnInitReport()
    begin
    PrintToExcel := TRUE;
    end;

    trigger OnPostReport()
    begin
    ///IF PrintToExcel THEN

    ///ExcelBuff.CreateBookAndOpenExcel('', 'Sales Register', '', '', USERID);
    IF PrintToExcel THEN begin
    ExcelBuff.CreateNewBook('Sales Register');
    ExcelBuff.WriteSheet('Sales Register', CompanyName(), UserId());
    ExcelBuff.CloseBook();
    ExcelBuff.OpenExcel();
    Error('');
    end;
    end;

    trigger OnPreReport()
    begin
    CompanyInfo.GET;
    IF CompanyInfo.FINDFIRST THEN compname := CompanyInfo.Name;
    IF PrintToExcel THEN MakeExcelInfo;
    end;

    var
    CustDesc: Text[50];
    RecCust: Record "Customer";
    TotCost: Decimal;
    ProfitPct: Decimal;
    SalesInvHdr: Record "Sales Invoice Header";
    Recitem: Record "Item";
    SalesPerson: Record "Salesperson/Purchaser";
    TotalAdjCostLCY: Decimal;
    CostLCY: Decimal;
    CostCalcMgt: Codeunit "Cost Calculation Management";
    UnitCost: Decimal;
    VendorCost: Decimal;
    RecVend: Record "Vendor";
    ValueEntry: Record "Value Entry";
    intRowNo: Integer;
    txtData: array[255] of Text[250];
    ExcelBuff: Record "Excel Buffer" temporary;
    PrintToExcel: Boolean;
    RecShipmentHe: Record "Sales Shipment Header";
    txtOrderNo: Text[20];
    txtShipmentNo: Text[20];
    RecDimensionSet: Record "Dimension Set Entry";
    Dimensextion: Text[40];
    DefaultDIme: Record "Dimension Set Entry";
    Issuetype: Text[40];
    Recitem1: Record "Item";
    RecCust1: Record "Customer";
    recSalesInvLine: Record "Sales Invoice Line";
    txtSalesInfo: array[22] of Text[250];
    txtDocDate: Date;
    txtCustNo: Integer;
    txtCustName: Text[50];
    txtDateFilter: Date;
    dtDateFilterTo: Date;
    recGL: Record "G/L Account";
    CompanyInfo: Record "Company Information";
    compname: Text[100];
    //StudentsStatus: Record "50000";
    DimensionValue: Record "Dimension Value";
    grade: Text;
    GLSetup: Record "General Ledger Setup";
    DimSetEntry: Record "Dimension Set Entry";

    procedure MakeExcelInfo()
    begin
    MakeExcelHeader;
    end;

    procedure MakeExcelHeader()
    begin
    ExcelBuff.NewRow;
    ExcelBuff.AddColumn(compname, FALSE, '', TRUE, FALSE, TRUE, '', ExcelBuff."Cell Type"::Text);
    ExcelBuff.NewRow;
    ExcelBuff.AddColumn('Sales Register', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text);
    ExcelBuff.NewRow;
    ExcelBuff.AddColumn('Date Range : ' + FORMAT(txtDateFilter) + '..' + FORMAT(dtDateFilterTo), FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text);
    ExcelBuff.NewRow;
    ExcelBuff.AddColumn('', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text);
    ExcelBuff.NewRow;
    ExcelBuff.AddColumn('Inv No', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //1
    ExcelBuff.AddColumn('Invoice Date', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //2

    ExcelBuff.AddColumn('External Document No.', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //23

    ExcelBuff.AddColumn('Customer No.', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //3
    ExcelBuff.AddColumn('Customer Name', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //4
    ExcelBuff.AddColumn('No.', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //5
    ExcelBuff.AddColumn('Description', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //6
    ExcelBuff.AddColumn('Qty', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //7
    ExcelBuff.AddColumn('Unit Price', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //8
    ExcelBuff.AddColumn('Total Price', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //9
    ExcelBuff.AddColumn('Unit Cost', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //10
    ExcelBuff.AddColumn('VAT %', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //11
    ExcelBuff.AddColumn('VAT Amount', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //12
    ExcelBuff.AddColumn('Total Cost', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //13
    ExcelBuff.AddColumn('Total Profit', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //14
    ExcelBuff.AddColumn(GLSetup."Shortcut Dimension 1 Code", FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //15
    ExcelBuff.AddColumn(GLSetup."Shortcut Dimension 2 Code", FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //16
    ExcelBuff.AddColumn(GLSetup."Shortcut Dimension 3 Code", FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //17
    end;

    procedure MakeExcelBody()
    begin
    ExcelBuff.NewRow;
    ExcelBuff.AddColumn(txtSalesInfo[1], FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //1
    ExcelBuff.AddColumn(txtSalesInfo[2], FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //2

    ExcelBuff.AddColumn(txtSalesInfo[23], FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //23 code added

    ExcelBuff.AddColumn(txtSalesInfo[3], FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //3
    ExcelBuff.AddColumn(txtSalesInfo[4], FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //4
    ExcelBuff.AddColumn(txtSalesInfo[5], FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Number); //5
    ExcelBuff.AddColumn(txtSalesInfo[6], FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Number); //6
    ExcelBuff.AddColumn(txtSalesInfo[7], FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Number); //7
    ExcelBuff.AddColumn(txtSalesInfo[8], FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Number); //8
    ExcelBuff.AddColumn(txtSalesInfo[9], FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Number); //9
    ExcelBuff.AddColumn(FORMAT(UnitCost), FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Number); //10
    ExcelBuff.AddColumn(txtSalesInfo[10], FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Number); //11
    ExcelBuff.AddColumn(txtSalesInfo[11], FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Number); //12
    ExcelBuff.AddColumn(FORMAT(TotCost), FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //13
    ExcelBuff.AddColumn(ProfitPct, FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Number); //14
    ExcelBuff.AddColumn(txtSalesInfo[18], FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //15
    ExcelBuff.AddColumn(txtSalesInfo[21], FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //16
    ExcelBuff.AddColumn(txtSalesInfo[22], FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //17
    end;
    }

  • CrackBC Profile Picture
    CrackBC on at
    RE: Sales Inv Num in Report

    after adding these fields, now extrernal doc number is not coming  on column heading and values are coming date

    txtSalesInfo[23]:="Sales Cr.Memo Header"."External Document No.";

    txtSalesInfo[23]:="Sales Invoice Header"."External Document No."; 

    Like this its coming now

    sr1.jpg

  • Suggested answer
    pankaj.k Profile Picture
    pankaj.k 976 on at
    RE: Sales Inv Num in Report

    Hi

    Please add Green Color Row in your code that will help you to insert Ext. Doc No

    report 50093 "Sales Register"
    {
    // DRGKK :: Shipment no. added
    ProcessingOnly = true;
    UsageCategory = ReportsAndAnalysis;
    ApplicationArea = Basic, Suite;

    dataset
    {
    dataitem("Sales Invoice Header";"Sales Invoice Header")
    {
    RequestFilterFields = "No.";

    dataitem("Sales Invoice Line";"Sales Invoice Line")
    {
    DataItemLink = "Document No."=FIELD("No.");
    DataItemTableView = SORTING("Document No.", "Line No.");

    trigger OnAfterGetRecord()begin
    txtShipmentNo:='';
    CostLCY:=0;
    TotalAdjCostLCY:=0;
    TotCost:=0;
    VendorCost:=0;
    ProfitPct:=0;
    IF RecCust.GET("Sell-to Customer No.")THEN;
    CostLCY:=CostLCY + (Quantity * "Unit Cost (LCY)");
    //TotalAdjCostLCY := TotalAdjCostLCY + CostCalcMgt.CalcSalesInvLineCostLCY("Sales Invoice Line");
    //UNTIL NEXT = 0;
    //END;
    //TotCost := TotalAdjCostLCY - CostLCY;
    ValueEntry.RESET;
    ValueEntry.SETCURRENTKEY("Item No.", "Document No.", "Posting Date");
    ValueEntry.SETRANGE("Item No.", "No.");
    ValueEntry.SETRANGE("Document No.", "Document No.");
    ValueEntry.SETRANGE("Posting Date", "Posting Date");
    IF ValueEntry.FINDFIRST THEN REPEAT TotCost+=ValueEntry."Cost Amount (Actual)";
    UNTIL ValueEntry.NEXT = 0;
    IF Quantity <> 0 THEN UnitCost:=(TotCost) / Quantity;
    IF "Sales Invoice Line".Quantity = 0 THEN CurrReport.SKIP;
    IF "Sales Invoice Line".Amount <> 0 THEN ProfitPct:=(("Sales Invoice Line".Amount - ABS(TotCost)));
    IF Quantity <> 0 THEN UnitCost:=TotCost / Quantity;
    IF Recitem.GET("No.")THEN IF "Sales Invoice Line".Type = "Sales Invoice Line".Type::"G/L Account" THEN txtSalesInfo[17]:=''
    ELSE
    txtSalesInfo[17]:=Recitem."Vendor No.";
    txtSalesInfo[1]:="Document No.";
    txtSalesInfo[2]:=FORMAT("Posting Date");

    txtSalesInfo[23]:=FORMAT(AddYourField);// add code

    txtSalesInfo[3]:="Sell-to Customer No.";
    txtSalesInfo[4]:="Sales Invoice Header"."Sell-to Customer Name";
    txtSalesInfo[5]:="No.";
    txtSalesInfo[6]:=Description;
    txtSalesInfo[7]:=FORMAT(Quantity);
    txtSalesInfo[8]:=FORMAT("Unit Price");
    txtSalesInfo[9]:=FORMAT("Line Amount");
    txtSalesInfo[10]:=FORMAT("VAT %");
    txtSalesInfo[11]:=FORMAT("VAT Base Amount" * ("VAT %") / 100);
    txtSalesInfo[16]:="Sales Invoice Line"."Location Code";
    txtSalesInfo[12]:=Recitem."Inventory Posting Group";
    ///txtSalesInfo[13] := "Product Group Code";
    txtSalesInfo[14]:="Sales Invoice Header"."External Document No.";
    txtSalesInfo[19]:="Sales Invoice Header"."Currency Code";
    txtSalesInfo[20]:="Sales Invoice Header"."Order No.";
    //txtSalesInfo[22] := "Sales Invoice Header"."Posting Description";
    IF SalesPerson.GET("Sales Invoice Header"."Salesperson Code")THEN txtSalesInfo[15]:=SalesPerson.Name;
    txtSalesInfo[18]:="Sales Invoice Line"."Shortcut Dimension 1 Code";
    //-----DRGKK, BEGIN-----//
    txtOrderNo:='';
    txtShipmentNo:='';
    SalesInvHdr.RESET;
    SalesInvHdr.SETRANGE("No.", "Sales Invoice Line"."Document No.");
    IF SalesInvHdr.FIND('-')THEN BEGIN
    txtOrderNo:=SalesInvHdr."Order No." END;
    RecShipmentHe.RESET;
    RecShipmentHe.SETRANGE(RecShipmentHe."Order No.", txtOrderNo);
    IF RecShipmentHe.FIND('-')THEN BEGIN
    txtShipmentNo:=RecShipmentHe."No.";
    END;
    txtSalesInfo[21]:="Shortcut Dimension 2 Code";
    DimSetEntry.reset;
    DimSetEntry.SetRange("Dimension Set ID", "Dimension Set ID");
    DimSetEntry.SetRange("Dimension Code", GLSetup."Shortcut Dimension 3 Code");
    if DimSetEntry.FindFirst()then begin
    DimSetEntry.CalcFields("Dimension Value Name");
    DimSetEntry.CalcFields("Dimension Value Name");
    txtSalesInfo[22]:=DimSetEntry."Dimension Value Name";
    end;
    /*
    CLEAR(grade);
    StudentsStatus.RESET;
    StudentsStatus.SETRANGE("Student ID", "Sales Invoice Line"."Sell-to Customer No.");
    StudentsStatus.SETFILTER("Academic Year", "Sales Invoice Line"."Shortcut Dimension 2 Code");
    IF StudentsStatus.FINDLAST THEN
    grade := StudentsStatus.Grade;
    */
    /*
    DimensionValue.RESET;
    DimensionValue.SETFILTER("Dimension Code", 'GRADE');
    DimensionValue.SETRANGE(Code, grade);
    IF DimensionValue.FINDFIRST THEN
    txtSalesInfo[21] := DimensionValue.Name;
    //-----DRGKK, END-----//
    Issuetype := ' ';
    DefaultDIme.RESET;
    DefaultDIme.SETFILTER("Dimension Code", '%1', 'GRADE');
    DefaultDIme.SETRANGE("Dimension Set ID", "Dimension Set ID");
    //DefaultDIme.SETFILTER("Dimension Code",'%1','CUSTOMERSEGMENT');
    IF DefaultDIme.FIND('-') THEN BEGIN
    Issuetype := DefaultDIme."Dimension Value Code";
    END;
    */
    IF PrintToExcel THEN MakeExcelBody;
    end;
    }
    trigger OnPreDataItem()begin
    GLSetup.get();
    IF txtDateFilter <> 0D THEN "Sales Invoice Header".SETFILTER("Sales Invoice Header"."Posting Date", '%1..%2', txtDateFilter, dtDateFilterTo);
    end;
    }
    dataitem("Sales Cr.Memo Header";"Sales Cr.Memo Header")
    {
    RequestFilterFields = "No.";

    dataitem("Sales Cr.Memo Line";"Sales Cr.Memo Line")
    {
    DataItemLink = "Document No."=FIELD("No.");
    DataItemTableView = WHERE("No."=FILTER(<>' '), Type=FILTER(<>' '));

    trigger OnAfterGetRecord()begin
    //-----DRGKH0011----//BEGIN
    txtShipmentNo:='';
    TotCost:=0;
    ProfitPct:=0;
    IF Recitem.GET("No.")THEN;
    IF "Sales Cr.Memo Line".Type = "Sales Cr.Memo Line".Type::"G/L Account" THEN txtSalesInfo[17]:=''
    ELSE
    txtSalesInfo[17]:=Recitem."Vendor No.";
    ValueEntry.RESET;
    ValueEntry.SETCURRENTKEY("Item No.", "Document No.", "Posting Date");
    ValueEntry.SETRANGE("Item No.", "No.");
    ValueEntry.SETRANGE("Document No.", "Document No.");
    ValueEntry.SETRANGE("Posting Date", "Posting Date");
    IF ValueEntry.FINDFIRST THEN REPEAT TotCost+=ValueEntry."Cost Amount (Actual)";
    UNTIL ValueEntry.NEXT = 0;
    IF Quantity <> 0 THEN UnitCost:=(TotCost) / Quantity;
    IF "Sales Cr.Memo Line".Amount <> 0 THEN ProfitPct:=(("Sales Cr.Memo Line".Amount - ABS(TotCost)));
    /*
    CLEAR(grade);
    StudentsStatus.RESET;
    StudentsStatus.SETRANGE("Student ID", "Sales Cr.Memo Line"."Sell-to Customer No.");
    StudentsStatus.SETFILTER("Academic Year", "Sales Cr.Memo Line"."Shortcut Dimension 2 Code");
    IF StudentsStatus.FINDLAST THEN
    grade := StudentsStatus.Grade;
    */
    /*
    DimensionValue.RESET;
    DimensionValue.SETFILTER("Dimension Code", 'GRADE');
    DimensionValue.SETRANGE(Code, grade);
    IF DimensionValue.FINDFIRST THEN
    txtSalesInfo[21] := DimensionValue.Name;
    */
    DimSetEntry.reset;
    DimSetEntry.SetRange("Dimension Set ID", "Dimension Set ID");
    DimSetEntry.SetRange("Dimension Code", GLSetup."Shortcut Dimension 3 Code");
    if DimSetEntry.FindFirst()then begin
    txtSalesInfo[22]:=DimSetEntry."Dimension Value Name";
    end;
    txtSalesInfo[21]:="Shortcut Dimension 2 Code";
    txtSalesInfo[1]:="Document No.";
    txtSalesInfo[2]:=FORMAT("Posting Date");

    txtSalesInfo[23]:=FORMAT(AddYourField);// add code


    txtSalesInfo[3]:="Sell-to Customer No.";
    txtSalesInfo[4]:="Sales Cr.Memo Header"."Sell-to Customer Name";
    txtSalesInfo[5]:="No.";
    txtSalesInfo[6]:=Description;
    txtSalesInfo[7]:=FORMAT(-Quantity);
    txtSalesInfo[8]:=FORMAT(-"Unit Price");
    txtSalesInfo[9]:=FORMAT(-"Line Amount");
    txtSalesInfo[10]:=FORMAT(-"VAT %");
    txtSalesInfo[11]:=FORMAT(-"VAT Base Amount" * ("VAT %") / 100);
    txtSalesInfo[12]:=Recitem."Inventory Posting Group";
    ///txtSalesInfo[13] := "Product Group Code";
    txtSalesInfo[14]:="Sales Cr.Memo Header"."External Document No.";
    IF SalesPerson.GET(SalesInvHdr."Salesperson Code")THEN txtSalesInfo[15]:=SalesPerson.Name;
    txtSalesInfo[16]:="Location Code";
    txtSalesInfo[18]:="Shortcut Dimension 1 Code";
    txtSalesInfo[19]:="Sales Cr.Memo Header"."Currency Code";
    txtSalesInfo[20]:='';
    //txtSalesInfo[22] := "Sales Cr.Memo Header"."Posting Description";
    //-----DRGKH0011----//END
    IF PrintToExcel THEN MakeExcelBody;
    end;
    }
    trigger OnPreDataItem()begin
    //"Sales Cr.Memo Header".SETFILTER("Posting Date", txtDateFilter);
    "Sales Cr.Memo Header".SETFILTER("Sales Cr.Memo Header"."Posting Date", '%1..%2', txtDateFilter, dtDateFilterTo);
    end;
    }
    }
    requestpage
    {
    layout
    {
    area(content)
    {
    field(txtDateFilter;txtDateFilter)
    {
    ApplicationArea = all;
    Caption = 'Date From';
    }
    field(dtDateFilterTo;dtDateFilterTo)
    {
    ApplicationArea = all;
    Caption = 'Date To';
    }
    }
    }
    actions
    {
    }
    }
    labels
    {
    }
    trigger OnInitReport()begin
    PrintToExcel:=TRUE;
    end;
    trigger OnPostReport()begin
    ///IF PrintToExcel THEN

    ///ExcelBuff.CreateBookAndOpenExcel('', 'Sales Register', '', '', USERID);
    IF PrintToExcel THEN begin
    ExcelBuff.CreateNewBook('Sales Register');
    ExcelBuff.WriteSheet('Sales Register', CompanyName(), UserId());
    ExcelBuff.CloseBook();
    ExcelBuff.OpenExcel();
    Error('');
    end;
    end;
    trigger OnPreReport()begin
    CompanyInfo.GET;
    IF CompanyInfo.FINDFIRST THEN compname:=CompanyInfo.Name;
    IF PrintToExcel THEN MakeExcelInfo;
    end;
    var CustDesc: Text[50];
    RecCust: Record "Customer";
    TotCost: Decimal;
    ProfitPct: Decimal;
    SalesInvHdr: Record "Sales Invoice Header";
    Recitem: Record "Item";
    SalesPerson: Record "Salesperson/Purchaser";
    TotalAdjCostLCY: Decimal;
    CostLCY: Decimal;
    CostCalcMgt: Codeunit "Cost Calculation Management";
    UnitCost: Decimal;
    VendorCost: Decimal;
    RecVend: Record "Vendor";
    ValueEntry: Record "Value Entry";
    intRowNo: Integer;
    txtData: array[255]of Text[250];
    ExcelBuff: Record "Excel Buffer" temporary;
    PrintToExcel: Boolean;
    RecShipmentHe: Record "Sales Shipment Header";
    txtOrderNo: Text[20];
    txtShipmentNo: Text[20];
    RecDimensionSet: Record "Dimension Set Entry";
    Dimensextion: Text[40];
    DefaultDIme: Record "Dimension Set Entry";
    Issuetype: Text[40];
    Recitem1: Record "Item";
    RecCust1: Record "Customer";
    recSalesInvLine: Record "Sales Invoice Line";
    txtSalesInfo: array[22]of Text[250];
    txtDocDate: Date;
    txtCustNo: Integer;
    txtCustName: Text[50];
    txtDateFilter: Date;
    dtDateFilterTo: Date;
    recGL: Record "G/L Account";
    CompanyInfo: Record "Company Information";
    compname: Text[100];
    //StudentsStatus: Record "50000";
    DimensionValue: Record "Dimension Value";
    grade: Text;
    GLSetup: Record "General Ledger Setup";
    DimSetEntry: Record "Dimension Set Entry";
    procedure MakeExcelInfo()begin
    MakeExcelHeader;
    end;
    procedure MakeExcelHeader()begin
    ExcelBuff.NewRow;
    ExcelBuff.AddColumn(compname, FALSE, '', TRUE, FALSE, TRUE, '', ExcelBuff."Cell Type"::Text);
    ExcelBuff.NewRow;
    ExcelBuff.AddColumn('Sales Register', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text);
    ExcelBuff.NewRow;
    ExcelBuff.AddColumn('Date Range : ' + FORMAT(txtDateFilter) + '..' + FORMAT(dtDateFilterTo), FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text);
    ExcelBuff.NewRow;
    ExcelBuff.AddColumn('', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text);
    ExcelBuff.NewRow;
    ExcelBuff.AddColumn('Inv No', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //1
    ExcelBuff.AddColumn('Invoice Date', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //2

    ExcelBuff.AddColumn('AddYourField', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //23

    ExcelBuff.AddColumn('Customer No.', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //3
    ExcelBuff.AddColumn('Customer Name', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //4
    ExcelBuff.AddColumn('No.', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //5
    ExcelBuff.AddColumn('Description', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //6
    ExcelBuff.AddColumn('Qty', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //7
    ExcelBuff.AddColumn('Unit Price', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //8
    ExcelBuff.AddColumn('Total Price', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //9
    ExcelBuff.AddColumn('Unit Cost', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //10
    ExcelBuff.AddColumn('VAT %', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //11
    ExcelBuff.AddColumn('VAT Amount', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //12
    ExcelBuff.AddColumn('Total Cost', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //13
    ExcelBuff.AddColumn('Total Profit', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //14
    ExcelBuff.AddColumn(GLSetup."Shortcut Dimension 1 Code", FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //15
    ExcelBuff.AddColumn(GLSetup."Shortcut Dimension 2 Code", FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //16
    ExcelBuff.AddColumn(GLSetup."Shortcut Dimension 3 Code", FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //17
    end;
    procedure MakeExcelBody()begin
    ExcelBuff.NewRow;
    ExcelBuff.AddColumn(txtSalesInfo[1], FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //1
    ExcelBuff.AddColumn(txtSalesInfo[2], FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //2

    ExcelBuff.AddColumn(txtSalesInfo[2], FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //23 code added

    ExcelBuff.AddColumn(txtSalesInfo[3], FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //3
    ExcelBuff.AddColumn(txtSalesInfo[4], FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //4
    ExcelBuff.AddColumn(txtSalesInfo[5], FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Number); //5
    ExcelBuff.AddColumn(txtSalesInfo[6], FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Number); //6
    ExcelBuff.AddColumn(txtSalesInfo[7], FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Number); //7
    ExcelBuff.AddColumn(txtSalesInfo[8], FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Number); //8
    ExcelBuff.AddColumn(txtSalesInfo[9], FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Number); //9
    ExcelBuff.AddColumn(FORMAT(UnitCost), FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Number); //10
    ExcelBuff.AddColumn(txtSalesInfo[10], FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Number); //11
    ExcelBuff.AddColumn(txtSalesInfo[11], FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Number); //12
    ExcelBuff.AddColumn(FORMAT(TotCost), FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //13
    ExcelBuff.AddColumn(ProfitPct, FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Number); //14
    ExcelBuff.AddColumn(txtSalesInfo[18], FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //15
    ExcelBuff.AddColumn(txtSalesInfo[21], FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //16
    ExcelBuff.AddColumn(txtSalesInfo[22], FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //17
    end;
    }

  • CrackBC Profile Picture
    CrackBC on at
    RE: Sales Inv Num in Report

    Right now there is now preview option is coming. only schedule and Ok button is coming. if i make it as a new report, pls help me to add in External doc as from Posted Sales cred Memo in this

  • Suggested answer
    Nitin Verma Profile Picture
    Nitin Verma 21,571 Moderator on at
    RE: Sales Inv Num in Report

    Why dont you create RDLC layout and then without preview the report, just print in Excel. like below. and your required fields in the layout only.

    pastedimage1671606562914v1.png

  • CrackBC Profile Picture
    CrackBC on at
    RE: Sales Inv Num in Report

    Yes i got it. now how can i create/add that field in this report and in excel layout also

    report 50093 "Sales Register"

    report 50093 "Sales Register"
{
    // DRGKK :: Shipment no. added
    ProcessingOnly = true;
    UsageCategory = ReportsAndAnalysis;
    ApplicationArea = Basic, Suite;

    dataset
    {
        dataitem("Sales Invoice Header";"Sales Invoice Header")
        {
            RequestFilterFields = "No.";

            dataitem("Sales Invoice Line";"Sales Invoice Line")
            {
                DataItemLink = "Document No."=FIELD("No.");
                DataItemTableView = SORTING("Document No.", "Line No.");

                trigger OnAfterGetRecord()begin
                    txtShipmentNo:='';
                    CostLCY:=0;
                    TotalAdjCostLCY:=0;
                    TotCost:=0;
                    VendorCost:=0;
                    ProfitPct:=0;
                    IF RecCust.GET("Sell-to Customer No.")THEN;
                    CostLCY:=CostLCY   (Quantity * "Unit Cost (LCY)");
                    //TotalAdjCostLCY := TotalAdjCostLCY   CostCalcMgt.CalcSalesInvLineCostLCY("Sales Invoice Line");
                    //UNTIL NEXT = 0;
                    //END;
                    //TotCost := TotalAdjCostLCY - CostLCY;
                    ValueEntry.RESET;
                    ValueEntry.SETCURRENTKEY("Item No.", "Document No.", "Posting Date");
                    ValueEntry.SETRANGE("Item No.", "No.");
                    ValueEntry.SETRANGE("Document No.", "Document No.");
                    ValueEntry.SETRANGE("Posting Date", "Posting Date");
                    IF ValueEntry.FINDFIRST THEN REPEAT TotCost =ValueEntry."Cost Amount (Actual)";
                        UNTIL ValueEntry.NEXT = 0;
                    IF Quantity <> 0 THEN UnitCost:=(TotCost) / Quantity;
                    IF "Sales Invoice Line".Quantity = 0 THEN CurrReport.SKIP;
                    IF "Sales Invoice Line".Amount <> 0 THEN ProfitPct:=(("Sales Invoice Line".Amount - ABS(TotCost)));
                    IF Quantity <> 0 THEN UnitCost:=TotCost / Quantity;
                    IF Recitem.GET("No.")THEN IF "Sales Invoice Line".Type = "Sales Invoice Line".Type::"G/L Account" THEN txtSalesInfo[17]:=''
                        ELSE
                            txtSalesInfo[17]:=Recitem."Vendor No.";
                    txtSalesInfo[1]:="Document No.";
                    txtSalesInfo[2]:=FORMAT("Posting Date");
                    txtSalesInfo[3]:="Sell-to Customer No.";
                    txtSalesInfo[4]:="Sales Invoice Header"."Sell-to Customer Name";
                    txtSalesInfo[5]:="No.";
                    txtSalesInfo[6]:=Description;
                    txtSalesInfo[7]:=FORMAT(Quantity);
                    txtSalesInfo[8]:=FORMAT("Unit Price");
                    txtSalesInfo[9]:=FORMAT("Line Amount");
                    txtSalesInfo[10]:=FORMAT("VAT %");
                    txtSalesInfo[11]:=FORMAT("VAT Base Amount" * ("VAT %") / 100);
                    txtSalesInfo[16]:="Sales Invoice Line"."Location Code";
                    txtSalesInfo[12]:=Recitem."Inventory Posting Group";
                    ///txtSalesInfo[13] := "Product Group Code"; 
 txtSalesInfo[14]:="Sales Invoice Header"."External Document No.";
                    txtSalesInfo[19]:="Sales Invoice Header"."Currency Code";
                    txtSalesInfo[20]:="Sales Invoice Header"."Order No.";
                    //txtSalesInfo[22] := "Sales Invoice Header"."Posting Description";
                    IF SalesPerson.GET("Sales Invoice Header"."Salesperson Code")THEN txtSalesInfo[15]:=SalesPerson.Name;
                    txtSalesInfo[18]:="Sales Invoice Line"."Shortcut Dimension 1 Code";
                    //-----DRGKK, BEGIN-----//
                    txtOrderNo:='';
                    txtShipmentNo:='';
                    SalesInvHdr.RESET;
                    SalesInvHdr.SETRANGE("No.", "Sales Invoice Line"."Document No.");
                    IF SalesInvHdr.FIND('-')THEN BEGIN
                        txtOrderNo:=SalesInvHdr."Order No." END;
                    RecShipmentHe.RESET;
                    RecShipmentHe.SETRANGE(RecShipmentHe."Order No.", txtOrderNo);
                    IF RecShipmentHe.FIND('-')THEN BEGIN
                        txtShipmentNo:=RecShipmentHe."No.";
                    END;
                    txtSalesInfo[21]:="Shortcut Dimension 2 Code";
                    DimSetEntry.reset;
                    DimSetEntry.SetRange("Dimension Set ID", "Dimension Set ID");
                    DimSetEntry.SetRange("Dimension Code", GLSetup."Shortcut Dimension 3 Code");
                    if DimSetEntry.FindFirst()then begin
                        DimSetEntry.CalcFields("Dimension Value Name");
                        DimSetEntry.CalcFields("Dimension Value Name");
                        txtSalesInfo[22]:=DimSetEntry."Dimension Value Name";
                    end;
                    /*
                    CLEAR(grade);
                    StudentsStatus.RESET;
                    StudentsStatus.SETRANGE("Student ID", "Sales Invoice Line"."Sell-to Customer No.");
                    StudentsStatus.SETFILTER("Academic Year", "Sales Invoice Line"."Shortcut Dimension 2 Code");
                    IF StudentsStatus.FINDLAST THEN
                        grade := StudentsStatus.Grade;
                    */
                    /*
                    DimensionValue.RESET;
                    DimensionValue.SETFILTER("Dimension Code", 'GRADE');
                    DimensionValue.SETRANGE(Code, grade);
                    IF DimensionValue.FINDFIRST THEN
                        txtSalesInfo[21] := DimensionValue.Name;
                    //-----DRGKK, END-----//
                    Issuetype := ' ';
                    DefaultDIme.RESET;
                    DefaultDIme.SETFILTER("Dimension Code", '%1', 'GRADE');
                    DefaultDIme.SETRANGE("Dimension Set ID", "Dimension Set ID");
                    //DefaultDIme.SETFILTER("Dimension Code",'%1','CUSTOMERSEGMENT');
                    IF DefaultDIme.FIND('-') THEN BEGIN
                        Issuetype := DefaultDIme."Dimension Value Code";
                    END;
                    */
                    IF PrintToExcel THEN MakeExcelBody;
                end;
            }
            trigger OnPreDataItem()begin
                GLSetup.get();
                IF txtDateFilter <> 0D THEN "Sales Invoice Header".SETFILTER("Sales Invoice Header"."Posting Date", '%1..%2', txtDateFilter, dtDateFilterTo);
            end;
        }
        dataitem("Sales Cr.Memo Header";"Sales Cr.Memo Header")
        {
            RequestFilterFields = "No.";

            dataitem("Sales Cr.Memo Line";"Sales Cr.Memo Line")
            {
                DataItemLink = "Document No."=FIELD("No.");
                DataItemTableView = WHERE("No."=FILTER(<>' '), Type=FILTER(<>' '));

                trigger OnAfterGetRecord()begin
                    //-----DRGKH0011----//BEGIN
                    txtShipmentNo:='';
                    TotCost:=0;
                    ProfitPct:=0;
                    IF Recitem.GET("No.")THEN;
                    IF "Sales Cr.Memo Line".Type = "Sales Cr.Memo Line".Type::"G/L Account" THEN txtSalesInfo[17]:=''
                    ELSE
                        txtSalesInfo[17]:=Recitem."Vendor No.";
                    ValueEntry.RESET;
                    ValueEntry.SETCURRENTKEY("Item No.", "Document No.", "Posting Date");
                    ValueEntry.SETRANGE("Item No.", "No.");
                    ValueEntry.SETRANGE("Document No.", "Document No.");
                    ValueEntry.SETRANGE("Posting Date", "Posting Date");
                    IF ValueEntry.FINDFIRST THEN REPEAT TotCost =ValueEntry."Cost Amount (Actual)";
                        UNTIL ValueEntry.NEXT = 0;
                    IF Quantity <> 0 THEN UnitCost:=(TotCost) / Quantity;
                    IF "Sales Cr.Memo Line".Amount <> 0 THEN ProfitPct:=(("Sales Cr.Memo Line".Amount - ABS(TotCost)));
                    /*
                    CLEAR(grade);
                    StudentsStatus.RESET;
                    StudentsStatus.SETRANGE("Student ID", "Sales Cr.Memo Line"."Sell-to Customer No.");
                    StudentsStatus.SETFILTER("Academic Year", "Sales Cr.Memo Line"."Shortcut Dimension 2 Code");
                    IF StudentsStatus.FINDLAST THEN
                        grade := StudentsStatus.Grade;
                    */
                    /*
                    DimensionValue.RESET;
                    DimensionValue.SETFILTER("Dimension Code", 'GRADE');
                    DimensionValue.SETRANGE(Code, grade);
                    IF DimensionValue.FINDFIRST THEN
                        txtSalesInfo[21] := DimensionValue.Name;
                    */
                    DimSetEntry.reset;
                    DimSetEntry.SetRange("Dimension Set ID", "Dimension Set ID");
                    DimSetEntry.SetRange("Dimension Code", GLSetup."Shortcut Dimension 3 Code");
                    if DimSetEntry.FindFirst()then begin
                        txtSalesInfo[22]:=DimSetEntry."Dimension Value Name";
                    end;
                    txtSalesInfo[21]:="Shortcut Dimension 2 Code";
                    txtSalesInfo[1]:="Document No.";
                    txtSalesInfo[2]:=FORMAT("Posting Date");
                    txtSalesInfo[3]:="Sell-to Customer No.";
                    txtSalesInfo[4]:="Sales Cr.Memo Header"."Sell-to Customer Name";
                    txtSalesInfo[5]:="No.";
                    txtSalesInfo[6]:=Description;
                    txtSalesInfo[7]:=FORMAT(-Quantity);
                    txtSalesInfo[8]:=FORMAT(-"Unit Price");
                    txtSalesInfo[9]:=FORMAT(-"Line Amount");
                    txtSalesInfo[10]:=FORMAT(-"VAT %");
                    txtSalesInfo[11]:=FORMAT(-"VAT Base Amount" * ("VAT %") / 100);
                    txtSalesInfo[12]:=Recitem."Inventory Posting Group";
                    ///txtSalesInfo[13] := "Product Group Code"; 
 txtSalesInfo[14]:="Sales Cr.Memo Header"."External Document No.";
                    IF SalesPerson.GET(SalesInvHdr."Salesperson Code")THEN txtSalesInfo[15]:=SalesPerson.Name;
                    txtSalesInfo[16]:="Location Code";
                    txtSalesInfo[18]:="Shortcut Dimension 1 Code";
                    txtSalesInfo[19]:="Sales Cr.Memo Header"."Currency Code";
                    txtSalesInfo[20]:='';
                    //txtSalesInfo[22] := "Sales Cr.Memo Header"."Posting Description";
                    //-----DRGKH0011----//END
                    IF PrintToExcel THEN MakeExcelBody;
                end;
            }
            trigger OnPreDataItem()begin
                //"Sales Cr.Memo Header".SETFILTER("Posting Date", txtDateFilter);
                "Sales Cr.Memo Header".SETFILTER("Sales Cr.Memo Header"."Posting Date", '%1..%2', txtDateFilter, dtDateFilterTo);
            end;
        }
    }
    requestpage
    {
        layout
        {
            area(content)
            {
                field(txtDateFilter;txtDateFilter)
                {
                    ApplicationArea = all;
                    Caption = 'Date From';
                }
                field(dtDateFilterTo;dtDateFilterTo)
                {
                    ApplicationArea = all;
                    Caption = 'Date To';
                }
            }
        }
        actions
        {
        }
    }
    labels
    {
    }
    trigger OnInitReport()begin
        PrintToExcel:=TRUE;
    end;
    trigger OnPostReport()begin
        ///IF PrintToExcel THEN 
 
///ExcelBuff.CreateBookAndOpenExcel('', 'Sales Register', '', '', USERID); 
 IF PrintToExcel THEN begin
            ExcelBuff.CreateNewBook('Sales Register');
            ExcelBuff.WriteSheet('Sales Register', CompanyName(), UserId());
            ExcelBuff.CloseBook();
            ExcelBuff.OpenExcel();
            Error('');
        end;
    end;
    trigger OnPreReport()begin
        CompanyInfo.GET;
        IF CompanyInfo.FINDFIRST THEN compname:=CompanyInfo.Name;
        IF PrintToExcel THEN MakeExcelInfo;
    end;
    var CustDesc: Text[50];
    RecCust: Record "Customer";
    TotCost: Decimal;
    ProfitPct: Decimal;
    SalesInvHdr: Record "Sales Invoice Header";
    Recitem: Record "Item";
    SalesPerson: Record "Salesperson/Purchaser";
    TotalAdjCostLCY: Decimal;
    CostLCY: Decimal;
    CostCalcMgt: Codeunit "Cost Calculation Management";
    UnitCost: Decimal;
    VendorCost: Decimal;
    RecVend: Record "Vendor";
    ValueEntry: Record "Value Entry";
    intRowNo: Integer;
    txtData: array[255]of Text[250];
    ExcelBuff: Record "Excel Buffer" temporary;
    PrintToExcel: Boolean;
    RecShipmentHe: Record "Sales Shipment Header";
    txtOrderNo: Text[20];
    txtShipmentNo: Text[20];
    RecDimensionSet: Record "Dimension Set Entry";
    Dimensextion: Text[40];
    DefaultDIme: Record "Dimension Set Entry";
    Issuetype: Text[40];
    Recitem1: Record "Item";
    RecCust1: Record "Customer";
    recSalesInvLine: Record "Sales Invoice Line";
    txtSalesInfo: array[22]of Text[250];
    txtDocDate: Date;
    txtCustNo: Integer;
    txtCustName: Text[50];
    txtDateFilter: Date;
    dtDateFilterTo: Date;
    recGL: Record "G/L Account";
    CompanyInfo: Record "Company Information";
    compname: Text[100];
    //StudentsStatus: Record "50000";
    DimensionValue: Record "Dimension Value";
    grade: Text;
    GLSetup: Record "General Ledger Setup";
    DimSetEntry: Record "Dimension Set Entry";
    procedure MakeExcelInfo()begin
        MakeExcelHeader;
    end;
    procedure MakeExcelHeader()begin
        ExcelBuff.NewRow;
        ExcelBuff.AddColumn(compname, FALSE, '', TRUE, FALSE, TRUE, '', ExcelBuff."Cell Type"::Text);
        ExcelBuff.NewRow;
        ExcelBuff.AddColumn('Sales Register', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text);
        ExcelBuff.NewRow;
        ExcelBuff.AddColumn('Date Range : '   FORMAT(txtDateFilter)   '..'   FORMAT(dtDateFilterTo), FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text);
        ExcelBuff.NewRow;
        ExcelBuff.AddColumn('', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text);
        ExcelBuff.NewRow;
        ExcelBuff.AddColumn('Inv No', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //1
        ExcelBuff.AddColumn('Invoice Date', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //2
        ExcelBuff.AddColumn('Customer No.', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //3
        ExcelBuff.AddColumn('Customer Name', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //4
        ExcelBuff.AddColumn('No.', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //5
        ExcelBuff.AddColumn('Description', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //6
        ExcelBuff.AddColumn('Qty', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //7
        ExcelBuff.AddColumn('Unit Price', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //8
        ExcelBuff.AddColumn('Total Price', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //9
        ExcelBuff.AddColumn('Unit Cost', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //10
        ExcelBuff.AddColumn('VAT %', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //11
        ExcelBuff.AddColumn('VAT Amount', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //12
        ExcelBuff.AddColumn('Total Cost', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //13
        ExcelBuff.AddColumn('Total Profit', FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //14
        ExcelBuff.AddColumn(GLSetup."Shortcut Dimension 1 Code", FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //15
        ExcelBuff.AddColumn(GLSetup."Shortcut Dimension 2 Code", FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //16
        ExcelBuff.AddColumn(GLSetup."Shortcut Dimension 3 Code", FALSE, '', TRUE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //17
    end;
    procedure MakeExcelBody()begin
        ExcelBuff.NewRow;
        ExcelBuff.AddColumn(txtSalesInfo[1], FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //1
        ExcelBuff.AddColumn(txtSalesInfo[2], FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //2
        ExcelBuff.AddColumn(txtSalesInfo[3], FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //3
        ExcelBuff.AddColumn(txtSalesInfo[4], FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //4
        ExcelBuff.AddColumn(txtSalesInfo[5], FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Number); //5
        ExcelBuff.AddColumn(txtSalesInfo[6], FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Number); //6
        ExcelBuff.AddColumn(txtSalesInfo[7], FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Number); //7
        ExcelBuff.AddColumn(txtSalesInfo[8], FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Number); //8
        ExcelBuff.AddColumn(txtSalesInfo[9], FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Number); //9
        ExcelBuff.AddColumn(FORMAT(UnitCost), FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Number); //10
        ExcelBuff.AddColumn(txtSalesInfo[10], FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Number); //11
        ExcelBuff.AddColumn(txtSalesInfo[11], FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Number); //12
        ExcelBuff.AddColumn(FORMAT(TotCost), FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //13
        ExcelBuff.AddColumn(ProfitPct, FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Number); //14
        ExcelBuff.AddColumn(txtSalesInfo[18], FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //15
        ExcelBuff.AddColumn(txtSalesInfo[21], FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //16
        ExcelBuff.AddColumn(txtSalesInfo[22], FALSE, '', FALSE, FALSE, FALSE, '', ExcelBuff."Cell Type"::Text); //17
    end;
}

  • Verified answer
    pankaj.k Profile Picture
    pankaj.k 976 on at
    RE: Sales Inv Num in Report

    Please download the latest Extension then check the code its not possible that this code create Excel report.

  • CrackBC Profile Picture
    CrackBC on at
    RE: Sales Inv Num in Report

    this is the whole code for this report. once again i will share and in report selection also i cannot able to see the excel layout. in this case what we need to.

    cod.jpg

    report 50093 "Sales Register"
    {
      dataset
      {
        dataitem("Sales Invoice Header"; "Sales Invoice Header")
        {
          dataitem("Sales Invoice Line"; "Sales Invoice Line")
          {
          }
        }
        dataitem("Sales Cr.Memo Header"; "Sales Cr.Memo Header")
        {
          dataitem("Sales Cr.Memo Line"; "Sales Cr.Memo Line")
          {
          }
        }
      }
      requestpage
      {
        layout
        {
          area(content)
          {
            field(txtDateFilter; txtDateFilter)
            {
              Caption = 'Date From';
            }
            field(dtDateFilterTo; dtDateFilterTo)
            {
              Caption = 'Date To';
            }
          }
        }
      }
      var CustDesc: Text[50];
      RecCust: Record Customer;
      TotCost: Decimal;
      ProfitPct: Decimal;
      SalesInvHdr: Record "Sales Invoice Header";
      Recitem: Record Item;
      SalesPerson: Record "Salesperson/Purchaser";
      TotalAdjCostLCY: Decimal;
      CostLCY: Decimal;
      CostCalcMgt: codeunit "Cost Calculation Management";
      UnitCost: Decimal;
      VendorCost: Decimal;
      RecVend: Record Vendor;
      ValueEntry: Record "Value Entry";
      intRowNo: Integer;
      txtData: array[255]of Text[250];
      ExcelBuff: Record "Excel Buffer" temporary;
      PrintToExcel: Boolean;
      RecShipmentHe: Record "Sales Shipment Header";
      txtOrderNo: Text[20];
      txtShipmentNo: Text[20];
      RecDimensionSet: Record "Dimension Set Entry";
      Dimensextion: Text[40];
      DefaultDIme: Record "Dimension Set Entry";
      Issuetype: Text[40];
      Recitem1: Record Item;
      RecCust1: Record Customer;
      recSalesInvLine: Record "Sales Invoice Line";
      txtSalesInfo: array[22]of Text[250];
      txtDocDate: Date;
      txtCustNo: Integer;
      txtCustName: Text[50];
      txtDateFilter: Date;
      dtDateFilterTo: Date;
      recGL: Record "G/L Account";
      CompanyInfo: Record "Company Information";
      compname: Text[100];
      DimensionValue: Record "Dimension Value";
      grade: Text;
      GLSetup: Record "General Ledger Setup";
      DimSetEntry: Record "Dimension Set Entry";
    }
  • Suggested answer
    Nitin Verma Profile Picture
    Nitin Verma 21,571 Moderator on at
    RE: Sales Inv Num in Report

    Hi,

    I can not see any column defined in the report. can you paste all your report's code.

  • Suggested answer
    pankaj.k Profile Picture
    pankaj.k 976 on at
    RE: Sales Inv Num in Report

    Please share Triger's code also.

    or contact to your partner they will help you to add sales invoice no in  report

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Ramesh Kumar – Community Spotlight

We are honored to recognize Ramesh Kumar as our July 2025 Community…

Congratulations to the June Top 10 Community Leaders!

These are the community rock stars!

Announcing the Engage with the Community forum!

This forum is your space to connect, share, and grow!

Leaderboard > Small and medium business | Business Central, NAV, RMS

#1
Sohail Ahmed Profile Picture

Sohail Ahmed 2,655

#2
Mansi Soni Profile Picture

Mansi Soni 1,574

#3
YUN ZHU Profile Picture

YUN ZHU 1,453 Super User 2025 Season 1

Last month Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans