Session E-FIRE

Beyond Ad Hoc Reporting:
Getting Results with Foxfire!

Alan Schwartz
Micromega Systems, Inc.

Managed Query Environment: A Definition

The steps involved in transforming a set of information-based requirements into an application that genuinely solves a set of business problems have been described for over twenty years. Despite numerous theories and technologies set forth to streamline the process and improve the result, the real-world practice of application development is still largely unimproved upon:

At the successful conclusion of this process is a "system", which, if successful, takes its place much like the tables, chairs, and filing cabinets as a part of the working environment. However, a valuable but hidden asset has been created at the same time: the data captured at the foundation of the system. Although numerous reporting rerquirements were undoubtedly designed, programmed, and documented (e.g. hard-coded) during application development, there is a wealth of additional knowledge which could be harvested if the data could be surveyed and explored in ways the users were unable to define in advance.

Thus the term "ad hoc" querying and reporting has come into common usage in the context of database applications. In fact, a whole new class of applications -- Data Warehouses -- are being created specifically to provide this broad, unstructured access to data for management purposes. Whether for a specific application or a general-purpose data warehouse, the usual approach is to give knowledgeable users a general purpose querying report writing tool that allows them to make the system’s data visible without requiring the services of a programmer.

When such a facility works as intended, users will eventally enter into the world of the data driven by their own curiosity or the desire to answer a business question of importance to them at the time. One set of answers leads to further questions, and information is developed in an iterative fashion. Each exploration paves a path of further insight into the informational content of the data, and serves to extend the horizon of its usefulness.

The term "easy to use" has become so overused in the software industry that it has dissolved into a near-meaningless cliche. But it is clear that users won’t drive the information discovery process unless the querying environment environment is

obvious enough to lower the price of entry to a carefree impulse,

forgivinging enough so the user doesn’t experience frustration or inadequacy, and

fast enough so the iterative cycle of information -- in which each answer stimulates the next question -- can flow freely.

Ease of use is much more significant issue than set of tacked-on interface tricks; if it is not built into the core of what is presented to users, the ad hoc reporting capability will remain merely a checkbox on a feature list, but it will never fulfill its potential to serve as an informational resource.

When we consider current trends in organizational development, the managed query environment is seen in an even larger and more significant context. As organizations flatten out and middle management is reduced in both headcount and authority, decisions with strategic implications are being driven down the hierarchy toward line responsibility. With this delegated responsibility comes a greater need for information on the part of a wider circle of workers, in order to provide a basis in facts for management decisions.

It is often impossible for programmers to understand and a source of considerable resentment or cynicism why users are unable to elucidate their requirements in advance during the requirements definition phase. The explanation has several parts.

First, circumstances change. It is often changes to the business environment that drive the need for information. The deregulation of the telephone business, for instance, causes the telecommunication service providers to make dramatic changes in the way they compete for new business, define products, provide service, and process revenue. The business model in which they operate is changing ever more rapidly, and shows no sign of abating. As the world economy becomes more globalized, these rapid changes are impacting nearly all businesses. Since it is impossible to predict next year’s requirements, it is impossible to design next year’s queries and reports.

A recent sketch by David Vaskevitch, Microsoft’s VP for Enterprise Development, illustrates the core of Business Process Reengineering as pushing decision-making down the enterprise pyramid, while pumping information back up like this:

Information and Authority in the Business Process Reengineering Movement

Second, appx 75% of the population is composed of people who are explicit, rather than intuitive, thinkers. Explicit thinkers find it nearly impossible to elucidate a design where none existed before. Present a white canvas, and they are not stimulated to creative thinking about constructs which might solve a problem. However, show these folks your solution, and they’ll be entirely capable of picking it apart piece by piece, and enumerating its shortcomings in gruesome detail.

This attribute of human psychology infuriates and frustrates many programmers. In fact, it is because most programmers are intuitive thinkers, and one of the classic (and incorrect) assumptions of intuitive thinkers is to believe that all people’s thought processes are similar to their own.

