To download the files for this article for your own use, click here

One of the most common needs of an application is the ability to produce columnar listing reports from the underlying data. dBASE provides a powerful Report Designer, but for the beginner this can be a daunting experience. Even for the experienced developer, using the Report Designer can be time consuming and difficult. Often developers find that some elements of report design can be achieved more quickly in the Source Editor than with the designer.

There is of course the dBASE Report Wizard, but the problems with a wizard are that it is a one way trip through the steps, and start from scratch each time. For the beginner this can be a good head start, but in order to change a wizard produced report, it’s back to the Report Designer.

Then there is dQuery, a powerful data tool which has amongst its capabilities, the ability to produce Reports. Many aspects of dQuery report design are very good. The hinge pin of dQuery however is the datamodule, and any report produced with dQuery is dependent on its associated datamodule. While the use of datamodules is very worthwhile, many developers prefer to use freestanding reports which will run on their own.

One of the main problems dBASE developers have faced, is how to provide the users of their distributed applications with the ability to produce reports. No one would expect the end user of an application to have to buy the full dBASE development product in order to do this. One way would be to ship a copy of dQuery, but this will be seen as a third party product which adds to the cost, and cannot be integrated in terms of look and feel with a distributed application.

What we need is a quick method of producing dBASE reports, which :

• is easy for the end user who has limited knowledge of the subject
• is useful to the developer as a quick way to design individual reports (often a dull and repetitive task)
• allows an easy method of User Interface integration with a distributed application
• can be programmatically driven in the background if required
• can produce reports in the runtime environment of a distributed application

Quick Reports 3 is intended to fulfill those needs.

Back to index

In 1998, Ken Chan presented a paper at Icon 98 demonstrating Dynamic Report Layout, and the production of a “Choose-Your-Columns” Report. This concept was picked up by Jørgen Feder and Bob Rimmington who collaborated in the production of the “User Report Generator”. Bob continued to develop the URG, and in May 2000 published an article in the 9th edition of the dBulletin.

In the middle of 2002 my interest in the URG was sparked, partly by the concept of an application which wrote dBASE dBL code, and partly because the potential benefit to both the developer and end user in ease of report generation was obvious.

A complete redesign of the URG code base was embarked upon, although elements of the user interface design were maintained to ease transition for the existing URG user base. This new version is QR3.

Back to index

The main part of QR3 itself consists of the QR3 custom control QR3.cc and its resource dll, QR3Res.dll. All of the functionality of QR3 is provided by this custom control, and is independent of user interface. Based on a paintbox control, an instance of QR3 can simply be dragged and dropped onto a form, and is then available for use.

The supplied user interface, QR3.wfm, doubles as sample code demonstrating how to interface with QR3.cc.

QR3 makes considerable use of custom controls, and these are available in a control library file, QR3Controls.cc. These controls can be dropped onto a form, and provide a quick link with QR3.cc for many key functions. This is intended to make it very easy to surface a subset of QR3 options within another application.

The creation of a flat table for reporting on multiple tables is provided by Quick Query which is also a two part product, with a Query engine — QQ.cc and separate user interface — QQuery.wfm. An instance of Quick Query can be dropped on a form, and can also be driven programmatically.

The main purpose of QR3 is to provide the end user, and the developer alike, with a quick and easy way to produce dBASE report code for listing type reports. Report code generated by QR3 can be reloaded and re-used by QR3, making it easy to produce variations on existing reports.

In addition QR3 has the ability to produce .html and .pdf output.

Back to index

The QR3 user interface is designed to be easy and intuitive to use, but comprehensive help is provided. To see how easy it is to use QR3 to produce a simple report, try the following:

• Unzip the zip file from the top of this article into a new directory
• From the dBASE Plus Navigator, run QR3.wfm
• Click on New Report
• Select the DBASECONTAX Alias
• Click on the Table section button and select Employees.dbf
• Click on the Fields section button and select both available fields
• Click on the Report Save button and save the report
• Click on the Report Preview button

To see the report code you have just created, open this report in the Source Editor. Could you have produced this stand alone report as quickly as this, in any other way ?

