Share This Post

Dynamics AX Updates

AX General Ledger Cube for AX 2012 R2 and R3

The AX General ledger cube for Microsoft Dynamics AX is used to report on ledger accounts and bank accounts. This article provides details about the cube.

Deployment

The AX General ledger cube is included in the Dynamics AX project.

Configuration keys

The following configuration keys are required to use all features of the Dynamics AX General ledger cube:

  • Bank (Bank)
  • Check (bank cheque)
  • Reporting currency (CurrencySecondaryCurrency)
  • General ledger (LedgerBasic)

Tables and views

The AX General ledger cube uses data from the following tables and views:

  • BankAccountTable table
  • BankAccountTrans table
  • BankTransType table
  • BudgetModel table
  • CustTransOpen table
  • BankChequePaymTransCube view
  • BudgetTransactionCube view
  • CustCollectionLetterJourCube view
  • CustInterestTransCube view
  • DimensionFocusBalanceCube view
  • FreeTextInvoiceCube view
  • GeneralJournalCube view
  • LedgerDerivedFinHierarchyCategory view
  • LedgerDerivedFinHierarchyResults view
  • LedgerTrvExpTransCube view
  • PurchaseOrderCube view
  • SalesOrderCube view
  • VendTransOpenCube view

Measures

The Microsoft Dynamics AX General ledger cube includes the following measure groups.

Open customer transactions

This measure group is based on the CustTransOpen table and involves the following measures.

Measure

Measure field name Aggregation Description Associated dimensions
Accounts receivable open amount – accounting currency CustTransOpen.AmountMST Sum The Accounts receivable open amount, an accounting currency.  Company

Customer

Collection letter fee

Date (last interest  date)

Due date

Date (cash discount  date)

Date (transaction  date)

Exchange rate date

Fiscal period date ( date of a transaction –  fiscal calendar)

Due date – fiscal  calendar

Fiscal period date  (date of cash discount –  fiscal date)

Fiscal period date  (date of last interest –  fiscal calendar)

Bank transactions

AX General ledger

Bank transactions

This measure group is based on the BankAccountTrans table and includes the following measures.

Measure Measure field name Aggregation Description Associated dimensions
Amount of a Bank – bank currency BankAccountTrans.BankTransAmountCur Sum The total bank transaction amount, in bank currency.  Currency

Company

Bank account  description

Bank transaction type

AX General Ledger  derived  financial  attribute  value  combinations

Bank transaction

Date (due date)

Currency (currency –  registration currency)

Date (transaction  date)

Bank statement date

Date  (acknowledgment date)

Date (exchange rate  date)

Fiscal period date  (transaction date –  fiscal calendar)

Bank statement date    – fiscal calendar

Fiscal period date  (acknowledgment date – fiscal calendar)

Date of Fiscal period (  due date – fiscal  calendar)

Bank amount – transaction currency BankAccountTrans.AmountCur Sum The total bank transaction amount, in transaction currency.
Bank amount – accounting currency BankAccountTrans.AmountMST Sum The total bank transaction amount, an accounting currency.

Purchase orders

This measure group is based on the PurchaseOrderCube view and includes the following measures.

Measure Measure field name Aggregation Description Associated dimensions
AX General ledger purchase line amount – accounting currency PurchaseOrderCube.LineAmountMST Sum The AX General ledger purchase line amount, an accounting currency.  Company

Purchase  order

Vendor

Date (due    date)

Date    (exchange    rate date)

Fiscal period  date (due date  – fiscal  calendar)

Expenses

This measure group is based on the LedgerTrvExpTransCube view and includes the following measures.

Measure Measure field name Aggregation Description Associated dimensions
AX General ledger expense amount – accounting currency LedgerTrvExpTransCube.AmountMST Sum The AX General ledger expense amount, an accounting currency.  Company

Expense

Date (due date)

Date  (exchange    rate date)

Fiscal period  date (due date  –  fiscal  calendar)

Interest notes

