Getting better reports out of your accounts package. By David Carter

This is a fascinating topic and even more interesting is the lack of Standard report fields or seemingly the will to suggest any Standards.

If Standards are in place covering 75% of reporting requirements then to a large extent the need for access to the underlying data is reduced; unfortunately so is the 'fun factor'.

When Standards are combined with Data Dictionaries (or an exposed object model) then just about all the eventualities are covered.

With todays software, data is held in tables and may only form part of the story. Without an overall knowledge of the underlying Object Model / Data Structures, giving access to the database may cause more problems than it solves.

As an example - data in some tax products is XML based; which is then held in a single or multiple fields in the database. In order to use this data it needs to be retrieved from the database and parsed into an Object Model (classes / objects) before it can be used. In such circumstances providing access to the database tables would not be productive.

Many databases also consist of multiple tables and relationships (200+) and frequently have Metadata driving other data - again fairly meaningless when accessing the base tables, unless one knows the structure. Furthermore without an explanation of the enumerated constants used in the program one is also at a loss.

Ok - so once the manual deviates from being a User Guide and into the realms of the technical area, where does one draw the line?

For a vendor to fail to produce a User Manual is bad practice - however, one could argue that to turn the User Manual into a technical specification is also bad practice and not the function of a manual

Richard, it looks like there's a missing human link (of know-how) between yourselves and your vendor; each expecting the other to supply it.

Hiran says 'if the manual is deficient' etc.
It may be indicative of the problems that we are facing to say that, after 2years in the marketplace our vendors are yet to produce a manual for our package, nor is there any sensible on-line help facility!!! It IS MS SQL-based, and yes, there is a data dictionary.

When, on one accasion, I questioned why I could not access a certain field in G/L journals, the answer I got was 'well you only know it exists because you looked in the database'!

What a lovely debate and full marks to David and all the contributors.