When a developer completes an information system, the entity-relationship diagram for that system is so intimately known that it seems stitched onto the inside of the developer’s eyelids. She can simply close her eyes and envision how the information is arrayed in a neat and orderly fashion. This is an obvious example of intuitive thinking. To the system’s users, however, the "system" is comprised of its tangible representations: the menus, screens, and pre-defined report options. The informational potental of the relational database architecture is invisible to them.


For both these reasons, it is imperative that a managed query environment present the intuitive as obvious, and invites the user to interact in ways that produce tangible results as quickly as possible.

The Great Bell Curve of Computer Fluency

No matter how slick the interface, the "one-size-fits-all" personality of a general-purpose query tool presents an ocean of options when a user wants a guided pathway to the only result that matters -- the answer to the question of the moment. Toward this end, questions whose answers which might seem obvious to a developer are just in the user’s way.

What’s lacking in a general purpose query tool is context -- the specific knowledge about the database that would expedite the user’s path to the bottom line without extraneous explanations or frustrations.

In a managed environment, everything the developer knows about the application can be embedded into features of the query environment to simplify the user’s task. This may include:

This requirement set strongly suggests a query environment made up of a 3-layer sandwich:

User Interface
Definition Layer


Data Access


Since all users will not be the same or have the same requirements, it can be anticipated that the intermediate layer would include definitions of users, any security restrictions upon their data access, the feature set they should have access to, and default environment information like printer definition. Of course, all this should be data driven as much as possible.

Finally, for maximum effectiveness, the environment should be unified -- a consistent, single metaphor should be presented which contains the entire querying universe of the user. Techniques that repeat for different tasks (like filter building for labels and spreadsheet extracts) should be the same, and ideally not have to be repeated if the user changes the desired output from one type to another in the middle of the task.

Wizards and Experts are becoming very popular and common in commercial software as a technique to guide the user to a result which otherwise requires a series of steps that might present some difficulty, or with which the user is unfamiliar. A Wizard or Expert is a set sequence of modal dialogs which take a user through a group of definition steps, each panel presenting a single question.

A group of Wizards cannot add up to the overall useability of a query environment. They require the user to know in advance the type of result that will be required, in order to select the "right" wizard for the task. Further problems arise when a user has to reopen an existing a task to modify, extend, or clone it -- which is very common in a query environment. If it is possible to reload the Wizard-built task at all, it is difficult to get a consistent overview of all the choices made during the previous wizard session. Finally, the constructed component is rarely distributable for use by others, or for a different environment than the one in which it was built.

The Foxfire!™ Approach

When we first saw RQBE functioning in FoxPro, it seemed the threshold of what was possible for data retrieval in a PC environment set had been raised by several orders of magnitude. However, when we observed what our own customers and end users were able to produce, what they weren’t able to do, how long it took, and what problems they incurred along the way, our enthusiasm cooled considerably.

Ultimately we concluded that FoxPro’s incredible potential as a platform for applications, and in particular the power of its Rushmore query optimizer, would go largely unfulfilled if there weren’t a more comprehensive and straightforward solution for end user queries. Having built several ad hoc query environments previously, we set out to create a managed query environment for FoxPro.

The design goals were rather simple:

Foxfire! introduces itself to the user with a list. The entities on the list are called Requests. A Request is a combination of a query and its formatted result. Foxfire! manages these two components as a unit, which simplifies things from the user perspective. (While in reality it is possible to need one query to output in many formats, or many queries to feed their results through one format, these are relatively exceptional, and the simplifying assumption of a Request as a unit of work is too clear to pass up.)

