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

Reporting Tools #2 - on-screen report writers . By David Carter

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

List-type reports help you run your business on a day-to-day basis. If your package incorporates a banded report writer such as Crystal Reports or Sage Report Designer, writing your own reports is going to be painful. But in the software development world right now, grids are hot. So get on to your package supplier and ask when they plan to release their new on-screen report writer.

In the first article in this series, What sort of reports do you want?, I mentioned that there are three different types of report: list-type reports; forms-type reports; and summary-type reports. In this article we'll look at the tools available to help you write list reports.

My own introduction to the magic of data processing was finding out that I could take a list of records, sort them, then print them out in a different sequence. It was so simple, but so empowering. From that point on I was hooked.

List'type reports: why they matter
A list is the oldest and simplest type of report - for a finance or sales system, it would comprise a list of records such as customer names and addresses, or a sales daybook listing the invoices issued today, or an outstanding orders report.

Producing list-type reports is simple: you choose the fields you want to print, then sort, select and subtotal any fields which are numeric - the 'three S's' ' and that's about it.

Listing reports are essential for low-level staff who work at the coal-face and for departmental supervisors rather than for senior managers, who don't want all the detail.

With all the talk these days about snazzy summary reports for top management, listing reports tend to be taken for granted. But if they help people do their jobs well at the lowest, day-to-day level, everything higher up the scale will go right too. You can also earn yourself a lot of brownie points with colleagues if you can help save them time, for example by making a tiny change so a report can come out in account code order rather than in date order.