If the accounting package provides sufficient documentation of tables/fields (reference manual) simple drag-and-drop from the visual tools in our everyday tools should suffice, without having to rely on techies. The visual inteface in SQL Server is similar to that in Access (where they're called Queries, a topic covered in any starter course. Both Access and SQL Server write the SQL code for you in the background. Oracle too). For views/queries the brand/vendor of accounting package is unimportant - it's just the underlying database you are dealing with. Ok, if the manual is deficient you might need a short conversation with the support people. Also, creating a view poses no threat to the integrity of the accounting data if it is read-only, and as noted already, it is a once only task that should take no more than ten minutes, and (hey) it's a lot of fun.

As David says, after that we can report to our hearts content - with Excel, reporting tools, direct to dynamic web pages (always live, on-demand and real-time), anything ...

Ten minutes. Happy to show anyone how. Free.

Hiran de Silva
hiran@desilva.com

My reluctance to tell which package we use is not really based on a desire to ensure that others are inflicted with it. It is because the situation with the sotware house is a little delicate at present! Suffice it to say that in the process of arriving at a remotely useable solution, we logged £30k + of lost or wasted man hours.

With reference to the reporting functionality, an example. The package has no means of reporting the totals of ALL warehouses in the stock, such that one can vouch the G/L balance -v- Stock inventory. It gives a single report for each warehouse, but we have about 200 virtual warehouses, many of which are empty, or should be. Short of running the said report for all 200 of them, one cannot see which have a balance, right or wrong. Only by using external data in pivot tables have I been able to achieve any semblance of a handle on our stock position. I can now output either the G/L total by warehouse, or the entire stock by Item by warehouse, both balancing to the G/L account to within a GBP (rounding/Currency diffs.). Without Excel we would be lost!

my own view is that software should be written to provide the best functional solution. In this day and age the best solution is using a SQL type database (of which microsoft SQL server is a well known example). If the application is well written it will provide a stable transactional environment (so that it always balances), and it will use business rules to maintain its database (so that transactions are processed consistently, regardless of source). The typical user should be blissfully unaware of all of this.

whilst presentation methods change, reporting requirements are generic. For example, executives are usually interested in highly aggregated numbers, but managers require lots of details. And some managers require financial stuff, some require operating stuff, some require marketing stuff, etc; and most require the ability to produce stuff that is non-standard, so the provision of a raft of generic reports is not the modern answer.

data views available to analysis tools (such as Excel), where the data items are either logical and/or defined (so the user is able to know exactly what is being provided) is a good answer, and something that SQL products are able to provide. which is why it will be interesting to see what microsoft produce. If the user has to interpret the raw data tables the chances are that (s)he will get it wrong.

because this is all relatively new, it is not surprising that most offerings provide generic reports, and a report writer looking at the raw tables; and thus it is also not surprising that most users view the reporting aspects of accounting applications as poor.

Sql Views are not issue because they are relatively simple to implement. Agreeing on their content and the necessary end reports is the problem.

This is where the profession could make a major contribution to the whole process. It should be a fairly simple matter for the profession as a whole to decide upon the minimum number & type of reports (together with their content) and this could be used as the basis for future progress.

The benefits of a standard approach are immense - no more re-inventing the wheel each time in Excel, reduced time/costs standard formats for comparison etc...

The requirement is to analyse the problem and provide as near generic solutions as possible. If the template solutions provided are deficient then they need to be added to over time until a definative model exists for industry sectors.

It should not be an insurmountable task but it falls to the profession to set the ball rolling

Finally as a pre-requisite Microsoft Excel should correctly write csv files - see

http://support.microsoft.com/default.aspx?scid=kb;en-us;77295

Hi David,

Maybe I'm missing the point then? Surely if Richard's software is based on SQL Server then he can use the tools that your article advocates?

Regards

John Clough
Numerica LLP
Tel: 023 8070 2345
john.clough@numerica.biz

<can content="worms" status="open">

There is a common thread amongst the IT community to presume that anyone that doesn't know their right outer joins from their left ones, can't tell SOAP from UDDI, or can't disassemble a printer driver at 30 paces must be somehow inferior. Yet it is this mentality which keeps 99.999% (five nines!) of IT people away from the boardroom and locked away in some darkened computer room!

I don't care how sheering forces impact the viscosity and flow parameters of diesel oil at the extremes of temperature and pressure - I just turn the key and my car starts.

So why should Accountants / FDs / CEOs *have* to care about Structured Query Language or whether this transaction table requires a right outer join with that one? Yes, it's easy when you know how, but that's not the point...

</can>

Stewart Twynham MBCS MIEE
stewart@bawden-quinn.co.uk

I agree with the writers who say "you shouldn't need to know how it is arrived at, just that you can do it"

I would start by saying that (and it still is a big assumption) hopefully you can assume your solution can give you all the standard reports (day books, aged listings, etc) without too much difficulty, but I'd suggest that what users should consider and demand to see during the software purchase process are all those slice and dice reports that they need to really run the business, that in the end point to the concepts being talked about (tables, links, queries, relational databases) being dealt with behind the scenes. You can always find a package where the query process is written so badly the database increases as the day progresses

Most industries have the vast majority of their business units made up of small and medium businesses. It is these businesses that can't afford specialist IT support, so are relying on the package (even if it is largely bespoke) to have already dealt with these problems. I think getting an SME's to understand about a flat file is a bonus. I know I can do loads of slice and dice in SAP, I know I can do some in Quickbooks, but the trick is for the purchaser to know what they need to run their business and trade it off with the price being offered by the software vendor. David has written some excellent stuff on the use of excel for users of Sage and for users in general. I'd still see this as the major tool for the SME. I have looked at many reporting tools that take raw data and enable you to slice and dice, but as an ex-FD, I used to reduce the number of reports until someone screamed. Then I knew there was a possibility it was an important report. In the end it is likely that 20% of the available reports provide 80% of you required needs. I'd find a package that gets close to that 20%!

Kind Regards

Daniel Clark
Ryba Macaulay Ltd
info@ryba-macaulay.co.uk

With any proper database it is always just one operation to create a 'View'. After all they are just stored queries.

The problem is when the view changes, which it will do.

The main issue you will have with any database is relating the tables together, and understanding what the things mean. And for that you need to understand SQL query language and how to use it.

It seems to me that the author of these piece is either heavily dumbing down for the audience, or fundamentally doesn't understand the nature of relational databases.

NeilW

Yes, but, I think the point David was trying to make is that not every small business has a handy team of Analysts and DBAs creating and running complex queries on a DB2 UDB DBMS running on an IBM mainframe.

Yes, Microsoft is behind the curve - but if their previous "me too" ventures are anything to go by, they'll take the best bits, add usability, take away the price and exploit their market dominance to deliver something to the masses.

And, no, it won't be good enough for the purists or the developers amongst us, but it will be perfect for the accountant bod sat in a darkened room in XYZ manufacturing.

Which at the end of the day is all that matters...

Stewart Twynham
stewart@bawden-quinn.co.uk

It must be difficult for software authors.

As even just these few responses show, software authors have to cater for everyone from the systems accountant who wants all the tools, to the FD/MD who just wants to press a button without care for how the tables link together (please dont reply in respect of my stereotyping).

So as a software author you have 3 choices:

1. Build a comprehensive set of proprietory reports.

2. Allow open access of the database and rely on the inbuilt tools, MS Access, MS Excel etc, or other third party tools such as Crystal Reports, Cognos etc.

3. Build a number of reporting and enquiry tools so user can use their preferred method, AND allow open access to the database, links to Excel etc.

From our experience, even the latter attracts complaints of confusion that the user doesnt know which method is the best to retrieve their information.

This further proves the theory; you can please some of the people all of the time...

PS David, I think it's a bit disingenuous to out the software that is difficult to report from to "save some readers from making an expensive mistake" as reporting is only part of the equation when choosing an accounting package, especially as Richard mentions that he can report using Office/SQL facilities which would certainly suit some users.

Regards

John Clough
Numerica LLP
Tel: 023 8070 2345
john.clough@numerica.biz

As one who has to struggle daily with an integrated system in which the reporting most certainly WAS left 'till later, I must say that without the ability to use the MS Office/SQL facilities, I would be totally lost. As I have said in another string, I won't say what package we use; why should I be the only one to suffer!!!