The list states the name and description of the Request, the type of output, the owner, and the date of creation. In character mode, or in FoxPro 2.6 Windows where lists don’t have support for tab stops, this content makes for a wide, edge-to-edge display. While not reflective of the "small and tidy" Windows dialog box aesthetic, it tells you at a glance what makes the Request distinct from the others. And since the list definition expression is passed in through Foxfire!’s configuration mechanism, the list contents (and the column headers above the list) can be easily modified by the developer without changing any source code within Foxfire!

The short name is significant only if the user will need to know what files the Request is attached to. For instance, an Excel spreadsheet or customized report layout (.FRX) will have by default the same name as the Request short name.

The owner of a Request is most significant if Foxfire!’s security features have been activated. Under those conditions, the right to modify a Request is restricted to the Request’s creator. This insures that the rationale behind a Request isn’t subtly modified by another user, or that a change creeps into a Request unknown to its creator.

Again the Request Editor presents an interface design that is a bit unconventional when compared to the customary Windows aesthetic. But it is designed to solve a shortcoming of many, if not all, commercially available query tools: tell the user exactly what will happen if they press the ‘Run’ button.

We have been frustrated by numerous query interfaces that force you into a cumbersome survey of several levels of dialogs just to find out what makes one query different than another, or to learn precisely what your query is going to do.

Even the state-of-the-art WYSIWYG query tools make you dig, infer, or guess. Even when you’re looking at a live-data preview of your query, it is often difficult to know what the sort order or filter rules are by examining a small sample of the resulting data.

What’s significant here is not what we ask, its what we don’t ask. Foxfire! presents the user a list of Data Items -- defined within the management layer by the developer, in the user’s own terms, of course. The user simply picks which ones they want, and the order in which they will be displayed.

What’s significant is what the user isn’t asked: what tables does the data come from, and how is it joined. Foxfire! already knows that, and can join Data Items from tables that are only linked indirectly -- even if it means drawing the intermediate tables into the query -- behind the scenes.

Filter building is at the very heart of query creation. In Foxfire! we wished to create an environment for "in place" editing of a filter line. This was quite difficult in FoxPro. The alternative, published numerous times as well as included with the FoxPro sample applications, involves building a filter line at the top or bottom of the screen, then pressing a <Move> or <Insert> button to place it on the filter list. We found this clumsy and counterintuitive. Editing-in-place within the context of the complete filter, spelled out in common language, is far easier to understand.

The order in which rules are arranged may be significant to the user just for clarity’s sake, even if their arrangement is arbitrary from the point of view of query execution. Foxfire! allows you to rearrange the order of rules to your own liking. (Of course the OR operator and parentheses are supported and respected -- these can be affected by reordering the rules.)

One thing that doesn’t make sense to the casual end-user is piling on top of the filter rules the "matching key join conditions" required by SQL syntax in multi-table queries. These just don’t belong in the same list from the user’s point of view. Key-matching expressions are required to define how the tables are "virtually joined", and are a totally different mechanism from the rule-based filters "Invoice Date After 05/31/94".

Including rows in the result set based on the non-existence of a matching key is a common, everyday occurrence in many datasets. FoxPro has created an artificial distinction about this type of query because its particular flavor of SQL syntax doesn’t easily support it.

It is common for a user to want to "tack on" outer join records to an existing query. The clearest way to present the interface is not to teach the user what an "outer join" is, but to simply pose the concrete question "Include CUSTOMERS with no INVOICES?" with a checkbox.

Clearly, an "Outer Join Wizard" is not the best way to deliver this basic reporting capability. The user shouldn’t have to know in advance what the "Outer Join Wizard" is, much less that they would need to use it.

Outer Join Reduced to an Explicit Pair of Context-driven questions that cannot be mistaken

One of the most powerful filtering features is the ability to specify a set of "ask at runtime" behaviors. The simplest of these is just to fill in criteria -- like start and end dates -- when the Request is executed.

Of course to get a good fit to diverse requirements, there are many possible variations on a theme. Some developers want to display a whole list of possible filter rules, and let the users fill in only the ones they want to use. The filter rules left empty should be ignored during the current run.

