Bill Mell - Effective Reporting in a Client/Server environment

by Mike Hanson

Published 1997-09-01    Printer-friendly version

Bill Mell's presentation on "Effective Reporting in a Client/Server Environment" was an interesting perspective into the use of SQL and various other tricks to make reporting more efficient. Many TopSpeed developers are lulled into a false sense of contentment with Clarion's VIEW technology. As Bill illustrated, there are a few "gotchas" that we must be aware of.

First of all, he stressed the importance of reports in general. It seems that all the audience members shared the view that writing reports is not one of their favorite activities. Bill was attempting (although I suspect sarcastically so) to imply that writing reports can be a "fun" activity. I must admit that implementing a few of his tips would certainly keep your users happy, which certainly makes a programmer's existence more enjoyable.

Although Bill encouraged the use of third-party reporting tools, all of his examples used the regular Clarion Report template. With the intelligent use of embeds, you can extend the functionality of this template to suit your needs.

In this case, the use of ODBC was stressed. It is a good general-purpose platform for database access to the various SQL servers, and it supports the full SQL command set. This means that you can use Clarion's regular VIEW structures, or send your own SQL statements with PROP:SQL. In addition, the SQL driver can utilize server side operations (like summarization of data).

Of course, ODBC has its limitations too. It adds an additional layer to the equation, which can decrease speed. Also, some Clarion constructs are not supported (like PROP:Order). All desired accessed orders must be defined as keys in your database dictionary (even though they do not have to be in the ODBC database).

One of the inefficiencies of the CW Report template is that it does a "redundant" lookup into all secondary parent files. If all necessary fields are already #PROJECTed in the VIEW structure, then these are unnecessary. Bill's solution to this is to bracket the area where the lookups are performed using an OMIT statement. Assuming that the programmer is judiciously using "Hot Fields" to ensure inclusion of all needed fields in the View, this is a smart optimization.

The other major suggestion covered involved the elimination of the View usage, and sending SQL statements directly to the server. This must be done with an intermediate file definition to represent each of the columns being returned by the SQL call. All references to the View are eliminated through the use of OMIT statements.

This was a little bit of a heavy topic to cover in a short time, but overall the presentation was very informative and I believe that many of the audience members were awakened to the new possibilities.

It was obvious that Bill was very experienced with the technique, so he occasionally made the mistake of not relating to his audience's lack of experience in the area. However, I believe that most of the people walked away with some good knowledge. All of his presentation materials (including his sample program) are available at www.cwsuperpage.com. I would strongly suggest that you grab the file and take a look. It's very cool stuff.

Printer-friendly version

Copyright © 1999-2008 by CoveComm Inc. All Rights Reserved. Reproduction in any form without the express written consent of CoveComm Inc., except as described in the subscription agreement, is prohibited.

Clarion Magazine ISSN 1718-9942