Question Status

Suggested Answer
No Man asked a question on 15 May 2013 12:02 PM

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.

Reply
Suggested Answer
André Arnaud de Calavon responded on 15 May 2013 2:53 PM

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.

Reply
Suggested Answer
Brandon Wiese responded on 25 May 2013 12:38 PM

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.

Reply
No Man responded on 28 May 2013 12:51 PM

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

Reply
André Arnaud de Calavon responded on 28 May 2013 1:22 PM

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.

Reply
No Man responded on 28 May 2013 1:28 PM

Hi,

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

Reply
André Arnaud de Calavon responded on 28 May 2013 1:50 PM

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.

Reply
Brandon Wiese responded on 28 May 2013 3:12 PM

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?

Reply
SKYLARK responded on 18 Jul 2013 10:50 AM

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.

Reply
lally responded on 30 Aug 2013 4:38 AM

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 ?

Reply
lally responded on 31 Aug 2013 8:51 PM

Hi Andre ,

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

Reply
Brandon Ahmad responded on 1 Sep 2013 8:14 AM

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

Reply
venkatesh vadlamani responded on 2 Sep 2013 12:41 AM

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);

}

Reply
André Arnaud de Calavon responded on 2 Sep 2013 12:51 AM

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.

Reply
Mahesh Bind responded on 28 Oct 2013 10:14 PM

Hi Brandon

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

Reply
Suggested Answer
Brandon Wiese responded on 29 Oct 2013 4:25 PM

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.

Reply
Suggested Answer
André Arnaud de Calavon responded on 15 May 2013 2:53 PM

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.

Reply
Suggested Answer
Brandon Wiese responded on 25 May 2013 12:38 PM

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.

Reply
Suggested Answer
Brandon Wiese responded on 29 Oct 2013 4:25 PM

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.

Reply