Other developers want to let users add or delete rules, or change the relation operator. In this case, the entire filter builder dialog becomes the "ask-at-runtime" interface. Of course, the developer’s desired restrictions are placed on the interface, so the integrity of the query can be maintained.

There is no word in the database arena, or software in general, that is as heavily overloaded as ‘Group’. To a SQL technician, group has a definite meaning: the specification for the common data key upon which numerous records will be "rolled up" and summarized into one record. This feature only applies to summary reporting, which means that casual users often get surprising results in RQBE, because the effect of a "Group" will not likely be guessed correctly by the average user.

However, when a novice user uses the term group, they will most likely be talking about something completely different -- the behavior of the report layout to create a "group break" and a visual block when some key data changes.

In FoxPro, the relationship between sorting and group breaks is particularly vexing. Despite the fact that the grouping is utterly dependent upon the sort order (if you create an order that does not start with the group breaks defined, you get one detail line per group break, which is ugly and wasteful) group breaks in FoxPro are defined in a completely different place than sort orders. (Sort orders are set in RQBE, whereas group breaks are set in the report bands of the report layout.)

Foxfire! puts these specifications together, where the natural relationship between sorts and groups can be reinforced visually.

If the user actually intends to create a summary report, there is an implicit relationship between all the selected columns of output. A column must be either part of the identification of the row, or it should be an aggregation comprised of all the rows in the group. Any other case is positively misleading.

For example, if we list ‘Cars.Make, Cars.Model, Cars.Year, Count(Cars), and Sum(Cars.Purchase Price), its clear that we want to see a count for each combination of make, model, and year, and a sum of the purchase price of all cars in each group. But if we add Cars.Mileage to the field list, what is that to mean? RQBE will dutifully deliver a car’s mileage into the column, but which one? According to SQL, this will be detailed data from one of the records summarized into each row. However, in reality this is almost totally meaningless. By imposing a simplifying assumption, Foxfire! helps assure that the user gets what they mean.

One of SQL’s power features is the ability to create filters on the aggregated result of a group query. In the above exmaple, we might wish to report only combinations of Make, Model and Year that represented at least 3 actual vehicles, or only those groups where the aggregate purchase price exceeded $5,000. This type of filter requires aggregating the result first, then filtering again to produce the final output. The SQL technical term for this is derived from the syntax of the clause that specifies it : HAVING.

Again, this makes no sense to an end-user at all. A filter on a group is just that -- a special type of filter. Foxfire! feels the user is better served by calling that powerful filter just what it is: a group filter. Why create a separate interface for it? Instead, it is far simpler to treat it like other filters in the same familiar inteface, with a separator.

Near-instant feedback is a tremendous asset when the user presses the ‘Run’ button. It confirms that the data selection options are indeed producing what was intended, and sets the stage for further refinements to the query process. The ability to refine, retry, refine some more, and retry again quickly is one of the most powerful self-paced learning techniques available in software.

However, if query results take so long to deliver that the user forgets exactly what was the train of thoughts that brought them to the current point, the continuity of retry and refine is broken. If each pass causes an interminable display of the hourglass, pressing the ‘Run’ button one more time is likely to cause hesitation and irritation rather than enthusiasm, curiosity, and confidence.

In FoxPro, the Rushmore optimizer provides the power to turn out result sets from very large and complex data sets with near-incredible efficiency. However, Rushmore’s rules for determining how and when to optimize execution are cryptic and unobvious. A casual user can quickly turn a one-second RQBE query into a one-hour query without any visual feedback as to why the results are so drastically slower to produce.

Case sensitivity and compound indexes are two of the reasons why optimization can be quickly undermined in FoxPro. Since we consider fastest possible results to be an inherent useability feature of the querying environment, Foxfire! takes great care to construct its SQL so that the maximum benefit of FoxPro’s Rushmore is always obtained. The developer can easily specify that the filter value entry is uppercased, validated, or formatted properly to match the underlying index expression -- all automatically and transparently from the point of view of the user.