Producing a report: the basics
Before we discuss listing reports, it's important to get one concept clear in your head. Writing a report - any report ' breaks down into two distinct stages:

  • Stage One: Select the data (ie the fields and records you are going to use).
  • Stage Two: Format this data as it will appear on the printed report.

    Professional or end-user?
    In Stage One ' selecting the data - you go through the data tables picking out the fields you wish to use, and finally assemble them into a list. This process is pretty similar in all report writers. But they differ greatly in the way they handle Stage Two ' formatting.

    With a report writer such as Crystal or the Sage Report Designer, after you have completed Stage One you now switch into Design mode. The fields you have selected are laid across the screen. The screen itself is divided into three bands, where you design the header, the body and the footer of the report. You now have lots of on-screen design facilities enabling you to drag and drop the fields into position, resize the fonts, make bold or italics, and so on (a bit like PowerPoint).

    Then, to see the results of your handiwork, you can switch into Preview mode, which displays the report with real data. If you want to modify the layout you can switch back into Design mode. You keep toggling between Design and Preview until you are finally satisfied with the report format, then Save.

    But with other types of report writer this Design & Preview formatting stage does not exist. Instead, at the end of Stage One data selection, next to your list of fields there are some extra columns. Here you tick which fields you want sub-totalled, which fields you want to sort on, which fields to apply a filter to and so on. These are the only formatting facilities available.

    The first type with Design/Preview is known as a 'professional' report writer and it can create any type of report. It has all the formatting facilities required for designing forms-type reports such as statements and invoices where the header, the body and the footer are quite different.

    The second type is known as an 'end-user' report writer. It contains only the minimum amount of formatting that is required to produce a List-type report. To summarise:

    a) Professional report writer
    Select data + sophisticated formatting (list- and form-type)

    b) End'user report writer
    Select data + minimal formatting (list-type reports only)

    Which type does your accounts package use?
    This article is based on the assumption that you want to analyse your company's data, which is sitting in your accounts package - Sage, Pegasus, Exchequer, SunSystems or another system. The key question is: what type of report writer does your accounts package have?

    Vendors can either develop their own report writer in-house, or they can buy a report writer package from a third party and map it to their own data files.

    Of these third party report writers, the most widely used is Crystal Reports. Business Objects and Cognos are also well known brands but, like the Excel add-in products you can find in ExcelZone, they are primarily designed for summary reporting. We'll cover them in a later article.

    The problem with products like Crystal is that they are designed for IT professionals who specialise in producing all types of report. Packed with formatting features which end-users don't need anyway, they demand a step learning curve.

    I've seen many customers who buy a new accounts package with the intention of mastering Crystal, but rarely do. Often they end up frustrated because whenever they want a new report they have to bring in someone from outside to write it for them.

    Fortunately many package suppliers realise that their average accountant customer isn't going to be able to master Crystal, so they write a report writer of their own. I mentioned Exchequer from IRIS Enterprise, which sensibly includes a separate Format Editor for designing forms like invoices and statements, and a nice easy end-user Visual Report Writer for designing list reports.

    On-screen report writers and "grids"
    However, the most exciting developments taking place right now are likely to make all list-type report writers obsolete. In a year or two's time, if you want to write your own customer ledger report, for example, you won't need a report writer module on your accounts package to do it: you'll be able to write it directly from the customer ledger screen.

    It all comes down to "grids". In Windows terminology, a grid is a screen layout where you display a list (magic word) of records. An Excel screen is a grid. Examples in an accounts package would be a customer ledger account, or a GL account transaction enquiry.

    Re-arrange or widen columns, sort, add new fields
    Consider a GL account transaction enquiry on the Marketing account. The screen will show all a list of purchase invoices analysed to the Marketing account over the past few months. There will be columns for Date, Period, Supplier, Invoice No, Supplier Ref, Description, Amount, and so on.

    Now if you have a reasonably modern package, you may have noticed that in recent years you can do a lot more things with these screen list displays. For example, you may be able to use drag and drop to re-arrange the order of columns on the screen. Or you can make the columns wider or narrower.

    Double-clicking on a column heading will sort the transactions into a new sequence. And by right clicking on the column headings you may be able to display a list of fields which are available for display. Just tick the additional ones you want to see and they now appear as new columns on the screen.

    These features are rapidly becoming standard on any Windows-based packages nowadays - Sage Line 50, for example, can do all of them and more. Most accounts package suppliers are currently doing a lot of work to add these new facilities to all their grid displays.

    Why not an on-screen report writer?
    Accounting software developers are thinking in terms of how the data is displayed on-screen, and maybe exported to Excel. But one or two designers have realised that when they let users select fields, re-arrange columns, apply sorts, what they are effectively doing is designing their own reports.

    So the software companies realised they might as well go the whole hog and create a complete on-screen report writer. Work is going on within several development teams that will let you tick a column where you want to print sub-totals, or let you Group a field (ie print sub-totals whenever the value in the Sort column changes) by dragging and dropping its column heading above the row of column headings. And when you press the Print button, it doesn't print just what's displayed on screen, but a fully-formatted report with sub-totals, report titles etc.

    So is your package supplier one of those working on such features. Can you expect the release of a new on-screen report writer for September? Or perhaps the penny hasn't dropped yet? User requests are a very powerful influcence on software companies, so why not give them a ring and ask when it's due? If you are already able to re-format your on-screen lists, developing a full on-screen report writer will only take them a couple of weeks.

    As far as I'm aware, no vendor has cracked the on-screen report writer challenge yet, but some of them are getting close. The next installment in this series on reporting will get back to reality, and explain what you can do now by getting data into Excel and using its grid-like capabilities.

    What type of reports do you want?
    This is the second in a series of articles in which David Carter addresses current reporting trends and offers practical tips on the following topics:

    Related material

  • Management reporting - AccountingWEB archive
  • An introduction to Excel-driven reporting tools
  • ExcelZone provides regular reporting tips and tutorials
  • Interested in Pivot Tables? Start here

    Subscribe to the ExcelZone newswire
    For regular updates on management reporting techniques with Excel, click the button below to subscribe to the free fortnightly ExcelZone newswire. The subscribe function will take you back to the AccountingWEB home page after it adds your name to the subscription list.

    Subscribe to the ExcelZone Newswire

Tags:

Replies (1)

Please login or register to join the discussion.

avatar
By mikeopolo
15th Jan 2010 18:03

5 stages to a report?

This 2006 series of articles is well worth the new link in the latest newsletter.

I suggest there are 5 stages to writing a report:

1. Understand what the client wants

2. Connect to the data source or sources

3. Select (and possibly manipulate - in a good way) the source data

4. Design the report layout (dictated by user and output choice)

5. Choose output destination - printer/screen/file/application (eg XL)

Some iteration through any/all these steps will be required. What the client ends up accepting may be quite different to what they said they wanted originally.

Regards
Mike

Thanks (0)