UPDATED AGAIN (7/30/2010): Corrected WHERE clause to fix problem with TotalLine designations.
UPDATED: With a shorter version of the query and added explanation of the new approach.
If you have been waiting on a final solution to my Accounts Payable / General Ledger reconciliation query, I'm afraid you'll have to wait a bit longer. It has come to my attention that the APInv table can actually have multiple rows representing the same original invoice. Ditto for the APInvDetail table. This complicates the problem significantly, and so far I have not yet overcome this challenge in finding a way to correlate the individual detail lines from the GL to the individual detail lines from AP. I promise to post something once I have a solution, whether complete or partial.
Recently, however, I was pulled in a different direction by a customer request to display a fairly simple set of columns from the GL tables on the Shelby v.5 Dashboard. The customer was looking for a basic year-to-date summary of budget information, like a simplified Budgeted Financial Statement report. This request gave me an opportunity to revisit a difficulty with General Ledger queries that I have struggled for years to overcome, only to be forced to rely on VBScript in the Report Designer to work around: the problem of total lines in the chart of accounts.
Total lines are difficult to deal with for three main reasons:
- They are fundamentally different than detail lines, yet still need to display a value like detail lines do.
- They are arbitrary, at least in principle, making it impossible to predict where they occur in any given chart of accounts.
- They are hierarchical, generating a nesting effect that means overlapping group values.
These properties of total lines have defied my query abilities for years. Until I worked out the solution I'm about to share, I used VBScript to calculate the total line values on a row-by-row procedural approach. This was not only inefficient and a bit of a kluge, but it meant that the only way to include total lines was to use the Report Designer; there could be no totals in the basic set of query results.
I took the recent customer request as a fresh start, and I thought through the problem from scratch. The first observation I made was that total lines display aggregate information; therefore, some form of grouping would have to be involved. This immediately led to two conclusions: the GROUP BY clause would be used to calculate the values; and the final query would have to use subqueries for the total lines, in order to preserve the scalar detail lines while including aggregate total lines in the same set of results.
Focusing first on the GROUP BY question, I had to tackle the question: GROUP BY what? In order to be able to group a set of rows, they have to share a common value. This common value would have to be related in some way to the total line level (0 through 9). I took the simplest case to think through: a single total line near the top of the chart of accounts; i.e., no total lines above it and the top of the chart of accounts. In order to calculate the total line, all the detail rows before the total line need to share the same value, and all the rows after the total line need to be a different value. Because I would need to trigger the grouping based on the total line itself, it would also be best for the total line itself to share the same value with the rows above it.
Looking at the aggregate functions available, it seemed that the most likely candidate for giving the value I needed was COUNT(). I could count the number of total lines with an account number less than or equal to the account number of the total line itself. All the lines before the single total line would be 0 (zero) and all the lines after it would be 1 (one). The total line itself would have a COUNT() value of 1, so I would need to subtract 1 from the COUNT() on the total line, so it can share the value of the rows above it.
I wrote a query using COUNT(), but it required a separate subquery for each Total Line rank 0 - 9. This meant having ten subqueries running in the SELECT statement. I later revised my approach to use a non-equi-join between two instances of the GLAcct table. This new query uses SUM() in place of COUNT(). Inside the SUM() function is a CASE statement to add 1 only for Total Line types. This has the same effect of counting the number of total lines. Because I want the total line to share the same value as the detail lines above it, I am also adding one to the SUM() for the detail lines. This works because the non-equi-join returns all the Total Lines from instance 2 that appear at or before each fund/dept/account number in instance 1.
In the GLAcct table, which contains the complete chart of accounts for each company and fiscal year, the total lines are easily distinguished from other types of lines by the LineType column. Total lines have a numbered value, ranging from 0 to 9, representing the total line level. So the SUM() function should only add 1 for the rows with a numbered value. Because a total line of a certain level should ignore any total lines of levels less than its own (a total level 5 should ignore total levels 0 through 4, for example), each total line level must be handled separately, resulting in a separate column of values for each total line level.
At the end of the process, here is the query. It takes the GLAcct table and adds 10 columns to it, one each for total levels 0 through 9. Each column yields a common value for the detail lines that should be summed together for each total line of its level. The counting is reset for each company-and-year chart of accounts.
selectA1.CoNu,A1.BeginDate,A1.FundNu,A1.DeptNu,A1.AcctNu,LineType = A1.LineType,Descr = max(A1.Descr),StmtType = max(A1.StmtType),T0 = sum(case when A2.LineType like '[0-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end,T1 = sum(case when A2.LineType like '[1-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end,T2 = sum(case when A2.LineType like '[2-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end,T3 = sum(case when A2.LineType like '[3-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end,T4 = sum(case when A2.LineType like '[4-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end,T5 = sum(case when A2.LineType like '[5-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end,T6 = sum(case when A2.LineType like '[6-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end,T7 = sum(case when A2.LineType like '[7-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end,T8 = sum(case when A2.LineType like '[8-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 end,T9 = sum(case when A2.LineType like '[9-9]' then 1 else 0 end) + case when A1.LineType = 'D' then 1 else 0 endfromShelby.GLAcct as A1 left joinShelby.GLAcct as A2 on A1.CoNu = A2.CoNu and A1.BeginDate = A2.BeginDate and (A1.FundNu > A2.FundNu or (A1.FundNu = A2.FundNu and A1.DeptNu > A2.DeptNu) or (A1.FundNu = A2.FundNu and A1.DeptNu = A2.DeptNu and A1.AcctNu >= A2.AcctNu)) and isnumeric(A2.LineType) = 1group byA1.CoNu,A1.BeginDate,A1.FundNu,A1.DeptNu,A1.AcctNu,A1.LineType
Once this query has been saved as a view, it can be used in place of GLAcct to provide queries on the GL tables the ability to pull out the total line sums. This is only a building-block for a complete solution. My next post will show how to take this building block and join it to General ledger transaction history and budget information, taking a step closer to a full query solution.