RSS

Category Archives: Financial Dimensions

Import Opening Balance Through AX 2012 Data import/Export Framework

Yesterday I came across the following issue  and thought I’d share my findings and solution.

Customer asked me to import opening balances from their existing legacy system.Data Migration is the key point for any implementation and Data Import/Export tool is most powerful tool with in Dynamics AX 2012 for data migration.

There are so many blogs written corresponding to this topic”How to import Opening balance through DIXF”.But everyone explained the feature of Dynamics AX Data Import/Export Framework and gave example importing of financial dimensions into a ledger journal for ledger accounts only But my customer requirement is different he wants to import financial dimensions into a ledger journal for customer,Vendor,Project account as well.

Unfortunately, I could not find any article which helps me to import financial dimensions into a ledger journal for Project,Bank & customer/vendor accounts.

Here I would not explain from the scratch how to import opening balance by using Dynamics AX 2012 data import/export framework.

You can refer Hai Nguyen’s Blog, How to start with Opening balances through DIXF

Importing a General Journal using Data Import/Export Framework AX 2012

I have been trying to import data from an CSV file using Data Import Export Framework and generated a sample file.

DIXF1.png

To demonstrate this,I am using AX 2012 R3 CU8 INMF Company.I am taking Bank Account “IND OPER”and Default dimension 024-003 where 024 is Deparment and 003 is Bussines unit.On other side Offset Account is ledger account 110125-024-005.Here 110125 is Main Account,024 Department and 005 is Business unit because my account structure is configured like below

Dixf2.png

Important note:If you want to import financial default dimension corresponding to then you have to choose extra field Default dimension for Account and for offset account choose field OffsetDefaultDimension while generating sample file(screenshot above).

So here question can arise why i am taking DefaultDimension field  for bank only and why not for ledger account.Because framework is capable to import financial dimensions without any issue for ledgers.

So lets take forward with the issue and solution,Even though the mapping  and staging data is validated successfully but when I try to import the data into target table – I get the below error:

Results. Record (‘INMF-00392-VN00000002-1.00’) . Unable to return DimensionAttributeValue record for 024
Data written to target ‘Opening balance’ (‘0’ records created, ‘0’ records updated)

When I tried to debug GenerateDefaultDimension methods in DMFLedgerBalanceEntityClass then I found cause.

As I mentioned above chart of account structure is with sequence Main Account-Department-Business Unit So Ledger accounts(offset account) should be according to account structure only.

Dixf3.png

But why its not working for Bank,even we have used same sequence for Default Dimension as well?? Big question ??

After debugging multiple time i got to know the reason that system not able to generate default dimension rec id then   I found DIXF framework is taking in alphabatical order of Source Data format Dimension code which is different from account structure. 🙂

Dixf4.png

 

So I changed the DefaultDimension value with Business Unit-Department(003-024) instead of Department-Business Unit(024-003).

Dixf5.pngDixf5.png

Now system won’t give you any error this time and opening balance is imported successfully. 🙂

Dixf6.png

Happy Daxing. 🙂

Advertisements
 

How to delete unused Financial Dimension

yesterday I came across the following issue  and thought I’d share my findings and solution.

I’ve setup a chart of accounts and shared it between 3 companies.(one of them a test company).i have mapped one ledger account (Main Account) with bank accounts of all three companies.I have attached a “Test” account structure.and made a couple of postings in the test company.

when I want to modify a main account or delete a bank account that was not used (but defined with a main account).The message is the bank account cannot be deleted because the financial dimension related to the account has transactions.

A financial dimension value is based on the <dimension value> record and has been used on a transaction. You cannot delete the <dimension value> record.

error

We knew these dimension values weren’t used on any posted or un-posted transactions, so the expectation was that we could simply delete these without any issues. So you can imagine the surprise when we tried to delete the dimension values(Ledger,Bank,customer,vendor etc.)

The error means that there are some existing reference records.I can explain the reason for the message and why you are not able to delete the main account, but unfortunately cannot give you a good solution for how to fix it.

When you add new records to financial dimension entities (Main account, Department, Cost center, etc.), they are merely available for use and can be immediately deleted.  However, as soon as they are actually used, additional records are created in tables such as DimensionAttributeValue, DimensionAttributeValueCombination, DimensionAttributeLabelValue, and many others part of the dimension framework.

These tables are used in storing data for a Ledger dimension  Dynamics AX 2012. There will be multiple entries in these tables for a single ledger dimension.AX provides no mechanism for deleting these records.The solution will be to make a utility that deletes all the record in those tables that don’t have any reference in any other tables.

To find out which DimensionAttributeLevelValue record corresponds to the main account, you need to use a few more tables.

LedgerChartOfAccountStructure –> Links COA to account structure

DimensionHierarchy –> AKA Account structure table.  Also links to LedgerChartOfAccountsStructure, so you match the MainAccount by COA all the way to the account structure.

DimensionAttributeValueGroup –> Stores groups of values for the dimension set, which is linked to both DimensionAttributeLevelValue and DimensionHierarchy.

So you can start from DimensionAttributeLevelValue and drill back (or join) in this way…

DimensionAttributeLevelValue.DimensionAttributeValueGroup = DimensionAttributeValueGroup.Recid

DimensionAttributeValeuGroup.DimensionHierarchy = DimensionHierarchy.Recid

DimensionHierarchy.Recid = LedgerChartOfAccountsStructure.DimensionHierarchy

LedgerChartOfAccountsStructure.ChartOfAccounts = MainAccount.LedgerChartOfAccounts

MainAccount.MainAccountId = ‘Your account number’

Simple Solution

1) Error occurring as there are record exist in the DimesionAttributeLevelValue table for the customer

2) Go to the DimensionAttributeLevelValue table –> filter by Bank,Vendor,Customer number in the DisplayValue field –> you can see the records exist in the table for the customer

3) Delete the record from the table

4) After this try deleting the Bank,Vendor,Customer.

(It will allow you to delete the Bank,Vendor,Customer record)\

Happy Daxing.  🙂

 

Tags: , , , , , ,

Financial Dimensions – Deep Dive Part-1

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

 
Comments Off on Financial Dimensions – Deep Dive Part-1

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

 

Tags: , , , , ,