Report 1142 Export Cost Budget to Excel, source in 29
Source29
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;
}