Reporting
for End-Users: Canned or Fresh?
By Daniel Levin, President of Liveware Publishing Inc.
One of the most difficult aspects faced
by software development staff when supporting database systems is the continual
flow of requests from end-users for reports. This applies to both commercially
published and custom-written applications equally, and I have yet to find
developers eager to take on this responsibility. One can hardly blame them since
prioritizing, scheduling and fulfilling these requests – and the probable
follow-up modifications and support – are disruptive to other development
projects.
During the initial development phase,
software engineers will identify dozens, and perhaps hundreds, of pre-defined,
“canned”, reports. These reports will make their way into the
application’s menu structure where end-users can access programs to generate
them and use the information for some clearly defined tasks. Yet most systems
include dozens of tables with hundreds of fields. No number of canned reports
can anticipate every potential combination.
Elements of Reports
Multiply the potential combination of
tables and fields by the myriad ways these elements may be processed within a
report and the task becomes even more challenging. If one thinks of a report as
processing stored data then presenting the results, just about every report will
include some combination from among the list below. (For brevity, the list is
coded and I will refer to them by those codes for the remainder of this essay.)
(A) joining or linking multiple tables
to extract fields for the result set
(B) sorting the records in the result
set
(C) defining record groupings to
generate sub-totals and breaks in the report presentation
(D) filtering the result set to include
only the desired records
(E) deriving calculations and totals
from the stored data
(F) presenting the result set contents
in a banded form
That’s a lot of responsibility for a
report and it is no surprise that even most programmers struggle with report
programming. For canned reports, developers will define all of the above, and
then allow users a variable level of control of (D) with, perhaps, lesser
control of (B). With great programming difficulty, developers can give end-users
very limited control of (A) and (E) – and the related fields to include in the
final report – but only at the expense of coding complex variables into the
report program.
My experiences have shown, however, that
a canned report can fail an end-user completely for want of a single data
element or the option to control any of the items above. In many canned reports,
where usage patterns can be anticipated, this does not cause a problem. Yet
changing needs make canned reports obsolete long before the source database, so
greater flexibility in reporting is necessary to maintain a high ROI on any
database system.
Opening Up Canned Reports
Into this breach come reporting tools,
a.k.a. report writers. Well-designed reporting tools allow complete,
simultaneous control of (A)-(F) so that the entire report program can be defined
within a single set of instructions. Most commercial report writers for Xbase
tables do so (to a greater or lesser extent) as does FoxPro’s internal
reporting tool. (It should be noted that FoxPro does require the definition of
(A) and portions of (B) and (C) prior to submission to its report designer.)
Report writer generated reports excel
versus canned reports where greater end-user manipulation of some portion of the
report instructions is too varied to anticipate. That is to say, realistically,
most of the time. A client of my consulting firm, active in the ski trip
business, was using a popular Xbase application to track group sales. This
package included many pre-defined reports, including many for analyzing sales
activities. None came close, however, to summarizing the variety of steps in the
sales cycle for his business. Instead, we designed a short series of
reports that directly addressed each sales rep’s progress versus goals.
End-users employ reports to fulfill
their needs in context of their work. Reports guide their actions, support
decision-making processes, communicate relevant facts to others, and so on.
Contexts change, often in subtle yet important ways, as does the data from which
the reports are generated. For example, the acquisition of a new, large contract
often requires special management and associated reporting. The existing
database application may nonetheless allow tracking of all the necessary data
elements. Report writers would let end-users adapt the reports to these types of
shifting contexts and data.
In order to assist end-users toward
developing and maintaining reports, many software publishers provide or
incorporate reporting subroutines. In these tools, users can often control a
little of (A), most of (B) and (D), and some of (C), (E) and (F). I recall using
a commercial Xbase application’s internal report writer. I could do some
simple columnar reports, but I would hit wall with it when the user’s context
required a function the slimmed-down report writer didn’t offer.
I admired the effort this publisher and
others had made to direct the end-user, since reporting can be complex and most
cannot handle it unaided. But I discovered that these efforts are misguided. The
advantages of a “limited” report writer were outweighed by its restrictions.
Ultimately, other end-users and I had to learn to use a full-control report
writer. The publisher later agreed and abandoned their reporting tool in favor
of a licensed full report writer in the new FoxPro application.
It took a long time to learn how to use
all the capabilities of a full-control report writer. Linking tables (A), in
particular, was a complicated process, even on a relatively small database of a
couple dozen tables. (B), (D) and
(E) were not nearly as tough to master, and know-how with (C) and (F) came with
practice. Many end-users I’ve trained have had similar experiences.
Fresh Benefits
The long-term payoff for this learning
curve has been exceptional for all involved. Understanding how to put to work
the copious information stored within an organization’s databases are critical
skills to master. Those who learn how – and a relative few have taken the time
to do so – become highly productive members of their teams. For example, a
nationwide healthcare provider developed a custom FoxPro system for tracking
patient referral, intake, progress and outcome data. Several departments within
each of dozens of independent sites contributed records to the database,
covering physician and hospital referrals (marketing), patient treatments
(care), and costs of services (finance).
By the time I presented the first of
many regional seminars on use of a full-control report writer, the databases at
each site were fully populated, but little of the data was put to use to focus
marketing efforts, compare outcomes among the facilities, and find the most
profitable specialties. When the trainees discovered that a reporting tool could
replace their manual efforts to compile the information, it was quite an
eye-opener for them!! They had been producing basic canned reports, then
entering totals into spreadsheets to send to regional and national
administrators. That effort was so time consuming they had no chance to use the
information any other way.
After streamlining mandated reporting
processes with the report writer, the participants began to use it to develop
form letters to the top referral producing doctors, analyze cost per day for
various patient categories, and highlight unusual outcomes to improve
assessments, and so on. Those individuals became the power users at their
facilities who, in turn, passed their knowledge to others. Not everyone could
master all the nuances of developing reports, but many more could learn to
modify existing reports to meet new requirements.
The original developers at this firm’s
national headquarters could spend the time to discover and design many of these
reports both prior to and after the system’s distribution. That would demand a
much greater understanding by those developers of the end-users disciplines. Few
MIS people have the background or education to understand the rigors of such
varied departments as marketing, finance and nursing. It is quite a challenge
just keeping up with IT issues!!
End-users comprehend their own fields of
expertise, and the data in their tables as well. The knowledge they generally
lack is how to get the information they need out of the database. This takes
knowledge of relational data structures and other fairly complex concepts. Yet
which is easier: training IT personnel on varied professional fields or teaching
professional what they need to know about databases? My experience with hundreds
of companies and organizations suggests the latter approach.
A typical training program goes
something like this. End-users are trained on some basic principles of
relational databases, such as the difference between one-to-one relations and
one-to-many. They also learn how to break a report into bands and how to derive
new information from stored data. After gaining some experience with a report
writer doing simple reports, IT identifies those individuals with a knack for it
and takes them to an intermediate level.
The MIS department and the “power
users” share knowledge on changing needs and distribute production reports and
report templates. Power users help new departmental staff and other report users
learn to access and modify existing reports and templates. Developers help power
users to acquire more advanced techniques, and perhaps address more complex
reporting needs using MIS resources.
Conclusion
This model frees programmers from
tedious reporting tasks, where they often don’t possess the specialized
knowledge, and empowers end-users to shape their own use of the valuable data.
And best of all, this approach has been tested and IT WORKS.
|