SUMMARY
=======
User customization of reports at run time - selecting a particular set
of records, and selecting a particular sort order and subtotaling (or
"grouping") - is an important part of many database applications.
However, due to the lack of standardized concepts and tools supporting
this functionality, developers often find themselves forced to waste
time "rolling their own" run-time report customization facilities.
The purpose of this posting is twofold:
(1) to inquire whether anyone out there knows of any useful conceptual
frameworks or reusable tools (either development environments or add-in
utilities) which allow the programmer to offer the user a degree of
practical control over customizing database reports at run time; and
(2) to suggest some reasonable user requirements which a good set of
run-time report customization tools ought to satisfy, and report on the
progress and difficulties I have encountered in this area.
INTRODUCTION
============
We are considering rewriting an existing VB/Access database using a
better front-end (candidates include Delphi or PowerBuilder - other
suggestions are welcome). The users like to generate lots of custom
reports on-the-fly, and although the system is already in use for
production, it's expected to undergo many design changes over the next
year (new tables, fields and relationships). This means that the set of
fields you can filter or sort & subtotal reports on is sufficiently
large and unstable that the report filtering and sorting/grouping dialog
needs to be able to AUTOMATICALLY examine the database design and figure
out which fields to offer as candidate filters or sorting & grouping
levels. (This includes not only all the fields in the table being
printed, but also certain fields in the table's direct and indirect
"parents" - namely the foreign keys and indexes). In addition, once a
particular field has been selected for use as a report filter, it would
be nice to help the user set the filter's value by offering a drop-down
list of potential values the filter can take on, to save typing and
avoid typos. Finally, this drop-down list should be sensitive to any
filters higher up in the inheritance hierarchy which have ALREADY been
specified for this custom report: for example, if Offices have
Salespersons and SalesPersons have Customers, and the user is already
filtering on Offices.office_id = "New York", the drop-down list for
filtering by Customers.customer_id should only include Customers
belonging to SalesPersons working out of the "New York" Office. In
other words, the drop-down lists containing filter values for a given
custom report should be LINKED.
One of the most difficult components of this kind of run-time report
customization facility involves the creation of a kind of menu-driven
report query language, with the following characteristics:
(1) Each query is based primarily on a SINGLE table (the same table the
report's underlying query is primarily based on) - although a few extra
columns (indexes and foreign keys from direct and indirect "parent"
tables) can be added to the query output. For example, for a report
based on the Orders table, the user could filter or sort by Office (even
though the Offices table is only indirectly related to the Orders
table).
(2) The creation of the joins required to include these additional
columns from parent tables would be handled automatically by the
system. The user wouldn't need to explicitly request that the parent
tables be added to the query - filtering on an index or foreign key
field from a parent table would cause the system to automatically add
the necessary join to the evolving custom report query.
(3) For filters based on indexed fields and foreign key fields, let the
user set criteria via a data-aware drop-down list showing all potential
values for the filter (rather than the blank edit-text box most
graphical query editors provide). The collection of data-aware
drop-down lists for a particular custom report should be linked to
reflect the database's relationship hierarchy. For example, filtering
on Offices.office_id = "New York" should narrow down the lists contained
in the SalesPersons.salesperson_id drop-down list and the
Customers.customer_id drop-down list.
WANTED: SUPPORT FOR RUN-TIME CUSTOMIZATION OF REPORTS
=====================================================
In a database oriented toward "on-line transaction processing" (OLTP),
there's typically only one type of thing the user needs to be concerned
with adding, changing or deleting at run time: records, records and more
records.
On the other hand, in a "decision support system" (DSS), with its
emphasis on the customized presentation of data, the user would probably
find it convenient to be able to add, change and delete one more type of
thing at run time: namely, custom reports.
Most RDBMS implementations and data-modeling methodologies are equipped
with powerful, standardized notations and mechanisms (such as Structured
Query Language, or Entity-Relationship theory) for representing the data
created and destroyed by the user at run time (records) as well as the
"meta-data" created and destroyed by the programmer at design time
(tables, fields, relationships and rules).
But when it comes to designing and instantiating custom reports, there
don't seem to be any universally agreed-upon concepts or tools. Data
modeling only deals with real-world objects such as "Customers" and
"Orders", neglecting artificial objects such as reports. Development
environments may make it easy to specify the class of all "Customers"
(by defining a table) at design time and then instantiate individual
"Customers" (by entering records) at run time, but most report design
facilities encourage the programmer to prematurely INSTANTIATE
individual custom reports at DESIGN time: it's hard to design a report
without committing to a particular underlying query (pre-determining the
report's dataset) and a particular set of bands or sections
(pre-determining the report's sorting and grouping levels).
AD HOC APPROACHES TO RUN-TIME REPORT CUSTOMIZATION
==================================================
In practice, an acceptable degree of report customization can often be
achieved simply by splitting a "report" into the following three
components, and allowing the user to set the first two at run time:
(1) a dataset or "filtering" component (which the user can change, if
the programmer exposes the parameters or SQL property of the custom
report's underlying query to the user via a some kind of run-time
dialog);
(2) a "sorting & grouping" component (which the user can also change, if
the programmer exposes the report's sorting & grouping levels via a
run-time dialog); and
(3) a "layout" component (which usually needs to be specified via a
graphical report layout editor available only at design time, making it
a bad candidate for run-time modification).
Many programmers, myself included, end up creating some kind of
"home-grown" facility to give the user some control over report
filtering and sorting & grouping at run time. But we shouldn't be
spending time extending our development environment; we should be
solving our clients' business problems.
SUGGESTED CONCEPTUAL FRAMEWORK FOR RUN-TIME REPORT CUSTOMIZATION
================================================================
A straightforward approach to developing a conceptual framework for
run-time customization of reports would be to simply extend the domain
of traditional data modeling to include new entities such as "Reports".
This approach has the advantage of building on a methodology which is
already familiar to the database programming community, and once a model
of "CustomReports" is developed, it should be easily mappable into an
RDBMS implementation, the same way models of "Customers" and "Orders"
are mapped to the RDBMS.
A simple data model for custom reports might start by defining the
following TABLES and fields (with primary and foreign keys indicated by
"PK" and "FK"):
Table: TABLE
------------
table_id (PK)
Table: FIELD
------------
field_id (PK)
table_id (FK)
Table: REPORT
-------------
report_id (PK)
table_id (FK)
Table: FILTER
-------------
filter_id (PK)
table_id (FK)
field_id (FK)
Table: FILTERING
-----------------
filtering_id (PK)
table_id (FK)
Table: FILTER_USE
-----------------
filter_use_id (PK)
filtering_id (FK)
filter_id (FK)
logical_connector_id [enumerated domain: AND, OR]
comparator_id [enumerated domain: =, <>, >, <, >=, <=]
value
Table: CUSTOM_REPORT
--------------------
custom_report_id (PK)
report_id (FK)
filtering_id (FK)
In the absence of diagrams, the relationships among the tables
(indicated by the foreign keys above) could be expressed verbally as
follows:
(1) Every FIELD is part of a TABLE.
(2) A FILTER applies to a particular TABLE and is based on a particular
FIELD (perhaps from a TABLE other than the one being FILTERed).
(2) A FILTER_USE represents a particular instantiation of a FILTER.
(3) A FILTERING consists of a set of FILTER_USEs.
(4) Every CUSTOM_REPORT is based on particular REPORT.
Every CUSTOM_REPORT includes a FILTERING.
A FILTER_USE is like a fragment of an SQL where-clause: it compares a
field to an actual value using a comparator (such as =, > or <).
Using a logical connector (AND or OR), multiple FILTER_USEs can be
strung together into a FILTERING, which is like a complete SQL
where-clause.
In this simple model, a CUSTOM_REPORT is created by combining a REPORT
(layout) with a FILTERING (a set of FILTER_USEs). (Report sorting &
grouping levels are not addressed in this abbreviated model.)
Note that the TABLE, FIELD, FILTER and REPORT tables can be
automatically populated by applying reflective techniques to the
database structure at design time, while the FILTER_USE, FILTERING and
CUSTOM_REPORT tables are manually populated by the user during the
actual report
...
