NAME

DBIx::Knowledge::Report - Handler routines to serve report builder form and report execution

SYNOPSIS

 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();

DESCRIPTION

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.

DETAILS

Linear convergent data sets

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.

Data point definitions

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:

TABLE_NAME

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

FIELD_NAME

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

APPEAR_ORDER

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).

HEADER

Friendly name of FIELD_NAME that will appear in the report builder and reports.

SELECT_SQL

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.

ORDER_LEGEND

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.

ORDER_BY_SQL

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')

or simply

ORDER_BY_SQL = base_hour

GROUP_BY_EXPR

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).

GROUP_BY_SQL

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'

NO_DEFAULT_ORDER_BY

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.

NO_REPEAT

To make the report look cleaner, within subgroups the name of this data point will only appear in the first line of many.

HTML_CLASS

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'

NP_ALLOW

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.

NP_HEADER

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.

Report Builder

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.

CSS

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.

report_data

All table elements are tagged with this class.

report_summary

The final tr element is tagged with this class if a grand total report summary is requested.

data_subtotalX

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 data_subtotal2, etc.

subtotal_label

td elements are tagged with this class only if they follow tr elements tagged with a data_subtotalX or report_summary classes. These fields normally won't contain data, just a label indicating this row is a summary row.

custom HTML_CLASS

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 HTML_CLASS.

Saving and editing reports

At the bottom of every report generated are two links.

Save report URL for later

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.

Further customize this report

This link will take you to the report builder with all parameters saved and pre-selected.

METHOD DETAILS

new (constructor)

 $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.

Upon error, server_error() will be called on $crud_output and undef will be returned.

select_list_from_table

 $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.

Upon error, server_error() or perror will be called on $crud_output and false will be returned, otherwise return true.

take_action

 $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').

BUGS

No known bugs.

SEE ALSO

DBIx::Knowledge, CGI::CRUD, CGI::AutoForm, SmartCruddy! http://www.thesmbexchange.com/smartcruddy/index.html, Cruddy! http://www.thesmbexchange.com/cruddy/index.html

AUTHOR

Reed Sandberg, <reed_sandberg Ó’ yahoo>

COPYRIGHT AND LICENSE

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.

cpanm DBIx::Knowledge

perl -MCPAN -e shell
install DBIx::Knowledge