Back to index

Much of the use of QR3 User Interface should be intuitive, but it is probably worth running quickly through the various sections of the user interface, and discussing relevant issues which might be of interest to the developer.

QR3 opens with a choice of three quick start options on the top menu button bar, which are self explanatory. The interface is a multi-page form, and the menu buttons switch form pages.

There are two banks of menu buttons which form the Basic and Advanced menus, which are selected by the radioButtons in the top right corner. Switching between these menu banks will take you to the last used section on each bank. This makes it easy to alternate between sections on different menu banks.

The selected menu button is highlighted by covering it with a mobile Highlight Button which provides a colour cue for the current section, and also prevents the underlying menu button being clicked again until another section is selected. Examine the menu button code in QR3Controls, and QR3.wfm to see how this has been achieved.

Every time you change something in the QR3 UI, the corresponding QR3.cc property is updated. Every time you select a UI section, the controls on that form page are updated using the corresponding QR3.cc property values. This model removes one of the headaches of application design, that of updating controls affected by a change in another control. Since QR3.cc doesn’t use or rely on any UI properties to do its job, it doesn’t matter that controls on out of view pages might not be up-to-date. They will be brought up-to-date in one code location only, just before the user sees them, in the onClick() code for the menu button, just before the form page change occurs. Have a look at the function code for the menu buttons in QR3.wfm to see how this is achieved. In fact you will see that most of the UI functions are menu button page change code. The setting of QR3 properties is done from the individual page controls themselves. This design gives clean and uncluttered code, which is easy to maintain.

Back to index

The Path section allows the choice of an existing Alias from a QR3.cc array created automatically on opening. The use of a data Alias is preferred, since the reports produced will work regardless of the actual directory location of the data. This makes for easy transfer of a report, designed on one machine, to another system. Alternatively browse for directory and set to current directory options are provided.

Back to index

The Table Section uses the first of many complex QR3 controls, the QR3TablePicker control. This control can simply be dropped onto a form, and the background colour of the control set. This makes it very easy to use these controls in your own application, and match them to the colour scheme used.
 

	this.P09_QR3TABLEPICKER = new QR3TABLEPICKER(this) with (this.P09_QR3TABLEPICKER)     onOpen = {; this.SetColoursTP("0xEBFFFF")}     left = 188     top = 128     width = 225     height = 221     pageno = 9 endwith

Every time the form’s page is changed to the Table Section, the contents of this control are updated, initiated by a single line of code, providing an object reference parameter for the instance of the QR3 object with which the QR3TablePicker control has to interface:
 

	form.P09_QR3TablePicker.InitialiseTP(form.QR3)

This design means that if you wish to mix the programmatic driving of QR3 and user interaction you can. When you return to this form page, the control will always reflect the current QR3 Table setting regardless of how or where it was set.

As an aside, all of the complex QR3 controls have an initialise method. I found that with several controls all having methods with an identical name, occasionally dBASE became confused, and ran the method for the wrong control, presumably already in memory. That is why the Initialise() and SetColour() methods for this control are differentiated with a TP suffix. This problem was intermittent, proved impossible to reproduce at will, and therefore did not result in a bug report. It is probably worth remembering to differentiate method names in this way when designing controls of this type. Note that this occasional confusion only seems to be an issue when the first few letters of these different control’s classnames are the same.

Back to index

The Fields section uses another complex QR3 control, the QR3FieldPicker control. The setup of this control is much the same as the TablePicker, but the initialisation is slightly different:
 

	form.P02_QR3FieldPicker.Initialise(form.QR3, form, "P04_UpdateSizingInfo()")

In addition, this control is given a reference to the UI form, and a string containing the name of a method of the UI form. These are used here to trigger the update of a size warning which is displayed if the width of the selected fields exceeds that available on the page.

The right hand listbox of this control will also list any virtual fields, (calculated or MemoText fields), in addition to normal table fields, and it is here that virtual fields are deleted.

Right-clicking a field name will display field information, and double-clicking is a quick way to pick or unpick a field.

Back to index

