Take a look at the Excel-based report writers with a quick summary of how they work. David Carter also gives you a comprehensive set of links to Excel competitors and their data marts to help you improve company reporting.
Excel-based report writers are very easy to use. You open up a new Excel workbook and find that it now contains an extra menu. From this menu you can view the data in your accounts package and pull it into Excel, and with this comes many other benefits, such as:
Formatting - once the data is in the workbook you can use Excel to format it into a nice-looking report for management: add boldface text, background colours, different fonts and so on. There's no need to learn any new software to achieve this, just use your existing knowledge of Excel. And if you want to query any number in the report, you can drill down on it to see the underlying transactions.
Reports can be re-run any time - any report only has to be created once. The next time you want to run this report, just press the button and the report writer updates the numbers directly from the data in your accounts package. There's no need to reformat or rebuild the report as is required with cutting and pasting or exporting as a CSV.
Automated distribution - the report writer will automatically schedule and send reports directly to recipients via email or load them onto your company intranet.
It all sounds so simple and obvious, and Excel is where we all create reports anyway. So why isn't everybody using Excel-based report writers?
But you've got to have data marts!
Unfortunately, another ingredient is required - data marts. Only 20% or so of the data in your accounts package is actually useful for reporting, and it's scattered around ("normalised") in multiple data files. This 20% has to be pulled together into a single file, then tidied up and made ready for reporting. Life then becomes infinitely easier for the person writing the report: no need to make joins to link tables, field names now mean something, all the irrelevant fields have been been stripped out, etc.
In the business intelligence (BI) world these customised reporting files are called "data marts", and they make the report-writer suitable for end-users. Without them the user is working within the raw database, and at this level you really have to be an IT specialist.
In this roundup, I'll list the various Excel-based report writers I know of where the developers have provided data marts for a particular accounts package. We'll look at each one in more detail in later articles.