About
Documentation
..Frontend
..Database
..Gedafe Pearls
..PearlReports
..CPPTemplate
Screenshots
Download
People
Mailing List
Page Maintainer:
Tobias Oetiker

Last Update:
2003-09-27
Pearl Reports

NAME

DBIx::PearlReports -- A Versatile Report Generator

SYNOPSIS

Using the SIMPLE mode

 use DBIx::PearlReports qw(:Simple);
 create (
    -datasource => 'dbi:Pg:dbname=database;host=hostname',
    -query => 'SELECT * FROM customers ORDER BY state, city;'
 );

or

 create (
    -datasource => 'dbi:Pg:dbname=database;host=hostname',
    -query => 'SELECT * FROM customers ORDER BY state, city WHERE name = ? AND age = ?',
    -param => ['Tobi',34],
 );

 group (
    -trigger => sub { $filed{state} },
    -head => sub { "State: $field{state}\n" },
    -foot => sub { "Average Age for $field{state}".rpavg($field{age}) }
 );

 group (
    -trigger => sub { $field{city} },
    -head => sub { "City: $field{city} (".rpcnt($field{name}.")\n" },
    -foot => sub { "Total Customers in Customers in ".
                   "$field{city}: ".rpcnt($field{name})."\n" }
 );

 body (
     -contents => sub { "$field{firstname} $field{lastname} $field{age}\n" }
 );

 print makereport;

PearlReports can also be used in an Object Oriented Context:

 use lib qw( /usr/pack/postgresql-7.3.2-ds/lib/site_perl /usr/isgtc/lib/perl);
 use DBIx::PearlReports;
 $r = DBIx::PearlReports::create ( ... );
 $r->group( ... );
 $r->body( ... )
 print $r->makereport;

DESCRIPTION

PearlReports is a system for pulling information from an SQL database and produce Reports from this information. PearlReports provides a very flexible system for creating reports based on SQL queries

While it is sufficient to use the simple statements provided by PearlReports to create your reports, the full power of perl is only a keystroke away.

Creating a Report using the PearlReports involves writing a short perl script which first loads the PearlReports module:

 use DBIx::PearlReports qw(:SIMPLE);

Then you call the create function to create a new report:

 create (
     -username  => 'myname',
     -datasource => 'dbi:Pg:dbname=customers',
     -query => 'SELECT * from customers ORDER by state,city'
 );

When creating a report you have to define username and password for the database you want to access. Because different people may use the report. If you do not mention either the -username or -password arguments, the module will ask you to supply one at run time.

The -datasource argument defines which database this report is going to use. Check the DBI/DBD documentation for the syntax apropriate for your Database. In the example above we are accessing a PostgreSQL database called customers which runs on the local host. The -query argument of the create function defines the data we want to use in our report.

A central element of PearlReports is the ability to work with groups of records. In this example the report contains two nested groups. Note that the data

must arive from the database in an order which is comatible with the required groups. (Check the ORDER BY cause above).

 group
  ( -trigger => sub { $field{state} },
    -head => sub { "Customers from $field{state}\n" },
    -foot => sub { "Avg Age for $field{state} Customer:".
                    rpavg($field{age})."\n\n"  } );

 group
  ( -trigger => sub { $field{zip} },
    -head => sub { "Customers from $field{town}\n" },
    -foot => sub { "Min Age in $field{town}:".
                   rpmin($field{age})."\n" } );

Each group definition requires a trigger and either a header or a footer or both. Each argument of the group definition is a little perl function definition. The Trigger function gets called for each record in the query. Whenever the value returned from the function changes the group iterates. Each iteration of a group is enclosed by the apropriate header and footer.

The $field{xxx} variables refer to the columns of the query. The footer and the header (!) can contain agregation functions (rpmin, rpmax, rpbig, rpsmall, rpsum, rpavg). See the section below

Finally the actual data can get printed.

 body
  ( -contents => sub { "$field{firstname} $field{lastname} $field{age}\n" } );

The body function will get called for each row in the database and its return value will be printed into the report.

When groups and body are set up you can create the actual report by executing:

 print makereport;

When you want to make a new report using the existion database connection for another report, you can reset it with the command

 reset;

METHODS

All functions provided by PearlReports expect named arguments.

create or new

Defines the data the report should be based on. This involves configuring the parameters for accessing the database server as well as defining the query string.

If you are working with the OO interface you can use b<new> instead of create to be more in line with established naming conventions.

-username
the username for the database. If this option is not set, PearlReports will prompt for a username.
-password
the password. If this option is not supplied PearlReports will prompt for a password.
-datasource
the DBI connect string. Check the DBI/DBD manpage for the syntax apropriate for your database.
-handle
instead of the previous 3 arguments you can also give PearlReports an existing database handle to use.
-query
A SELECT query
-param
If you use '?' placeholders in the query, you can supply contents for them using the reference to an arry holding the relevant data.

group

The 'salt' of most reports is that some grouping structure exists. The records in the report get collected into groups of records which bear some common feature. Each group can have a header and a footer.

-trigger
Is a pointer to a anonymous function. The function gets called for each row in the result of the query. Whenever the return value from this function changes the group goes into its next iteration. There are other events which can cause a new iteration: A higher order group goes into another iteration or the last record from the query has been consumed.
-head and -foot
After each iteration, the anonymouse functions pointed to by the head and foot options get executed. The return values from the functions are used as header and footer for the material inside the group.

body

The inner most 'group' of the report does have neither foot nor head, it has just a body which gets printed for every row in the query result.

-contents
Stores a pointer to an anonymous function which gets executed for each record returned by the query.

makereport

returns an array containing the report ...

reset

clears the group and body data from the report. This can be used to run a second report of the same database connection without reconnecting.

AGGREGATE FUNCTIONS

rpsum

Builds the sum of all the values its argument takes during the traversal of the records in the current group iteration.

rpmin, rpmax

Finds the min and max values in a group iteration.

rpsmall, rpbig

Finds the first and last value when sorting alphabetically.

rpcnt

Count the rows in the current group iteration.

rpavg

The same as above only that the average gets calculated.

HOW TO WRITE NEW AGGREGATE FUNCTIONS

If you want to write your own aggregat functions. Follow the examples below. Note that the first two lines of each function are mandatory. The structure you store in the $arr is up to you.

 use PearlReports qw(:MyAgg);

 sub mycnt ($) {
    my $cnt = $aggmem->{counter}++;
    my $arr = \$aggmem->{array}->[$cnt];
    $$arr++
    return $$arr;
 }

 sub myavg ($) {
    my $cnt = $aggmem->{counter}++;
    my $arr = \$aggmem->{array}->[$cnt];
    $$arr->{sum} += $_[0];
    $$arr->{cnt}++;
    return $$arr->{sum} / $$arr->{cnt};
 }

If you create cool aggregate functions please drop me a line.

HISTORY

 2002-06-12 to Initial ISGTC release
 2003-07-16 to Added -handle option
 2003-07-29 to Added -param option

AUTHOR

Tobias Oetiker <tobi@oetiker.ch>

COPYRIGHT

(C) 2000 by ETH Zurich

LICENSE

This code is made available under the GNU General Public License Version 2.0 or later (see www.gnu.org)