The Order section uses another custom control, the QR3IndexPicker control, and the setup of this control follows the same pattern. QR3 will use any existing Index, and because this interface is aimed at end users as well as developers, it was decided that Index creation would not be provided. If a suitable Index does not exist, or if the data requires to be ordered on multiple fields, then a flat table can be produced by Quick Query with the data order defined as part of the SQL query.

The QR3 Index picker provides index information with a right-click on the Index name, and a double-click is a quick way to select an Index. The small eraser button will clear the selected Index.

Back to index

The Range section uses another custom control, the QR3RangeBuilder control. The setup of this control follows the established pattern.

One of the nice things about this control, is that the formatting of the entryFields automatically changes to match the data type of the first field of the Index expression. The entryFields have a dynamic picture property allocated to them, which helps to validate the user entry whether the required data type is character, numeric, or date. Look at the InitialiseRB() code for this control to see how this has been achieved.

Small eraser buttons allow easy deletion of individual Range entries, or the clearing of the entire Range setting. A blank entry for one entryField is handled to produce a Range of “First to MyEntry” or “MyEntry to Last”, and a quick copy button will copy the contents of one entryField to the other to save typing.

Back to index

The Sizing section is all to do with fitting the columns for the selected fields onto the page. When you add a field to the selected fields for the report, QR3 calculates a default width based on the length of the underlying table field and the font and font size selected for the body of the report. In many cases the defaults will provide good initial output, but often the developer or end user will want to fine tune column widths to suit the circumstances.

QR3 has the ability to scale the overall contents of a report to fit the available width. To do this it scales the font size used as well as the actual column width. The column widths can be scaled up, but more commonly may need to be scaled down. This can be achieved with a single click of the scaling RadioButtons. While this option is very quick and easy, and works well where a small amount of scaling is required, a large scaling factor will soon reduce the font size to something unreadable.

Individual columns are spaced using the marginHorizontal property of the report text objects, and this QR3 setting is applied globally. Individual column widths can be set, and a visual representation of the column width with respect to the column title can be seen in the Column Heading entryField, where an alternative column heading can be entered if required.

In order to make individual Field settings, the field name can be selected by using another custom control, the NudgeCombo control, (shown at the tip of the cursor). This control is a type two comboBox with two “spinbox” type “nudge buttons” added. In this type of situation often the user will work through all the fields from first to last, and the NudgeCombo control allows the selection of the next or previous Field with a single mouse click.

The measurements displayed give reasonably accurate correlation to the printed page. For fine adjustments it is often quickest just to print the report, and then with a ruler on the printed page, measure the column width changes you need.

Back to index

This section needs no explanation, but it is worth pointing out that the user interface can display its dimensions in either millimeters or inches, and either can be set as the default. At first it might seem difficult to design a user interface which can seamlessly switch from one metric to another, but in reality it is easy to achieve. The QR3 report engine requires any dimension setting to be made in millimeters. A UI form property is set to either “Inches” or “Millimeters” and this is used to determine how QR3 dimension properties are displayed. The display side of things is all done in the menu button page change code. Let’s have a look at the code for just the margin settings bounding rectangle, and the top margin spinbox:
 

form.P03_RECT_Margins.text := " Margins in " + form.UIMetric + " " do case    case form.UIMetric == "Millimeters"       form.P03_SB_TopMargin.value := form.QR3.PageTopMargin       form.P03_SB_TopMargin.picture := "999"       form.P03_SB_TopMargin.rangeMax := 50       form.P03_SB_TopMargin.step := 1    case form.UIMetric == "Inches"       form.P03_SB_TopMargin.value := form.QR3.PageTopMargin / 25.4       form.P03_SB_TopMargin.picture := "99.99"       form.P03_SB_TopMargin.rangeMax := 2       form.P03_SB_TopMargin.step := 0.01 endcase

This part of the operation is the “get” side of things, getting information from QR3 and formatting it for display. The opposite “set” job needs to be done when any change is made to the setting, and this is passed back to the QR3 engine. This can all be done in the instantiation code for the control, with a code block keeping small chunks of code with the control’s instantiation code, rather than littering the application with dozens of tiny functions. Again let’s look at how the top margin spinbox achieves this:
 

	this.P03_SB_TOPMARGIN = new SPINBOX(this) with (this.P03_SB_TOPMARGIN)      onChange = {; tv = iif(form.UIMetric = "Inches",this.value * 25.4, this.value); form.QR3.SetMargins(tv,-1,-1,-1)}      height = 22      left = 417      etc.

