RSS

Financial Dimensions – Deep Dive Part-1

23 Mar

Dimensions are ways to tag each financial transaction so that you later can track and group them in a meaningful way. In this article we will dig deeper into what it is and how we can use them.

1. Business perspective

In the end every financial activity becomes a voucher transaction: every sales, production,purchase and invoice.

Voucher

From a legal perspective we keep track of all voucher transactions with main accounts (listed in the chart of accounts). But this is not enough, dimensions are needed to tag every transaction, 

In AX 2012, Main Account is one dimension and we can have unlimited dimensions. Common ones are Business unit and Department, but you can choose Customer or your own implemented dimension.

The dimensions used for each legal entity can be different and can be configured in Ledger setup (General ledger > Setup > Ledger) and Account Structures (General ledger > Setup > Chart of accounts > Configure account structures).

2. Technical terms

  • Dimension Attribute is the type of information that is traced. In the image above the attributes are MainAccount, BusinessUnit, Department and ServiceLine.
  • Dimension Attribute Value is the value of the attribute. In the image above the values are 140200, 004, 023 and Application Development
Ledger dimension vs. default dimension

There are many ways to store dimension attribute values. The most common ways are ledger dimensions and default dimensions. This is used for instance in General journal (General ledger > Journals > General journal). The table below will explain it more detailed. Because the label, field names and EDT are not consistent (for its reasons), it is important that you get this right from the beginning, before we continue to elaborate all related concepts.

 

Terms

Ledger dimension Default dimension

    

Label in forms Account or Ledger Account Financial Dimension
Field name LedgerDimension DefaultDimension
EDT DimensionDynamicAccount, LedgerDimensionAccount DimensionDefault
Referenced table DimensionAttributeValueCombination DimensionAttributeValueSet
Comments Ledger dimension is an ordered list of dimension attribute values. Default dimensions unordered set of dimension attribute values. In the scenario of general journal lines, default dimensions are inherited from customers, vendors, journals and so on, all the way to the journal lines, but only if the account type is not Ledger. If the account type is ledger then only ledger dimension is enough.

 

Both ledger account and financial dimensions are stored as RecIds referencing to the tables listed above (Referenced table). The string in ledger dimension is a form control that gets the value from DimensionAttributeValueCombination.DisplayValue.

It is important to understand how the dimensions are used and what the differences are. In the final posted voucher only ledger dimension is used. But in the journal lines, default dimensions might be used. If the account type is Ledger then only ledger dimension will be used. But in all other cases Customer, Vendor, Fixed assets, Project and Bank – the default dimension will be used. The default dimensions will be combined with the main account into a ledger dimension. The strength with this is that the default dimensions can be set in the customer level and journal level and inherited to the journal lines.

Important patterns used in ledger dimension:

  • Default Account – Ledger Dimension, but with only one dimension attribute value, main account.
  • Dynamic Account – Similar to Ledger Dimension but it handles the dynamic usage as in journals. When a journal type is Ledger then it stores Ledger Dimension. But when the ledger type is for instance customer, then the dynamic account will have a lookup for customer accounts instead.

Lesser used terms:

  • Dimension Attribute Set – An unordered set for Dimension Attributes.
  • Dimension Set – Similar to Ledger Dimension but without requirement of Main Account. Mainly used for reports.

3. Data Model

There are a lot of tables that involves dimension. In this section we will focus on a few important ones and how we can use these.

Get display value from LedgerJournalTrans

In the table LedgerJournalTrans, the ledger dimension is stored as a RecId reference. To get the display value you need to get it from DimensionAttributeValueCombination.

DimensionAttributeValue Data model

 

static DimensionDisplayValue getDisplayValue(LedgerJournalTrans _transaction) { DimensionAttributeValueCombination combination; ; select DisplayValue from combination where combination.recId == _transaction.LedgerDimension; return combination.DisplayValue; }

Get specific attribute from a combination

This can be useful for instance in a reporting situation where you need to extract the BusinessUnit. In the example below we assume that you have used the previous code to get the DimensionAttributeValueCombination already.

DimensionAttributeValue Data model

static DimensionDisplayValue getDisplayValue(bankAccountTrans _bankAccountTrans) { GeneralJournalAccountEntry generalJournalAccountEntry; GeneralJournalEntry generalJournalEntry; DimensionAttributeValueCombination dimensionAttributeValueCombination; DimensionAttributeLevelValueView dimensionAttributeLevelValueView; MainAccount mainAccount; SubledgerVoucherGeneralJournalEntry subledgerVoucherGeneralJournalEntry; FiscalCalendarPeriod fiscalCalendarPeriod; ; select firstfast * from generalJournalEntry order by generalJournalEntry.AccountingDate ASC, generalJournalEntry.JournalNumber ASC, generalJournalEntry.SubledgerVoucher ASC join LedgerDimension from generalJournalAccountEntry where generalJournalAccountEntry.GeneralJournalEntry == generalJournalEntry.RecId join RecId,MainAccount from dimensionAttributeValueCombination where dimensionAttributeValueCombination.RecId == generalJournalAccountEntry.LedgerDimension join RecId, MainAccountId from mainAccount where mainAccount.RecId == dimensionAttributeValueCombination.MainAccount outer join RecId FROM fiscalCalendarPeriod where generalJournalEntry.FiscalCalendarPeriod == fiscalCalendarPeriod.RecId exists join * FROM SubledgerVoucherGeneralJournalEntry where GeneralJournalEntry.RecId == subledgerVoucherGeneralJournalEntry.GeneralJournalEntry && _bankAccountTrans.Voucher==subledgerVoucherGeneralJournalEntry.Voucher && _bankAccountTrans.TransDate==subledgerVoucherGeneralJournalEntry.AccountingDate; select DisplayValue from dimensionAttributeLevelValueView where generalJournalAccountEntry.LedgerDimension == dimensionAttributeLevelValueView.ValueCombinationRecId && dimensionAttributeLevelValueView.DimensionAttribute == DimensionAttribute::findByName("BusinessUnits").RecId; return dimensionAttributeLevelValueView.DisplayValue; }

Get main account from a combination

 

DimensionAttributeValue Data model

static AccountName getMainAccountFromCombination( DimensionAttributeValueCombination _combination) { MainAccount mainAccount; ; select mainAccount where mainAccount.RecId == _combination.MainAccount; return mainAccount.MainAccountId; }

Get specific attribute from customer

DimensionAttributeValue Data model

static DimensionDisplayValue getAttributeValueFromCustomer( CustTable _custTable, Name _attribute = 'BusinessUnit') { DimensionAttribute attribute = DimensionAttribute::findByName(_attribute); DimensionAttributeValueSetItemView valueSetItemView; ; select DisplayValue from valueSetItemView where valueSetItemView.DimensionAttributeValueSet == _custTable.DefaultDimension && valueSetItemView.DimensionAttribute == attribute.RecId; return valueSetItemView.DisplayValue; }

Advertisements
 
Comments Off on Financial Dimensions – Deep Dive Part-1

Posted by on March 23, 2015 in Financial Dimensions, Microsoft Dynamics AX, X++

 

Tags: , , , , ,

Comments are closed.

 
%d bloggers like this: