Codeunit 7152 Export Item Analysis View
src/Layers/W1/BaseApp/Inventory/Analysis/ExportItemAnalysisView.Codeunit.al649 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.Inventory.Analysis;using Microsoft.Finance.Dimension;using Microsoft.Foundation.Period;using Microsoft.Inventory.Item;using Microsoft.Utilities;using System.IO;codeunit 7152 "Export Item Analysis View"{ trigger OnRun() begin end; var Item: Record Item; TempDimValue2: Record "Dimension Value" temporary; TempDimValue3: Record "Dimension Value" temporary; TempExcelBuffer: Record "Excel Buffer" temporary; FileMgt: Codeunit "File Management"; NoOfColumns: Integer; MaxLevelDim: array[3] of Integer;#pragma warning disable AA0074 Text000: Label 'You can only export Actual amounts and Budgeted amounts.\Please change the option in the Show field.'; Text002: Label 'General Info._'; Text003: Label 'None'; Text004: Label 'Day'; Text005: Label 'Week'; Text006: Label 'Month'; Text007: Label 'Quarter'; Text008: Label 'Year'; Text009: Label 'Accounting Period'; Text011: Label 'Analysis by Dimension '; Text012: Label 'Amount Type'; Text015: Label 'Date Filter'; Text016: Label 'Budget Filter'; Text018: Label 'Item'; Text020: Label 'Budg. Sales Amount'; Text022: Label 'Level'; Text023: Label 'Analysis View Name'; Text028: Label 'Show Opposite Sign'; Text029: Label 'Yes'; Text030: Label 'No'; Text031: Label 'Data_'; Text032: Label 'Sales Amount'; Text033: Label 'Cost Amount'; Text035: Label 'Budg. Cost Amount'; Text036: Label 'Budg. Quantity'; Text039: Label 'Location';#pragma warning restore AA0074 ServerFileName: Text; SkipDownload: Boolean; [Scope('OnPrem')] procedure ExportData(var ItemAnalysisViewEntry: Record "Item Analysis View Entry"; ShowName: Boolean; DateFilter: Text; ItemFilter: Text; BudgetFilter: Text; Dim1Filter: Text; Dim2Filter: Text; Dim3Filter: Text; ShowActualBudg: Option "Actual Amounts","Budgeted Amounts",Variance,"Variance%","Index%"; LocationFilter: Text; Sign: Boolean) begin if (ShowActualBudg <> 0) and (ShowActualBudg <> 1) then Error(Text000); ServerFileName := FileMgt.ServerTempFileName('xlsx'); CreateFillGeneralInfoSheet( ItemAnalysisViewEntry, Sign, DateFilter, ItemFilter, BudgetFilter, Dim1Filter, Dim2Filter, Dim3Filter, LocationFilter); TempExcelBuffer.CreateBook(ServerFileName, StrSubstNo('%1%2', Text002, ItemAnalysisViewEntry."Analysis View Code")); TempExcelBuffer.WriteSheet(StrSubstNo('%1%2', Text002, ItemAnalysisViewEntry."Analysis View Code"), CompanyName, UserId); CreateDataSheet( ItemAnalysisViewEntry, ShowName, ItemFilter, Dim1Filter, Dim2Filter, Dim3Filter, DateFilter, LocationFilter, BudgetFilter, Sign); TempExcelBuffer.SelectOrAddSheet(StrSubstNo('%1%2', Text031, ItemAnalysisViewEntry."Analysis View Code")); TempExcelBuffer.WriteAllToCurrentSheet(TempExcelBuffer); TempExcelBuffer.CloseBook(); if not SkipDownload then TempExcelBuffer.OpenExcel(); end; local procedure CreateDataSheet(var ItemAnalysisViewEntry: Record "Item Analysis View Entry"; ShowName: Boolean; ItemFilter: Text; Dim1Filter: Text; Dim2Filter: Text; Dim3Filter: Text; DateFilter: Text; LocationFilter: Text; BudgetFilter: Text; Sign: Boolean) var ItemAnalysisViewEntry2: Record "Item Analysis View Entry"; ItemAnalysisView: Record "Item Analysis View"; StartDate: Date; EndDate: Date; WeekNo: Integer; Year: Integer; SignValue: Integer; NoOfLeadingColumns: Integer; begin TempExcelBuffer.DeleteAll(); ItemAnalysisViewEntry2.Copy(ItemAnalysisViewEntry); ItemAnalysisView.Get(ItemAnalysisViewEntry."Analysis Area", ItemAnalysisViewEntry."Analysis View Code"); ItemAnalysisViewEntry2.SetRange("Analysis Area", ItemAnalysisView."Analysis Area"); ItemAnalysisViewEntry2.SetRange("Analysis View Code", ItemAnalysisView.Code); FindDimLevel(ItemAnalysisView."Dimension 1 Code", Dim1Filter, 1); FindDimLevel(ItemAnalysisView."Dimension 2 Code", Dim2Filter, 2); FindDimLevel(ItemAnalysisView."Dimension 3 Code", Dim3Filter, 3); SignValue := 1; if Sign then SignValue := -1; CreateRowWithColumnsCaptions(ItemAnalysisViewEntry2, ItemAnalysisView); CreateItemAnalysisViewEntryPart(ItemAnalysisViewEntry2, ItemAnalysisView, StartDate, EndDate, ShowName, SignValue); CreateItemAnalysisViewBudgetEntryPart(ItemAnalysisView, StartDate, EndDate, DateFilter, ItemFilter, LocationFilter, BudgetFilter, Dim1Filter, Dim2Filter, Dim3Filter, ShowName, SignValue); if ItemFilter <> '' then Item.SetFilter("No.", ItemFilter); if Item.Find('-') then repeat if not Item.Mark() then begin FillOutItem(Item."No.", ShowName); StartNewRow(); end; until Item.Next() = 0; NoOfLeadingColumns := 1; if ItemAnalysisView."Dimension 1 Code" <> '' then WriteDimLine(1, Dim1Filter, ItemAnalysisView."Dimension 1 Code", NoOfLeadingColumns, ShowName); NoOfLeadingColumns := NoOfLeadingColumns + MaxLevelDim[1] + 1; if ItemAnalysisView."Dimension 2 Code" <> '' then WriteDimLine(2, Dim2Filter, ItemAnalysisView."Dimension 2 Code", NoOfLeadingColumns, ShowName); NoOfLeadingColumns := NoOfLeadingColumns + MaxLevelDim[2] + 1; if ItemAnalysisView."Dimension 3 Code" <> '' then WriteDimLine(3, Dim3Filter, ItemAnalysisView."Dimension 3 Code", NoOfLeadingColumns, ShowName); NoOfLeadingColumns := NoOfLeadingColumns + MaxLevelDim[3] + 1; WeekNo := Date2DWY(StartDate, 2); Year := Date2DWY(StartDate, 3); StartDate := DWY2Date(1, WeekNo, Year); NoOfLeadingColumns += 1; while StartDate <= EndDate do begin SetStartColumnNo(NoOfLeadingColumns); FillNextCellInRow(CalculatePeriodStart(StartDate, 0)); FillNextCellInRow(CalculatePeriodStart(StartDate, 1)); FillNextCellInRow(CalculatePeriodStart(StartDate, 2)); FillNextCellInRow(CalculatePeriodStart(StartDate, 3)); FillNextCellInRow(CalculatePeriodStart(StartDate, 4)); StartNewRow(); StartDate := CalcDate('<1W>', StartDate); end; end; local procedure CreateFillGeneralInfoSheet(var ItemAnalysisViewEntry: Record "Item Analysis View Entry"; Sign: Boolean; DateFilter: Text; ItemFilter: Text; BudgetFilter: Text; Dim1Filter: Text; Dim2Filter: Text; Dim3Filter: Text; LocationFilter: Text) var ItemAnalysisView: Record "Item Analysis View"; ItemAnalysisViewFilter: Record "Item Analysis View Filter"; RowNoCount: Integer; begin TempExcelBuffer.Reset(); TempExcelBuffer.DeleteAll(); FillCell(1, 1, ItemAnalysisView.TableCaption()); FillCell(2, 2, ItemAnalysisViewEntry.FieldCaption(ItemAnalysisViewEntry."Analysis View Code")); FillCell(2, 3, ItemAnalysisViewEntry."Analysis View Code"); FillCell(3, 2, Text023); ItemAnalysisView.Get(ItemAnalysisViewEntry."Analysis Area", ItemAnalysisViewEntry."Analysis View Code"); FillCell(3, 3, ItemAnalysisView.Name); RowNoCount := 3; if ItemAnalysisView."Item Filter" <> '' then begin RowNoCount := RowNoCount + 1; FillCell(RowNoCount, 2, ItemAnalysisView.FieldCaption("Item Filter")); FillCell(RowNoCount, 3, ItemAnalysisView."Item Filter"); end; RowNoCount := RowNoCount + 1; FillCell(RowNoCount, 2, ItemAnalysisView.FieldCaption("Date Compression")); case ItemAnalysisView."Date Compression" of 0: FillCell(RowNoCount, 3, Text003); 1: FillCell(RowNoCount, 3, Text004); 2: FillCell(RowNoCount, 3, Text005); 3: FillCell(RowNoCount, 3, Text006); 4: FillCell(RowNoCount, 3, Text007); 5: FillCell(RowNoCount, 3, Text008); 6: FillCell(RowNoCount, 3, Text009); end; if ItemAnalysisView."Starting Date" <> 0D then begin RowNoCount := RowNoCount + 1; FillCell(RowNoCount, 2, ItemAnalysisView.FieldCaption("Starting Date")); FillCell(RowNoCount, 3, ItemAnalysisView."Starting Date"); end; RowNoCount := RowNoCount + 1; FillCell(RowNoCount, 2, ItemAnalysisView.FieldCaption("Last Date Updated")); FillCell(RowNoCount, 3, ItemAnalysisView."Last Date Updated"); ItemAnalysisViewFilter.SetRange("Analysis View Code", ItemAnalysisViewEntry."Analysis View Code"); if ItemAnalysisViewFilter.FindSet() then repeat RowNoCount := RowNoCount + 1; FillCell(RowNoCount, 2, ItemAnalysisViewFilter."Dimension Code"); FillCell(RowNoCount, 3, ItemAnalysisViewFilter."Dimension Value Filter"); until ItemAnalysisViewFilter.Next() = 0; RowNoCount := RowNoCount + 1; FillCell(RowNoCount, 1, Text011); RowNoCount := RowNoCount + 1; FillCell(RowNoCount, 2, Text012); if DateFilter <> '' then begin RowNoCount := RowNoCount + 1; FillCell(RowNoCount, 2, Text015); FillCell(RowNoCount, 3, DateFilter); end; if ItemFilter <> '' then begin RowNoCount := RowNoCount + 1; FillCell(RowNoCount, 2, ItemAnalysisView.FieldCaption("Item Filter")); FillCell(RowNoCount, 3, ItemFilter); end; if LocationFilter <> '' then begin RowNoCount := RowNoCount + 1; FillCell(RowNoCount, 2, Text039); FillCell(RowNoCount, 3, LocationFilter); end; if BudgetFilter <> '' then begin RowNoCount := RowNoCount + 1; FillCell(RowNoCount, 2, Text016); FillCell(RowNoCount, 3, BudgetFilter); end; if Dim1Filter <> '' then begin RowNoCount := RowNoCount + 1; FillCell(RowNoCount, 2, ItemAnalysisView."Dimension 1 Code"); FillCell(RowNoCount, 3, Dim1Filter); end; if Dim2Filter <> '' then begin RowNoCount := RowNoCount + 1; FillCell(RowNoCount, 2, ItemAnalysisView."Dimension 2 Code"); FillCell(RowNoCount, 3, Dim2Filter); end; if Dim3Filter <> '' then begin RowNoCount := RowNoCount + 1; FillCell(RowNoCount, 2, ItemAnalysisView."Dimension 3 Code"); FillCell(RowNoCount, 3, Dim3Filter); end; RowNoCount := RowNoCount + 1; FillCell(RowNoCount, 2, Text028); if Sign then FillCell(RowNoCount, 3, Text029) else FillCell(RowNoCount, 3, Text030); end; local procedure CreateRowWithColumnsCaptions(ItemAnalysisViewEntry: Record "Item Analysis View Entry"; ItemAnalysisView: Record "Item Analysis View") var i: Integer; begin NoOfColumns := NoOfColumns + 1; FillCell(1, NoOfColumns, Format(Text018) + ' ' + Format(Text022) + ' ' + Format(0)); if ItemAnalysisView."Dimension 1 Code" <> '' then for i := 0 to MaxLevelDim[1] do begin NoOfColumns := NoOfColumns + 1; FillCell(1, NoOfColumns, ItemAnalysisView."Dimension 1 Code" + ' ' + Format(Text022) + ' ' + Format(i)); end; if ItemAnalysisView."Dimension 2 Code" <> '' then for i := 0 to MaxLevelDim[2] do begin NoOfColumns := NoOfColumns + 1; FillCell(1, NoOfColumns, ItemAnalysisView."Dimension 2 Code" + ' ' + Format(Text022) + ' ' + Format(i)); end; if ItemAnalysisView."Dimension 3 Code" <> '' then for i := 0 to MaxLevelDim[3] do begin NoOfColumns := NoOfColumns + 1; FillCell(1, NoOfColumns, ItemAnalysisView."Dimension 3 Code" + ' ' + Format(Text022) + ' ' + Format(i)); end; FillNextCellInRow(Text004); FillNextCellInRow(Text005); FillNextCellInRow(Text006); FillNextCellInRow(Text007); FillNextCellInRow(Text008); FillNextCellInRow(Text009); FillNextCellInRow(Text032); FillNextCellInRow(Text033); FillNextCellInRow(ItemAnalysisViewEntry.FieldCaption(Quantity)); FillNextCellInRow(Text039); FillNextCellInRow(Text020); FillNextCellInRow(Text035); FillNextCellInRow(Text036); StartNewRow(); end; local procedure CreateItemAnalysisViewEntryPart(var ItemAnalysisViewEntry: Record "Item Analysis View Entry"; ItemAnalysisView: Record "Item Analysis View"; var StartDate: Date; var EndDate: Date; ShowName: Boolean; SignValue: Integer) begin StartDate := ItemAnalysisViewEntry."Posting Date"; if ItemAnalysisViewEntry.Find('-') then repeat if ItemAnalysisViewEntry."Item No." <> Item."No." then if Item.Get(ItemAnalysisViewEntry."Item No.") then Item.Mark(true); if ItemAnalysisViewEntry."Posting Date" = NormalDate(ItemAnalysisViewEntry."Posting Date") then begin if ItemAnalysisViewEntry."Posting Date" >= EndDate then EndDate := ItemAnalysisViewEntry."Posting Date" else if ItemAnalysisViewEntry."Posting Date" <= StartDate then StartDate := ItemAnalysisViewEntry."Posting Date"; FillOutItem(ItemAnalysisViewEntry."Item No.", ShowName); if ItemAnalysisView."Dimension 1 Code" <> '' then FillOutDim(ItemAnalysisViewEntry."Dimension 1 Value Code", ItemAnalysisView."Dimension 1 Code", 1, ShowName); if ItemAnalysisView."Dimension 2 Code" <> '' then FillOutDim(ItemAnalysisViewEntry."Dimension 2 Value Code", ItemAnalysisView."Dimension 2 Code", 2, ShowName); if ItemAnalysisView."Dimension 3 Code" <> '' then FillOutDim(ItemAnalysisViewEntry."Dimension 3 Value Code", ItemAnalysisView."Dimension 3 Code", 3, ShowName); FillNextCellInRow(CalculatePeriodStart(NormalDate(ItemAnalysisViewEntry."Posting Date"), -1)); FillNextCellInRow(CalculatePeriodStart(NormalDate(ItemAnalysisViewEntry."Posting Date"), 0)); FillNextCellInRow(CalculatePeriodStart(NormalDate(ItemAnalysisViewEntry."Posting Date"), 1)); FillNextCellInRow(CalculatePeriodStart(NormalDate(ItemAnalysisViewEntry."Posting Date"), 2)); FillNextCellInRow(CalculatePeriodStart(NormalDate(ItemAnalysisViewEntry."Posting Date"), 3)); FillNextCellInRow(CalculatePeriodStart(NormalDate(ItemAnalysisViewEntry."Posting Date"), 4)); FillNextCellInRow((ItemAnalysisViewEntry."Sales Amount (Actual)" + ItemAnalysisViewEntry."Sales Amount (Expected)") * SignValue); FillNextCellInRow((ItemAnalysisViewEntry."Cost Amount (Actual)" + ItemAnalysisViewEntry."Cost Amount (Expected)" + ItemAnalysisViewEntry."Cost Amount (Non-Invtbl.)") * SignValue); FillNextCellInRow(ItemAnalysisViewEntry.Quantity * SignValue); FillNextCellInRow(Format(ItemAnalysisViewEntry."Location Code")); StartNewRow(); end; until ItemAnalysisViewEntry.Next() = 0; end; local procedure CreateItemAnalysisViewBudgetEntryPart(ItemAnalysisView: Record "Item Analysis View"; var StartDate: Date; var EndDate: Date; DateFilter: Text; ItemFilter: Text; LocationFilter: Text; BudgetFilter: Text; Dim1Filter: Text; Dim2Filter: Text; Dim3Filter: Text; ShowName: Boolean; SignValue: Integer) var ItemAnalysisViewBudgEntry: Record "Item Analysis View Budg. Entry"; begin ItemAnalysisViewBudgEntry.SetRange("Analysis Area", ItemAnalysisView."Analysis Area"); ItemAnalysisViewBudgEntry.SetRange("Analysis View Code", ItemAnalysisView.Code); ItemAnalysisViewBudgEntry.SetFilter("Posting Date", DateFilter); ItemAnalysisViewBudgEntry.SetFilter("Item No.", ItemFilter); ItemAnalysisViewBudgEntry.SetFilter("Location Code", LocationFilter); ItemAnalysisViewBudgEntry.SetFilter("Budget Name", BudgetFilter); ItemAnalysisViewBudgEntry.SetFilter("Dimension 1 Value Code", Dim1Filter); ItemAnalysisViewBudgEntry.SetFilter("Dimension 2 Value Code", Dim2Filter); ItemAnalysisViewBudgEntry.SetFilter("Dimension 3 Value Code", Dim3Filter); if ItemAnalysisViewBudgEntry.Find('-') then repeat if ItemAnalysisViewBudgEntry."Item No." <> Item."No." then if Item.Get(ItemAnalysisViewBudgEntry."Item No.") then Item.Mark(true); if ItemAnalysisViewBudgEntry."Posting Date" = NormalDate(ItemAnalysisViewBudgEntry."Posting Date") then begin if ItemAnalysisViewBudgEntry."Posting Date" >= EndDate then EndDate := ItemAnalysisViewBudgEntry."Posting Date" else if ItemAnalysisViewBudgEntry."Posting Date" <= StartDate then StartDate := ItemAnalysisViewBudgEntry."Posting Date"; FillOutItem(ItemAnalysisViewBudgEntry."Item No.", ShowName); if ItemAnalysisView."Dimension 1 Code" <> '' then FillOutDim(ItemAnalysisViewBudgEntry."Dimension 1 Value Code", ItemAnalysisView."Dimension 1 Code", 1, ShowName); if ItemAnalysisView."Dimension 2 Code" <> '' then FillOutDim(ItemAnalysisViewBudgEntry."Dimension 2 Value Code", ItemAnalysisView."Dimension 2 Code", 2, ShowName); if ItemAnalysisView."Dimension 3 Code" <> '' then FillOutDim(ItemAnalysisViewBudgEntry."Dimension 3 Value Code", ItemAnalysisView."Dimension 3 Code", 3, ShowName); FillNextCellInRow(CalculatePeriodStart(NormalDate(ItemAnalysisViewBudgEntry."Posting Date"), -1)); FillNextCellInRow(CalculatePeriodStart(NormalDate(ItemAnalysisViewBudgEntry."Posting Date"), 0)); FillNextCellInRow(CalculatePeriodStart(NormalDate(ItemAnalysisViewBudgEntry."Posting Date"), 1)); FillNextCellInRow(CalculatePeriodStart(NormalDate(ItemAnalysisViewBudgEntry."Posting Date"), 2)); FillNextCellInRow(CalculatePeriodStart(NormalDate(ItemAnalysisViewBudgEntry."Posting Date"), 3)); FillNextCellInRow(CalculatePeriodStart(NormalDate(ItemAnalysisViewBudgEntry."Posting Date"), 4)); FillNextCellInRow(''); FillNextCellInRow(''); FillNextCellInRow(''); FillNextCellInRow(ItemAnalysisViewBudgEntry."Location Code"); FillNextCellInRow(ItemAnalysisViewBudgEntry."Sales Amount" * SignValue); FillNextCellInRow(ItemAnalysisViewBudgEntry."Cost Amount" * SignValue); FillNextCellInRow(ItemAnalysisViewBudgEntry.Quantity * SignValue); StartNewRow(); end; until ItemAnalysisViewBudgEntry.Next() = 0; end; local procedure CalculatePeriodStart(PostingDate: Date; DateCompression: Integer): Date var AccountingPeriod: Record "Accounting Period"; PrevPostingDate: Date; PrevCalculatedPostingDate: Date; begin PrevPostingDate := 0D; case DateCompression of 0:// Week : PostingDate := CalcDate('<CW+1D-1W>', PostingDate); 1:// Month : PostingDate := CalcDate('<CM+1D-1M>', PostingDate); 2:// Quarter : PostingDate := CalcDate('<CQ+1D-1Q>', PostingDate); 3:// Year : PostingDate := CalcDate('<CY+1D-1Y>', PostingDate); 4:// Period : begin if PostingDate <> PrevPostingDate then begin PrevPostingDate := PostingDate; AccountingPeriod.SetRange("Starting Date", 0D, PostingDate); if AccountingPeriod.FindLast() then PrevCalculatedPostingDate := AccountingPeriod."Starting Date" else PrevCalculatedPostingDate := PostingDate; end; PostingDate := PrevCalculatedPostingDate; end; end; exit(PostingDate); end; local procedure FindDimLevel(DimCode: Code[20]; DimFilter: Text; ArrayNo: Integer) var DimValue: Record "Dimension Value"; begin if DimCode = '' then exit; DimValue.SetRange("Dimension Code", DimCode); if DimValue.Find('-') then repeat TempDimValue2.Copy(DimValue); TempDimValue2.Insert(); TempDimValue3.Copy(DimValue); TempDimValue3.Insert(); until DimValue.Next() = 0; TempDimValue2.SetFilter(Code, DimFilter); if TempDimValue2.Find('-') then repeat if MaxLevelDim[ArrayNo] < TempDimValue2.Indentation then MaxLevelDim[ArrayNo] := TempDimValue2.Indentation; until TempDimValue2.Next() = 0; end; local procedure FindDimParent(var Account: Code[20]; DimensionCode: Code[20]) begin TempDimValue3.Reset(); TempDimValue3.SetRange("Dimension Code", DimensionCode); TempDimValue3.Get(DimensionCode, Account); if TempDimValue3.Indentation <> 0 then begin TempDimValue3.SetRange(Indentation, TempDimValue3.Indentation - 1); TempDimValue3.Next(-1); end; Account := TempDimValue3.Code; end; local procedure FillOutDim(DimValueCode: Code[20]; DimCode: Code[20]; DimNo: Integer; ShowName: Boolean) var TempParentNameValueBuffer: Record "Name/Value Buffer" temporary; DimensionValue: Record "Dimension Value"; Indent: Integer; i: Integer; DimValueCode2: Code[20]; begin if DimValueCode <> '' then begin if TempDimValue2.Get(DimCode, DimValueCode) then TempDimValue2.Mark(true) else TempDimValue2.Init(); DimValueCode2 := DimValueCode; Indent := TempDimValue2.Indentation; if (Indent <> 0) and (DimValueCode2 <> '') then for i := Indent downto 1 do begin FindDimParent(DimValueCode2, DimCode); TempDimValue2.Get(DimCode, DimValueCode2); AddParentToBuffer(TempParentNameValueBuffer, i, TempDimValue2.Code, TempDimValue2.Name); end; if TempParentNameValueBuffer.FindSet() then repeat AddAcc(ShowName, TempParentNameValueBuffer.Name, TempParentNameValueBuffer.Value); until TempParentNameValueBuffer.Next() = 0; if DimensionValue.Get(DimCode, DimValueCode) then; if DimensionValue.Indentation <> MaxLevelDim[DimNo] then for i := DimensionValue.Indentation + 1 to MaxLevelDim[DimNo] do AddAcc(ShowName, DimensionValue.Code, DimensionValue.Name); AddAcc(ShowName, DimensionValue.Code, DimensionValue.Name); end else for i := 0 to MaxLevelDim[DimNo] do AddAcc(false, '', ''); end; local procedure FillOutItem(ItemNo: Code[20]; ShowName: Boolean) begin AddAcc(ShowName and (ItemNo <> ''), ItemNo, Item.Description); end; local procedure FillCell(RowNo: Integer; ColumnNo: Integer; Value: Variant) begin TempExcelBuffer.Init(); TempExcelBuffer.Validate("Row No.", RowNo); TempExcelBuffer.Validate("Column No.", ColumnNo); case true of Value.IsDecimal or Value.IsInteger: TempExcelBuffer.Validate("Cell Type", TempExcelBuffer."Cell Type"::Number); Value.IsDate: TempExcelBuffer.Validate("Cell Type", TempExcelBuffer."Cell Type"::Date); else TempExcelBuffer.Validate("Cell Type", TempExcelBuffer."Cell Type"::Text); end; TempExcelBuffer."Cell Value as Text" := CopyStr(Format(Value), 1, MaxStrLen(TempExcelBuffer."Cell Value as Text")); TempExcelBuffer.Insert(); end; local procedure FillNextCellInRow(Value: Variant) var RowNo: Integer; ColumnNo: Integer; begin RowNo := TempExcelBuffer."Row No."; ColumnNo := TempExcelBuffer."Column No." + 1; TempExcelBuffer.Init(); TempExcelBuffer.Validate("Row No.", RowNo); TempExcelBuffer.Validate("Column No.", ColumnNo); case true of Value.IsDecimal or Value.IsInteger: TempExcelBuffer.Validate("Cell Type", TempExcelBuffer."Cell Type"::Number); Value.IsDate: TempExcelBuffer.Validate("Cell Type", TempExcelBuffer."Cell Type"::Date); else TempExcelBuffer.Validate("Cell Type", TempExcelBuffer."Cell Type"::Text); end; TempExcelBuffer."Cell Value as Text" := CopyStr(Format(Value), 1, MaxStrLen(TempExcelBuffer."Cell Value as Text")); TempExcelBuffer.Insert(); end; local procedure StartNewRow() var RowNo: Integer; begin RowNo := TempExcelBuffer."Row No." + 1; TempExcelBuffer.Init(); TempExcelBuffer.Validate("Row No.", RowNo); TempExcelBuffer.Validate("Column No.", 0); end; local procedure SetStartColumnNo(ColumntNo: Integer) begin TempExcelBuffer."Column No." := ColumntNo; end; procedure SetCommonFilters(CurrentAnalysisArea: Option; CurrentAnalysisViewCode: Code[10]; var ItemAnalysisViewEntry: Record "Item Analysis View Entry"; DateFilter: Text[30]; ItemFilter: Code[250]; Dim1Filter: Code[250]; Dim2Filter: Code[250]; Dim3Filter: Code[250]; LocationFilter: Code[250]) begin ItemAnalysisViewEntry.SetRange("Analysis Area", CurrentAnalysisArea); ItemAnalysisViewEntry.SetRange("Analysis View Code", CurrentAnalysisViewCode); if DateFilter <> '' then ItemAnalysisViewEntry.SetFilter("Posting Date", DateFilter); if ItemFilter <> '' then ItemAnalysisViewEntry.SetFilter("Item No.", ItemFilter); if Dim1Filter <> '' then ItemAnalysisViewEntry.SetFilter("Dimension 1 Value Code", Dim1Filter); if Dim2Filter <> '' then ItemAnalysisViewEntry.SetFilter("Dimension 2 Value Code", Dim2Filter); if Dim3Filter <> '' then ItemAnalysisViewEntry.SetFilter("Dimension 3 Value Code", Dim3Filter); if LocationFilter <> '' then ItemAnalysisViewEntry.SetFilter("Location Code", LocationFilter); OnAfterSetCommonFilters(ItemAnalysisViewEntry, CurrentAnalysisArea, CurrentAnalysisViewCode); end; local procedure AddAcc(ShowName: Boolean; Account: Text; AccName: Text) var CellValueAsText: Text; IsHandled: Boolean; begin IsHandled := false; OnBeforeAddAcc(ShowName, Account, AccName, IsHandled, Item, TempExcelBuffer); if not IsHandled then begin if Account = '' then CellValueAsText := '' else if ShowName then CellValueAsText := Account + ' ' + AccName else CellValueAsText := Account; FillNextCellInRow(CellValueAsText); end; OnAfterAddAcc(ShowName, Account, AccName, CellValueAsText, Item, TempExcelBuffer); end; local procedure AddParentToBuffer(var NameValueBuffer: Record "Name/Value Buffer"; id: Integer; AccountNumber: Text[250]; AccountName: Text[250]) begin NameValueBuffer.Init(); NameValueBuffer.ID := id; NameValueBuffer.Name := AccountNumber; NameValueBuffer.Value := AccountName; NameValueBuffer.Insert(); end; local procedure WriteDimLine(DimNo: Integer; DimFilter: Text; DimCode: Code[20]; NoOfLeadingColumns: Integer; ShowName: Boolean) begin SetStartColumnNo(NoOfLeadingColumns); TempDimValue2.SetFilter(Code, DimFilter); TempDimValue2.SetFilter("Dimension Code", DimCode); TempDimValue2.SetRange("Dimension Value Type", TempDimValue2."Dimension Value Type"::Standard); if TempDimValue2.Find('-') then repeat if not TempDimValue2.Mark() then begin FillOutDim(TempDimValue2.Code, DimCode, DimNo, ShowName); StartNewRow(); SetStartColumnNo(NoOfLeadingColumns); end; until TempDimValue2.Next() = 0; end; procedure GetServerFileName(): Text begin exit(ServerFileName); end; procedure SetSkipDownload() begin SkipDownload := true; end; [IntegrationEvent(false, false)] local procedure OnAfterSetCommonFilters(var ItemAnalysisViewEntry: Record "Item Analysis View Entry"; CurrentAnalysisArea: Option; ItemAnalysisViewCode: Code[10]) begin end; [IntegrationEvent(false, false)] local procedure OnBeforeAddAcc(ShowName: Boolean; var Account: Text; var AccName: Text; var IsHandled: Boolean; Item: Record Item; var TempExcelBuffer: Record "Excel Buffer" temporary) begin end; [IntegrationEvent(false, false)] local procedure OnAfterAddAcc(ShowName: Boolean; var Account: Text; var AccName: Text; var CellValueAsText: Text; Item: Record Item; var TempExcelBuffer: Record "Excel Buffer" temporary) begin end;}