This approach works well, and allows you to design a complete application with just one user interface metric, but offer a choice to the user. In Europe and most of the world millimeters are now standard, with the obvious benefits of a decimal system, while the USA and older generations around the world still prefer to work in Inches. The odd thing about working with Inches in a computer environment, is that 1/4", 1/8", 1/16" all disappear, and you are suddenly working with a decimal portion of a non decimal unit. Does anyone manufacture a ruler with decimal fractions of an inch ?

Back to index

Both of these sections require little explanation, other than to say that a Report title can be defined, along with font and font size, and there are options to include a date and a page number.

The Body section allows the choice of report body font and font size, and options for detail underline and stripe. For summary reports detail rows can be suppressed, and Grand Totals and Grand Count can be selected.

Back to index

The Advanced menu buttons are made available by selecting the appropriate radioButton in the top right corner. The sections available from this menu bar are intended for advanced users and developers. Despite this, these sections have been designed to be accessible to the novice user, with only a little guidance required.

Back to index

The Filter section uses another custom control, the CanGetRowBuilder control, again found in QR3Controls.cc. This is the “mark 2” version of a filter builder for QR3, and while the first version actually used the rowset filter property, this version uses canGetRow().

The advantage of using the filter property is that behind the scenes, dBASE will use any available simple indexes in order to optimise the filtering process. This makes filtering very fast. The downside is perhaps that the filter expression has to be a SQL expression, and this reduces flexibility.

QR3 retains its filter property, which can be set by the developer, and will be handled by the QR3 report engine, but the user interface now uses canGetRow().

The main advantage of using canGetRow() is quite simply that you are essentially using dBL code rather than SQL, to test each row in a rowset and decide whether it is to be included. The trade off in speed is not particularly noticeable unless you are dealing with a large number of rows.

Novice users will tend to use a filter in preference to a range, but best results are usually obtained by performing the initial constraint on the data by a range setting, and the canGetRow() filter is then working with just a subset of the data.

So what’s so special about the QR3 canGetRowBuilder control ? Well a number of things set it apart from most filter builders.

Most filter builders require at least the press of a “Build Filter” button, and there may be a further step of “Set” or “OK” before the filter is useable. For the novice user this often means that they think they have defined a filter, but it hasn’t actually been built or set. With this control every change is immediately invoked. The filter conditions are validated, and if the filter conditions are invalid or incomplete, a warning is displayed.

As with the RangeBuilder control, the Field value entryField has its data type matched to that of the chosen field. This helps to ensure that meaningful valid values are entered by the user. In addition a Match case option is made available for character fields.

Commonly used filter terms, AND, OR, and brackets can be added with a single click, and a custom user entry can be added to the list of filter components if required.

The big advantage with this filter builder, is its ability to combine conditions. It is easy to set up a filter along the lines of :
 

	include if :-    x = y and ( a >= b or d = e )

Most filter builders only allow the exclusive use of AND  or the exclusive use of OR when combining multiple filter conditions.

Another big advantage is that this filter builder can be reloaded, and can be edited on an individual component basis. With most filter builders it’s a one way trip, and once the filter has been built the user will need to start from scratch or make manual edit changes only.

The design of this filter builder arose from watching ordinary end users with little technical knowledge struggle with existing filter builders. I believe that this design is more intuitive for the end user, and more flexible and powerful for the expert user or developer.

For the developer learning to code in dBL, the CanGetRowBuilder control in QR3 can be used to create and validate the required filter, and then the relevent code can be copied and pasted from the QR3 produced report code and used elsewhere.

Back to index

The Group section offers the ability to group the output data on a given field, in addition to various group header and group total options.

It is normal practice to group data on the same field as is used for the Index order, or the first field in the index expression. Indeed if you don’t do that, then if you are not careful, really meaningless output will be achieved. So why has the ability to choose a group field been included ?

