Writing Reports for LedgerSMB 1.4 part 2: Input Filter Screens

The input filter screen system is designed to be fairly simple and yet easily extended.  This post will cover the overall design of the system, how to handle custom dropdowns, and general reusable components.

In general, common controls and the like have been centralized to ensure consistency of interfaces for both user and developer.  Not only does the user get to see common date selection routines, for example, but the developer can count on dates being available to the report.

Common Controls

 The report_base.html template contains a number of basic controls used for data entry. In general these are the best way to add certain kinds of common data to a report because they ensure a consistent API and user experience.  To use one of these controls, just PROCESS them  like:

<?lsmb PROCESS date_row_div ?>

We offer the following common controls for report filter screens in the report_base.html template:

• business_classes, a table row for selecting the business reporting class (project, department, and the like)
• business_classes_div, similar to business_classes above but in a div instead of a table row
• entity_class, a table row for selecting entity class (customer, vendor, etc)
• gifi_or_standard, a radio button set for selecting GIFI or standard reports (table row)
• igore_yearend, a table row for selecting whether yearends should be ignored or not (options: all, none, or last)
• date_row is a set of two table rows for selecting date, either by year, month, and period or by from and two dates identifying a range.
• date_row_div uses divs instead of table rows.
• employee_row is a row for selecting an employee for a report

Now, all of the controls come in a table row form and some come in a div form.  The reason for this is that migrated forms have been only minimally changed at present, and div-based forms have not been written yet.  In 1.5 or even possibly 1.4, it is likely that div-based versions of the table-based forms will be written.

General Functionality Available

For reports only requiring general functionality you can simply put the filter screen in UI/Reports/filters/ with a name based on the report name (myreport.html for example) and then call it with a URL like: http://myhost/ledgersmb/reports.pl&action=start_report&report_name=myreport&module=gl.

The module option determines which business unit classes are available for selection.

The following are added to the request hash before passing it on to the template:

• heading_list is a list of account headings
• account_list is a list of accounts
• all_years is a list of years for which there is financial activity (does not include uninvoiced orders)
• all_months is a list of localized months for the date selection dropdown
• batch_classes is a list of batch classes
• bu_classes is an array of business unit classes, for the appropriate common control
• b_units is an array of business units by class, for the common control
• country_list is a list of countries
• entity_classes is an array of entity classes for the common control
• employees is a list of employees for the common control

If you do not  require more option sets than these, you can use the basic method mentioned above.

Handling Custom Selection Boxes

There are of course times when one needs more option sets than these.  The best way to handle this is to add a new workflow script, which preprocesses the $request hash before passing this on to same function.

For example the relevant code for searching for orders is (abbreviated):

use LedgerSMB::Scripts::reports;
use LedgerSMB::Report::Orders;

sub get_criteria {
    my ($request) = @_;
    my $locale = $LedgerSMB::App_State::Locale;
    $request->{entity_class} = $request->{oe_class_id} % 2 + 1;
    $request->{report_name} = 'orders';
    $request->{open} = 1 if $request->{search_type} ne 'search';
    if ($request->{oe_class_id} == 1){
        if ($request->{search_type} eq 'search'){
            $request->{title} = $locale->text('Search Sales Orders');
        } elsif ($request->{search_type} eq 'generate'){
            $request->{title} =
                   $locale->text('Generate Purchase Orders from Sales Orders');
        } elsif ($request->{search_type} eq 'combine'){
            $request->{title} = $locale->text('Combine Sales Orders');
        } elsif ($request->{search_type} eq 'ship'){
            $request->{title} = $locale->text('Ship');
        }
    } elsif ($request->{oe_class_id} == 2){
        if ($request->{search_type} eq 'search'){
            $request->{title} = $locale->text('Search Purchase Orders');
        } elsif ($request->{search_type} eq 'combine'){
            $request->{title} = $locale->text('Combine Purchase Orders');
        } elsif ($request->{search_type} eq 'generate'){
            $request->{title} =
                   $locale->text('Generate Sales Orders from Purchase Orders');
        } elsif ($request->{search_type} eq 'ship'){
            $request->{title} = $locale->text('Receive');
        }
    } elsif ($request->{oe_class_id} == 3){
        if ($request->{search_type} eq 'search'){
            $request->{title} = $locale->text('Search Quotations');
        }
    } elsif ($request->{oe_class_id} == 4){
        if ($request->{search_type} eq 'search'){
            $request->{title} = $locale->text('Search Requests for Quotation');
        }
    }
    LedgerSMB::Scripts::reports::start_report($request);
}

The point is you can effectively put whatever logic you want before passing on to our general functionality.

You can use the templates in UI/Reports/filters as examples for coding the template itself.

Conclusions

The filter screen system is intended to make it relatively easy to create filters for report inputs.  In the future we will probably add more common controls and the like.  However this gives you the basic information needed to start writing reports.

Others in Series

1. Overview

3. Stored Procedure Best Practices

4.  (Mostly) Declarative Reporting Perl Modules

5.  Conclusions

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
work.

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:

http://lsmb/reports.pl?action=begin_report&report_name=mycustomreport

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/MyCustomReport.pm, then your workflow script would need to have, minimally the following few lines:

use LedgerSMB::Reports::MyCustomReport;

sub my_custom_report {
    my ($request)  = @_;
    LedgerSMB::Reports::MyCustomReport
        ->new(%$request)->render($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