This measure group is based on the CustInterestTransCube view and includes the following measures.

Measure Measure field name Aggregation Description Associated dimensions
General ledger interest note amount – accounting currency CustInterestTransCube.InterestAmountMST Sum  The AX General ledger interest note amount, an accounting currency.  Company

Interest note

Date (due  date)

Date  (exchange  rate date)

Fiscal period  date (due  date – fiscal  calendar)

Sales orders

This measure group is based on the SalesOrderCube view and includes the following measures.

Measure Measure field name Aggregation Description Associated dimensions
General ledger sales line AX amount – accounting currency SalesOrderCube.LineAmountMST Sum The AX General ledger sales line amount, an accounting currency.  Company

Customer

Ledger sales  order (sales  order)

Due date

Date (exchange  rate date)

Fiscal period  date (due date –  fiscal calendar)

Ledger derived financial hierarchy results

This measure group is based on the LedgerDerivedFinHierarchyResults see and involve the following measures.

Measure Measure field name Aggregation Description Associated dimensions
Ledger derived financial hierarchy results of a count Not applicable Count The number of ledger hierarchy results.  Company

Derived financial category  hierarchy

Ledger derived financial hierarchy  results

Ledger derived financial attribute  value combinations

Exchange rates by day

This measure group is based on the BIExchangeRateView view and includes the following measures.

Measure Measure field name Aggregation Description Associated dimensions
Exchange rate BIExchangeRateView.CrossRate Max The exchange rate.  Currency

Date (exchange rate date)

Analysis of currency

Key performance indicators

The following sections describe the key performance indicators (KPIs) in the AX General ledger cube.

KPI calculations

The KPIs in the AX General ledger cube depends on account categories.

An asterisk (*) indicates the KPI calculations use accumulated amounts. Accumulated amounts, such as some period to date amounts, are not stored in the Microsoft Dynamics AX database. For example, to calculate a period-to-date amount, all the transactions from the starting of the fiscal calendar to the end of the previous period are totaled. Then all the amounts from the starting of the fiscal calendar to the current date are totaled. The difference between these 2 amounts is the period to date amount.

You can use the data in the below table to help verify the information in your KPIs. Export your chart of accounts to Office Excel and verify that the accounts that should be added the KPIs are assigned to the correct ledger account category.

KPI

Associated measure group Calculation
Accounts payable turnover* AX General Ledger transactions  [Cost of the Goods Sold (Reference ID = 35) + (Ending Inventory (Reference ID = 5) for period –  Starting Inventory (Reference ID = 5) for period)] / [Beginning Accounts Payable (Reference ID =  15) + Payable Ending Accounts (Reference ID = 15) / 2]

 Note

* Only Inventory and Accounts Payable parts of this KPI are accumulated.

Accounts receivable turnover* AX General Ledger transactions  [Sales (ID  for Reference = 33) *-1] – Sales Returns and the Discounts (Reference ID = 34) / [Beginning  Accounts Receivable (Reference ID = 4) + Ending Accounts Receivable (Reference ID = 4) / 2]

 Note

* Only Accounts Receivable part of this KPI is accumulated.

Average days to pay* Ledger transactions  Ending Accounts Payable (Reference ID = 15) balance for a period / [Cost of Goods Sold  (Reference ID = 35) + (Ending Inventory (Reference ID = 5) for the period – Beginning Inventory  (Reference ID=5) for the period)] * Number of days in the period

 Note

* Only Accounts Payable and Inventory parts of this KPI are accumulated.

