Report 1142 Export Cost Budget to Excel
src/Layers/W1/BaseApp/CostAccounting/Budget/ExportCostBudgettoExcel.Report.al329 lines, Copyright (c) Microsoft Corporation. MIT
// ------------------------------------------------------------------------------------------------// Copyright (c) Microsoft Corporation. All rights reserved.// Licensed under the MIT License. See License.txt in the project root for license information.// ------------------------------------------------------------------------------------------------namespace Microsoft.CostAccounting.Budget;using Microsoft.CostAccounting.Account;using Microsoft.CostAccounting.Setup;using Microsoft.Finance.Analysis;using Microsoft.Foundation.Enums;using System.IO;using System.Utilities;report 1142 "Export Cost Budget to Excel"{ Caption = 'Export Cost Budget to Excel'; ProcessingOnly = true; dataset { dataitem("Cost Budget Entry"; "Cost Budget Entry") { trigger OnAfterGetRecord() begin Clear(TempCostBudgetBuf1); TempCostBudgetBuf1."Cost Type No." := "Cost Type No."; TempCostBudgetBuf1."Budget Name" := "Budget Name"; TempCostBudgetBuf1.Date := CalcPeriodStart(Date); TempCostBudgetBuf1."Cost Center Code" := "Cost Center Code"; TempCostBudgetBuf1."Cost Object Code" := "Cost Object Code"; TempCostBudgetBuf1.Amount := Amount; TempCostBudgetBuf2 := TempCostBudgetBuf1; if TempCostBudgetBuf2.Find() then begin TempCostBudgetBuf2.Amount := TempCostBudgetBuf2.Amount + TempCostBudgetBuf1.Amount; TempCostBudgetBuf2.Modify(); end else TempCostBudgetBuf2.Insert(); end; trigger OnPostDataItem() var CostType: Record "Cost Type"; Window: Dialog; RecNo: Integer; TotalRecNo: Integer; LastBudgetRowNo: Integer; begin Window.Open( Text005 + '@1@@@@@@@@@@@@@@@@@@@@@@@@@\'); Window.Update(1, 0); TotalRecNo := CostType.Count(); RecNo := 0; CostBudgetName.Init(); RowNo := 1; EnterCell(RowNo, 1, Text006, false, true, '', TempExcelBuffer."Cell Type"::Text); EnterCell(RowNo, 2, '', false, true, '', TempExcelBuffer."Cell Type"::Text); EnterFilterInCell(GetFilter("Budget Name"), FieldCaption("Budget Name")); EnterFilterInCell(GetFilter("Cost Center Code"), FieldCaption("Cost Center Code")); EnterFilterInCell(GetFilter("Cost Object Code"), FieldCaption("Cost Object Code")); CostAccSetup.Get(); RowNo := RowNo + 2; HeaderRowNo := RowNo; EnterCell(HeaderRowNo, 1, FieldCaption("Cost Type No."), false, true, '', TempExcelBuffer."Cell Type"::Text); EnterCell(HeaderRowNo, 2, CostType.FieldCaption(Name), false, true, '', TempExcelBuffer."Cell Type"::Text); EnterCell(HeaderRowNo, 3, CostType.FieldCaption("Cost Center Code"), false, true, '', TempExcelBuffer."Cell Type"::Text); EnterCell(HeaderRowNo, 4, CostType.FieldCaption("Cost Object Code"), false, true, '', TempExcelBuffer."Cell Type"::Text); i := 0; ColNo := 4; if TempPeriod.Find('-') then repeat ColNo := ColNo + 1; EnterCell(HeaderRowNo, ColNo, Format(TempPeriod."Period Start"), false, true, '', TempExcelBuffer."Cell Type"::Date); until TempPeriod.Next() = 0; CopyFilter("Cost Type No.", CostType."No."); if CostType.Find('-') then repeat RecNo := RecNo + 1; Window.Update(1, Round(RecNo / TotalRecNo * 10000, 1)); RowNo := RowNo + 1; EnterCostType(RowNo, CostType); if (CostType.Totaling = '') or (not IncludeTotalingFormulas) then begin TempCostBudgetBuf2.SetRange("Cost Type No.", CostType."No."); if TempCostBudgetBuf2.Find('-') then begin TempCostBudgetBuf1 := TempCostBudgetBuf2; EnterCCCO(RowNo, CostType); if TempPeriod.Find('-') then repeat if (TempCostBudgetBuf2."Cost Object Code" <> TempCostBudgetBuf1."Cost Object Code") or (TempCostBudgetBuf2."Cost Center Code" <> TempCostBudgetBuf1."Cost Center Code") then begin RowNo := RowNo + 1; EnterCostType(RowNo, CostType); EnterCCCO(RowNo, CostType); TempCostBudgetBuf1 := TempCostBudgetBuf2; end; TempPeriod.Get(0, TempCostBudgetBuf2.Date); EnterCell( RowNo, 4 + TempPeriod."Period No.", MatrixMgt.FormatAmount(TempCostBudgetBuf2.Amount, RoundingFactor, false), CostType.Type <> CostType.Type::"Cost Type", false, '', TempExcelBuffer."Cell Type"::Number); TempPeriod.Next(); until TempCostBudgetBuf2.Next() = 0; end else Clear(TempCostBudgetBuf2); end else if TempPeriod.Find('-') then repeat EnterFormula( RowNo, 4 + TempPeriod."Period No.", CostType.Totaling, CostType.Type <> CostType.Type::"Cost Type", false); until TempPeriod.Next() = 0; until CostType.Next() = 0; if IncludeTotalingFormulas then HasFormulaError := TempExcelBuffer.ExportBudgetFilterToFormula(TempExcelBuffer); Window.Close(); LastBudgetRowNo := RowNo; RowNo := RowNo + 200; // Move way below the budget if HasFormulaError then if not Confirm(StrSubstNo(Text007, TempExcelBuffer.GetExcelReference(7))) then CurrReport.Break(); TempExcelBuffer.CreateBook(ServerFileName, TempExcelBuffer.GetExcelReference(10)); TempExcelBuffer.SetCurrent(HeaderRowNo + 1, 1); TempExcelBuffer.StartRange(); TempExcelBuffer.SetCurrent(LastBudgetRowNo, 1); TempExcelBuffer.EndRange(); TempExcelBuffer.CreateRange(TempExcelBuffer.GetExcelReference(11)); if TempPeriod.Find('-') then repeat TempExcelBuffer.SetCurrent(HeaderRowNo + 1, 4 + TempPeriod."Period No."); TempExcelBuffer.StartRange(); TempExcelBuffer.SetCurrent(LastBudgetRowNo, 4 + TempPeriod."Period No."); TempExcelBuffer.EndRange(); TempExcelBuffer.CreateRange(TempExcelBuffer.GetExcelReference(9) + '_' + Format(TempPeriod."Period No.")); until TempPeriod.Next() = 0; TempExcelBuffer.WriteSheet(PadStr(CostBudgetName.Name, 30), CompanyName, UserId); TempExcelBuffer.CloseBook(); TempExcelBuffer.OpenExcel(); end; trigger OnPreDataItem() begin if GetRangeMin("Budget Name") <> GetRangeMax("Budget Name") then Error(Text001); if (StartDate = 0D) or (NoOfPeriods = 0) or (Format(PeriodLength) = '') then Error(Text002); for i := 1 to NoOfPeriods do begin if i = 1 then TempPeriod."Period Start" := StartDate else TempPeriod."Period Start" := CalcDate(PeriodLength, TempPeriod."Period Start"); TempPeriod."Period End" := CalcDate(PeriodLength, TempPeriod."Period Start"); TempPeriod."Period End" := CalcDate('<-1D>', TempPeriod."Period End"); TempPeriod."Period No." := i; TempPeriod.Insert(); end; SetRange(Date, StartDate, TempPeriod."Period End"); TempCostBudgetBuf2.DeleteAll(); TempExcelBuffer.DeleteAll(); end; } } requestpage { layout { area(content) { group(Options) { Caption = 'Options'; field(StartDate; StartDate) { ApplicationArea = CostAccounting; Caption = 'Start Date'; ToolTip = 'Specifies the first date to be included in the budget to be exported to Excel.'; } field(NoOfPeriods; NoOfPeriods) { ApplicationArea = CostAccounting; Caption = 'No. of Periods'; ToolTip = 'Specifies the number of accounting periods to be exported to Excel.'; } field(PeriodLength; PeriodLength) { ApplicationArea = CostAccounting; Caption = 'Period Length'; ToolTip = 'Specifies the length of the accounting periods for the budget to be exported to Excel.'; } field(IncludeTotalingFormulas; IncludeTotalingFormulas) { ApplicationArea = CostAccounting; Caption = 'Include Totalling Formulas'; ToolTip = 'Specifies if you want sum formulas to be created in Excel based on the totaling fields used in the Chart of Cost Types window.'; } } } } actions { } } labels { } var TempPeriod: Record Date temporary; TempCostBudgetBuf1: Record "Cost Budget Buffer" temporary; TempCostBudgetBuf2: Record "Cost Budget Buffer" temporary; CostAccSetup: Record "Cost Accounting Setup"; CostBudgetName: Record "Cost Budget Name"; TempExcelBuffer: Record "Excel Buffer" temporary; MatrixMgt: Codeunit "Matrix Management"; PeriodLength: DateFormula; ServerFileName: Text; StartDate: Date; RoundingFactor: Enum "Analysis Rounding Factor"; NoOfPeriods: Integer; i: Integer; RowNo: Integer; ColNo: Integer; HeaderRowNo: Integer; IncludeTotalingFormulas: Boolean; HasFormulaError: Boolean;#pragma warning disable AA0074 Text001: Label 'You can only export one budget at a time.'; Text002: Label 'You must specify the starting date, number of periods, and period length.'; Text005: Label 'Analyzing Data...\\'; Text006: Label 'Export Filters';#pragma warning disable AA0470 Text007: Label 'Some filters cannot be converted into Excel formulas. You must verify %1 errors in the Excel worksheet. Do you want to create the Excel worksheet?';#pragma warning restore AA0470#pragma warning restore AA0074 local procedure CalcPeriodStart(EntryDate: Date): Date begin TempPeriod."Period Start" := EntryDate; TempPeriod.Find('=<'); exit(TempPeriod."Period Start"); end; local procedure EnterCell(RowNo: Integer; ColumnNo: Integer; CellValue: Text[250]; Bold: Boolean; UnderLine: Boolean; NumberFormat: Text[30]; CellType: Option) begin TempExcelBuffer.Init(); TempExcelBuffer.Validate("Row No.", RowNo); TempExcelBuffer.Validate("Column No.", ColumnNo); TempExcelBuffer."Cell Value as Text" := CellValue; TempExcelBuffer.Formula := ''; TempExcelBuffer.Bold := Bold; TempExcelBuffer.Underline := UnderLine; TempExcelBuffer.NumberFormat := NumberFormat; TempExcelBuffer."Cell Type" := CellType; TempExcelBuffer.Insert(); end; local procedure EnterFilterInCell("Filter": Text[250]; FieldName: Text[100]) begin if Filter <> '' then begin RowNo := RowNo + 1; EnterCell(RowNo, 1, FieldName, false, false, '', TempExcelBuffer."Cell Type"::Text); EnterCell(RowNo, 2, Filter, false, false, '', TempExcelBuffer."Cell Type"::Text); end; end; local procedure EnterFormula(RowNo: Integer; ColumnNo: Integer; CellValue: Text[250]; Bold: Boolean; UnderLine: Boolean) begin TempExcelBuffer.Init(); TempExcelBuffer.Validate("Row No.", RowNo); TempExcelBuffer.Validate("Column No.", ColumnNo); TempExcelBuffer."Cell Value as Text" := ''; TempExcelBuffer.Formula := CellValue; // is converted to formula later. TempExcelBuffer.Bold := Bold; TempExcelBuffer.Underline := UnderLine; TempExcelBuffer.Insert(); end; local procedure EnterCCCO(RowNo: Integer; var CostType: Record "Cost Type") begin EnterCell( RowNo, 3, TempCostBudgetBuf2."Cost Center Code", CostType.Type <> CostType.Type::"Cost Type", false, '', TempExcelBuffer."Cell Type"::Text); EnterCell( RowNo, 4, TempCostBudgetBuf2."Cost Object Code", CostType.Type <> CostType.Type::"Cost Type", false, '', TempExcelBuffer."Cell Type"::Text); end; local procedure EnterCostType(RowNo: Integer; var CostType: Record "Cost Type") begin EnterCell(RowNo, 1, CostType."No.", CostType.Type <> CostType.Type::"Cost Type", false, '', TempExcelBuffer."Cell Type"::Text); EnterCell( RowNo, 2, CopyStr(CopyStr(PadStr(' ', 100), 1, 2 * CostType.Indentation + 1) + CostType.Name, 2), CostType.Type <> CostType.Type::"Cost Type", false, '', TempExcelBuffer."Cell Type"::Text); end; procedure SetRoundingFactor(NewRoundingFactor: Enum "Analysis Rounding Factor") begin RoundingFactor := NewRoundingFactor; end; procedure SetFileNameSilent(NewFileName: Text) begin ServerFileName := NewFileName; end;}