Post#23: GL – Advanced Rules via SQL Query

Tracking all the Accounting Structures and advanced rules created across all entities in AX is a daunting task especially if you have a lot of entities. A lot of users try to do this manually but there is a better way to document and verify the same via SQL.

The following tables hold the required info you need, understanding how they work is key:

  • DimensionHierarchy: Different Structure and Dimensions sets are stored here (e.g Account Structure, Advanced Rules, Journal Control, Dimension sets, built in dimensions all differentiated based on structure type field). MSDN link
  • DimensionHierarchyLevel: Defines no. of levels of the dimension hierarchy. MSDN link
  • DimensionAttribute: Name of the dimension attribute (e.g dimensions + ledger). MSDN link
  • DimensionRuleAppliedHierarchy: Maps Rules to the Hierarchy. MSDN link
  • Dimension Rule: Stores the Advanced Rules. MSDN link
  • DimensionRuleCriteria: Stores the filters on the Advanced Rule Structures. MSDN link
  • DimensionConstraintNode: Defines constraints on the node eg. Allow blanks. MSDN link

I have mapped the same visually to understand better:
post23_img1

Based on this I proceeded with the SQL creation, kindly see the below. Keep in mind that since the DimensionHierarchy contains details for each type it is easier to use separate queries by type rather than one query for all. The below query is for Advanced rules but has a matrix to view the associated accounting structure as well as the advanced rule structure.

--Advanced rules structure by Ashwin Padmanabhan
select Structure_Type=
case
when dh.STRUCTURETYPE=0 then 'Account Structure'
when dh.STRUCTURETYPE=1 then 'Advanced rule Structure'
when dh.STRUCTURETYPE=2 then 'Journal Control Structure'
when dh.STRUCTURETYPE=6 then 'Dimension Set'
when dh.STRUCTURETYPE=7 then 'Customer'
when dh.STRUCTURETYPE=8 then 'Vendor'
when dh.STRUCTURETYPE=9 then 'Project'
when dh.STRUCTURETYPE=10 then 'Fixed Asset'
when dh.STRUCTURETYPE=11 then 'Bank Account'
when dh.STRUCTURETYPE=12 then 'Employee'
when dh.STRUCTURETYPE=13 then 'Item'
when dh.STRUCTURETYPE=14 then 'Single Dimension attribute Rule Structure'
when dh.STRUCTURETYPE=16 then 'Default Account'
when dh.STRUCTURETYPE=16 then 'All dimension attribute '
end,
Accounting_Structure=dh2.name,
Advanced_Rule=dr.NAME,
Advanced_Rule_Structure_ID=dh.name,
Advanced_Rule_Structure_Name=dh.DESCRIPTION,
dh.ISDRAFT,
dhl.LEVEL_,
da.NAME,
Filter_on=isnull(da2.NAME,''),
Filter_on_range_from=isnull(drc.rangefrom,''),
Filter_on_range_to=isnull(drc.RANGETO,'')
,dcn.ISOPTIONAL
,dcnc.ORDINAL
,dcnc.RANGEFROM
,dcnc.RANGETO
from DIMENSIONHIERARCHY dh with(nolock) --store advanced rule structures info
left outer join DIMENSIONHIERARCHYLEVEL dhl with(nolock) on dhl.DIMENSIONHIERARCHY=dh.RECID
left outer join DIMENSIONATTRIBUTE da with(nolock) on da.RECID=dhl.DIMENSIONATTRIBUTE
left outer join DIMENSIONRULEAPPLIEDHIERARCHY drah with(nolock) on drah.DIMENSIONHIERARCHY= dh.RECID
left outer join dimensionrule dr with(nolock) on dr.RECID= drah.DIMENSIONRULE
left outer join DIMENSIONHIERARCHY dh2 with(nolock) on dh2.RECID=dr.ACCOUNTSTRUCTURE
left outer join dimensionrulecriteria drc with(nolock) on drc.DIMENSIONRULE=dr.RECID
left outer join DIMENSIONATTRIBUTE da2 with(nolock) on da2.RECID=drc.DIMENSIONATTRIBUTE
left outer join DIMENSIONCONSTRAINTNODE dcn with(nolock) on dcn.DIMENSIONHIERARCHYLEVEL=dhl.RECID
left outer join DIMENSIONCONSTRAINTNODECRITERIA dcnc  with(nolock) on dcnc.DIMENSIONCONSTRAINTNODE=dcn.RECID
Where dh.STRUCTURETYPE=1 --only advanced rules
order by dh2.name,dr.NAME,dh.name,dhl.LEVEL_

Please see the below sql results and how it links to the AX screens:

post23_img2

post23_img3

post23_img4

I hope this helps you. If it does please like or comment.

Thank for for taking the time to read this and all the very best.

Happy daxing ppl.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s