Saturday, November 23, 2013

Reporting in LedgerSMB 1.4: Part 5, Conclusions

I hope many of you have enjoyed this series.  We've tried hard to avoid inner platform syndrome here by making reporting something that a developer does.

Skills Required to Write Basic Reports

The developer skills required to write reports tend to fall on the database side.  In general one should have:

  1. A good, solid understanding of SQL and PL/PGSQL in a PostgreSQL environment.  This is the single most important skill and it is where most of the reporting effort lies.
  2. Basic understanding of Perl syntax.  Any basic tutorial will do.
  3. A basic understanding of Moose.  A general understanding of the documentation is sufficient, along with existing examples.
  4. A very basic understanding of the LedgerSMB reporting framework as discussed in this series.
These are required for general tabular reports, and they allow you to build basic tabular reports that can be output in HTML, CSV, ODS, and PDF formats.

Skills Required to Write More Advanced Reports

For more advanced reports, such as new financial statements, government forms, and the like, the following skills are required.  These are not fully discussed here.  These typically require, additionally:

  1. An in-depth understanding of our HTML elements abstraction system (this will be discussed in a future post here)
  2. A general proficiency with Template Toolkit, possibly including the LaTeX filter for running portions of the template through a LaTeX filter.


The reporting framework here is very database-centric.  In general you cannot have a non-database-centric reporting structure because the data resides in the database, and some knowledge there is required to get it out in a working form.  We have tried hard to make a system where only minimal knowledge elsewhere is required to do this.  If you have db folks who work with your finance folks, they can write the reports.


Ad hoc reporting is outside the scope of this reporting.  A one-off report is unlikely to be particularly helpful.  Additionally this generates reports as documents that can be shared.  Updating the data requires running the report again, and while this can be done as a sharable URL, it is not necessarily ideal for all circumstances.

Other Reporting Options

In cases where this reporting framework is not ideal, there are a number of other options available:

  1. Views can be made which can be pulled in via ODBC into spreadsheets like Excel.
  2. Third party report engines like JasperReports can be used instead, and
  3. One-off SQL queries in PSQL can be used to generate HTML and (in the most recent versions) LaTeX documents that can be shared.


  1. Hi Chris,

    I need to validate data in PostGreSQL database against the data in an XML file.
    Could you please help me by providing any idea or sugession.

    Thanks & Regards,
    Venkatesh Gubba