web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Error in excel report ...
Microsoft Dynamics AX (Archived)

Error in excel report in ax 2012 r3

(0) ShareShare
ReportReport
Posted on by srinivas pamidi 1,737

When i am running the excel report it is throwing the error like this 

"Method 'numberFormat' in COM object of class 'Range' returned error code 0x800A03EC (<unknown>) which means: Unable to set the NumberFormat property of the Range class."

It is working in the Test Environment, but it is not working in the Production Environment.

*This post is locked for comments

I have the same question (0)
  • Luan Nguyen Profile Picture
    Luan Nguyen 689 on at
    RE: Error in excel report in ax 2012 r3

    I still dont know what exactly your problem, what kind of excel report, where is that come from?

    Can you give us more details, some pics may be good.

  • srinivas pamidi Profile Picture
    srinivas pamidi 1,737 on at
    RE: Error in excel report in ax 2012 r3

    I am trying to fetch the Actual vs budget report in to excel sheet

    Budgeting->Reports->Basic Budgeting->Actual Vs Budget

    I am change the code in the Budget Balances ActualsDP -> sumbudgetBalance Method to get the Data in to in to excel

    /// <summary>

    ///    Sums the temporary table data.

    /// </summary>

    /// <returns>

    ///    The budget amounts in a <c>BudgetTmpBalance</c> temporary table.

    /// </returns>

    public BudgetTmpBalance sumBudgetTmpBalance()

    {

       BudgetTmpBalance           budgetTmpBalanceLocal;

       BudgetControlConfiguration budgetControlConfiguration;

       //srini

       DimensionDisplayValue      mainAccount,businessUnit,department,worker;

       HcmPersonnelNumberId       PersonnelNumber;

       MainAccountNum             mainaccountId;

       Name                       mainAccountName,businessUnitName,departmentName,workerName;

       MainAccount                mainAccountTable;

       OMOperatingUnit            oMOperatingUnit;

       HcmWorker                  hcmWorker;

       DimensionDisplayValue      dimensionFocus,LedgerAccount;

       container                  offsetAccount;

       RefRecId                   ledgerDimension;

       //Code for Splitting the Ledger Account based on the ledger dimension..

       DimensionAttributeValueCombination  dimensionAttributeValueCombination;

       //GeneralJournalAccountEntry is one such tables that refrences DimensionAttributeValueCombination

       GeneralJournalAccountEntry          generalJournalAccountEntry;

       // Excel object definitions

       SysExcelApplication     xlsApplication;

       SysExcelWorkBooks       xlsWorkBookCollection;

       SysExcelWorkBook        xlsWorkBook;

       SysExcelWorkSheets      xlsWorkSheetCollection;

       SysExcelWorkSheet       xlsWorkSheet;

       // random variable declarations

       int                     row = 1;

       // define and initialize the progress bar so the user knows what is going on

       SysOperationProgress progress = new SysOperationProgress();

       #AviFiles

       Progress.setAnimation(#aviTransfer);

       progress.setCaption("Sending information to Excel");

       progress.setText("Processing...");

       progress.update(true);

       //Initialize Excel instance

       xlsApplication           = SysExcelApplication::construct();

       //Create Excel WorkBook and WorkSheet

       xlsWorkBookCollection    = xlsApplication.workbooks();

       xlsWorkBook              = xlsWorkBookCollection.add();

       xlsWorkSheetCollection   = xlsWorkBook.worksheets();

       xlsWorkSheet             = xlsWorkSheetCollection.itemFromNum(1);

       //Excel columns captions

       // columns should autofit

       xlsWorkSheet.columns().autoFit();

       // headings

       xlsWorkSheet.cells().item(row,1).value("Budget Model");

       xlsWorkSheet.cells().item(row,2).value("Period Start");

       xlsWorkSheet.cells().item(row,3).value("Main Account");

       xlsWorkSheet.cells().item(row,4).value("Main Account Name");

       xlsWorkSheet.cells().item(row,5).value("Business Unit");

       xlsWorkSheet.cells().item(row,6).value("Business Unit Name");

       xlsWorkSheet.cells().item(row,7).value("Department");

       xlsWorkSheet.cells().item(row,8).value("Department Name");

       xlsWorkSheet.cells().item(row,9).value("Worker");

       xlsWorkSheet.cells().item(row,10).value("Worker Name");

       xlsWorkSheet.cells().item(row,11).value("Original Budget");

       xlsWorkSheet.cells().item(row,12).value("Revised Budget");

       xlsWorkSheet.cells().item(row,13).value("Actual Expenditures");

       xlsWorkSheet.cells().item(row,14).value("Variance Amount");

       xlsWorkSheet.cells().item(row,15).value("Variance Percent");

       xlsWorkSheet.cells().item(row,16).value("Percent of Budget");

       row++;

       // Temp table could contain multiple records for each dimension focus \ budget model combination; sum up all the common combinations.

       if (isConfigurationkeyEnabled(configurationKeyNum(PublicSector)))

       {

           budgetControlConfiguration = BudgetControlConfiguration::findActiveByPrimaryLedger(Ledger::current());

           while select sum(OriginalAmount), sum(RevisedAmount), sum(LedgerAmount), sum(Variance), sum(PreliminaryAmount), sum (ApportionmentAmount)

               from budgetTmpBalance

               group by BudgetModelId, DimensionFocus, Period

           {

               budgetTmpBalance.UseOnlyApportionment = budgetControlConfiguration.UseApportionedAmount;

               budgetTmpBalance.SumPreliminaryBudget = budgetControlConfiguration.SumPreliminaryBudget && !budgetTmpBalance.UseOnlyApportionment;

               buf2Buf(budgetTmpBalance, budgetTmpBalanceLocal);

               if (contract.parmBudgetType() == BudgetType::Revenue)

               {

                   // Invert Revenue amounts. This will only occur for the chart because the BudgetType parameter isn't exposed elsewhere.

                   budgetTmpBalanceLocal.RevisedAmount = budgetTmpBalanceLocal.RevisedAmount * -1;

                   budgetTmpBalanceLocal.LedgerAmount = budgetTmpBalanceLocal.LedgerAmount * -1;

               }

               if (budgetTmpBalanceLocal.UseOnlyApportionment)

               {

                   budgetTmpBalanceLocal.PercentOfBudget = (budgetTmpBalanceLocal.ApportionmentAmount == 0 || budgetTmpBalanceLocal.Variance == 0)?0: budgetTmpBalanceLocal.Variance / budgetTmpBalanceLocal.ApportionmentAmount;

                   budgetTmpBalanceLocal.VariancePercent = (budgetTmpBalanceLocal.ApportionmentAmount == 0 || budgetTmpBalanceLocal.LedgerAmount == 0)?0: budgetTmpBalanceLocal.LedgerAmount / budgetTmpBalanceLocal.ApportionmentAmount;

               }

               else

               {

                   budgetTmpBalanceLocal.PercentOfBudget = (budgetTmpBalanceLocal.RevisedAmount == 0 || budgetTmpBalanceLocal.Variance == 0)?0: budgetTmpBalanceLocal.Variance / budgetTmpBalanceLocal.RevisedAmount;

                   budgetTmpBalanceLocal.VariancePercent = (budgetTmpBalanceLocal.RevisedAmount == 0 || budgetTmpBalanceLocal.LedgerAmount == 0)?0: budgetTmpBalanceLocal.LedgerAmount / budgetTmpBalanceLocal.RevisedAmount;

                   dimensionFocus = budgetTmpBalanceLocal.DimensionFocus;

                   LedgerAccount = subStr(dimensionFocus,1,44);

                     mainAccount = subStr(dimensionFocus,1,8);

                     select mainAccountTable

                     where mainAccountTable.MainAccountId == mainAccount;

                        mainAccountName = mainAccountTable.Name;

                     businessUnit = subStr(dimensionFocus,12,8);

                        select oMOperatingUnit

                        where oMOperatingUnit.OMOperatingUnitNumber == businessUnit;

                       businessUnitName = oMOperatingUnit.Name;

                     department = subStr(dimensionFocus,23,8);

                        select oMOperatingUnit

                        where oMOperatingUnit.OMOperatingUnitNumber == department;

                        departmentName = oMOperatingUnit.Name;

                   worker = subStr(dimensionFocus,34,7);

                   PersonnelNumber = worker;

                      select * from hcmWorker

                       where hcmWorker.PersonnelNumber == PersonnelNumber;

                        workerName = hcmWorker.name();

                   //offsetAccount = ['MainAccount','4000',,4,'BusinessUnit',010,'Department',022,'Worker',033,'Project','-','Budget','-'];

                    /*offsetAccount = ['MainAccount',mainAccount,'BusinessUnit',businessUnit,'Department',department,'Worker',worker,'Project','-','Budget','-'];

                   ledgerDimension = AxdDimensionUtil::getLedgerAccountId(offsetAccount);

                   select generalJournalAccountEntry

                   where generalJournalAccountEntry.LedgerAccount == LedgerAccount;*/

                  // info(strFmt("%1: %2, %3", segmentName, segmentValue, segmentDescription));

           progress.setText(strFmt("Transferring Budget Information %1",budgetTmpBalanceLocal.BudgetModelId));

           xlsWorkSheet.cells().item(row,1).comObject().numberFormat('@');

           xlsWorkSheet.cells().item(row,1).value(budgetTmpBalanceLocal.BudgetModelId);

           xlsWorkSheet.cells().item(row,2).comObject().numberFormat('@');

          xlsWorkSheet.cells().item(row,2).value(strfmt('%1', budgetTmpBalanceLocal.Period));

           xlsWorkSheet.cells().item(row,3).comObject().numberFormat('@');

           xlsWorkSheet.cells().item(row,3).value(mainAccount);

                   xlsWorkSheet.cells().item(row,4).comObject().numberFormat('@');

           xlsWorkSheet.cells().item(row,4).value(mainAccountName);

                   xlsWorkSheet.cells().item(row,5).comObject().numberFormat('@');

           xlsWorkSheet.cells().item(row,5).value(businessUnit);

                   xlsWorkSheet.cells().item(row,6).comObject().numberFormat('@');

           xlsWorkSheet.cells().item(row,6).value(businessUnitName);

                   xlsWorkSheet.cells().item(row,7).comObject().numberFormat('@');

           xlsWorkSheet.cells().item(row,7).value(department);

                   xlsWorkSheet.cells().item(row,8).comObject().numberFormat('@');

           xlsWorkSheet.cells().item(row,8).value(departmentName);

                   xlsWorkSheet.cells().item(row,9).comObject().numberFormat('@');

           xlsWorkSheet.cells().item(row,9).value(worker);

                   xlsWorkSheet.cells().item(row,10).comObject().numberFormat('@');

           xlsWorkSheet.cells().item(row,10).value(workerName);

                   xlsWorkSheet.cells().item(row,11).comObject().numberFormat('@');

                   xlsWorkSheet.cells().item(row,11).value(strfmt('%1', budgetTmpBalanceLocal.OriginalAmount));

                   xlsWorkSheet.cells().item(row,12).comObject().numberFormat('@');

                   xlsWorkSheet.cells().item(row,12).value(strfmt('%1', budgetTmpBalanceLocal.RevisedAmount));

               xlsWorkSheet.cells().item(row,13).comObject().numberFormat('@');

                   xlsWorkSheet.cells().item(row,13).value(strfmt('%1', budgetTmpBalanceLocal.LedgerAmount));

                   xlsWorkSheet.cells().item(row,14).comObject().numberFormat('@');

                   xlsWorkSheet.cells().item(row,14).value(strfmt('%1', budgetTmpBalanceLocal.Variance));

                   xlsWorkSheet.cells().item(row,15).comObject().numberFormat('@');

                   xlsWorkSheet.cells().item(row,15).value(strfmt('%1', budgetTmpBalanceLocal.VariancePercent) + '%');

                   xlsWorkSheet.cells().item(row,16).comObject().numberFormat('@');

                   xlsWorkSheet.cells().item(row,16).value(strfmt('%1', budgetTmpBalanceLocal.PercentOfBudget) + '%');

           row++;

           //Open Excel document

           xlsApplication.visible(true);

                   }

               budgetTmpBalanceLocal.insert();

           }

       info(strFmt("%1","Transfer data ToDate excel sheet is completed"));

       }

       else

       {

           while select sum(OriginalAmount), sum(RevisedAmount), sum(LedgerAmount), sum(Variance) from budgetTmpBalance

               group by BudgetModelId, DimensionFocus, Period

           {

               buf2Buf(budgetTmpBalance, budgetTmpBalanceLocal);

               if (contract.parmBudgetType() == BudgetType::Revenue)

               {

                   // Invert Revenue amounts. This will only occur for the chart because the BudgetType parameter isn't exposed elsewhere.

                   budgetTmpBalanceLocal.RevisedAmount = budgetTmpBalanceLocal.RevisedAmount * -1;

                   budgetTmpBalanceLocal.LedgerAmount = budgetTmpBalanceLocal.LedgerAmount * -1;

               }

               budgetTmpBalanceLocal.PercentOfBudget = (budgetTmpBalanceLocal.RevisedAmount == 0 || budgetTmpBalanceLocal.Variance == 0)?0: budgetTmpBalanceLocal.Variance / budgetTmpBalanceLocal.RevisedAmount;

               budgetTmpBalanceLocal.VariancePercent = (budgetTmpBalanceLocal.RevisedAmount == 0 || budgetTmpBalanceLocal.LedgerAmount == 0)?0: budgetTmpBalanceLocal.LedgerAmount / budgetTmpBalanceLocal.RevisedAmount;

               budgetTmpBalanceLocal.insert();

           }

       }

       return budgetTmpBalanceLocal;

    }

    It is working in Development, but it is not working in Production.

  • Suggested answer
    Luan Nguyen Profile Picture
    Luan Nguyen 689 on at
    RE: Error in excel report in ax 2012 r3

    How much data in your Development Environment and Production Environment ?

    that error was cause of Excel could not handle that much data.

    take a look on this for more details: blogs.msdn.microsoft.com/.../export-to-excel-can-fail-on-a-windows-terminal-server

  • srinivas pamidi Profile Picture
    srinivas pamidi 1,737 on at
    RE: Error in excel report in ax 2012 r3

    Hi Max Nguyen,

    Thank you for your reply...

    I am tested with the Dev and Production data same data is there in the dev and production.

    I am thinking that this problem is not related to data.

    Now one more error is coming.

    "Method 'numberFormat' is not supported by the Automation interface of the COM object of class 'Range'."

    After searching in google i am find out one link please go through it..

    dynamicsuser.net/.../export-to-excel-by-using-com-class-in-x

    I am unable understand the link you mentioned in the previous post.

  • srinivas pamidi Profile Picture
    srinivas pamidi 1,737 on at
    RE: Error in excel report in ax 2012 r3

    Hi Max Nguyen,

    These many errors are coming randomly.

    Method 'numberFormat' is not supported by the Automation interface of the COM object of class 'Range'.

    Method 'item' is not supported by the Automation interface of the COM object of class 'Range'.

    Method 'cells' is not supported by the Automation interface of the COM object of class '_Worksheet'.

    Method 'numberFormat' in COM object of class 'Range' returned error code 0x800A03EC (<unknown>) which means: Unable to set the NumberFormat property of the Range class.

    Method '%1' in COM object of class '%2' returned error code 0x%3 (%4) which means: %5.

    Method 'value' is not supported by the Automation interface of the COM object of class '<unknown>'.

    can you suggest one solution for this.

  • Suggested answer
    Luan Nguyen Profile Picture
    Luan Nguyen 689 on at
    RE: Error in excel report in ax 2012 r3

    Sorry for late reply, Can you please create a job and test it with the same code and using Server method

    Static void Server createExcel()

  • srinivas pamidi Profile Picture
    srinivas pamidi 1,737 on at
    RE: Error in excel report in ax 2012 r3

    Hi Max Nguyen,

    I am writing the below code in job, but i am unable to run the excel sheet.

    in this job how to get the record from budgettmpbalance table?

    static void CreateExcel(Args _args)

    {

       /// <summary>

    ///    Sums the temporary table data.

    /// </summary>

    /// <returns>

    ///    The budget amounts in a <c>BudgetTmpBalance</c> temporary table.

    /// </returns>

       BudgetTmpBalance           budgetTmpBalanceLocal;

       BudgetTmpBalance                budgetTmpBalance;

       BudgetBalancesActualsContract   contract;

       BudgetControlConfiguration budgetControlConfiguration;

       //srini

       DimensionDisplayValue      mainAccount,businessUnit,department,worker;

       HcmPersonnelNumberId       PersonnelNumber;

       MainAccountNum             mainaccountId;

       Name                       mainAccountName,businessUnitName,departmentName,workerName;

       MainAccount                mainAccountTable;

       OMOperatingUnit            oMOperatingUnit;

       HcmWorker                  hcmWorker;

       DimensionDisplayValue      dimensionFocus,LedgerAccount;

       container                  offsetAccount;

       RefRecId                   ledgerDimension;

       //Code for Splitting the Ledger Account based on the ledger dimension..

       DimensionAttributeValueCombination  dimensionAttributeValueCombination;

       //GeneralJournalAccountEntry is one such tables that refrences DimensionAttributeValueCombination

       GeneralJournalAccountEntry          generalJournalAccountEntry;

       // Excel object definitions

       SysExcelApplication     xlsApplication;

       SysExcelWorkBooks       xlsWorkBookCollection;

       SysExcelWorkBook        xlsWorkBook;

       SysExcelWorkSheets      xlsWorkSheetCollection;

       SysExcelWorkSheet       xlsWorkSheet;

       // random variable declarations

       int                     row = 1;

       // define and initialize the progress bar so the user knows what is going on

       SysOperationProgress progress = new SysOperationProgress();

       #AviFiles

       Progress.setAnimation(#aviTransfer);

       progress.setCaption("Sending information to Excel");

       progress.setText("Processing...");

       progress.update(true);

       //Initialize Excel instance

       xlsApplication           = SysExcelApplication::construct();

       //Create Excel WorkBook and WorkSheet

       xlsWorkBookCollection    = xlsApplication.workbooks();

       xlsWorkBook              = xlsWorkBookCollection.add();

       xlsWorkSheetCollection   = xlsWorkBook.worksheets();

       xlsWorkSheet             = xlsWorkSheetCollection.itemFromNum(1);

       //Excel columns captions

       // columns should autofit

       xlsWorkSheet.columns().autoFit();

       // headings

       xlsWorkSheet.cells().item(row,1).value("Budget Model");

       xlsWorkSheet.cells().item(row,2).value("Period Start");

       xlsWorkSheet.cells().item(row,3).value("Main Account");

       xlsWorkSheet.cells().item(row,4).value("Main Account Name");

       xlsWorkSheet.cells().item(row,5).value("Business Unit");

       xlsWorkSheet.cells().item(row,6).value("Business Unit Name");

       xlsWorkSheet.cells().item(row,7).value("Department");

       xlsWorkSheet.cells().item(row,8).value("Department Name");

       xlsWorkSheet.cells().item(row,9).value("Worker");

       xlsWorkSheet.cells().item(row,10).value("Worker Name");

       xlsWorkSheet.cells().item(row,11).value("Original Budget");

       xlsWorkSheet.cells().item(row,12).value("Revised Budget");

       xlsWorkSheet.cells().item(row,13).value("Actual Expenditures");

       xlsWorkSheet.cells().item(row,14).value("Variance Amount");

       xlsWorkSheet.cells().item(row,15).value("Variance Percent");

       xlsWorkSheet.cells().item(row,16).value("Percent of Budget");

       row++;

       // Temp table could contain multiple records for each dimension focus \ budget model combination; sum up all the common combinations.

       if (isConfigurationkeyEnabled(configurationKeyNum(PublicSector)))

       {

           budgetControlConfiguration = BudgetControlConfiguration::findActiveByPrimaryLedger(Ledger::current());

           while select sum(OriginalAmount), sum(RevisedAmount), sum(LedgerAmount), sum(Variance), sum(PreliminaryAmount), sum (ApportionmentAmount)

               from budgetTmpBalance

               group by BudgetModelId, DimensionFocus, Period

               //where budgetTmpBalance.DimensionFocus == "14210101-00000002"

           {

               budgetTmpBalance.UseOnlyApportionment = budgetControlConfiguration.UseApportionedAmount;

               budgetTmpBalance.SumPreliminaryBudget = budgetControlConfiguration.SumPreliminaryBudget && !budgetTmpBalance.UseOnlyApportionment;

               buf2Buf(budgetTmpBalance, budgetTmpBalanceLocal);

               if (contract.parmBudgetType() == BudgetType::Revenue)

               {

                   // Invert Revenue amounts. This will only occur for the chart because the BudgetType parameter isn't exposed elsewhere.

                   budgetTmpBalanceLocal.RevisedAmount = budgetTmpBalanceLocal.RevisedAmount * -1;

                   budgetTmpBalanceLocal.LedgerAmount = budgetTmpBalanceLocal.LedgerAmount * -1;

               }

               if (budgetTmpBalanceLocal.UseOnlyApportionment)

               {

                   budgetTmpBalanceLocal.PercentOfBudget = (budgetTmpBalanceLocal.ApportionmentAmount == 0 || budgetTmpBalanceLocal.Variance == 0)?0: budgetTmpBalanceLocal.Variance / budgetTmpBalanceLocal.ApportionmentAmount;

                   budgetTmpBalanceLocal.VariancePercent = (budgetTmpBalanceLocal.ApportionmentAmount == 0 || budgetTmpBalanceLocal.LedgerAmount == 0)?0: budgetTmpBalanceLocal.LedgerAmount / budgetTmpBalanceLocal.ApportionmentAmount;

               }

               else

               {

                   budgetTmpBalanceLocal.PercentOfBudget = (budgetTmpBalanceLocal.RevisedAmount == 0 || budgetTmpBalanceLocal.Variance == 0)?0: budgetTmpBalanceLocal.Variance / budgetTmpBalanceLocal.RevisedAmount;

                   budgetTmpBalanceLocal.VariancePercent = (budgetTmpBalanceLocal.RevisedAmount == 0 || budgetTmpBalanceLocal.LedgerAmount == 0)?0: budgetTmpBalanceLocal.LedgerAmount / budgetTmpBalanceLocal.RevisedAmount;

                   dimensionFocus = "14210101-00000002";//budgetTmpBalanceLocal.DimensionFocus;

                   LedgerAccount = subStr(dimensionFocus,1,44);

                     mainAccount = subStr(dimensionFocus,1,8);

                     select mainAccountTable

                     where mainAccountTable.MainAccountId == mainAccount;

                        mainAccountName = mainAccountTable.Name;

                     businessUnit = subStr(dimensionFocus,12,8);

                        select oMOperatingUnit

                        where oMOperatingUnit.OMOperatingUnitNumber == businessUnit;

                       businessUnitName = oMOperatingUnit.Name;

                     department = subStr(dimensionFocus,23,8);

                        select oMOperatingUnit

                        where oMOperatingUnit.OMOperatingUnitNumber == department;

                        departmentName = oMOperatingUnit.Name;

                   worker = subStr(dimensionFocus,34,7);

                   PersonnelNumber = worker;

                      select * from hcmWorker

                       where hcmWorker.PersonnelNumber == PersonnelNumber;

                        workerName = hcmWorker.name();

                   //offsetAccount = ['MainAccount','4000',,4,'BusinessUnit',010,'Department',022,'Worker',033,'Project','-','Budget','-'];

                    /*offsetAccount = ['MainAccount',mainAccount,'BusinessUnit',businessUnit,'Department',department,'Worker',worker,'Project','-','Budget','-'];

                   ledgerDimension = AxdDimensionUtil::getLedgerAccountId(offsetAccount);

                   select generalJournalAccountEntry

                   where generalJournalAccountEntry.LedgerAccount == LedgerAccount;*/

                  // info(strFmt("%1: %2, %3", segmentName, segmentValue, segmentDescription));

           progress.setText(strFmt("Transferring Budget Information %1",budgetTmpBalanceLocal.BudgetModelId));

           xlsWorkSheet.cells().item(row,1).comObject().numberFormat('@');

           xlsWorkSheet.cells().item(row,1).value(budgetTmpBalanceLocal.BudgetModelId);

           xlsWorkSheet.cells().item(row,2).comObject().numberFormat('@');

          xlsWorkSheet.cells().item(row,2).value(strfmt('%1', budgetTmpBalanceLocal.Period));

           xlsWorkSheet.cells().item(row,3).comObject().numberFormat('@');

           xlsWorkSheet.cells().item(row,3).value(mainAccount);

                   xlsWorkSheet.cells().item(row,4).comObject().numberFormat('@');

           xlsWorkSheet.cells().item(row,4).value(mainAccountName);

                   xlsWorkSheet.cells().item(row,5).comObject().numberFormat('@');

           xlsWorkSheet.cells().item(row,5).value(businessUnit);

                   xlsWorkSheet.cells().item(row,6).comObject().numberFormat('@');

           xlsWorkSheet.cells().item(row,6).value(businessUnitName);

                   xlsWorkSheet.cells().item(row,7).comObject().numberFormat('@');

           xlsWorkSheet.cells().item(row,7).value(department);

                   xlsWorkSheet.cells().item(row,8).comObject().numberFormat('@');

           xlsWorkSheet.cells().item(row,8).value(departmentName);

                   xlsWorkSheet.cells().item(row,9).comObject().numberFormat('@');

           xlsWorkSheet.cells().item(row,9).value(worker);

                   xlsWorkSheet.cells().item(row,10).comObject().numberFormat('@');

           xlsWorkSheet.cells().item(row,10).value(workerName);

                   xlsWorkSheet.cells().item(row,11).comObject().numberFormat('@');

                   xlsWorkSheet.cells().item(row,11).value(strfmt('%1', budgetTmpBalanceLocal.OriginalAmount));

                   xlsWorkSheet.cells().item(row,12).comObject().numberFormat('@');

                   xlsWorkSheet.cells().item(row,12).value(strfmt('%1', budgetTmpBalanceLocal.RevisedAmount));

               xlsWorkSheet.cells().item(row,13).comObject().numberFormat('@');

                   xlsWorkSheet.cells().item(row,13).value(strfmt('%1', budgetTmpBalanceLocal.LedgerAmount));

                   xlsWorkSheet.cells().item(row,14).comObject().numberFormat('@');

                   xlsWorkSheet.cells().item(row,14).value(strfmt('%1', budgetTmpBalanceLocal.Variance));

                   xlsWorkSheet.cells().item(row,15).comObject().numberFormat('@');

                   xlsWorkSheet.cells().item(row,15).value(strfmt('%1', budgetTmpBalanceLocal.VariancePercent) + '%');

                   xlsWorkSheet.cells().item(row,16).comObject().numberFormat('@');

                   xlsWorkSheet.cells().item(row,16).value(strfmt('%1', budgetTmpBalanceLocal.PercentOfBudget) + '%');

           row++;

           //Open Excel document

           xlsApplication.visible(true);

                   }

               budgetTmpBalanceLocal.insert();

           }

       info(strFmt("%1","Transfer data ToDate excel sheet is completed"));

       }

       else

       {

           while select sum(OriginalAmount), sum(RevisedAmount), sum(LedgerAmount), sum(Variance) from budgetTmpBalance

               group by BudgetModelId, DimensionFocus, Period

           {

               buf2Buf(budgetTmpBalance, budgetTmpBalanceLocal);

               if (contract.parmBudgetType() == BudgetType::Revenue)

               {

                   // Invert Revenue amounts. This will only occur for the chart because the BudgetType parameter isn't exposed elsewhere.

                   budgetTmpBalanceLocal.RevisedAmount = budgetTmpBalanceLocal.RevisedAmount * -1;

                   budgetTmpBalanceLocal.LedgerAmount = budgetTmpBalanceLocal.LedgerAmount * -1;

               }

               budgetTmpBalanceLocal.PercentOfBudget = (budgetTmpBalanceLocal.RevisedAmount == 0 || budgetTmpBalanceLocal.Variance == 0)?0: budgetTmpBalanceLocal.Variance / budgetTmpBalanceLocal.RevisedAmount;

               budgetTmpBalanceLocal.VariancePercent = (budgetTmpBalanceLocal.RevisedAmount == 0 || budgetTmpBalanceLocal.LedgerAmount == 0)?0: budgetTmpBalanceLocal.LedgerAmount / budgetTmpBalanceLocal.RevisedAmount;

               budgetTmpBalanceLocal.insert();

           }

       }

       //return budgetTmpBalanceLocal;

    }

  • Suggested answer
    Luan Nguyen Profile Picture
    Luan Nguyen 689 on at
    RE: Error in excel report in ax 2012 r3

    hey man,

    not a hole DP Class man, just Create excel function in job, and give it some sample specific data. Run and see on Dev, then production environment.

  • Community Member Profile Picture
    Community Member on at
    RE: Error in excel report in ax 2012 r3

    What is your version of Office in Production?

  • srinivas pamidi Profile Picture
    srinivas pamidi 1,737 on at
    RE: Error in excel report in ax 2012 r3

    2016

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

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Community Member Profile Picture

Community Member 4

#2
Nayyar Siddiqi Profile Picture

Nayyar Siddiqi 2

#2
NNaumenko Profile Picture

NNaumenko 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans