Getting current account balances in axapta via SQL

Question

I'm trying to figure out how I can get the current balances of my mainaccounts via SQL from the Axapta 2012 tables.

Does anyone know how to do this?

Answer 1

Method getBalance of table MainAccount seems to be a good place to start. Unfortunately, this method uses class LedgerBalanceMainAccountAmounts to calculate the balance. I think it would be very hard if not impossible to recreate the logic of that class in SQL.

One alternative depending on your requirements could be to write a function in x++ that writes the results of the getBalance method into a new table, then you could use SQL to query that new table.

But I suggest you look into out-of-the-box options to analyze main accounts data such as management reporter or BI.

Answer 2

Thanks. But I've figured out a solution.

Heres my thread on a different forum with the solution.

https://community.dynamics.com/ax/f/33/p/268863/762367#762367

Basically what I wanted is the main account ids with the structure defined via -> "traditional finance report" -> "Rowdefinition" -> "Structure designer".

With these account ids I go into the [DIMENSIONATTRIBUTEVALUECOMBINATION] then into [GENERALJOURNALACCOUNTENTRY] and into [GENERALJOURNALENTRY] to get the "transactions" for a specified timeframe.

Then I sum those transactions up to get the accounts balance.

WITH mainaccs(recid, PARENTRECID,[Name]) AS (
  SELECT RecId,
         PARENTRECID,
         [Name]
  FROM   [LEDGERROWDEFLINE]
    where PARENTRECID = 0
  UNION ALL
  SELECT cur.RecId,
         cur.PARENTRECID,
         cur.[Name]
  FROM   [LEDGERROWDEFLINE] cur, mainaccs
  WHERE  cur.PARENTRECID = mainaccs.recid
)
SELECT 
    ma.MAINACCOUNTID,
    Sum(gjae.TRANSACTIONCURRENCYAMOUNT) amount
FROM  mainaccs maccs
    inner join [MAINACCOUNT] ma on ma.MainaccountId = maccs.Name
    inner join [DIMENSIONATTRIBUTEVALUECOMBINATION] davc on ma.RECID = davc.MAINACCOUNT
    inner join [GENERALJOURNALACCOUNTENTRY] gjae on gjae.LEDGERACCOUNT = davc.DISPLAYVALUE
    inner join [GENERALJOURNALENTRY] gje on gje.RecId = gjae.GENERALJOURNALENTRY
where YEAR(gje.ACCOUNTINGDATE) = 2017
group by ma.MAINACCOUNTID
order by ma.MAINACCOUNTID