Email a collegue.| Headline: | Getting better reports out of your accounts package |
| Description: | Inadequate reports have been a source of complaints ever since accounting packages were invented. Now at last a solution may be on the horizon.
David Carter discusses the issues and finds that all roads lead to Microsoft. Management reporting tools have improved greatly over recent years, and with Windows it is much easier to gain access to data. ODBC enables you to create reports that are directly linked to the company database. An IT literate accountant can use Crystal Reports or Excel pivot tables to create extremely sophisticated reports. Yet why does poor reporting remain such an enduring issue? Reporting tools are not the problem It is commonly assumed that to create better reports you need better reporting tools. But this is to misunderstand the nature of the problem. As anyone who has to produce reports will tell you, actually designing and creating a report is relatively simple. The hard bit is pulling all the data together in the first place. In practice, problems with reporting usually derive from the way the source data is organised. Where is this source data? For most organisations the data they need for reports lies in their transaction processing systems. So data for sales reports will derive from the systems that process the company’s sales orders and invoices. Data for accounting reports will derive from the system that processes purchase invoices and payments - usually an accounts package such as Oracle, Sun, Sage and so on. The need for an integrated package If you are going to produce a report, one obvious prerequisite is that, before you write it, you have first to collect all the data together in one place. For many large organisations this is a major problem since different transactions may be processed by different systems within the group. The accounts might be run on one package, the sales orders on another, and stock control on a third. With substantial systems running the different operations, the IT department has to spend a lot of time simply getting these systems to talk to each other. However, this really only affects big organisations. Small or medium-size companies can implement a single integrated accounts package (commonly called enterprise resource planning or ERP) to handle all their business transactions. Most mid-range accounts packages now incorporate modules to handle all major areas of the business. Data is spread around different tables While collecting all the data together within a single integrated package is essential for good reporting, it is not in itself sufficient. The data may reside within the one system, but is spread about in different data files or “tables�. The fundamental task of these packages is transaction processing. In order to process transactions with sub-second reponse times and minimal errors, data in transaction processing systems is “normalised� - split up into multiple small files or tables which are organised in a hierarchy. Unfortunately, this technique is unsuitable for reporting, which is easiest when the data is held in one large “flat� file. To illustrate the problem, suppose you wish to produce a report analysing Marketing Expenses, which is a Nominal Ledger account in your trial balance. The source data for the report will be all the transactions which have been analysed to the Marketing Expenses account. These will be held in the nominal transaction table. When the manager looks at the list of marketing expenses in a report, s/he will also want to know who they were bought from. But the name of the supplier will probably be held in another table - the one that stores purchase ledger transactions. To create an acceptable report the designer has to be able to pull in data from both these tables. To make the data in different tables available for reporting, the package vendor needs to offer a data dictionary which provides “joins� between the different tables. However, the AccountingWEB Lab Tests we have found that some well known packages don’t have data dictionaries. Others join some tables but not others, so essential information cannot be accessed. These packages are going to be poor at reporting, whatever reporting tools you use. After all, what’s the point of having a marketing expenses report that can’t give the name of the supplier? With such packages, writing reports is a lengthy and expensive business. Usually the developer has to write a special program in Microsoft Access simply to collect together the data. Raw data tables are still not sufficient Most accounts packages, however, do provide a data dictionary and make it possible to collect all the data together in one place. This means that the report can actually be produced. Problems, however, still remain. The package is designed for transaction processing, and the data is held in a format that is suitable for transaction processing rather than reporting. For example, the package may hold debit amounts in one field and credit amounts in another. But to create a report all amounts need to be combined into a single field. Or the package may hold both sales invoices and credit notes as positive values because that is the way they are keyed in by the operator. But to add up correctly in a report the credits need to be expressed as minuses. With nominal reporting in particular, data will derive from several transaction types such as purchase invoices, sales invoices, cash payments, nominal journals. Each transaction type usually has its own table, but the layouts may be different and it is difficult to combine them into one. Or the different transaction type tables may each hold transactions in the same layout, but some of those transactions need to be excluded. For example, transactions which have been deleted will need to be retained on the database for the audit trail. But if they are not filtered out of the report, the report totals will be wrong. The need for an “analysis-ready file� The difficulty, then, is that these data files were originally designed to optimise transaction processing. Reporting considerations were secondary, if reporting was considered at all. Someone who knows the package well will be able to pick his way through the data in order to produce a report, but these raw data tables are full of pitfalls for the unwary and inexperienced. Everything would be so much easier if vendors took it upon themselves to provide an “analysis-ready file� with their package. By this I mean a large flat file which pulls together from all the various tables the data needed for reports. This data would then be re-formatted in such a way as to make it instantly useable in a report (such as a single Amount field, credit amounts shown as negatives, and so on). In practice, only two analysis-ready files are required in order to produce the great majority of reports an organisation needs. A Nominal Transaction file is required for analysing costs in the Nominal Ledger, and a Sales Invoice item file for analysing sales by product. Each comprises about 40 fields apiece, maybe 15% of the data fields in an average mid-range package. Developments from Microsoft Transaction processing and reporting operate in different worlds. Some of the package developers I’ve met concentrate entirely on the first and ignore reporting completely. To be fair, there is some logic in this since, before you even think of reporting, the package has to be able to “do� all the transactions effectively in the first place. But if reporting is left till later, by the time problems in reporting become apparent, the data structures have been laid down and it’s too late to correct them. It appears, however, that Microsoft is pulling these worlds together around its SQL Server database. SQL Server is proving to be an attractive platform for developers of transaction processing systems because it is cheap enough to run a three- or four-user system, but can scale up to several hundred users. So the one version of the software can appeal to a wide marketplace. Within SQL Server it is a simple matter to create Views of your data. You can pull in data from multiple tables and amend it as necessary, and the resultant file can serve as the basis for a report. Thus an analysis-ready file can be created very quickly. Microsoft provides additional modules with SQL Server called Analysis Services and Reporting Services, which make it easy to create reports out of your data. The former allows you to create “cubes� containing summary totals such as sales by product, by customer, by month etc, which will enable managers to “slice and dice� their data. Finally, Microsoft is aiming to consolidate the iron grip it already holds on the desktop with Microsoft Office. Within this strategy, Excel’s role is to be the principal medium through which managers view their company data. Therefore Microsoft is making it easy to integrate Excel with SQL Server data in order to create pivot table reports, and is providing tools which make it easy for developers to create and output reports in Excel. Microsoft, in short, is constructing a single environment which makes it very easy to produce reports, but also provides developers with all the facilities they need to create an effective transaction processing system. A variety of new products and companies have arisen which are designed to take advantage of Analysis Services and output reports into Excel. This shows the way the wind is blowing. While management reporting may currently be dominated by packages such as Business Objects, Crystal and Cognos, in the future it will increasingly be dominated by Microsoft. |
| Date: | 07.04.2005 |
| Email a colleague | |
| back | |
|
|
|
Adapt, sruvive, thrive
