Back to The Source

Understanding Your Chartfield Design

December 23, 2015 | Josh Dickson

Clients often question their chartfield design. It is a big question comprised of many smaller questions. Some of those questions are best answered with questions. Here is one such answer in the form of a question.

Can you derive a balance sheet and income statement using only your ACCOUNT chartfield? Not every company can. But it pays dividends to have an ACCOUNT list which can produce key financials unsupported by other chartfields.

It can be easy to wind up with a chartfield design which, for instance, requires the combination of DEPTID and ACCOUNT to distinguish between below-the-line expenses and Cost of sales. After all, one of the tenets of data normalization is that a field should have one and only one meaning. With this rule in mind, an accountant designing the chart of accounts (COA) might opt for one single salary expense account. In the same mindset, they might designate the DEPTID to distinguish between cost centers, and organize the cost centers so that a range of them constitute cost of sales. From a general data design standpoint, they’ve done well. From a COA design standpoint, they are in for some challenges.

The following assumes financial statements are nVision reports, although the same challenges would exist with virtually any reporting tool that relies on a tree-like hierarchy of data elements. Consider the following IFRS line items from a Statement of comprehensive income:

  • Revenue
  • Cost of sales
  • Gross profit
  • Other income
  • Distribution costs
  • Administrative expenses
  • Other expenses
  • Finance costs
  • Income tax expense

Even with controls in place such as tree auditing and requiring all values in trees, a check-figure is advisable. Without a check figure, how would one know whether every P&L account is included in the report? So, the nVision layout might include a single nPloded line at the bottom containing every P&L account. Because this figure should agree with the total comprehensive income. But there’s a problem. The Cost of sales line is defined both by ACCOUNT and DEPTID. So much for the check figure. So much for knowing easily whether the report is accurate.

Here is another challenge one faces in this scenario, albeit one specific to nVision. If the tree performance options use selector tables, the combination of chartfields sometimes result in a Cartesian product. Then what should be a thirty second report crawls for hours before finishing. One might avoid the Cartesian product by changing the performance options to static values. This can work. However, static values introduce the risk of creating a SQL statement too large to parse by the database management system. Neither predicament is good.

A robust ACCOUNT list avoids all these problems. How robust? Enough to define every line on your financial statements. One might ask whether this defies good data normalization: whether the ACCOUNT then carries the burden of defining multiple things. From one general perspective, it does define both a category of expense (say, salaries) and something else (whether this is above or below the line e.g. Cost of sales). But within the context of financial reporting, it does not define multiple things. The ACCOUNT defines one thing. It defines the line items on your financial statements.

This is not to say it is a mistake to designate DEPTID values as being Cost of sales. On the contrary, it is rational to do so. The point is, DEPTID does not exist to provide key financial statements. Neither do the other chartfields. Rather, they exist do enable analysis of your data in other dimensions, which is great discussion for another day.

Let’s Chat


The Importance of PUM Strategy Planning

Oracle has transformed the traditional PeopleSoft application life cycle management with the introduction of PeopleSoft Selective Adoption.  The traditional planned upgrade and downtime now take place throughout the year as a normal part of updating and maintaining the system.  The idea behind the change is to provide customers with smaller … continue

Keeping Your System Fit – Data Archiving

I was able to pull up the page in seconds in January.  How come it takes minutes in October? The batch processing window used to be much shorter before; now we are not able to meet the SLA requirements. What’s happening? Remember the help desk calls regarding PeopleSoft application performance issues and DBAs, PeopleSoft Administrators and … continue

Part of the Bigger Story

Find your solution