web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Getting current balanc...
Microsoft Dynamics AX (Archived)

Getting current balances of main accounts

(0) ShareShare
ReportReport
Posted on by Community Member

Hi there,

I've been searching the web for some time now to find a solution, but cant find any.

I need a list of my main accounts with their current balances via a SQL statement.

Does anybody know how to do this?

*This post is locked for comments

I have the same question (0)
  • Ludwig Reinhard Profile Picture
    Ludwig Reinhard Microsoft Employee on at

    Hi Menico,

    For what reason do you need that?

    What is your business requirement?

    Best regards,

    Ludwig

  • Community Member Profile Picture
    Community Member on at

    Hi Ludwig,

    I need to set the businesses turnover into relation with data from another application via QlikView.

    Therefore I need to get the turnover for a specified timeframe from several mainaccounts.

  • Suggested answer
    Ludwig Reinhard Profile Picture
    Ludwig Reinhard Microsoft Employee on at

    I see. Have you tried extracting those data via the standard GL cube?

  • Community Member Profile Picture
    Community Member on at

    Which views are you referring to? I couldn't find any that deliver what I want.

    Currently I'm trying to Join the [MAINACCOUNT] table with the [DIMENSIONATTRIBUTEVALUECOMBINATION] to get the transactions from the [GENERALJOURNALACCOUNTENTRY] table.

  • Community Member Profile Picture
    Community Member on at

    I'm almost done. I can now get the sums of all accounts.

    The last problem to solve is, that I only want some accounts and not all of them. In order to filter the ones I need, I need to have the cost center per main account.

    Do you know how I can figure out which cost center is assigned to a main account via sql. I know where to find it in axapta.

  • Suggested answer
    Ludwig Reinhard Profile Picture
    Ludwig Reinhard Microsoft Employee on at

    Hi Menico,

    Finding the cost center is not that easy because it might be anywhere in the accounting string consisting of your main account and financial dimensions.

    I would recommend that you have a look at the following blow from the MS GFM team that contains several articles on ledger account combinations. blogs.msdn.microsoft.com/.../2

    Best regards,

    Ludwig

  • Verified answer
    Community Member Profile Picture
    Community Member on at

    Thank you for your help. But I've found the answer I was looking for.

    Since I'm new to this company and also fairly new to axapta I guess I didn't really know how to ask this question correctly.

    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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Priya_K Profile Picture

Priya_K 4

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#3
Ali Zaidi Profile Picture

Ali Zaidi 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans