DBIx::Knowledge::Report - Handler routines to serve report builder form and report execution
use DBIx::Knowledge::Report; use CGI::CRUD::SkipperOutput; # Example implementation in mod_perl $crud = new CGI::CRUD::SkipperOutput($r) or return OK; $report = new DBIx::Knowledge::Report($crud,'PROCESS_LOAD_LOG') or return OK; $report->select_list_from_table() or return OK; return $report->take_action();
Provides methods to display a report builder form and to execute the reports. To be run in the context of a webserver.
See SmartCruddy! http://www.thesmbexchange.com/smartcruddy/ for more info and an implementation.
Your data set must be in a form that can be grouped by a set of fields. An example may be as simple as a table populated with webserver log records. This table can be aggregated by the timestamp field of the webserver request (say you want to report on the number of hits to /index.html from 9am to 10am) and will fit nicely into a model that can readily be used by this abstraction.
This may be accomplished by creating views or semi-aggregated tables (materialized views) usually along an axis of time (temporal linear convergence). Semi-aggregated tables are useful for reducing the size of very large data sets aggregated by key fields analysts might be interested in so they can be queried efficiently in real-time.
Once you have an appropriate table or view to build reports from (see "Linear convergent data sets") you'll need to define a set of data points the report builder can present. A data point may be a field in the table or view or a derivative of field(s), essentially any valid expression in a select list of an SQL SELECT statement. Example:
SELECT request_date, TO_CHAR(request_date, 'YYYY') FROM...
represents two data points.
CAREFUL! the terms "field" and "data point" may be used interchangeably below but they are not the same thing (the author will try to avoid such confusion).
These data point definitions may exist in a table
DBIX_KNOWLEDGE_DATA_POINT so they can be managed easily (probably by data analysts, preferably someone who knows SQL). Each record in the table defines a data point and each column is explained below:
Name of table or view. Name doesn't actually have to refer to an object in the database, this is a way to identify a group of data points and this name will be passed to methods in this class. Syntax rules for general identifiers apply - no whitespace, alphanumeric characters only, may want to use ALL CAPS for consistency. E.g. APACHE_LOG
Name of the data point - don't confuse this with an actual column name in a table or view, although they may be the same. The syntax rules for general identifiers apply - no whitespace, alphanumeric characters only, may want to use ALL CAPS for consistency. E.g. REQUEST_YEAR
Ordering of the data points as they will appear in the report builder form and the final report. I recommend spacing by 10 (10, 20, 30, etc).
Friendly name of
FIELD_NAME that will appear in the report builder and reports.
Valid SQL that will appear in the select list of a SELECT statement (example above). This is how the data point will appear in the report so any formatting should happen here.
Another alias for the data point name, this will appear in the report builder and allows the report ordering to be modified by specifying a comma-separated list of these aliases in the "Order by" box of the report builder. The reason for another set of aliases is an abbreviation of
HEADER with stricter syntax that can be easily parsed when comma-separated.
The syntax rules for general identifiers apply - no whitespace, alphabetic characters only. Additionally, THESE MUST BE IN ALL CAPS.
When using these aliases in the "Order by" box, each may be appended with "desc" to specify order polarity.
Allows you to achieve an ordering in the report for this data point different from how it would order by in its appearance in the report (
SELECT_SQL). Example is for formatted dates where
SELECT_SQL = TO_CHAR(base_hour,'MM/DD/YY')
You'll want to order this field chronologically:
ORDER_BY_SQL = TO_CHAR(base_hour,'YYYYMMDD')
ORDER_BY_SQL = base_hour
Evaluated as a perl boolean as to whether or not this data point (
SELECT_SQL) is a group by expression (e.g. SUM(error_count) IS a group by expression).
If this data point is a group by expression and the
SELECT_SQL expression references fields that are not part of the group by expression, they must appear here so they are included in the GROUP BY clause. The field name(s) must be enclosed in quotes; multiple field names may be entered separated by commas. Example:
SELECT_SQL = TO_CHAR((payin_amt - payout_amt) * SUM(export_count),'L99,990.99')
The payin_amt and payout_amt fields are not part of any group by functions, so:
GROUP_BY_SQL = 'payin_amt', 'payout_amt'
The default ordering of the report is used if the "Order by" box is empty in the report builder. The default ordering uses the list of data points that are NOT group by expressions, (non-group by expressions are the ones that will appear in the GROUP BY clause) unless
NO_DEFAULT_ORDER_BY evaluates true as a perl boolean.
To make the report look cleaner, within subgroups the name of this data point will only appear in the first line of many.
HTML classes that will be prepended to those assigned to the <td> element (see CSS). Class name(s) must be enclosed in quotes; multiple class names may be entered separated by commas. Example:
HTML_CLASS = 'revenue', 'data_align_right'
Advanced: For authenticated users in the "np" group, this data point will appear in reports if
NP_ALLOW evaluates to true as a perl boolean.
Advanced: For authenticated users in the "np" group, an alternate friendly name that will appear in reports.
You may also pass an array of hash references to the constructor instead of managing records in
DBIX_KNOWLEDGE_DATA_POINT. Each hash having key/value pairs corresponding to a record as described above with the following differences:
TABLE_NAME is not needed (the array is the group). Rename `FIELD_NAME' to `ID' (UPPER CASE). All other field names must be lower case as keys in each hash. This array is passed to the constructor.
Once the data points have been defined, you will be able to build a report. The first screen presented by the take_action method will be a report builder form. The first section lets you add a title, pick the fields to report on, which fields to subtotal, specify repeated headings, auxiliary fields (advanced, see the code), override ordering and set result limits. The next section of the form allows you to set criteria on the result set.
Class attributes are hooked into the HTML that is generated to allow custom styling with CSS. The following is a list of class names and how/where they influence the presentation.
table elements are tagged with this class.
tr element is tagged with this class if a grand total report summary is requested.
Where X is an integer indicating the subtotal rank or nesting level.
tr elements on summary (subtotal) rows are tagged with this class, the name of which varies depending on the nesting level or subtotal rank. Example: if you subtotal on year/month/day, the yearly subtotal row
tr element will be tagged with a class named
data_subtotal1, month with
td elements are tagged with this class only if they follow
tr elements tagged with a
report_summary classes. These fields normally won't contain data, just a label indicating this row is a summary row.
td elements of data points with
HTML_CLASS populated (see above) are tagged with a set of classes prepended with the class(es) appearing in
At the bottom of every report generated are two links.
If you right click this link in most browsers you should have the option to copy the URL link location. You can then past it in an HTML document and anchor it, etc.
This link will take you to the report builder with all parameters saved and pre-selected.
$report = new DBIx::Knowledge::Report($crud_output, $report_table_name[, $data_points, $cache_list]);
Create a $report object where $crud_output is a CGI::CRUD output object and $report_table_name is the name of the table or view to be reported upon. An optional array reference $data_points may be given as described in "Data point definitions", otherwise you must invoke
select_list_from_table(). Optional boolean $cache_list will cache the data point list.
server_error() will be called on $crud_output and undef will be returned.
$bool = $report->select_list_from_table([$report_table_name, $cache_list, $dbh, $select_field_table_name]);
Retrieve the list of data points for report building from a database table. Optional $report_table_name overrides the same parameter given to the constructor only for the purpose of identifying the group of datapoints by matching the
TABLE_NAME column in the data points table. Boolean $cache_list will cache the data point list, $dbh is an appropriate DBI database handle. The table that stores the data points may be given by $select_field_table_name table, which defaults to $DBIx::Knowledge::Report::REPORT_SELECT_LIST_TABLE_NAME.
perror will be called on $crud_output and false will be returned, otherwise return true.
$status = $report->take_action();
Call within a webserver context to display a report builder form or execute and display report results. May return an error page if there are any problems along the way.
Return 0 (equivalent to Apache status code `OK').
No known bugs.
Reed Sandberg, <reed_sandberg Ó yahoo>
Copyright (C) 2005-2008 Reed Sandberg
This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.
The full text of the license can be found in the LICENSE file included with this module.