Trial Balance Total

This question has suggested answer(s)

Hi everyone.. i am new to Dynamics AX.  I realized that many of the forms/Grids total field is missing.  especially Trial Balance Total. Can any one clarify me the reason behind unavailability of total in reports like trial balance in a famous ERP application like Microsoft DAX. Also Account Name is missing in many forms.  Is there any easy way to bring Total to the trial balance.

All Replies
  • Hi,

    Which version of AX are you using? In AX2012 R2 the report detailed trial balance shows totals.

    If you mean the form Trial Balance then this is by design. If you need the totals, so can easily export the grid to Excel and have the totals. Sometimes a total is at the top op a form or within a factbox.

    Account name missing is because of the flexible data model on ledger accounts and dimensions. Sometimes you will see a account name or clarification only per line as retrieving the data for all row can slow down your user experience.

    kind regards,

    André Arnaud de Calavon  |  Microsoft Dynamics AX Solution architect  |  My blog  |  My company

    This post is my own opinion and does not necessarily reflect the opinion or view of my company, Microsoft, both its employees, or other MVPs.

  • Adding the account name into the Trial Balance list page is very easy in 2012 R2.  This is because the grid populates from the same data provider as the Trial Balance reports, and they show the name.  With a few small code changes, the account name plus any additional dimensions that are part of whatever dimension set you are viewing, can be built and supplied to the grid.  If anyone expresses interest I'll post a HOWTO on this.

  • Hi Andrew, i am using AX 2012.

    Also sometimes Trial balance is not showing updated value. We have update balance for financial dimension set.

    Eventhough auto update check box enabled, TB Report  balance not properly updating. What could be the problem

  • Hi,

    Which auto update check box are you talking about? There are 2 places in AX2012 RTM and R1. On the dimension sets you have to initialize balances first before setting the auto update. (General Ledger, Setup, Dimensions, ..)

    It is recommended to update the balances by use of the periodic batch framework for performance reasons.

    In AX2012 R2 it is not possible anymore to use the auto update balances.

    kind regards,

    André Arnaud de Calavon  |  Microsoft Dynamics AX Solution architect  |  My blog  |  My company

    This post is my own opinion and does not necessarily reflect the opinion or view of my company, Microsoft, both its employees, or other MVPs.

  • Hi,

    I am taking about General Ledger -> setup -> financial dimension sets -> Update balance during  posting process check box.

  • This tickbox is per financial dimension set. If it is not working anyway, there might be a bug. I have not seen this behaviour before.

    kind regards,

    André Arnaud de Calavon  |  Microsoft Dynamics AX Solution architect  |  My blog  |  My company

    This post is my own opinion and does not necessarily reflect the opinion or view of my company, Microsoft, both its employees, or other MVPs.

  • Is it mostly updating but the balances drift over time, or is it not updating at all?  Are you talking about straight general journals or subledger postings like invoices and inventory?

  • I would really appreciate a post on how to do this!  Having a column with the account name and any dimension descriptions in the grid would be a big help.

  • Hi Andre ,

    you mentioned " It is recommended to update the balances by use of the periodic batch framework for performance reasons."

    Can you explain bit more ?

  • Hi Andre ,

    Could you bit explain about the periodic batch to update the leger balances ?

  • 2 words come to mind on this one: MANAGEMENT REPORTER...  

    Why go through all that trouble to make reports over the ledger when you have a tool that was designed to do that with high ease of use?.  Drilldown, dimension slicing, and ledger totals are built-in to Management reporter.  Come to think of it, I can't really think of why one wouldn't look at Management Reporter first when making financial reports.  

    I went into a company and wrote 18 financial reports in 3 days with it.  They had been waiting on the reports for months, which is what it would have taken had I used SSRS instead of the tool designed for that sort of thing.  

    Independent, Freelance Consultant and Dynamics Development Instructor

    http://www.instructorbrandon.com

    http://www.youtube.com/user/BrandonAhmad

  • Yes for financial reporting Management report servers the purpose of playing around with balances and financial statements. For any MIS reporting it is beautiful tool.

    Coming to the requirement of dimension names

    you can use some thing like this based on primary focus for the trailBalance. Use this either as adisplay method or use this in the trial balance report DP class

    I have just looped through only the dimensions my customer uses. You can loop through all the dimension values using dimensionHierarchy configured in the chart of accounts

    private Description GetDescription(DimensionSortValue _primaryFocus)

    {

       String30 AccountDelimiter = '-';

       Query ledgerDimensionValuesQuery;

       QueryRun   qRun;

       RefRecId   ledgerRecId;

       DimensionAttribute dimAttribute;

       DimensionAttributeValueCombination valueCombination;

       DimensionAttributeLevelValue dimAttributeLevelValue;

       Description description;

       DimAttributeOMCostCenter division;

       DimAttributeOMDepartment department;

       DimAttributeCustTable clients;

       DimAttributeHcmWorker workers;

       DimAttributeRetailStore store;

       DimAttributeRetailTerminal  terminal;

       DimAttributeMainAccount mainAccount;

       QDFDimensionValues      QdfDimensionAttributeCombinationView;

       select firstOnly RecId from valueCombination where valueCombination.DisplayValue == _primaryFocus

           exists join QdfDimensionAttributeCombinationView where valueCombination.RecId == QdfDimensionAttributeCombinationView.DimenionAttributeValueCombination;

       ledgerDimensionValuesQuery = new Query(queryStr(QDFLedgerDimensionValues));

       ledgerDimensionValuesQuery.dataSourceTable(tableNum(DimensionAttributeValueCombination)).addRange(fieldNum(DimensionAttributeValueCombination, RecId)).value(SysQuery::value(valueCombination.RecId));

       ledgerRecId = Ledger::current();

       qRun = new QueryRun(ledgerDimensionValuesQuery);

       while(qRun.next())

       {

           dimAttribute = qRun.get(tableNum(DimensionAttribute));

           dimAttributeLevelValue = qRun.get(tableNum(DimensionAttributeLevelValue));

           switch (dimAttribute.BackingEntityType)

           {

               case tableNum(DimAttributeMainAccount):

                   select firstOnly Name from mainAccount

                       where mainAccount.Value == dimAttributeLevelValue.DisplayValue

                           && mainAccount.Category == Ledger::find(ledgerRecId).ChartOfAccounts;

                   description += mainAccount.Name + AccountDelimiter;

                   break;

               case tableNum(DimAttributeOMCostCenter):

                   select firstOnly Name from division where division.Value == dimAttributeLevelValue.DisplayValue;

                   description += division.Name + AccountDelimiter;

                   break;

               case tableNum(DimAttributeOMDepartment):

                   select firstOnly Name from department where department.Value == dimAttributeLevelValue.DisplayValue;

                   description += department.Name + AccountDelimiter;

                   break;

               case tableNum(CustTable):

                   select firstOnly Name from clients where clients.Value == dimAttributeLevelValue.DisplayValue;

                   description += clients.Name + AccountDelimiter;

                   break;

               case tableNum(HcmWorker):

                   select firstOnly Name from workers where workers.Value == dimAttributeLevelValue.DisplayValue;

                   description += workers.Name + AccountDelimiter;

                   break;

               case tableNum(RetailStoreTable):

                   select firstOnly Name from store where store.Value == dimAttributeLevelValue.DisplayValue;

                   description += store.Name + AccountDelimiter;

                   break;

               case tableNum(RetailTerminalTable):

                   select firstOnly Name from terminal where terminal.Value == dimAttributeLevelValue.DisplayValue;

                   description += terminal.Name + AccountDelimiter;

                   break;

               default:

                   description = '';

                   break;

           }

       }

       return subStr(description, 0, strLen(description) -1);

    }

  • Hi Lally,

    In AX2012 balances are maintained per Dimension Set. The initial release had the possibility to calculate the balances automatically. This could be accomplished by setting per Dimension Set the tick box “Update balances during the posting process”.

    In all versions of AX2012 it is possible to recalculate the balances. This can be done manually or run this job in batch.

    kind regards,

    André Arnaud de Calavon  |  Microsoft Dynamics AX Solution architect  |  My blog  |  My company

    This post is my own opinion and does not necessarily reflect the opinion or view of my company, Microsoft, both its employees, or other MVPs.

  • Hi Brandon

    Can you please post the code to display account name in trial balance.

  • This is for R2 only.  It's roughly the same but slightly more difficult in 2012 RTM.

    On the table LedgerTrialBalanceListPageTmp, add a new field called PrimaryFocusDescription of Extended Data Type DimensionDisplayValue.

    In the constructBalances() static method on that table, make the following changes.

    Find the block of code below --

       trialBalanceDP.parmCreatedTransactionId(createdTransactionId);

       trialBalanceDP.parmDataContract(trialBalanceContract);

       trialBalanceDP.processReport();

    ABOVE that block add the following line.

       trialBalanceDP.parmUserConnection(new UserConnection());

    This causes the data provider to populate the field you need in its output.

    Near the end of that same method you will find an insert_recordset (..) select .. statement.  Add PrimaryFocusDimension to the end of both field lists.

       insert_recordset _tmp (PrimaryFocus, LedgerDimension, OpeningBalance, AmountDebit, AmountCredit, ClosingBalance, EndingBalance, PrimaryFocusDescription)

           select PrimaryFocus, LedgerDimension, OpeningBalance, AmountDebit, AmountCredit, ClosingBalance, EndingBalance, PrimaryFocusDescription

    Finally, on the form LedgerTrialBalanceListPage, add the new field into your grid.

    Presto.