Notifications
Announcements
No record found.
Hello,
Can any one help me to get Main Account opening balance and closing balance.
Thanks
*This post is locked for comments
Hello Nishad,
I think you need to define what you mean by opening and closing balance. Do you refer to the opening balance at the beginning of a specific fiscal year or the balance at the beginning of the period for which you run your report.
As an example:
If you generate a BI-Report for the period from August to September 2015. What would be the beginning balance that you want to see in your Bi-report? The Balance on August 1st or something else?
Ludwig
Hi Ludwig,
The balance on August 1st only
To get the balance for August 1st in the example used you simply have to add up the sum of all prior postings on the specific ledger account you are analyzing.
Do you have the Dynamics AX analysis services database deployed on your system?
Adding up all the posting amounts at the time the report runs would be rather slow If you have the analysis services database available I'd suggest letting analysis services do this sum for you periodically and then you can query the analysis services database to get the account balances you want to show.
It looks like the GeneralLedgerCube in the DynamicsAX analysis services project already has account balances in it. So you'd just need a query to get the balance values up to the date you wish. The following mdx query seems to work for me to get account balances for one company as of one date.
SELECT NON EMPTY { [Measures].[Main account debit amount - accounting currency], [Measures].[Main account credit amount - accounting currency] } ON COLUMNS, NON EMPTY { ([Chart of accounts].[Main account name].[Main account name].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( null : [Transaction date - fiscal calendar].[Year quarter period month date].[Company accounts ID].&[dat].&[dat]&[1/1/2005].&[dat]&[3].&[dat]&[Period 12].&[dat]&[11].&[dat]&[2005-12-31T00:00:00] ) ON COLUMNS FROM [General ledger cube])
A few things would need to be modified in the query to make a report. The company name, [dat] in my example query, would need to be set from a parameter as would the end dates for the end of the date range.
You could also add a range to filter the query to return the balances for a specific account or set of accounts instead of all the accounts.
If working with the analysis services database seems overwhelming and you'd rather stick purely with ax code, analysis services gets the account balances by summing the debit and credit fields of view DimensionFocusBalanceCube. You could sum the data in that view yourself adding appropriate ranges to get only values up to your start and end period dates, but that would be significantly slower than getting the value from analysis services, where it's already summed for you and cached for when you need it.
Hope this helps,
--Lance
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.
As AI tools become more common, we’re introducing a Responsible AI Use…
We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…
These are the community rock stars!
Stay up to date on forum activity by subscribing.
Priya_K 4
Martin Dráb 4 Most Valuable Professional
Ali Zaidi 2
