You’re offline. This is a read only version of the page.
144
Hello
we have to implement about 50 reports that are not suitable for PowerBI because they are needed more frequently per day and have to be always up to date. These are typically Excel list reports (sometimes combined with some pivot tables or pivot charts).
Some of the reports should be automatically updated using the task scheduler and sent automatically by email.
I am now faced with the question of whether I should use Excel Buffer or the new Excel layouts.
What would you recommend?
When is which variant the better, faster, simpler?
kind regards
Ralf Müller
As with designing common reports (RLDC, Word), you can define multiple DataItems (Tables) in the Report Object (AL file).
You can refer to standard reports.
For example,
Share
Report
All responses (
Answers (
report 1305 "Standard Sales - Order Conf."
{
RDLCLayout = './StandardSalesOrderConf.rdlc';
WordLayout = './StandardSalesOrderConf.docx';
Caption = 'Sales - Confirmation';
DefaultLayout = Word;
PreviewMode = PrintLayout;
WordMergeDataItem = Header;
dataset
{
dataitem(Header; "Sales Header")
{
DataItemTableView = SORTING("Document Type", "No.") WHERE("Document Type" = CONST(Order));
RequestFilterFields = "No.", "Sell-to Customer No.", "No. Printed";
RequestFilterHeading = 'Sales Order';
column(CompanyAddress1; CompanyAddr[1])
{
}
column(CompanyAddress2; CompanyAddr[2])
{
}
column(CompanyAddress3; CompanyAddr[3])
{
}
column(CompanyAddress4; CompanyAddr[4])
{
}
column(CompanyAddress5; CompanyAddr[5])
{
}
column(CompanyAddress6; CompanyAddr[6])
{
}
column(CompanyHomePage; CompanyInfo."Home Page")
{
}
column(CompanyEMail; CompanyInfo."E-Mail")
{
}
column(CompanyPicture; DummyCompanyInfo.Picture)
{
}
column(CompanyPhoneNo; CompanyInfo."Phone No.")
{
}
column(CompanyPhoneNo_Lbl; CompanyInfoPhoneNoLbl)
{
}
column(CompanyGiroNo; CompanyInfo."Giro No.")
{
}
column(CompanyGiroNo_Lbl; CompanyInfoGiroNoLbl)
{
}
column(CompanyBankName; CompanyBankAccount.Name)
{
}
column(CompanyBankName_Lbl; CompanyInfoBankNameLbl)
{
}
column(CompanyBankBranchNo; CompanyBankAccount."Bank Branch No.")
{
}
column(CompanyBankBranchNo_Lbl; CompanyBankAccount.FieldCaption("Bank Branch No."))
{
}
column(CompanyBankAccountNo; CompanyBankAccount."Bank Account No.")
{
}
column(CompanyBankAccountNo_Lbl; CompanyInfoBankAccNoLbl)
{
}
column(CompanyIBAN; CompanyBankAccount.IBAN)
{
}
column(CompanyIBAN_Lbl; CompanyBankAccount.FieldCaption(IBAN))
{
}
column(CompanySWIFT; CompanyBankAccount."SWIFT Code")
{
}
column(CompanySWIFT_Lbl; CompanyBankAccount.FieldCaption("SWIFT Code"))
{
}
column(CompanyLogoPosition; CompanyLogoPosition)
{
}
column(CompanyRegistrationNumber; CompanyInfo.GetRegistrationNumber())
{
}
column(CompanyRegistrationNumber_Lbl; CompanyInfo.GetRegistrationNumberLbl())
{
}
column(CompanyVATRegNo; CompanyInfo.GetVATRegistrationNumber())
{
}
column(CompanyVATRegNo_Lbl; CompanyInfo.GetVATRegistrationNumberLbl())
{
}
column(CompanyVATRegistrationNo; CompanyInfo.GetVATRegistrationNumber())
{
}
column(CompanyVATRegistrationNo_Lbl; CompanyInfo.GetVATRegistrationNumberLbl())
{
}
column(CompanyLegalOffice; CompanyInfo.GetLegalOffice())
{
}
column(CompanyLegalOffice_Lbl; CompanyInfo.GetLegalOfficeLbl())
{
}
column(CompanyCustomGiro; CompanyInfo.GetCustomGiro())
{
}
column(CompanyCustomGiro_Lbl; CompanyInfo.GetCustomGiroLbl())
{
}
column(CompanyLegalStatement; GetLegalStatement())
{
}
column(CustomerAddress1; CustAddr[1])
{
}
column(CustomerAddress2; CustAddr[2])
{
}
column(CustomerAddress3; CustAddr[3])
{
}
column(CustomerAddress4; CustAddr[4])
{
}
column(CustomerAddress5; CustAddr[5])
{
}
column(CustomerAddress6; CustAddr[6])
{
}
column(CustomerAddress7; CustAddr[7])
{
}
column(CustomerAddress8; CustAddr[8])
{
}
column(SellToContactPhoneNoLbl; SellToContactPhoneNoLbl)
{
}
column(SellToContactMobilePhoneNoLbl; SellToContactMobilePhoneNoLbl)
{
}
column(SellToContactEmailLbl; SellToContactEmailLbl)
{
}
column(BillToContactPhoneNoLbl; BillToContactPhoneNoLbl)
{
}
column(BillToContactMobilePhoneNoLbl; BillToContactMobilePhoneNoLbl)
{
}
column(BillToContactEmailLbl; BillToContactEmailLbl)
{
}
column(SellToContactPhoneNo; SellToContact."Phone No.")
{
}
column(SellToContactMobilePhoneNo; SellToContact."Mobile Phone No.")
{
}
column(SellToContactEmail; SellToContact."E-Mail")
{
}
column(BillToContactPhoneNo; BillToContact."Phone No.")
{
}
column(BillToContactMobilePhoneNo; BillToContact."Mobile Phone No.")
{
}
column(BillToContactEmail; BillToContact."E-Mail")
{
}
column(CustomerPostalBarCode; FormatAddr.PostalBarCode(1))
{
}
column(YourReference; "Your Reference")
{
}
column(YourReference_Lbl; FieldCaption("Your Reference"))
{
}
column(ShipmentMethodDescription; ShipmentMethod.Description)
{
}
column(ShipmentMethodDescription_Lbl; ShptMethodDescLbl)
{
}
column(Shipment_Lbl; ShipmentLbl)
{
}
column(ShipmentDate; Format("Shipment Date", 0, 4))
{
}
column(ShipmentDate_Lbl; FieldCaption("Shipment Date"))
{
}
column(ShowShippingAddress; ShowShippingAddr)
{
}
column(ShipToAddress_Lbl; ShiptoAddrLbl)
{
}
column(ShipToAddress1; ShipToAddr[1])
{
}
column(ShipToAddress2; ShipToAddr[2])
{
}
column(ShipToAddress3; ShipToAddr[3])
{
}
column(ShipToAddress4; ShipToAddr[4])
{
}
column(ShipToAddress5; ShipToAddr[5])
{
}
column(ShipToAddress6; ShipToAddr[6])
{
}
column(ShipToAddress7; ShipToAddr[7])
{
}
column(ShipToAddress8; ShipToAddr[8])
{
}
column(PaymentTermsDescription; PaymentTerms.Description)
{
}
column(PaymentTermsDescription_Lbl; PaymentTermsDescLbl)
{
}
column(PaymentMethodDescription; PaymentMethod.Description)
{
}
column(PaymentMethodDescription_Lbl; PaymentMethodDescLbl)
{
}
column(DocumentCopyText; StrSubstNo(DocumentCaption(), CopyText))
{
}
column(BilltoCustumerNo; "Bill-to Customer No.")
{
}
column(BilltoCustomerNo_Lbl; FieldCaption("Bill-to Customer No."))
{
}
column(DocumentDate; Format("Document Date", 0, 4))
{
}
column(DocumentDate_Lbl; FieldCaption("Document Date"))
{
}
column(DueDate; Format("Due Date", 0, 4))
{
}
column(DueDate_Lbl; FieldCaption("Due Date"))
{
}
column(DocumentNo; "No.")
{
}
column(DocumentNo_Lbl; InvNoLbl)
{
}
column(QuoteNo; "Quote No.")
{
}
column(QuoteNo_Lbl; FieldCaption("Quote No."))
{
}
column(PricesIncludingVAT; "Prices Including VAT")
{
}
column(PricesIncludingVAT_Lbl; FieldCaption("Prices Including VAT"))
{
}
column(PricesIncludingVATYesNo; Format("Prices Including VAT"))
{
}
column(SalesPerson_Lbl; SalespersonLbl)
{
}
column(SalesPersonText_Lbl; SalesPersonText)
{
}
column(SalesPersonName; SalespersonPurchaser.Name)
{
}
column(SelltoCustomerNo; "Sell-to Customer No.")
{
}
column(SelltoCustomerNo_Lbl; FieldCaption("Sell-to Customer No."))
{
}
column(VATRegistrationNo; GetCustomerVATRegistrationNumber())
{
}
column(VATRegistrationNo_Lbl; GetCustomerVATRegistrationNumberLbl())
{
}
column(GlobalLocationNumber; GetCustomerGlobalLocationNumber())
{
}
column(GlobalLocationNumber_Lbl; GetCustomerGlobalLocationNumberLbl())
{
}
column(SellToFaxNo; GetSellToCustomerFaxNo())
{
}
column(SellToPhoneNo; "Sell-to Phone No.")
{
}
column(LegalEntityType; Cust.GetLegalEntityType())
{
}
column(LegalEntityType_Lbl; Cust.GetLegalEntityTypeLbl())
{
}
column(Copy_Lbl; CopyLbl)
{
}
column(EMail_Lbl; EMailLbl)
{
}
column(HomePage_Lbl; HomePageLbl)
{
}
column(InvoiceDiscountBaseAmount_Lbl; InvDiscBaseAmtLbl)
{
}
column(InvoiceDiscountAmount_Lbl; InvDiscountAmtLbl)
{
}
column(LineAmountAfterInvoiceDiscount_Lbl; LineAmtAfterInvDiscLbl)
{
}
column(LocalCurrency_Lbl; LocalCurrencyLbl)
{
}
column(ExchangeRateAsText; ExchangeRateText)
{
}
column(Page_Lbl; PageLbl)
{
}
column(SalesInvoiceLineDiscount_Lbl; SalesInvLineDiscLbl)
{
}
column(Invoice_Lbl; SalesConfirmationLbl)
{
}
column(Subtotal_Lbl; SubtotalLbl)
{
}
column(Total_Lbl; TotalLbl)
{
}
column(VATAmount_Lbl; VATAmtLbl)
{
}
column(VATBase_Lbl; VATBaseLbl)
{
}
column(VATAmountSpecification_Lbl; VATAmtSpecificationLbl)
{
}
column(VATClauses_Lbl; VATClausesLbl)
{
}
column(VATIdentifier_Lbl; VATIdentifierLbl)
{
}
column(VATPercentage_Lbl; VATPercentageLbl)
{
}
column(VATClause_Lbl; VATClause.TableCaption())
{
}
column(ExtDocNo_SalesHeader; "External Document No.")
{
}
column(ExtDocNo_SalesHeader_Lbl; FieldCaption("External Document No."))
{
}
column(ShowWorkDescription; ShowWorkDescription)
{
}
dataitem(Line; "Sales Line")
{
DataItemLink = "Document No." = FIELD("No.");
DataItemLinkReference = Header;
DataItemTableView = SORTING("Document No.", "Line No.");
UseTemporary = true;
column(LineNo_Line; "Line No.")
{
}
column(AmountExcludingVAT_Line; Amount)
{
AutoFormatExpression = "Currency Code";
AutoFormatType = 1;
}
column(AmountExcludingVAT_Line_Lbl; FieldCaption(Amount))
{
}
column(AmountIncludingVAT_Line; "Amount Including VAT")
{
AutoFormatExpression = "Currency Code";
AutoFormatType = 1;
}
column(AmountIncludingVAT_Line_Lbl; FieldCaption("Amount Including VAT"))
{
AutoFormatExpression = "Currency Code";
AutoFormatType = 1;
}
column(Description_Line; Description)
{
}
column(Description_Line_Lbl; FieldCaption(Description))
{
}
column(LineDiscountPercent_Line; "Line Discount %")
{
}
column(LineDiscountPercentText_Line; LineDiscountPctText)
{
}
column(LineAmount_Line; FormattedLineAmount)
{
AutoFormatExpression = "Currency Code";
AutoFormatType = 1;
}
column(LineAmount_Line_Lbl; FieldCaption("Line Amount"))
{
}
column(ItemNo_Line; "No.")
{
}
column(ItemNo_Line_Lbl; FieldCaption("No."))
{
}
column(ShipmentDate_Line; Format("Shipment Date"))
{
}
column(ShipmentDate_Line_Lbl; PostedShipmentDateLbl)
{
}
column(PlannedShipmentDate_Line; Format("Planned Shipment Date"))
{
}
column(PlannedShipmentDate_Line_Lbl; FieldCaption("Planned Shipment Date"))
{
}
column(Quantity_Line; FormattedQuantity)
{
}
column(Quantity_Line_Lbl; FieldCaption(Quantity))
{
}
column(Type_Line; Format(Type))
{
}
column(UnitPrice; FormattedUnitPrice)
{
AutoFormatExpression = "Currency Code";
AutoFormatType = 2;
}
column(UnitPrice_Lbl; FieldCaption("Unit Price"))
{
}
column(UnitOfMeasure; "Unit of Measure")
{
}
column(UnitOfMeasure_Lbl; FieldCaption("Unit of Measure"))
{
}
column(VATIdentifier_Line; "VAT Identifier")
{
}
column(VATIdentifier_Line_Lbl; FieldCaption("VAT Identifier"))
{
}
column(VATPct_Line; FormattedVATPct)
{
}
column(VATPct_Line_Lbl; FieldCaption("VAT %"))
{
}
column(TransHeaderAmount; TransHeaderAmount)
{
AutoFormatExpression = "Currency Code";
AutoFormatType = 1;
}
column(ItemReferenceNo; "Item Reference No.")
{
}
column(ItemReferenceNo_Lbl; FieldCaption("Item Reference No."))
{
}
dataitem(AssemblyLine; "Assembly Line")
{
DataItemTableView = SORTING("Document No.", "Line No.");
column(LineNo_AssemblyLine; "No.")
{
}
column(Description_AssemblyLine; Description)
{
}
column(Quantity_AssemblyLine; Quantity)
{
DecimalPlaces = 0 : 5;
}
column(UnitOfMeasure_AssemblyLine; GetUOMText("Unit of Measure Code"))
{
}
column(VariantCode_AssemblyLine; "Variant Code")
{
}
trigger OnPreDataItem()
begin
if not DisplayAssemblyInformation then
CurrReport.Break();
if not AsmInfoExistsForLine then
CurrReport.Break();
SetRange("Document Type", AsmHeader."Document Type");
SetRange("Document No.", AsmHeader."No.");
end;
}
trigger OnAfterGetRecord()
begin
if Type = Type::"G/L Account" then
"No." := '';
if "Line Discount %" = 0 then
LineDiscountPctText := ''
else
LineDiscountPctText := StrSubstNo('%1%', -Round("Line Discount %", 0.1));
if DisplayAssemblyInformation then
AsmInfoExistsForLine := AsmToOrderExists(AsmHeader);
TransHeaderAmount = PrevLineAmount;
PrevLineAmount := "Line Amount";
TotalSubTotal = "Line Amount";
TotalInvDiscAmount -= "Inv. Discount Amount";
TotalAmount = Amount;
TotalAmountVAT = "Amount Including VAT" - Amount;
TotalAmountInclVAT = "Amount Including VAT";
TotalPaymentDiscOnVAT = -("Line Amount" - "Inv. Discount Amount" - "Amount Including VAT");
OnLineOnAfterGetRecordOnAfterCalcTotals(Header, Line, TotalAmount, TotalAmountVAT, TotalAmountInclVAT);
FormatDocument.SetSalesLine(Line, FormattedQuantity, FormattedUnitPrice, FormattedVATPct, FormattedLineAmount);
if FirstLineHasBeenOutput then
Clear(DummyCompanyInfo.Picture);
FirstLineHasBeenOutput := true;
end;
trigger OnPreDataItem()
begin
MoreLines := Find(' ');
while MoreLines and (Description = '') and ("No." = '') and (Quantity = 0) and (Amount = 0) do
MoreLines := Next(-1) <> 0;
if not MoreLines then
CurrReport.Break();
SetRange("Line No.", 0, "Line No.");
TransHeaderAmount := 0;
PrevLineAmount := 0;
FirstLineHasBeenOutput := false;
DummyCompanyInfo.Picture := CompanyInfo.Picture;
end;
}
dataitem(WorkDescriptionLines; "Integer")
{
DataItemTableView = SORTING(Number) WHERE(Number = FILTER(1 .. 99999));
column(WorkDescriptionLineNumber; Number)
{
}
column(WorkDescriptionLine; WorkDescriptionLine)
{
}
trigger OnAfterGetRecord()
begin
if WorkDescriptionInstream.EOS then
CurrReport.Break();
WorkDescriptionInstream.ReadText(WorkDescriptionLine);
end;
trigger OnPostDataItem()
begin
Clear(WorkDescriptionInstream)
end;
trigger OnPreDataItem()
begin
if not ShowWorkDescription then
CurrReport.Break();
Header."Work Description".CreateInStream(WorkDescriptionInstream, TEXTENCODING::UTF8);
end;
}
dataitem(VATAmountLine; "VAT Amount Line")
{
DataItemTableView = SORTING("VAT Identifier", "VAT Calculation Type", "Tax Group Code", "Use Tax", Positive);
UseTemporary = true;
column(InvoiceDiscountAmount_VATAmountLine; "Invoice Discount Amount")
{
AutoFormatExpression = Header."Currency Code";
AutoFormatType = 1;
}
column(InvoiceDiscountAmount_VATAmountLine_Lbl; FieldCaption("Invoice Discount Amount"))
{
}
column(InvoiceDiscountBaseAmount_VATAmountLine; "Inv. Disc. Base Amount")
{
AutoFormatExpression = Header."Currency Code";
AutoFormatType = 1;
}
column(InvoiceDiscountBaseAmount_VATAmountLine_Lbl; FieldCaption("Inv. Disc. Base Amount"))
{
}
column(LineAmount_VatAmountLine; "Line Amount")
{
AutoFormatExpression = Header."Currency Code";
AutoFormatType = 1;
}
column(LineAmount_VatAmountLine_Lbl; FieldCaption("Line Amount"))
{
}
column(VATAmount_VatAmountLine; "VAT Amount")
{
AutoFormatExpression = Header."Currency Code";
AutoFormatType = 1;
}
column(VATAmount_VatAmountLine_Lbl; FieldCaption("VAT Amount"))
{
}
column(VATAmountLCY_VATAmountLine; VATAmountLCY)
{
}
column(VATAmountLCY_VATAmountLine_Lbl; VATAmountLCYLbl)
{
}
column(VATBase_VatAmountLine; "VAT Base")
{
AutoFormatExpression = Header."Currency Code";
AutoFormatType = 1;
}
column(VATBase_VatAmountLine_Lbl; FieldCaption("VAT Base"))
{
}
column(VATBaseLCY_VATAmountLine; VATBaseLCY)
{
}
column(VATBaseLCY_VATAmountLine_Lbl; VATBaseLCYLbl)
{
}
column(VATIdentifier_VatAmountLine; "VAT Identifier")
{
}
column(VATIdentifier_VatAmountLine_Lbl; FieldCaption("VAT Identifier"))
{
}
column(VATPct_VatAmountLine; "VAT %")
{
DecimalPlaces = 0 : 5;
}
column(VATPct_VatAmountLine_Lbl; FieldCaption("VAT %"))
{
}
column(NoOfVATIdentifiers; Count)
{
}
trigger OnAfterGetRecord()
begin
VATBaseLCY :=
GetBaseLCY(
Header."Posting Date", Header."Currency Code",
Header."Currency Factor");
VATAmountLCY :=
GetAmountLCY(
Header."Posting Date", Header."Currency Code",
Header."Currency Factor");
TotalVATBaseLCY = VATBaseLCY;
TotalVATAmountLCY = VATAmountLCY;
if "VAT Clause Code" <> '' then begin
VATClauseLine := VATAmountLine;
if VATClauseLine.Insert() then;
end;
end;
trigger OnPreDataItem()
begin
Clear(VATBaseLCY);
Clear(VATAmountLCY);
TotalVATBaseLCY := 0;
TotalVATAmountLCY := 0;
VATClauseLine.DeleteAll();
end;
}
dataitem(VATClauseLine; "VAT Amount Line")
{
DataItemTableView = SORTING("VAT Identifier", "VAT Calculation Type", "Tax Group Code", "Use Tax", Positive);
UseTemporary = true;
column(VATIdentifier_VATClauseLine; "VAT Identifier")
{
}
column(Code_VATClauseLine; VATClause.Code)
{
}
column(Code_VATClauseLine_Lbl; VATClause.FieldCaption(Code))
{
}
column(Description_VATClauseLine; VATClause.Description)
{
}
column(Description2_VATClauseLine; VATClause."Description 2")
{
}
column(VATAmount_VATClauseLine; "VAT Amount")
{
AutoFormatExpression = Header."Currency Code";
AutoFormatType = 1;
}
column(NoOfVATClauses; Count)
{
}
trigger OnAfterGetRecord()
begin
if "VAT Clause Code" = '' then
CurrReport.Skip();
if not VATClause.Get("VAT Clause Code") then
CurrReport.Skip();
VATClause.GetDescription(Header);
end;
}
dataitem(ReportTotalsLine; "Report Totals Buffer")
{
DataItemTableView = SORTING("Line No.");
UseTemporary = true;
column(Description_ReportTotalsLine; Description)
{
}
column(Amount_ReportTotalsLine; Amount)
{
AutoFormatExpression = Header."Currency Code";
AutoFormatType = 1;
}
column(AmountFormatted_ReportTotalsLine; "Amount Formatted")
{
}
column(FontBold_ReportTotalsLine; "Font Bold")
{
}
column(FontUnderline_ReportTotalsLine; "Font Underline")
{
}
trigger OnPreDataItem()
begin
CreateReportTotalLines();
end;
}
dataitem(LetterText; "Integer")
{
DataItemTableView = SORTING(Number) WHERE(Number = CONST(1));
column(GreetingText; GreetingLbl)
{
}
column(BodyText; BodyLbl)
{
}
column(ClosingText; ClosingLbl)
{
}
column(PmtDiscText; PmtDiscText)
{
}
trigger OnPreDataItem()
begin
PmtDiscText := '';
if Header."Payment Discount %" <> 0 then
PmtDiscText := StrSubstNo(PmtDiscTxt, Header."Pmt. Discount Date", Header."Payment Discount %");
end;
}
dataitem(Totals; "Integer")
{
DataItemTableView = SORTING(Number) WHERE(Number = CONST(1));
column(TotalNetAmount; TotalAmount)
{
AutoFormatExpression = Header."Currency Code";
AutoFormatType = 1;
}
column(TotalVATBaseLCY; TotalVATBaseLCY)
{
}
column(TotalAmountIncludingVAT; Format(TotalAmountInclVAT, 0, AutoFormat.ResolveAutoFormat("Auto Format"::AmountFormat, Header."Currency Code")))
{
}
column(TotalVATAmount; TotalAmountVAT)
{
AutoFormatExpression = Header."Currency Code";
AutoFormatType = 1;
}
column(TotalVATAmountLCY; TotalVATAmountLCY)
{
}
column(TotalInvoiceDiscountAmount; TotalInvDiscAmount)
{
AutoFormatExpression = Header."Currency Code";
AutoFormatType = 1;
}
column(TotalPaymentDiscountOnVAT; TotalPaymentDiscOnVAT)
{
}
column(TotalVATAmountText; VATAmountLine.VATAmountText())
{
}
column(TotalExcludingVATText; TotalExclVATText)
{
}
column(TotalIncludingVATText; TotalInclVATText)
{
}
column(TotalSubTotal; TotalSubTotal)
{
AutoFormatExpression = Header."Currency Code";
AutoFormatType = 1;
}
column(TotalSubTotalMinusInvoiceDiscount; TotalSubTotal TotalInvDiscAmount)
{
}
column(TotalText; TotalText)
{
}
column(CurrencyCode; CurrCode)
{
}
column(CurrencySymbol; CurrSymbol)
{
}
}
trigger OnAfterGetRecord()
var
CurrencyExchangeRate: Record "Currency Exchange Rate";
Currency: Record Currency;
GeneralLedgerSetup: Record "General Ledger Setup";
ArchiveManagement: Codeunit ArchiveManagement;
SalesPost: Codeunit "Sales-Post";
begin
FirstLineHasBeenOutput := false;
Clear(Line);
Clear(SalesPost);
VATAmountLine.DeleteAll();
Line.DeleteAll();
SalesPost.GetSalesLines(Header, Line, 0);
Line.CalcVATAmountLines(0, Header, Line, VATAmountLine);
Line.UpdateVATOnLines(0, Header, Line, VATAmountLine);
OnHeaderOnAfterGetRecordOnAfterUpdateVATOnLines(Header, Line, VATAmountLine);
if not IsReportInPreviewMode() then
CODEUNIT.Run(CODEUNIT::"Sales-Printed", Header);
CurrReport.Language := Language.GetLanguageIdOrDefault("Language Code");
CalcFields("Work Description");
ShowWorkDescription := "Work Description".HasValue;
FormatAddr.GetCompanyAddr("Responsibility Center", RespCenter, CompanyInfo, CompanyAddr);
FormatAddr.SalesHeaderBillTo(CustAddr, Header);
ShowShippingAddr := FormatAddr.SalesHeaderShipTo(ShipToAddr, CustAddr, Header);
if not CompanyBankAccount.Get(Header."Company Bank Account Code") then
CompanyBankAccount.CopyBankFieldsFromCompanyInfo(CompanyInfo);
if not Cust.Get("Bill-to Customer No.") then
Clear(Cust);
if "Currency Code" <> '' then begin
CurrencyExchangeRate.FindCurrency("Posting Date", "Currency Code", 1);
CalculatedExchRate :=
Round(1 / "Currency Factor" * CurrencyExchangeRate."Exchange Rate Amount", 0.000001);
ExchangeRateText := StrSubstNo(ExchangeRateTxt, CalculatedExchRate, CurrencyExchangeRate."Exchange Rate Amount");
CurrCode := "Currency Code";
if Currency.Get("Currency Code") then
CurrSymbol := Currency.GetCurrencySymbol();
end else
if GeneralLedgerSetup.Get() then begin
CurrCode := GeneralLedgerSetup."LCY Code";
CurrSymbol := GeneralLedgerSetup.GetCurrencySymbol();
end;
FormatDocumentFields(Header);
if SellToContact.Get("Sell-to Contact No.") then;
if BillToContact.Get("Bill-to Contact No.") then;
if not IsReportInPreviewMode() and
(CurrReport.UseRequestPage and ArchiveDocument or
not CurrReport.UseRequestPage and SalesSetup."Archive Orders")
then
ArchiveManagement.StoreSalesDocument(Header, LogInteraction);
TotalSubTotal := 0;
TotalInvDiscAmount := 0;
TotalAmount := 0;
TotalAmountVAT := 0;
TotalAmountInclVAT := 0;
TotalPaymentDiscOnVAT := 0;
end;
}
}
requestpage
{
SaveValues = true;
layout
{
area(content)
{
group(Options)
{
Caption = 'Options';
field(LogInteraction; LogInteraction)
{
ApplicationArea = Basic, Suite;
Caption = 'Log Interaction';
Enabled = LogInteractionEnable;
ToolTip = 'Specifies that interactions with the contact are logged.';
}
field(DisplayAsmInformation; DisplayAssemblyInformation)
{
ApplicationArea = Assembly;
Caption = 'Show Assembly Components';
ToolTip = 'Specifies if you want the report to include information about components that were used in linked assembly orders that supplied the item(s) being sold. (Only possible for RDLC report layout.)';
}
field(ArchiveDocument; ArchiveDocument)
{
ApplicationArea = Basic, Suite;
Caption = 'Archive Document';
ToolTip = 'Specifies if the document is archived after you print it.';
trigger OnValidate()
begin
if not ArchiveDocument then
LogInteraction := false;
end;
}
}
}
}
actions
{
}
trigger OnInit()
begin
LogInteractionEnable := true;
ArchiveDocument := SalesSetup."Archive Orders";
OnAfterOnInit(Header);
end;
trigger OnOpenPage()
begin
InitLogInteraction();
LogInteractionEnable := LogInteraction;
end;
}
labels
{
}
trigger OnInitReport()
begin
GLSetup.Get();
CompanyInfo.SetAutoCalcFields(Picture);
CompanyInfo.Get();
SalesSetup.Get();
CompanyInfo.VerifyAndSetPaymentInfo();
end;
trigger OnPostReport()
begin
if LogInteraction and not IsReportInPreviewMode() then
if Header.FindSet() then
repeat
Header.CalcFields("No. of Archived Versions");
if Header."Bill-to Contact No." <> '' then
SegManagement.LogDocument(
3, Header."No.", Header."Doc. No. Occurrence",
Header."No. of Archived Versions", DATABASE::Contact, Header."Bill-to Contact No."
, Header."Salesperson Code", Header."Campaign No.", Header."Posting Description", Header."Opportunity No.")
else
SegManagement.LogDocument(
3, Header."No.", Header."Doc. No. Occurrence",
Header."No. of Archived Versions", DATABASE::Customer, Header."Bill-to Customer No.",
Header."Salesperson Code", Header."Campaign No.", Header."Posting Description", Header."Opportunity No.");
until Header.Next() = 0;
end;
trigger OnPreReport()
begin
if Header.GetFilters = '' then
Error(NoFilterSetErr);
if not CurrReport.UseRequestPage then
InitLogInteraction();
CompanyLogoPosition := SalesSetup."Logo Position on Documents";
end;
var
GLSetup: Record "General Ledger Setup";
CompanyBankAccount: Record "Bank Account";
CompanyInfo: Record "Company Information";
DummyCompanyInfo: Record "Company Information";
SalesSetup: Record "Sales & Receivables Setup";
Cust: Record Customer;
RespCenter: Record "Responsibility Center";
VATClause: Record "VAT Clause";
AsmHeader: Record "Assembly Header";
SellToContact: Record Contact;
BillToContact: Record Contact;
Language: Codeunit Language;
FormatAddr: Codeunit "Format Address";
FormatDocument: Codeunit "Format Document";
SegManagement: Codeunit SegManagement;
AutoFormat: Codeunit "Auto Format";
WorkDescriptionInstream: InStream;
CustAddr: array[8] of Text[100];
ShipToAddr: array[8] of Text[100];
CompanyAddr: array[8] of Text[100];
SalesPersonText: Text[50];
TotalText: Text[50];
TotalExclVATText: Text[50];
TotalInclVATText: Text[50];
LineDiscountPctText: Text;
FormattedVATPct: Text;
FormattedUnitPrice: Text;
FormattedQuantity: Text;
FormattedLineAmount: Text;
MoreLines: Boolean;
CopyText: Text[30];
ShowShippingAddr: Boolean;
ArchiveDocument: Boolean;
LogInteraction: Boolean;
TransHeaderAmount: Decimal;
[InDataSet]
LogInteractionEnable: Boolean;
DisplayAssemblyInformation: Boolean;
AsmInfoExistsForLine: Boolean;
CompanyLogoPosition: Integer;
FirstLineHasBeenOutput: Boolean;
CalculatedExchRate: Decimal;
ExchangeRateText: Text;
VATBaseLCY: Decimal;
VATAmountLCY: Decimal;
TotalVATBaseLCY: Decimal;
TotalVATAmountLCY: Decimal;
PrevLineAmount: Decimal;
PmtDiscText: Text;
ShowWorkDescription: Boolean;
WorkDescriptionLine: Text;
CurrCode: Text[10];
CurrSymbol: Text[10];
CompanyInfoBankAccNoLbl: Label 'Account No.';
CompanyInfoBankNameLbl: Label 'Bank';
CompanyInfoGiroNoLbl: Label 'Giro No.';
CompanyInfoPhoneNoLbl: Label 'Phone No.';
CopyLbl: Label 'Copy';
EMailLbl: Label 'Email';
HomePageLbl: Label 'Home Page';
InvDiscBaseAmtLbl: Label 'Invoice Discount Base Amount';
InvDiscountAmtLbl: Label 'Invoice Discount';
InvNoLbl: Label 'Order No.';
LineAmtAfterInvDiscLbl: Label 'Payment Discount on VAT';
LocalCurrencyLbl: Label 'Local Currency';
PageLbl: Label 'Page';
PostedShipmentDateLbl: Label 'Shipment Date';
ShipmentLbl: Label 'Shipment';
ShiptoAddrLbl: Label 'Ship-to Address';
SubtotalLbl: Label 'Subtotal';
TotalLbl: Label 'Total';
VATAmtSpecificationLbl: Label 'VAT Amount Specification';
VATAmtLbl: Label 'VAT Amount';
VATAmountLCYLbl: Label 'VAT Amount (LCY)';
VATBaseLbl: Label 'VAT Base';
VATBaseLCYLbl: Label 'VAT Base (LCY)';
VATClausesLbl: Label 'VAT Clause';
VATIdentifierLbl: Label 'VAT Identifier';
VATPercentageLbl: Label 'VAT %';
ExchangeRateTxt: Label 'Exchange rate: %1/%2', Comment = '%1 and %2 are both amounts.';
NoFilterSetErr: Label 'You must specify one or more filters to avoid accidently printing all documents.';
GreetingLbl: Label 'Hello';
ClosingLbl: Label 'Sincerely';
PmtDiscTxt: Label 'If we receive the payment before %1, you are eligible for a %2% payment discount.', Comment = '%1 Discount Due Date %2 = value of Payment Discount % ';
BodyLbl: Label 'Thank you for your business. Your order confirmation is attached to this message.';
SellToContactPhoneNoLbl: Label 'Sell-to Contact Phone No.';
SellToContactMobilePhoneNoLbl: Label 'Sell-to Contact Mobile Phone No.';
SellToContactEmailLbl: Label 'Sell-to Contact E-Mail';
BillToContactPhoneNoLbl: Label 'Bill-to Contact Phone No.';
BillToContactMobilePhoneNoLbl: Label 'Bill-to Contact Mobile Phone No.';
BillToContactEmailLbl: Label 'Bill-to Contact E-Mail';
protected var
PaymentTerms: Record "Payment Terms";
PaymentMethod: Record "Payment Method";
SalespersonPurchaser: Record "Salesperson/Purchaser";
ShipmentMethod: Record "Shipment Method";
TotalSubTotal: Decimal;
TotalAmount: Decimal;
TotalAmountInclVAT: Decimal;
TotalAmountVAT: Decimal;
TotalInvDiscAmount: Decimal;
TotalPaymentDiscOnVAT: Decimal;
PaymentTermsDescLbl: Label 'Payment Terms';
PaymentMethodDescLbl: Label 'Payment Method';
SalesConfirmationLbl: Label 'Order Confirmation';
SalesInvLineDiscLbl: Label 'Discount %';
SalespersonLbl: Label 'Sales person';
ShptMethodDescLbl: Label 'Shipment Method';
local procedure InitLogInteraction()
begin
LogInteraction := SegManagement.FindInteractTmplCode(3) <> '';
end;
local procedure DocumentCaption() DocCaption: Text[250]
begin
DocCaption := SalesConfirmationLbl;
OnAfterDocumentCaption(Header, DocCaption);
end;
procedure InitializeRequest(NewLogInteraction: Boolean; DisplayAsmInfo: Boolean)
begin
LogInteraction := NewLogInteraction;
DisplayAssemblyInformation := DisplayAsmInfo;
end;
local procedure IsReportInPreviewMode(): Boolean
var
MailManagement: Codeunit "Mail Management";
begin
exit(CurrReport.Preview or MailManagement.IsHandlingGetEmailBody());
end;
local procedure FormatDocumentFields(SalesHeader: Record "Sales Header")
begin
with SalesHeader do begin
FormatDocument.SetTotalLabels("Currency Code", TotalText, TotalInclVATText, TotalExclVATText);
FormatDocument.SetSalesPerson(SalespersonPurchaser, "Salesperson Code", SalesPersonText);
FormatDocument.SetPaymentTerms(PaymentTerms, "Payment Terms Code", "Language Code");
FormatDocument.SetPaymentMethod(PaymentMethod, "Payment Method Code", "Language Code");
FormatDocument.SetShipmentMethod(ShipmentMethod, "Shipment Method Code", "Language Code");
end;
end;
local procedure GetUOMText(UOMCode: Code[10]): Text[50]
var
UnitOfMeasure: Record "Unit of Measure";
begin
if not UnitOfMeasure.Get(UOMCode) then
exit(UOMCode);
exit(UnitOfMeasure.Description);
end;
local procedure CreateReportTotalLines()
begin
ReportTotalsLine.DeleteAll();
if (TotalInvDiscAmount <> 0) or (TotalAmountVAT <> 0) then
ReportTotalsLine.Add(SubtotalLbl, TotalSubTotal, true, false, false);
if TotalInvDiscAmount <> 0 then begin
ReportTotalsLine.Add(InvDiscountAmtLbl, TotalInvDiscAmount, false, false, false);
if TotalAmountVAT <> 0 then
ReportTotalsLine.Add(TotalExclVATText, TotalAmount, true, false, false);
end;
if TotalAmountVAT <> 0 then
ReportTotalsLine.Add(VATAmountLine.VATAmountText(), TotalAmountVAT, false, true, false);
end;
[IntegrationEvent(false, false)]
local procedure OnAfterOnInit(var SalesHeader: Record "Sales Header")
begin
end;
[IntegrationEvent(false, false)]
local procedure OnAfterDocumentCaption(SalesHeader: Record "Sales Header"; var DocCaption: Text[250])
begin
end;
[IntegrationEvent(false, false)]
local procedure OnHeaderOnAfterGetRecordOnAfterUpdateVATOnLines(var SalesHeader: Record "Sales Header"; var SalesLine: Record "Sales Line"; var VATAmountLine: Record "VAT Amount Line")
begin
end;
[IntegrationEvent(false, false)]
local procedure OnLineOnAfterGetRecordOnAfterCalcTotals(var SalesHeader: Record "Sales Header"; var SalesLine: Record "Sales Line"; var VATBaseAmount: Decimal; var VATAmount: Decimal; var TotalAmountInclVAT: Decimal)
begin
end;
}
Hope this helps.
Thanks.
ZHU
You have convinced me that the Excel format will usually be the better choice for me.
But I'm not convinced about one point. According to the MS documentation, there can only ever be one data table in the generated Excel layout. How should it then be possible that I get item data + customer data (which are not linked) transferred in one Excel-File?
If so, do you have an example?
Thanks
Ralf Müller
Hi, Using Excel Buffer can only import and export data.
More details:
You can do even more with Excel Layout.
Hope this helps as well.
Thanks.
ZHU
Yes, you should be able to do that with an excel layout.
Does that mean that I can, for example, use an Excel layout to export an item overview to an Excel sheet and independently export the customer data to a second Excel sheet?
Then that would already meet many requirements.
Since I'm currently still using version 19, I can't test Excel layouts yet. But I could already test OData4 with Excel. However, I have to create a data sheet, for example, which corresponds to an entered article no. fetches a few item data - and to an entered customer no. some customer data. However, it seems that with Power Query in Excel (still Excel 2016) all items and customers are fetched and only then are the parameters filtered. With the result that it is very slow.
You can create pretty advanced datasets in a excel layout based report.
But i agree with you that the Excel buffer might be a better option if you have to do a lot of calculations on your data before you present them to Excel.
Another alternative can also be to create a web service that you use directly in Excel. Then you can have your data refreshed in Excel every time the excel sheet is opened with out having to run anything in BC first.
My recommendation would be to go with the Excel layout option.
Because then you provide the dataset and the end user can change the layout without you having to maintain layout.
Then you can schedule the reports using the job scheduler.
Using the Excel buffer will demand more work to manage report changes in my opinion.
#1
Sohail Ahmed
2,655
#2
Mansi Soni
1,574
#3
YUN ZHU
1,453
Super User 2025 Season 1
