Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

Towards an analysis-ready file for reporting #1

by
20th Jun 2006
Save content
Have you found this content useful? Use the button above to save it to your profile.

In a recent discussion on improving reports from accounts packages, developer JC remarked that 'the profession could make a major contribution to the whole process' by defining which data fields are needed in order to produce the reports they want. AccountingWEB consultant editor David Carter is happy to set the ball rolling.

On behalf of the developer community JC has asked accountants to specify which data fields they need from their accounts package in order to produce reports.

My own proposed list of fields is below. First, however, let's restate briefly the reason for going through this exercise.

Reporting: the problem
For many accountants, creating a report is the easy bit. Most of the time and effort is spent in pulling together all the data that's needed to produce the report in the first place. This data is usually held in raw data tables which are designed for transaction processing, not reporting.

Users often have to trawl through the tables to find the fields they need, decipher the field descriptions, and make joins to pull the tables together. And even when they've found the data there's often a lengthy process of tidying it all up before it can be used in the final report.

Analysis-ready file
This waste of effort could be avoided if the package offered an 'analysis-ready file'. Instead of having to search for the data through transaction processing tables, the user is presented with a single table which has been specially created by the package developer for reporting.

In this table the correct fields have been selected, field descriptions are meaningful, all the joins have been made, the data has been reformatted so it can be used for reporting immediately. All the data needed for reports is there and ready to go. It is, in short, analysis-ready.

Example
View screenshot - 135K, Opens in a new window

When you start up Excel, an additional Access Accounts menu option appears at the top of the screen. Select the Analysis Ready Information option from this menu, and you see the screen above.

There are two boxes. At the top, the Available Columns box contains a list of all the fields available in the Analysis ready table. Tick the fields you wish to bring into Excel to create your report.

At the bottom, the Search Criteria box applies a filter so that Excel imports only the records you want to bring across (for example only those dated for this financial year, those for a particular GL account, and so on.)

Once you've selected the fields and applied the filter, the records come over into Excel. You can immediately go ahead and create your report via a pivot table. And because the data is analysis-ready, a pivot table can now be saved as a permanent report. Tick the Refresh on Open box in Table Options and, when you open this report next month, it will re-calculate the figures automatically.

Proposed List of Fields
Any accounts package needs several of these analysis ready files - one for GL analysis, one for product sales analysis, etc.

Below is a list of the fields I think are needed for reports on the General Ledger. Underneath the list I've given a brief explanation of what each column means.

Ord FieldName Column Name Heading Use? Size
1 Comp Company Cpy Y 2
2 THTyp Transaction Type TType Y 5
3 THTypName Tran Type Name TTypeName Y 20
4 THStatus Status Status Y 6
5 THOurRef Our Reference OurRef Y 20
6 THNarrative Header Narrative TH Narrative Y 30
7 THDate Document Date DocDate Y 12
8 THYr Financial Year FinYr Y 4
9 THPd Period Pd Y 2
10 THPdName PdName PdName Y 6
11 THAccNo Account Code Accnt Y 10
12 ACAcName Account Name AcctName Y 30
13 TLNetBase Amount Base Amount Y 10.2
14 TLNarrative Line Narrative TL Narrative Y 40
15 TLGLAcNo Nominal Account GLCode Y 15
16 GLAcName Nominal Name GLDescription Y 30
17 TLCC Cost centre CC Y 6
18 CCName Cost centre Name CCName Y 30
19 TLJobNo Project Code ProjNo Y 10
20 Jobname Project Name ProjectName Y 30
21 TLPartNo ItemNo ItemNo Y 25
22 TLPartName ItemName ItemDesc Y 30
23 TLUser1 TL Userdefined1 TL User1 Y 20
24 GLAcBalMTD Acct Balance MTD BalMTD Y 15
25 GLAcBalYTD Acct Balance YTD BalYTD Y 15
26 ACAnalNo Account Analysis AccAnal Y 15
27 GLAcGroup GL Account Group GLGrp Y 15
28 CCGroup Cost Centre Group CCGrp Y 15
29 Job Group Job Group JobGrp Y 15
30 StockGroup StkGroup StkGrp Y 15
31 Ledger Source Ledger Srce N 2
32 GLTyp Nominal Type GLType N 3
33 THDEL Deleted Flag Del? N 2
34 THRef Reference TransRef N 10
35 THRef2 Reference 2 Trans Ref2 N 10
36 THAuditNo Audit No AuditNo N 10
37 THBatchNo Batch Ref BatchRef N 10
38 THOperator User User N 5
39 THDatEnt Date Entered DatEnt N 10
40 TLBankRef Bank Rec Ref BankRef N 10
41 TLNetCurr Amount(currency) AmountCurr N 10.2
42 THCurrCd Currency Code CurrCd N 5
43 CurrRate Currency Rate CurrRate N 10
44 TLVATCd Vat Code VatCde N 5
45 TLVATBase Vat Amount VAT Amount N 10.2
Explanation of the columns

