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

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