Sometimes it is required that data is ordered on a particular field, but grouped on only one element of the data in that same index field. For example it is common for data to be presented in date order, but you may wish to group the data on just the year of that same date.

With QR3 that is easily done by creating a calculated field on:
 

	ltrim( str( year( myDateField ) ) )

and grouping on that calculated field.

This ability is very useful, but for the novice, does provide the easy ability to shoot oneself in the foot ! For that reason, the UI will offer a default group field of the first field in the index expression, but only if that field is found in the chosen field list.

Back to index

The Calculated Field section uses another custom control, the CalcFieldBuilder control. With the QR3 Calculated Field Builder, the creation of a calculated field is brought within the reach of the end user, who most commonly would simply produce for example a “FullName” by concatenating Title, FirstName and Surname fields. For the developer with a knowledge of dBL, it possible to create just about anything you want, and let QR3 write the field creation code for you.

Some common useful examples would be the creation of somebody’s age today, or a date calculation to show time elapsed. Have a look at the “Complex Report” example in TestQR3Direct.wfm to see how a calculated field can be produced on the year part of a date field, and how this calculated field can then be used to group data by year.

Multiple calculated fields can be created, and existing fields can be edited at any time by selecting them from the comboBox at the top left of the control. Once a field has been created, it appears in the chosen field list in the Fields section, and its position in the report can be changed there. It may also be deleted there.

A field list comboBox provides a quick way to add field names, and commonly used components can be added with a single click of the relevent pushButton.

Custom entries can be added from the entryField at the bottom left of the control.

A big advantage of QR3 created calculated fields is that they can be reloaded by opening an existing QR3 created report in QR3, and can be edited on an individual component basis.

The design of this calculated field builder arose from the need to put the power of calculated fields into the hands of ordinary end users with little technical knowledge, and I believe that this design is intuitive for the end user, and yet flexible and powerful for the expert user or developer.

For the developer learning to code in dBL, the CalcFieldBuilder control in QR3 can be used to create the required calculated field. The relevent code can then be copied and pasted from the QR3 produced report code and used elsewhere.

Back to index

MemoText is a custom method of storing long length text in the rows of an ordinary dBASE .dbf table, rather than using a .dbt file. This is discussed in the 14th edition of the dBulletin.

To use MemoText in a report was relatively easy, but did require a little setting up. With QR3 it is simplicity itself. QR3 first needs to know the name of the table containing the MemoText data. A MemoText field is just a type of calculated field, and Multiple MemoText fields can be created, all sharing the same data table. For each MemoText field you need to select the Link Field. This is the field in the table you are using for the QR3 report, which contains the Link ID with which to find the data in the Memotext table.

QR3 does the rest, writing all the relevent dBL code into the QR3 report. It couldn’t be simpler !

Back to index

The Developer section provides a number of options which are beyond the scope of the average end user, but are still useful to the advanced user or the developer.

The Report Metric is nothing to do with the User Interface Metric, but allows the developer to have QR3 produce report code in the metric they are most used to working with. This is intended to make life simple for developers who use QR3 for rapid report design, and then further modify the resulting code in either the report designer or the source code editor.

Empty skeleton render functions can be included by QR3, to give the developer who wants to use these functions by coding in the Source editor a quick head start.

QR3 knows whether it is being run in the IDE environment, or in the runtime environment, and modifies its behavior accordingly. This should be transparent to the user. For the developer who wants to ensure that things will run properly in the runtime environment, while still in the IDE, this option may be selected to force QR3 to behave as if it was in the runtime environment.

In the absence of an Alias, QR3 will normally stream the full data path to the report code. This can cause problems where a report is developed on one machine, and then sent to the user who might have a different directory structure. The use of an Alias is recommended for this situation, but there are users of the predecessor to QR3 who store data, reports, the application itself, and just about everything in one directory. In order to accommodate this scenario, QR3 provides the option to suppress the data path. This option should be used only at the end of report design, to save the actual copy of the report which will be sent to the user. A report created this way will not run unless its data is located in the same directory.

