A technical blog about my projects, challenges, and discoveries in the world of data warehousing using SQL Server, Power BI Desktop, DevExpress, and more.

Tuesday, June 29, 2010

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.

Tuesday, June 22, 2010

Finding Your SQL Server Version

On two occasions now I have written somewhat elaborate queries only to have them fail on the client side because they were running under the Microsoft SQL Server Desktop Edition (MSDE) product that originally shipped with Shelby v.5 many years ago. That edition of SQL Server emulated SQL Server 2000, and it lacks some key support features that were released in SQL Server 2005, let alone any further enhancements in even more recent editions.

Here is a SQL script to reveal which version and edition of SQL Server you are running:

select top 1 Version = @@Version, Edition = serverproperty('edition') from NANames

If the version information is not self-evident, compare the version number against the list at http://www.sqlteam.com/article/sql-server-versions in order to determine which version you are running.

After you know your current version, check out the Hardware Requirements page from Shelby v.5 Support. If you are not meeting at least the minimum recommended requirement for SQL Server, seriously consider an upgrade.

By the way, the reference to NANames in the query is only there to "trick" ShelbyQUERY into running the query. Without some kind of FROM clause, some releases of ShelbyQUERY will refuse to run the query, since it is not always recognized as a standard "SELECT" query without one. There is nothing particular about the NANames table; any table could be used in its place, and the same results would appear.

Thursday, June 3, 2010

Relating AP tables to GL tables in Shelby v.5 data

I've been working on a query to correlate General Ledger detail lines to the corresponding Accounts Payable invoice detail lines. This sounds pretty straightforward; however, it is not. The only foreign key relationship available is at the top level: AP invoice header to GL entry header. This means that tying the next level down (detail-to-detail) involves matching up multiple detail values in addition to the header foreign key value. Unfortunately, because the customer I am working on this for has data entry practices that allow for multiple identical detail lines even on the same invoice, I have no truly unique key to work with. If I resolve this, I will post the finished join relationship.

On a related note, however, I have run across an important detail regarding the use of the GLEntriesCounter column, which appears in both the APInv table and the APInvDetail table. I have learned that APInv.GLEntriesCounter is a reference to the GLEntries.HeaderCounter for the rows created when the invoice is posted. APInvDetail.GLEntriesCounter is a reference to the GLEntries.HeaderCounter for the rows created when the invoice is paid. For reporting on accural-based companies, this distinction is critical.

If the invoice has not been paid, the APInvDetail.GLEntriesCounter value will be zero (0). If a company is on a cash basis, I would assume that the APInv.GLEntriesCounter value would be zero, though I have not tested this assumption. (If someone who is on a cash AP basis would check this out and post a confirmation or correction, I'll edit this post to give the definitive answer.)

Followers