Template reports

Today I wrote a reporting tool for our Web 2.0 project. I wanted a simple solution that enabled the end customer to create his own reports based on the ones we creates during development.

To make the system flexible I used the Smarty template engine. Smarty works by compiling templates describing layout into PHP files that is when combined with data from an application into a presentation. The normal use of Smarty is to create HTML pages for viewing in a browser, but the code is well written and can be used in many more ways.

I also wanted the SQL queries to be configurable, to allow even more flexibility in the design of the reports. The solution was to use a feature in Smarty that is normally used to avoid hard coding in the templates, the config files. In my solution the report is a config file that specifies the content of the report. A simple report looks like this:

name = Users
desc = This report list all active users in the system.

[list]
Users = SELECT iuser.cn FROM iuser WHERE istatus = 'active'

[screen]
users = users_table.tpl

[print]
head = print_header.tpl
users = users_table.tpl
foot = print_footer.tpl

[excel]
users = users_table.tpl

The application can easily build a list of all the reports in the system by looking at all the files *.rep in the reports directory, using the glob PHP function. The reports supports to be rendered in three different ways, screen, print or excel.

To init the Smarty package, I use:

$smarty = new Smarty;
$smarty->compile_check = true;
$smarty->debugging = false;

$smarty->config_dir = "reports";
$smarty->template_dir = "reports";

$conf = new Config_File("reports");

And to get information about a specific report, I use the Smarty Config_File class to query the parameters from the .rep file:

$res["name"] = $conf->get($report_file,null,'name'); // Get report name
$res["desc"] = $conf->get($report_file,null,'desc'); // Get report description

To generate the report, the PHP code fetches and executes the different queries configured in the report. Two different kinds of queries are supported, queries returning lists and queries returning a single value.

$lists = $conf->get($report_file,'list');
foreach($lists as $name => $query) {

$res = iDbSelect($query);
 
$smarty->assign($name,$res);
}

$vars = $conf->get($report_file,'var');
foreach($vars as $name => $query) {

 $res = iDbSelectOne($query);

 $smarty->assign($name,$res);
}

After all the data has been read in from the database, Smarty is called to render the report. The $show variable holds the type of report to generate; this could be screen, print or excel. This makes in possible to have some parts of the report generated the same way independently if the report is intended for the printer, screen or for Excel.

$displays = $conf->get($report_file,$show);
foreach($displays as $display) {
 $smarty->display($display);
}

By using this solution we can easily design reports made up from smaller parts that the customer later will be able to reuse in designing his own custom reports.