The contents of logical fields can be translated into any single character, including the space character. This is very useful where you only want to see report output if the field is, for example, true. It also allows the use of “X” or similar where the meaning of true or false would not be readily understood by the reader of the report. With this feature it is easy to produce rota or timetable type reports, which have many narrow columns of logical fields.

Back to index

The Interface section allows a number of settings to be made. The User Interface Metric, which has already been discussed, can be set here. The use of the choose printer dialogue can also be invoked here.

The Auto re-size of column widths on font change may not be immediately obvious. The logic is that default field column widths are calculated by QR3 when a field is selected. This calculation is based on the font and font size currently selected for the body of the report. If the font or font size is subsequently changed then the original defaults may no longer provide the best fit. I suspect that most people will find the adjustment of column widths is best dealt with on an individual field basis in the Sizing Section, but QR3 does offer the facility to automatically recalculate field column widths when the body font or font size is changed. Be aware that using this option will destroy any manual column width settings you have made, unless you have specifically locked individual field column widths in the Sizing Section. If you normally accept the default widths for your reports, then this is a quick way to maintain “best fit” when changing font.

QR3 will save all of the settings in this section, and in addition, Page size, orientation, and margins. Once saved these settings will be used as defaults for new reports. Having been saved, the Save defaults on Exit option can be unchecked to protect those defaults, or left checked for continual updating of defaults.

Back to index

The Reload section provides a quick way to re-use existing QR3 reports. The last ten reports saved are provided in a listbox, or you can browse your system to find and open a report.

A quick shortcut is provided whereby you can select an Alias from the available list, and this Alias location is then used as the starting point for the browse to find the report. This is particularly useful where reports are stored in a subdirectory of the main Alias location.

Back to index

The bottom bar of the QR3 Interface provides various output options. First of all, a report does not need to be saved in order to provide output. This has been designed in order that QR3 might run from read only storage, for example, an application which runs directly from CD. In reality the report is actually saved to a temporary file in the user’s Windows Temp directory.

A report can be saved in .rep, .htm, or .pdf format by use of the appropriate save button. Once the report file has been named, the same file name will be used with the appropriate suffix when saving to different file types. To over-ride this, a specific Save as should be used from the menu.

dBASE report preview is provided using Ken Mayer’s Preview.wfm, and version 2.20 or later should be used, since a number of issues which surfaced in the course of QR3 development have been addressed.

The direct printing of a report is handled directly by dBASE.

HTML output is not one of the strengths of the dBASE report engine, which quickly falls down if a data range or filter is set. QR3 provides rather better HTML output, and in most cases should be a good match for the actual dBASE report. Preview is provided by PreviewHTML.wfm which was written for QR3, but is now also available in the dUFLP. This form uses the MS WebBrowser ActiveX control.

The printing of an HTML report is provided by an instance of the same MS WebBrowser ActiveX control which, if it does not exist, is instantiated by QR3.cc on page 255 of its parent form, and used from there. I am indebted to Liam Egan for his help in using this control to provide print output.

PDF file creation is achieved by using the PDF printer driver, PDF995 version 6 or later. This is available as a free download from www.pdf995.com. The free version has an annoying habit of opening a browser window, and trying to “phone home” in order to “nag” you into paying a small amount for registered use.

PDF preview and print are provided by the default pdf application on your system.

Back to index

It has always been difficult to provide a method of designing new reports from within a distributed application. The simple reason for this is that the dBASE runtime engine will not compile code in the runtime environment. This has left the developer with the option of creating as many standard reports as possible for distribution with the application, and providing flexibility within those by means of parameters, perhaps for example, providing a date range for a monthly report. Some would argue that the future demand for new reports from the user of your application is good for business, but quite frankly the cash return on bespoke reports is unlikely to be rewarding. QR3 certainly speeds up report design for the developer, but it can also be used to put the capability into the hands of the end user. Another alternative is to look elsewhere for a reporting tool, but this is hardly likely to impress the user who expects to find reporting capability from within your application.

So, how does QR3 get over the hurdle of being unable to compile report code in the runtime environment ?