The Ord column shows the order in which the fields will be pulled into Excel. This should be amendable by the user.

The FieldName displays the code name of the field in the data dictionary. Not user-definable.

The Column Name gives the English language description of the field in the data dictionary. Not user definable.

The Heading displays the column heading that will appear in Excel. This should be amendable by the user.

Use? is where the user ticks Y or N for whether they want this field to come over. The first 30 fields in the list are set to default = Y because they will be used often. The remaining 15 fields will be used less often and have default = N.

Size indicates the length of the field. This should be definable by the user so that, for example, they can truncate a 45 character field and just bring over the first 20 characters. The figures in this list are just by way of example.

To Follow
This is a big topic. In later articles I'll cover:
1) The principles governing which fields were selected
2) What the default filters should be
3) Likely problems in importing data into Excel.

Replies (3)

Please login or register to join the discussion.

avatar
By User deleted
18th Apr 2005 07:53

Good Start
This is an excellent start to a complex issue.

I agree that the Size Column (when used to define anything other than text or numbers) should only be a guide line and not a hard and fast rule.

For instance when referring to Primary Keys (i.e. Account Code = 5 etc), different packages will adopt different methods of accessing their records - this would accommodate keys ranging from numeric or text through to GUIDS (128-bit number)

A description of the type of data expected should be associated with each field (i.e. text, numbers etc.)

It would also be helpfull to have an expanded description associated with each field, so that the 'layman' can understand its purpose. Say another column for explanation

Any Primary/Secondary key items should also be identified

What is the explanation behind the field naming conventions (col 2) - presumably the first 2 characters define the source table containing the source of record (i.e. GL = General Ledger - but what are the others?). Also in this respect one needs to be quite careful because all software packages have been designed differently and tables may not always line up with the field name prefix

Maybe an additional column defining the table where one would expect the data to come from (i.e. General Ledger, Cost Centres etc) - but not a mandatory issue.

Thanks (0)
avatar
By Richard Willis
18th Apr 2005 13:46

Another perspective!
I agree that it is useful to have a file with all the likely candidates for reporting included. However the software that we use (which I persistently refuse to name as we are in negotiations with them over a horrendous installation experience!) gets over many of the common problems by having only ONE transaction file. Although this is in essence the G/L, every transaction carries just about every piece of information possible with it. This results in a G/L with 141 fields, as opposed to the 45 suggested, but means that from a single source one can access just about any piece of information, including who did what and when.

The selection process is from within the software; one can select, in a split include/exclude table, which fields are displayed on the screen and in what order, or drag them into the required order by the column headings. By double clicking the column header required, the order can be pre-sorted then, at the click of the mouse, whatever is on the screen is exported into Excel in the same layout.

The main weaknesses are that it can only be done for one account at a time (for more I have to resort to Pivot Table/ External Data), and they have prescribed WHICH fields one is allowed to access.

Although there is a lot wrong with the software in question, in this respect they seem to have got it right; why have multiple transaction tables when, with modern high-speed computers, one will do?!

Thanks (0)
avatar
By David Carter
18th Apr 2005 18:22

Naming conventions coming up
JC, I've got an article on the stocks explaining the field names. Hope to publish it soon. Prefixes mean:

TL = field from the Transaction detail record
TH = field from the Transaction header record

Others are from the relevant master records (AC = from supplier/customer master; GL from GL account master, etc).

More details in the forthcoming article.

Richard, whatever their other sins, it looks like your suppliers have gone ahead and created an analysis ready file already! But it does prove the point, doesn't it? In practice, no vendor can ever write all the reports that users might want. But as long as they make all the data available for export into Excel, the users will be able to customise their own.

Thanks (0)