Querying the General Ledger with Total Lines

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.

select

A1.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 end

from

Shelby.GLAcct as A1 left join

Shelby.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) = 1

group by

A1.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.

Rollup and Grouping Functions

If you have done many queries at all, you have probably used the GROUP BY clause to generate aggregate values across a subset of rows. For instance, here is a simple query to calculate the total amount given to each Purpose Code for each year of history in the Shelby v.5 database:

select
year(hst.CNDate) as GiftYear,
pur.Purpose as GiftPurpose,
sum(det.Amount) as TotalGiving
from
Shelby.CNHst as hst inner join
Shelby.CNHstDet as det on hst.Counter = det.HstCounter inner join
Shelby.CNPur as pur on det.PurCounter = pur.Counter
group by
year(hst.CNDate),

pur.Purpose
order by
year(hst.CNDate),

pur.Purpose

This query yields one row per year/purpose combination, showing the total receipts for each purpose in each year.

This is fine as far as it goes, but a simple addition can also give us subtotals for each year of all purposes and a grand total of all years and purposes. All you have to do is add the key words WITH ROLLUP at the end of the GROUP BY clause.

select
year(hst.CNDate) as GiftYear,
pur.Purpose as GiftPurpose,
sum(det.Amount) as TotalGiving
from
Shelby.CNHst as hst inner join
Shelby.CNHstDet as det on hst.Counter = det.HstCounter inner join
Shelby.CNPur as pur on det.PurCounter = pur.Counter
group by
year(hst.CNDate),

pur.Purpose with rollup
order by
year(hst.CNDate),

pur.Purpose

With that simple addition, the results would look like this:

The NULL values are rather unfortunate, though. It would probably be better to replace them with a descriptive phrase to show that the row is a total line. T-SQL includes a function called GROUPING() that helps with that. The GROUPING() function returns a 1 whenever the column inside the parentheses returns a NULL because it is part of a ROLLUP function. It returns a 0 if it is not NULL or if it is a NULL for some other reason other than a ROLLUP function. Thus we can use GROUPING to test for ROLLUP nulls and translate them into better values. Here is the example with the simple query we have been using.

select
case grouping(year(hst.CNDate)) when 1 then 'All Years' else cast(year(hst.CNDate) as varchar(4)) end as GiftYear,
case grouping(pur.Purpose) when 1 then 'All Purposes' else pur.Purpose end as GiftPurpose,
sum(det.Amount) as TotalGiving
from
Shelby.CNHst as hst inner join
Shelby.CNHstDet as det on hst.Counter = det.HstCounter inner join
Shelby.CNPur as pur on det.PurCounter = pur.Counter
group by
year(hst.CNDate), pur.Purpose with rollup
order by
year(hst.CNDate), pur.Purpose

And here is a sample of the results:

The final step would be to use GROUPING() in conjunction with the ORDER BY clause in order to move the total and subtotals to the bottom of each section, where most people expect to find them. I will leave that exercise for you.