Average collection period Ledger transactions  (Accounts Receivable (Ref ID 4) / ((Sales (ref id = 33) + Sales returns and discounts (ref id 34)*-1) *  number of days in fiscal year to date]
Cash position* Ledger transactions  Cash (ID for Reference = 1) + Cash Equivalents (Reference ID = 2)

 Note

* Accumulation applies to all calculations.

Cash ratio* Ledger transactions  Cash Equivalents (Reference ID = 2) + Cash (Reference ID = 1) / (Accounts Payable (Reference ID  = 15) + Notes Payable (Reference ID = 16) + Current Maturities on Long Term of the Debt (Reference ID =  17) + Taxes Payable (Reference ID = 18) + Interest Payable (ID  for the Reference = 19) + Dividends Payable (Reference ID = 20) + Leases Payable (Current) (Reference ID = 21) + Sinking Fund  Payable (Current) (ID for Reference = 22) + Another Current Liabilities (Reference ID = 23))

 Note

* Accumulation applies to all calculations.

Cost of goods sold Ledger transactions  Cost of Goods Sold (Reference ID = 35)
Current ratio* Ledger transactions  [Cash (ID for Reference = 1) + Cash Equivalents (Reference ID = 2) + Short Term Investments  (Reference ID = 3) + Accounts Receivable (Reference ID = 4) + Inventory (Reference ID = 5) +  Notes Receivables (Reference ID = 6) + Work is in the Process (Reference ID = 7) + Prepaid Expenses  (Reference ID = 8) + Other Current Assets (ID for Reference = 9) + Inventory (Reference ID = 5)] /  [Accounts Payable (Reference ID = 15) + Notes Payable (Reference ID = 16) + Current Maturities  on Long Term Debt (Reference ID = 17) + Payable Taxes (Reference ID = 18) + Interest Payable  (ID for Reference = 19) + Dividends Payable (Reference ID = 20) + Leases Payable (Current)  (ID for Reference = 21) + Sinking Fund Payable (Current) (Reference ID = 22) + Another Current  Liabilities (Reference ID = 23)]

 Note

* Accumulation applies to all calculations.

Debt to equity* AX General Ledger transactions  Accounts Payable (ID for Reference = 15) + Notes Payable (Reference ID = 16) + Current Maturities on  Long Term Debt (Reference ID=17) + Taxes Payable (Reference ID = 18) + Interest Payable  (ID  for Reference = 19) + Dividends Payable (Reference ID = 20) + Leases Payable (Current)  (Reference ID = 21) + Sinking Fund Payable (Current) (Reference ID = 22) + Another Current  Liabilities (Reference ID = 23) + Long Term Debt (Reference ID = 24) / Common Stock (Reference  ID = 25) + Preferred Stock (Reference ID = 26) + Additional Paid in the Capital: Common (Reference  ID= 27) + Additional Paid in Capital: Preferred (Reference ID = 28) + Retained Earnings (Reference  ID = 29) + Treasury Stock (Reference ID = 30) + Common Dividends (Reference ID=31) + Preferred  Dividends (Reference ID=32) + Encumbrance (Reference ID = 54) + Pre-encumbrance (Reference  ID=55)

* Accumulation applies to all calculations.

Debt to total assets* Ledger transactions  [Accounts Payable (Reference ID = 15) + Payable Notes (Reference ID = 16) + Current Maturities  on  Long Term Debt (Reference ID = 17) + Taxes Payable (Reference ID = 18) + Interest Payable  (Reference ID = 19) + Dividends Payable (Reference ID = 20) + Leases Payable (Current)  (Reference ID = 21) + Sinking Fund Payable (Current) (Reference ID = 22) + Another Current  Liabilities (Reference ID = 23) + Long Term Debt (Reference ID = 24)] / [Cash (Reference ID = 1) +  Cash Equivalents (Reference ID = 2) + Short Term of the Investments (Reference ID = 3) + Accounts  Receivable (Reference ID = 4) + Inventory (ID for Reference = 5) + Notes Receivables (Reference ID =  6) + Work is in the Process (Reference ID = 7) + Prepaid Expenses (Reference ID = 8) + Other Current  Assets (Reference ID = 9) + Long Term Investments (ID for the Reference = 10) + Property Plant and  Equipment (Reference ID = 11) + Accumulated Depreciation (Reference ID = 12) + Intangible Assets  (Reference ID = 13) + Other Assets (Reference ID = 14)]

* Accumulation applies to all calculations.

Gross profit Ledger transactions  [Sales (Reference ID = 33)*-1] – Sales Returns and Discounts Reference ID = 34) – Cost of Goods  Sold
Gross profit margin Ledger transactions  [(Sales (Reference ID = 33) *-1] – Sales Returns and Discounts (Reference ID = 34) – Cost of Goods  Sold
Inventory turnover* Ledger transactions  Cost of Goods Sold (Reference ID = 35) / [Beginning Inventory (Reference ID = 5) + Ending  Inventory (Reference = 5) / 2]
Net income Ledger transactions  Result A – Result B

Result A = [Sales (Reference ID = 33) + Other Income (Reference ID = 48) + Revenues not  producing working capital (Reference ID = 50)+ Sales Returns and Discounts (Reference ID = 34)]  *-1

Result B = Cost of the Goods Sold (Reference ID = 35) + Selling Expense (Reference ID = 36) +  Administrative Expense (Reference ID = 37) + Manufacturing Expense (Reference ID = 38) + Travel  and the Entertainment Expenses (Reference ID = 39) + Project Operation Expenses (Reference ID =  40) + Salaries Expense (Reference ID = 41 ) + Other Employee Expenses (ID for Reference = 42) +  Interest Expense (Reference ID = 43) + Tax Expense (Reference ID = 44) + Depreciation Expense  (Reference ID = 45) + Income Tax Expense (Reference ID = 46) + Other Expenses (Reference ID =  47) + Charges not using working capital (Reference ID = 49) +Gain or Loss on Asset Disposal  (Reference ID = 51) + Amortization of Intangible Assets (Reference ID = 52)

Quick ratio* Ledger transactions  [Cash (ID for the Reference =1) + Cash Equivalents (Reference ID = 2) + Short Term Investments  (Reference ID = 3) + Accounts Receivable (Reference ID = 4) + Notes Receivables (Reference ID =  6)] / (Accounts Payable (Reference ID = 15) + Payable Notes (Reference ID = 16) + Current  Maturities on Long Term Debt (Reference ID = 17) + Taxes Payable (Reference ID = 18) + Payable Interest  (Reference ID=19) + Dividends Payable (Reference ID = 20) + Leases Payable (Current)  (Reference ID=21) + Sinking Fund Payable (Current) (Reference ID = 22) + Another Current Liabilities  (Reference ID = 23)

* Accumulation applies to all calculations.

Return on total assets* AX General Ledger transactions  (Result A/Result B) X 100

Result A: Net income + Interest expense (Reference ID =43) + Tax expense (Reference ID = 44) +  Income tax expense (Reference ID = 46)

Result B: Cash (Reference ID = 1) + Cash Equivalents (Reference ID = 2) + Short Term Investments  (Reference ID = 3) + Accounts Receivable (Reference ID = 4) + Inventory (Reference ID = 5) +  Notes Receivables (Reference ID = 6) + Work is in the Process (Reference ID = 7) + Prepaid Expenses  (Reference ID = 8) + Other Current Assets (Reference ID = 9) + Long Term Investments (Reference  ID = 10) + Property Plant and the Equipment (Reference ID = 11) + Accumulated Depreciation  (Reference ID = 12) + Intangible Assets (Reference ID = 13) + Another Assets (Reference ID = 14)

Revenue budget variance Ledger budgets  Sum of the budget amounts for the period selected for the accounts / Sum of actual balances for  revenue accounts for the same time period = Variance. The result is displayed as a percentage. The  following ledger account categories are added in the sum of the budget amounts and in the sum of  the actual amounts: Sales (Reference ID = 33) + Other Income (Reference ID = 48) + Revenues not  producing working capital (Reference ID =50) + Sales Returns and discounts (Reference ID = 34) +    Gain/Loss on Asset Disposal (Reference ID = 51)

 Note

If it is necessary, the amounts for each account category are summed by dimension combination to  compare the budget versus actual amounts by department, cost center, and so on. Reference ID 51  is added only if the balance of the accounts in that account category is negative. Otherwise it is  excluded.

Times interest earned AX General Ledger transactions  Result A/ Interest Expense (Reference ID = 43)

Result A: Net income + Interest expense (Reference ID =43) + Tax expense (Reference ID = 44) +  Income tax expense (Reference ID = 46)

Total expenses AX General Ledger transactions  Cost of Goods Sold (Reference ID = 35) + Selling Expense (Reference ID = 36) + Administrative  Expense (Reference ID = 37) + Manufacturing Expense (Reference ID = 38) + Travel and  the Entertainment Expense (ID for Reference = 39) + Project Operation Expenses (Reference ID = 40) +  Salaries Expense (Reference ID = 41 ) + Another Employee Expense (Reference ID = 42) + Interest  Expense (Reference ID = 43) + Tax Expense (Reference ID = 44) + Depreciation Expense  (Reference ID = 45) + Income Tax Expense (Reference ID = 46) + Other Expenses (Reference ID =  47) + Charges not using working capital (Reference ID =49) + Amortization of Intangible Assets  (Reference ID = 52) + Gain/Loss on the Asset Disposal (Reference ID = 51)
Total revenue AX General Ledger transactions  [Sales (Reference ID = 33) + Other Income (Reference ID = 48) + Revenues not producing working  capital (Reference ID =50) +Sales Returns and Discounts (ID  for Reference = 34) + Gain/Loss on the Asset  Disposal (Reference ID = 51)] *-1
Earnings before income tax AX General Ledger transactions  ( Sales (ref_id = 33) + Other Income (ref id = 48) + Sales Returns and Discounts (ref id = 34)) *-1 ) –  ( COGS (ref id =35) + Selling Expense (ref id = 36) + Admin Expense (ref id = 37) + Manuf Expense  (ref id = 38) + T& E Expense (ref id = 39) + Project and Operation Expense (ref id = 40) + Saleries  Expense (ref id = 41) + Other Employee Expenses (ref id = 42) + Interst Expense (ref id = 43) + Tax  Expense (ref id = 44) + Depreciation Expense (ref id = 45) + Other Expenses (ref id = 47) + Charges  not using working capital (ref ID = 49) ) + Revenues not producing working capital (Reference  ID=50) + Amortization of intangible assets (ref id = 52) + Gain/loss on asset disposal (refid = 51)
Expense budget variance Ledger budgets  Sum budget amounts for the: Cost of Goods Sold +Selling expense  + Admin  exp  + Manu exp + Travel and Enter exp + Project expenses + Salaries exp  + Other emp exp + Interest exp (ref id = 43) + Tax  exp + Depr exp + Income tax exp + Other expenses + Charges not using working capital + Amortization of intangible assets + Gain/loss on asset disposal / Sum of actual balances for expense accounts for  the same time period : Cost of Goods Sold +Selling expense + Admin exp  (refid = 37) + Manu exp + Travel and Enter exp + Project expenses (ref id =  40) + Salaries exp + Other emp exp + Interest exp (ref id = 43) + Tax exp  (ref id = 44) + Depr exp (ref id = 45) + Income tax exp (ref id = 46) + Other expenses +  Charges not using working capital +Amortization of intangible assets + Gain/loss on asset disposal

Security

The AX General ledger cube can be accessed by users assigned to the following Microsoft SQL Server Analysis Services roles.

  • Accountant
  • Account manager
  • Accounting supervisor
  • Accounts payable centralized payments clerk
  • Account payable clerk
  • Accounts payable manager
  • Account payable payments clerk
  • Accounts receivable centralized payments clerk
  • Accounts receivable clerk
  • Account receivable manager
  • Accounts receivable payments clerk
  • Budget clerk
  • Budget manager
  • Chief executive officer
  • Chief financial officer
  • Collections agent
  • Collections manager
  • Compliance manager
  • Financial controller
  • Treasurer

Share This Post

Leave a Reply

avatar
  Subscribe  
Notify of
Skip to toolbar