One of the superb things about using dBL, and this is not just restricted to reports, is the ability to create an object on the fly. In most languages, the object would have to be at least declared, and probably instantiated, before being modified at runtime. The power and scope that this gives the dBASE developer cannot and should not be under-rated.

QR3 makes use of this ability of dBASE by providing QR3Runtime.reo. This is a compiled report but has almost no controls defined within it.

Try this for yourself. Use the dBASE Report designer to create a new report. Change the metric to millimeters, and save the report as Driven.rep. Now compile it to Driven.reo. Delete the .rep file to ensure that the .reo file is used. Now use the following code to see how easy it is to add something to this report.
 

	private sMacro set procedure to Driven.rep additive oRep = new DrivenReport() sMacro = 'oRep.PAGETEMPLATE1.TEXT' + '01' + ' = new Text(oRep.PAGETEMPLATE1)' &sMacro. sMacro = 'oRef = oRep.PAGETEMPLATE1.TEXT' + '01' &sMacro. oRef.text = "Hello World" oRep.render() close procedure Driven.rep

Very easy and very impressive !

Well that’s not how it’s done in QR3 !

The above method is what I would call a “push” method. In your form code you instantiate the report object, and from the form code, you are “pushing” information to the report before finally rendering it. In QR3 we have the advantage of being able to reference the QR3 object, which contains all the properties we need to create the report. It is much more economical in terms of code, and in speed of execution, to “pull” the required information into the report, from the report code itself. Using this method, the runtime report can be called with an array of parameters, albeit only one parameter is required here, a reference to the QR3 object itself.
 

	aParams = new AssocArray() aParams[ "QR3Ref" ] = this set procedure to QR3Runtime.rep additive oRep = new QR3RuntimeReport() oRep.output = 1 oRep.params = aParams oRep.render()

We can now use an over-ridden render event in QR3Runtime.reo which builds all of the required report objects by referencing QR3 properties via the object reference we have provided.
 

	this.QR3Ref = this.params["QR3Ref"]

A report produced in this way should be identical in appearance to that produced by QR3 streamed dBASE report code. I have been asked, if this works so well, why bother to stream report code at all ? The answer is that QR3 is a developers’ tool, just as much as an end users’ tool, and the developer needs to produce dBASE report code quickly as the basis for further development. That report code needs to be compiled for distribution as a stand alone report.

This ability of QR3 is intended to address much of the criticism leveled at dBASE with regard to reporting in the runtime environment. It has always been possible, but has perhaps been a little out of easy reach.

Back to index

The QR3 Report engine does not need a user interface in order to be functional. It is possible to drive it purely by programmatic command. This ability opens up a whole new realm of possibilities in terms of reporting from within a distributed application, where not only the report design capabilities of QR3 are useful, but the preview, printing, and output capabilities are equally valuable.

It takes just five lines of code to produce the most basic of QR3 reports.
 

	form.QR3.Initialise() form.QR3.SetDataPath(set("DIRECTORY")) form.QR3.SetTable("TestData.dbf") form.QR3.SetChosenFields({"SaleDate","Title","FirstName","Surname","SaleValue"}) form.QR3.PreviewReport()

In order to provide reporting capabilities from your own distributed application, an instance of QR3 can be dropped on the form. From here, it is possible to set up as much of the report as you wish by programmatic command, and surface to the user only those report properties they require.

A number of samples of the programmatic control of QR3 are provided in TestQR3Direct.wfm. Run this form and see how easy it is to produce reports in this way. The function code for the report pushButtons should be examined to see exactly how easily all this is achieved.

At first it might seem that the task of writing all this code to drive QR3 is time consuming, but there is a very quick way. Let QR3 write the code for you ! Every QR3 report has a “reload” function appended to the end of the report file, beyond the end of the report class code. This function is used by QR3 when the report is reloaded. This very reload code is itself driving QR3 directly. Having designed the initial report in QR3, it is a simple copy and paste of this code into your own application. It really couldn’t be any easier !

Back to index

Quick Query is an integrated application with which to produce a single “flat” table from multiple tables. This “flat” table can then be used by QR3 to produce a report.