For most small to medium-sized database applications, Foxfire! can be installed and producing queries and reports in minutes. The Setup Wizard makes short work of the most basic level of integration into an application.

However, your users will get much better results out of Foxfire! with far less effort when you tailor it to precisely meet the unique requirements of their application. This section describes some of the ways you can make Foxfire! fit specific requirements.

The customization process embeds some of your knowledge of the database into the application itself, where it serves as a guiding hand to users as they build their queries and reports. In most cases, each enhancement you provide is leveraged, because Foxfire!'s architecture allows it to be used in many different combinations with other customized features to produce results. By contrast, a "hardcoded" custom report provides no leverage whatsoever. It only does one thing, and the "expertise" within it cannot be reused elsewhere without additional effort on your part.

In summary, investing a relatively small amount of your time tailoring Foxfire! as described here raises the perceived value of the delivered application much more, and with far fewer hours expended, than any other single feature you as a developer could build for them from scratch. A thoughtfully tailored Foxfire! installation opens a big window into the data for users. Through that window, users gain the ability to turn their data into information on their own, and they experience firsthand how valuable that information can be.

Although Foxfire!'s Developer's Edition comes with full source code, it has been designed so that most types of customization can be performed without changing the main programs. This is not only easier, it delivers a more maintainable result, since your customizations are partitioned and therefore protected from future updates to the software you may receive from us.

The following options are discussed in more detail here:

The most obvious type of customization is the way available data items are presented to the user. The data item list that appears in the Data Item Selector, Filter Builder, etc., serves as a catalog of the information contained in the application. The clearer and better organized it is, the easier it will be for the user to generate results. Some of the ways of customizing data items (all from within the Data Item Editor) are:

Construct the best possible Data Items

Type of Output Column Header
Character and Memo Left
Numbers Right
Dates, Logicals Center

Foxfire! allows you to simplify query and reporting of complex datasets by limiting a user's view to defined subsets of the files, fields and relationships that make sense together.

Enforce nearly any security requirement with Preference sets. Foxfire!'s Preference Sets enable you to control access to data and nearly all Foxfire! features.

Foxfire! reports are generated from templates which you can customize and control, as follows:

You can use Foxfire! to pre-define powerful "canned" reports which appear in the user’s list ad hoc reports. This makes your application more useful, saves you significant time, and allows you to incorporate powerful reporting techniques that users normally couldn't accomplish themselves, even with Foxfire!.

Establish default printer behaviors so each user or group gets the output they are accustomed to, but individual jobs can have override printing instructions. Full support for network queues and local printers in DOS and Windows.

Foxfire!'s incorporates a two-module architecture. You can add source code or change behavior-controlling memory variables in a smaller configuration/customization module (FFCONFIG.PRG) while preserving the integrity of the larger, main report program (FOXFIRE!.EXE or .APP).

All changes in FFCONFIG.PRG are partitioned from the Foxfire! core codebase, so you don't have to rebuild Foxfire! if you make changes here. Also, if you receive a maintenance update, your changes to FFCONFIG won't be overwritten.

There are, of course, tons of features and configuration tricks and techniques for tailoring Foxfire! to meet the variety of user requirements. It is not the purpose of this essay to list all of these.

We have observed, however, that when a user is turned loose in an environment carefully orchestrated to provide a "safe" place to experiment, where all the things they try seem to work the way they expect the first time, quickly and without penalty, they will begin to become self-sufficient very rapidly. Requirements that formerly required a programmer to engage in the most unproductive sort of work -- churning out variations on a theme of a report -- can now be delivered easily by users themselves, freeing programming resources for more important development work. As time goes by, the query environment grows in importance as an important working tool in the environment.

Of even more value, the iterative learning process of interacting with the data reveals new information, that wouldn’t have been otherwise discovered. This is the big hidden payoff of the managed query environment.