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.

 

Top

Back

Top

Back