This ability of Quick Query can also be put to good use in arranging data in a particular order, perhaps on multiple fields, where a dBASE index is not available. While the developer can often create a new index to suit, this option may not be available to the end user.

Quick Query can also be used to produce a small local dBASE table for reporting purposes, using an Alias to a non-dBASE database.

The origins of Quick Query are in code by Marc Hamelin, and subsequently developed by Michael Nuwer, and this code base has appeared in various guises. A version of “Custom Query” can be found in the dUFLP, and a spin-off version from QQ development, SQL Query Builder can be found at Michael’s site.

I have had the pleasure of collaborating with Michael on the development of a version of this code, specifically for QR3, and have taken the development of this code base to the next stage, that of User Interface and separate custom control Query Engine. This means that Quick Query can also be driven programmatically.

Examine the sample code for Quick Query in TestQR3Direct, and you will see that it takes just nine lines of code to drive QQ programmatically to use an Alias, two tables, fields from both tables, set a link, set a condition, set a data order, and build a flat table. It then takes a mere thirteen lines of code to produce a QR3 report from this table.

Quick Query makes use of QR3 resources, and in particular the preview and print of the results of the SQL query are produced using QR3. This is the code used by QQ.cc to build a table from a SQL query, and preview it:
 

// Preview SQL Data using QR3 cTempTable = getenv("TEMP") + "\QQTemp.dbf" this.MakeTable(cTempTable) oQR3 = new QR3(form) oQR3.Initialise() oQR3.SetDataPath(getenv("TEMP")) oQR3.SetTable("QQTemp.dbf") td = new TableDef() td.tableName = cTempTable td.load() aFields = new array() for nCount = 1 to td.fields.size     aFields.add(td.fields[nCount].fieldName) endfor  // next release object td oQR3.SetChosenFields(aFields) oQR3.ReportTitle = "Quick Query Data List" oQR3.UITitle = "Quick Query Data List" oQR3.PreviewReport() release object oQR3

Note that the TableDef object is used here to get the field names from the table, despite the fact that we already have the names of the chosen fields from all the source tables in an array. The reason for this is that Quick Query has the very powerful ability to create Group and Summary fields, (not to be confused with Groups in QR3) and the table created will have newly created field names accordingly, along the lines of “SUM_OF_MYFIELD”.

Quick Query is deserving of an article in its own right, and is a tremendously powerful way of bringing multi-table reporting and more, to QR3. At this time I will leave you to explore the User Interface, and the provided source code.

Back to index

QR3 is designed to provide a quick and easy method for the production of columnar listing type reports for end user and developer alike. The application design demonstrates, that with appropriate use of custom controls, a complex application can be produced with a relatively clean modular coding approach which in itself aids bug tracking and ease of future update. With the programmatically driven Report engine, QR3 provides flexibility of use with easy incorporation in distributed applications.

QR3 represents a huge investment in development time, but in use has probably already saved an equal amount of time. Above all else, it shows that the power and flexibility of dBL goes far beyond a language for database applications, but in reality is limited only by the developer’s imagination. The dBASE Report designer has suffered some criticism, and I suppose it is difficult to provide something which is both powerful, and yet easy for the beginner. With dBL, if you don’t like something in particular, you always have the option to write your own !

You can download all of the files for this article from the top of this page. I will be happy to receive any feedback by e-mail, or by posting in the dBASE Newsgroups.

Have Fun !

Thanks to Bob Rimmington, who first and foremost has provided testing, feedback, criticism and suggestions for the duration of development. Thanks to all other contributors who are credited in QR3 Help|About. Thanks to my wife Sarah, and to Jean-Pierre Martel for proof reading, and the improvements they brought to this text.

Ronnie MacGregor started developing dBASE IV applications around 15 years ago to help administer his businesses in Scotland. Some of these applications are still in daily use, but are gradually being replaced with 32bit dBASE apps. Although with no formal programming training, and essentially a “hobby” programmer, some of his niche applications are in daily use by other businesses in Britain. He can be contacted at the address below.

March 2005:
The latest versions of these controls are available from: www.dBASEdeveloper.co.uk