DBIx::SQLCrosstab 1.17

DBIx::SQLCrosstab Perl module creates a server-side cross tabulation from a database.

SYNOPSIS

use DBIx::SQLCrosstab;
my $dbh=DBI->connect("dbi:driver:database"
"user","password", {RaiseError=>1})
or die "error in connection $DBI::errstrn";

my $params = {
dbh => $dbh,
op => [ ['SUM', 'salary'] ],
from => 'person INNER JOIN departments USING (dept_id)',
rows => [
{ col => 'country'},
],
cols => [
{
id => 'dept',
value =>'department',
from =>'departments'
},
{
id => 'gender', from => 'person'
}
]
};
my $xtab = DBIx::SQLCrosstab->new($params)
or die "error in creation ($DBIx::SQLCrosstab::errstr)n";

my $query = $xtab->get_query("#")
or die "error in query building $DBIx::SQLCrosstab::errstrn";

# use the query or let the module do the dirty job for you
my $recs = $xtab->get_recs
or die "error in execution $DBIx::SQLCrosstab::errstrn";

# do something with records, or use the child class
# DBIx::SQLCrosstab::Format to produce well
# formatted HTML or XML output
#

my $xtab = DBIx::SQLCrosstab::Format->new($params)
or die "error in creation ($DBIx::SQLCrosstab::errstr)n";
if ($xtab->get_query and $xtab->get_recs) {
print $xtab->as_html;
my $xml_data = $xtab->as_xml;
}

DBIx::SQLCrosstab produces a SQL query to interrogate a database and generate a cross-tabulation report. The amount of parameters needed to achieve the result is kept to a minimum. You need to indicate which columns and rows to cross and from which table(s) they should be taken.

Acting on your info, DBIx::SQLCrosstab creates an appropriate query to get the desired result. Compared to spreadsheet based cross-tabulations, DBIx::SQLCrosstab has two distinct advantages, i.e. it keeps the query in the database work space, fully exploiting the engine capabilities, and does not limit the data extraction to one table.

Product's homepage

Requirements:

· Perl

What's New in This Release: [ read full changelog ]

· Now the {op} parameter is an array reference, containing pairs of operator/column, so that now it is possible to perform [ ['COUNT', 'id'], ['SUM', 'salary'], ['AVG', 'expenses'] ] in one query.