New in report generator class - Report generating and drawing package blog

Two years have passed since last update of "Reporting tool" class (as_reportool.php).
Now, after updating it to the current version, I've decided to notify possible class users and write down this blog post.
I'll tell about new functionality or maybe something that was not documented previously.

First, new CSS possibilities are used (for example, collapsible cell borders to prevent border doubling).

1. As noted in documentation, to render some data, you should pass the SQL query
to the class instance, so it can execute it over current MySQL connection and receive data to be printed.
Method CReporTool::SetQuery() is used for that.

Now you can set more than one SQL query, so your report will print data gathered by multiple queries.
In this case queries are executed in order as they were added, and every data block returned from query will be put into report.

To add multiple queries, you can use new method AddQuery($sqlquery).
By calling it many times, you'll add more data to the report. And remember, calling old SetQuery() method will "reset" query collection back to to just one query (one that was passed with this call).

What if you cannot get all needed data by executing SQL query(queries) ?

Now CReporTool supports direct passing of "raw" data, that was previously calculated in your code.

New method setReportData($userdata) does this. As a parameter you pass prepared data organized as array of rows, where each row is an associative array with keys that are known field names (unknown elements just ignored).

Note that if you have set grouping fields, make sure that your array is sorted correctly.

2. Report headings fine-tuning .

Previous CReporTool version could not construct "complex" report headings.
For example, you might have a four quarter fields (holding sales in quarter): Q1, Q2, Q3, Q4, and want to draw them "merged" by one "summary header" : "Year 2012".
In previous versions the only way to do this was making full HTML code with such a headings and include it into XML configuration, or passing it in function SetHeadings().

Now there is a simpler way. After adding all field definitions, you can call new method :

AddHeadingGroup($groupTitle, $field, $fcount=2)

First parameter $groupTitle in our case should be "Year 2012", it will become a title for the "group" of header columns.
Second, $field is a first field name, that starts a heading group. It should be one of defined field names.
Finally 3-rd parameter is amount af fields to be grouped. For our case it should be 4.
It means "merge 4 next fields starting from "first_field_name".

So, after calling this method, rendered headings will have additional table row(s), where "group title" will be placed, and all "child" field titles will be drawed below it.
And yes, multiple field heading groupings are possible.

XML configuration file has respective "headinggroup" tag with attributes "title", "startfield","fcount" :

<headinggroup title="Year 2012" startfield="Q1" fcount="4" />

To make generated online report more "interactive", it's possible to convert it's cells into HREF's, so user can go "down" into more detailed view. With this feature you can build unlimited nesting online report system that drives user from "common" into "details".

Method SetLinkedCell($fieldid, $options) is used for that.

$fieldid is abviously must be one of defined fields names. If there's no such field, this "linking" will be ignored.
$options is an associative array that contains following elements:
$options['uri'] : mandatory element, URI (or URL) that should be opened by clicking this field in rendered report. BTW, it can be a name of user defined function, prefixed with "@" char.
In that case it will be "evaluated" right before printing. Your function must have at least one parameter, as a whole data row will be passed in it,
so in your function you can use any field value to construct "final" URI.

In simplest case, it's just an URI relative to the current page, for example,

"reports/get_sub_report.php?itemid={1}"

You see {1} macro ? It will be substituted by "first" field value. But from what list ? all data row ? No !

Next options element called 'idparts', ($options['idparts']) must contain string with comma separated list of fields that will be a part of our URI.
Value of the data row with first listed field name will substitute {1}, and so on.
For example, we have a data row with "personid" field value 5001 and "rdate" value "2012-01-10", and for that case we want to create following URI :
"get_details.php?id=5001&fordate=2012-01-10"

So we prepare options array like this:

$options = array(
  'uri' => 'get_details.php?id={1}&fordate={2}'
  'idparts' => 'personid,rdate'
);

As a result, after handling such "LinkedCell" we'll have an HTML anchor that will open by clicking on the cell in report.

Other $options are:

$options['target'] - place a "_blank" value here to make child sub-report open in new window (standard HTML feature) : 'target'=>'_blank'
$options['title'] - if exist, your cell's href will have this title. This parameter can be '@UserFuncName' , to be evaluated before drawing.

Note: please avoid single apostrophs (single quote chars) in your titles or prefix them with "\" char, otherwise generated HTML code will be invalid.

SetGroupLink(): Similar "linking" is possible for a "grouping" header row that starts new data block.

For example, You create report about people in a list of departments. When grouping field for department is set, each new department will start with row like "Department {dept name here}".
When you need a "starting row" be a subreport href, use a method SetGroupLink($fieldid, $options), it has the same parameters as SetLinkedCell.

3. Now CReporTool supports creating of tab-delimited text file instead of HTML code. This feature was demanded by people who uses "spreedsheet" software (MS Excel, OpenOffice, LibreOffice etc.) and want to import or open report data in their spreedsheet program.

So, to do this, you can use two ways.

1) Use a new options element in constructor call: add non-empty 'totdf' element  ("TO Tab Delimited File") in passed options array, like this:

 $report = new CReporTool(array('totdf' =>1,/*...*/));

2) Creating Tab-Delimited file will be activated if GET or POST request parameters have non empty "_tofile" value:

mysite.com/my-report.php?_tofile=1

In "tab-delimited" mode all HTML code is omitted, tab character, chr(9) is used as delimiter between field values, no totals or subtotals calculated,
and number values will be printed without formatting (no spaces between thousands etc.). Generated file has ony one header row (field titles) and data rows delimited with CRLF.

4. Multi-totals.

What's this ?
Suppose we have a sales report. Your company trades with many countries, so sales can be in many currencies: USD, EUR, etc.
How to make correct totals or sub-totals ? We cannot summarize dollars with euros and so on !

Here is the use case for multi-totals. We can make our "totals" report row to have separated sub-row for each currency that found in report.
Method CReporTool::SetMultiTotals($fieldname) is used for that.

Parameter $fieldname defines field that defines "separation" values for our multi-totals. In our case we could pass name of field that contains currency name.

XML definition has a tag for defining multitotals :

<multitotals byfield="currency" />

Hope this post will be useful.