FoxPro Developer's Conference '94

Session 215
Turning Data Into Information Using FoxPro and Foxfire!

Alan Schwartz
Micromega Systems, Inc.

The 'Paradox' of Data vs Information


What steps are needed to produce an ad hoc report?


Basic RQBE navigation


Data Summarization Options:


Developer Tips & Traps:


The Importance of Optimization

Index Tag Expression Selection Expression Says Optimizable
MAKE + MODEL MAKE + MODEL= "Ford" YES (but no finds)
MAKE + MODEL MAKE+MODEL="FordMustang" YES (but no finds)
MAKE + MODEL make+model="FORD" YES (expression is standardized for spacing, capitalization and aliasing)
CARS.MAKE CARS.MAKE = "Ford" NO (aliases in index keys defeat optimization


View Window

Table Creation

Structure Modification (adding/removing/renaming fields)

Creating Indices

Setting Index Orders

Setting Relations (different from a SQL 'join' condition)

Establishing 1 to many relationships

Selecting fields for output

Creating Filtered Views

SQL/RQBE View Window
Fast Column Selection for one-shot query Field Selection issues an awkward SET FIELDS command which doesn't work for multi-table queries, and must be reset so unselected fields can be accessed again.
Read-only results.

Better separation of editable from non-modifiable user environments

Resulting subset can be edited -- user's choice

BROWSE button produces editable view of the data

Automatic full inner join Inner join requires awkward and slow filter
No partial outer join (parent with no child does not appear in output) Easily and efficiently accomplishes partial outer join.
Easy aggregating results Requires programming except for 1-file TOTAL command
Cross-tabbing capability Not available
Won't run Reports with embedded aliases Won't run multi-table reports created by RQBE: no aliases.

Unfortunately, in neither environment is there any indication or control of whether queries are optimized or not.


FoxPro Report Writer

FoxPro's Report Writer makes creating a new report from scratch very easy, even if you've never created one before. There is a 'mini-wizard' included with the report writer called a 'QUICK REPORT', which allows you to get started in report design with any table or SQL result set.

Feature Good Not so hot
Report Preview Fastest feedback for hand-eye skill building. Code generation unavailable -- if you can't achieve a result with the report builder, you're dead ended. The only alternative is to hard-code the output.
Fonting/Styling Full complement of Windows features Lot of tweaking and dinking needed.

Tools lack degree of polish. (replicating styles is time consuming, properties at-a-glance needed)

Include UDF's right on report Unprecedented flexibility. No limits on what can be brought into a report. Good workaround for sibling file reporting. If you change the data environment, be sure you put everything back where you found it.

Can't debug UDF's in a report. Best to assure they're clean at the command line or in a program prior to inserting them in a report form.

Calculations Lots of non-procedural flexibility Too many ways to accomplish the same thing. (Calculations vs Report Memvars)
Report Break Options No limit on levels. Visually intuitive. Lots of good options for control of breaks. Differences between DOS and Windows; all features aren't directly transportable.


The Data Driven Alternative

By examining the metadata requirements of end-user querying and reporting, it is possible to devise a FoxPro code-based alternative to the surfaces described above. Ideally, the user should have to identify what they want to see, and not have to worry about how the system retrieves the results.

Foxfire! represents an attempt to achieve this goal in 100% FoxPro source code -- a tool we need for ourselves and to deliver to our customers in nearly every custom application we write.

Turning Data into Information Using FoxPro and FoxFire!
(c)1994 Alan C. Schwartz