Wednesday, October 9, 2013

Writing Reports in LedgerSMB 1.4 Part 1: Overview

One of the major features of the upcoming LedgerSMB 1.4 branch is the new reporting system.  This post gives a very basic breakdown of this new reporting engine.

The goal of the system was to make a framework for quickly and easily converting stored procedures into reports.  The basic components are:

  1. The input criteria filter (usually required)
  2. The main Perl module that defines the report
  3. The stored procedure which does the main work
  4. An optional output template.
  5. A workflow script to coordinate.

Some of these may be very lightweight, if not almost optional.  The two however which are critical are the defining perl module, which defines a report in nearly declarative terms, and the stored procedure which does the

For simple reports, it is enough to put a form template for input values in UI/Reports/filters/ and then access it as through a URL referencing the name of the form as the filter template, like this:


This part is quite simple.

The next thing you need is the PostgreSQL user-defined-function.  This is usually a wrapper around an SQL query which provides a discoverable interface for input values to output values and returns tabular data.  This function must return all data related to the report, and needed for support functions like click-through links.

You also need a Perl module which defines the basic nature of the report, its name, layout and the like.  This is done mostly declaratively and will be covered in depth in a future article in this series.  The approach is sufficiently simple that basic reports can be written without any real knowledge of Perl (working from sample code only).

Finally there is a little bit of glue that is required for displaying the report in the workflow scripts.  This is simple enough that we can cover it here and be done with it.

Normally one will either have a new workflow script that managed this report or lump it together with a series of similar reports.  If the Perl module is in LedgerSMB/Reports/, then your workflow script would need to have, minimally the following few lines:

use LedgerSMB::Reports::MyCustomReport;

sub my_custom_report {
    my ($request)  = @_;

That's all that is required to render the report from this framework, based on inputs from the filter screen.

This system shows the general power that putting such reports in the database and detecting their arguments can have.  The key skill in writing such reports is thus SQL, not Perl.

Forthcoming parts:

2.  Understanding Input Filter Screens for Reports

3.  Reporting Stored Procedures Best Practices

4.  (Mostly) Declarative Reporting Perl Modules

5.  Conclusions