SQL Normailising GLM_CHART

GLM_CHART in Expert "is Not Normal" ... in terms of SQL Normal Forms that is ... But don't worry about that :D

In other words the parent/child relationships of GL accounts are not in a standard table JOIN format whereby we can join a child to it's parent in the Chart of Accounts. Thus there is no easy way to join accounts in the GL hierarchy to their parents via native SQL (and so on 'up the GL hierarchy tree').

The reason is because GLM_CHART is written for fast data retrieval via SQL. We write to it infrequently, but access it many times ... after all how often do we update the Chart of Accounts vs. how often we retrieve data from it?

For that reason the format in GLM_CHART is not "SQL Normalised", and instead uses POSITION and END_POS; and why reporting tools like SSRS can't make sense of the GLM_CHART 'hierarchy'... Though the Expert tools and reports do know the trick!

However that makes getting GL data in a tree format very difficult when it comes to 3rd party reporting such as SSRS - and why we've had to purchase products like FRx and Infor in order to make sense of things in the past.

But as many experienced Expert users have discovered, there is a way round this ... a way to create a SQL view whereby we can figure out the parent child relationships into a SQL Normal Form:

CREATE view [dbo].[My_flatchart] as
select book, acct_type, acct_uno, inactive, acct_code as child,
(select acct_code from glm_chart a2
where a2.position =
(select max(position) from glm_chart a3 where a1.book=a3.book and a3.position = a1.position
and a3.acct_type = 'H' and a1.acct_unoa3.acct_uno)

and a2.end_pos =
(select min(end_pos) from glm_chart a4 where a1.book=a4.book and a4.position = a1.position
and a4.acct_type = 'H' and a1.acct_unoa4.acct_uno)
and a2.book=a1.book
) as parent,
(select count(*) from glm_chart a5 where a5.position = a1.position and a5.book=a1.book) as leveldown

from glm_chart a1

From here, now the data is normalised, then you've cracked the trick of what FRx and Infor does. You can move on to write great reports in SSRS with multi dimensional drill downs ... And if you'd like to get in touch I've some nice SQL scripts to help you do just that.

FOC of course, but only to the members of the AEUC :)