Main account by accounting structure

(0) Share

Report

Posted on by Community Member

Hi I want to make a SQL Query to get accounts by structures, the main goal is to validate the structures configuration in order to make sure that account are associated to the right structure.

I tried with different queries but I am not sure if I am using the right tables.

The output should be:

Main account, structure

101010101, Structure A

101010102,null

SQL:

select *
from MAINACCOUNT MA
join DIMENSIONATTRIBUTEVALUECOMBINATION DAVC
on MA.RECID = DAVC.MAINACCOUNT
join DIMENSIONHIERARCHY DH
on DH.RECID = DAVC.ACCOUNTSTRUCTURE

Could someone help me identify how I should do this query, please.

thank you.

*This post is locked for comments

I have the same question (0)

All responses (11)

Answers (0)

Suggested answer

BrandonSA 1,673 on at

Like (0)

Report

RE: Main account by accounting structure

Hi

Will this help?

krishhdax.blogspot.com/.../ax2012-get-dimension-values-for-main.html

https://community.dynamics.com/ax/f/33/t/222711

Was this reply helpful? Yes No
Ludwig Reinhard Microsoft Employee on at

Like (0)

Report

RE: Main account by accounting structure

Hello Imingora,

How many account structures do you have that such a query is required?

Best regards,

Ludwig

Was this reply helpful? Yes No
André Arnaud de Cal... 299,576 Super User 2025 Season 2 on at

Like (0)

Report

RE: Main account by accounting structure

Hi Imingora,

What is the current SQL query providing you and what would you expect from the community now? There are probably some views in the AOT which might have some queries in this area already.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Main account by accounting structure

Hi, thanks for this information, unfortunately in my company I do not have anyone with knowledge in X++, but I will try to understand the code.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Main account by accounting structure

Hi, I have around 2000 account distributed in 30 structures.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Main account by accounting structure

This is my current query but checking the data there are missing account so my assumption is probably there is a missing table or a join in the query.

select *

from MAINACCOUNT MA

join DIMENSIONATTRIBUTEVALUECOMBINATION DAVC

on MA.RECID = DAVC.MAINACCOUNT

join DIMENSIONHIERARCHY DH

on DH.RECID = DAVC.ACCOUNTSTRUCTURE

Was this reply helpful? Yes No
Suggested answer

Ludwig Reinhard Microsoft Employee on at

Like (0)

Report

RE: Main account by accounting structure

Hello Imingora,

I have never seen a live system that used 30 structures for 2000 accounts.

Rather than trying to create a query that lists you things (accounts & findims), I would suggest that you talk with your accounting colleagues why this setup has been made this way and whether it should remain this way.

Best regards,

Ludwig

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Main account by accounting structure

Thank, I talked with accounting area and for this company this is the configuration that they need, in this momento I need to crear a SQL query to list the mainaccont and structure in order to validate if the configuration is correct and to maitain it for the future, bucause there are a lot of strructures and account. I hope someone could help me reviewing the query posted by me in this post, I think there i a missing table in the query.

Was this reply helpful? Yes No
Suggested answer

André Arnaud de Cal... 299,576 Super User 2025 Season 2 on at

Like (0)

Report

RE: Main account by accounting structure

Hi Imingora,

You can try to use outer joins. Currently, only left and right included records are part of the result.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Main account by accounting structure

Thanks, I tried that, but the SQL is showing an account associated am incorrect structure, so I think I am not joining the right tables, probably I need to uso another table to cross the data .

Was this reply helpful? Yes No