Microsoft has significantly improved the Report Writer in Visual FoxPro 9.
They also recognized the significant investment in existing FRX-based reports and designed the new Visual FoxPro 9 Report Writer to be compatible with previous versions of Visual FoxPro Reports. This makes it a great blend of the old and the new. In this article, you'll learn about some of the new features, including the new reusable data environments, report protection, and several user interface enhancements. You'll also learn about enhancements to layout objects and data groups. Finally, you'll learn about one of the best improvements to the Visual FoxPro 9 Report Writer: multiple detail bands.
Before I explain the new features, you need to understand how to use the new Report Designer and how to run reports using the new output engine.
Just like the Report Designer, you can control whether the new or old output engine is used.
The Report Designer: By default, the new Visual FoxPro 9 Report Writer uses the new Xbase Report Designer. It provides newer dialog boxes and is easier to use than the old version. It also provides access to many of the new features that are not available through the old Report Designer. You can easily control which Report Designer is used by changing the value of the new _REPORTBUILDER system variable.
*-- Use the new Report Builder
_REPORTBUILDER = HOME() + 'ReportBuilder.app'
*-- Use the old Report Builder
_REPORTBUILDER = ''
The Output Engine: Just as with the Report Designer, you can control whether the new or old output engine is used. Unlike the Report Designer, which defaults to the new style out of the box, Visual FoxPro 9 defaults to the older output engine. The reason for this is that GDI+ is used in the new engine, and renders slightly differently than the GDI used by the old engine. Therefore, some of your existing reports could render differently in Visual FoxPro 9, which means you'd have to tweak them to make them appear correctly. You can switch between the output engines with the following command:
*-- Use the new output engine
SET REPORTBEHAVIOR 90
*-- Use the old output engine
SET REPORTBEHAVIOR 80
The rest of this article assumes that the new Report Designer and the new output engine are both in use.
Data Environments
The Visual FoxPro 9 Report Writer can now share Data Environments with other reports. Data Environments can also be saved as a class and then loaded into reports as needed. This offers a great reuse scenario for defining common reporting needs.
Save As Class
To save a Data Environment as a class, start by defining the Data Environment in a report as usual. While the Data Environment window is still active, select the new Save As Class… option from the File menu.
After selecting the Save As Class… option, the Save As Class dialog box appears, as shown in Figure 1. The DataEnvironment button of the Save option group is the only option button-enabled when saving a Data Environment of a report.
Enter a name for the class in the Name textbox. Next, enter the name of the class library you want the new class saved in. If you enter the name of a class library that does not exist, the new class library is created for you. You can also use the ellipse button to brows for the location of an existing class library. Finally, you may optionally enter a description of the new class.
Loading a DE
In addition to manually defining the Data Environment for a new report, Visual FoxPro 9 also gives you the option to load the Data Environment from an existing report or from a saved DataEnvironment class. The Load Data Environment… option on the Report menu allows you to select which Data Environment to load.
From a Report
When loading the Data Environment from another report, all the code and members of the original Data Environment are copied into the new report. This means any changes made to the original report's Data Environment after the fact are not propagated into reports created from the original report.
In addition to manually defining the Data Environment for a new report, you have the option to load the Data Environment from an existing report or from a saved DataEnvironment class.
The Report Properties dialog box, as shown in Figure 2, appears after selecting Load Data Environment… from the Report menu. Use this menu to select the report from which you wish to copy the Data Environment.
Select the Copy from another report file option button and then click the Select… button. This invokes the Open dialog box so you can choose which report to copy from. Once you chose a report, a confirmation dialog box appears.
Visual FoxPro 9 is about to copy the Data Environment from another report to the current report. Visual FoxPro 9 notifies you that it's about to overwrite the current Data Environment, and you must click Yes to continue. This helps remind you that anything you have defined in the Data Environment of the current report is about to be overwritten. If you click No, changes are not made and the process is aborted. When you click Yes, the Data Environment is copied and you are notified by another dialog box.
You are now finished copying the Data Environment. You may manipulate the Data Environment as needed. However, remember that any changes made to the original report's Data Environment after this point are not propagated to this new report.
From a DE Class
When loading the Data Environment from a class, code is added to the Data Environment of the new report to bind to the original DataEnvironment class and instantiate it at runtime. This means that future changes made to the DataEnvironment class will propagate into any reports using the DataEnvironment class.
Using the Report Properties dialog box, shown in Figure 2, click the Link to a visual DE class button. Next, click the Select… button to invoke the Open dialog box that you can use to choose which class library and which class to use. After confirming your intentions, the Data Environment is updated and you are notified of its completion.
At this point, code has been added to five Data Environment methods: Init(), BeforeOpenTables(), AfterCloseTables(), Destroy(), and Error(). Some of the methods have very simple code with nothing more than a DODEFAULT() command. The reason for this is that BindEvents() does not function unless the method contains at least one line of code. Look at the code in these methods to see what it does, but I do not recommend that you change it.
Protection
In Visual FoxPro 9, you can create protection for one or more layout objects when using Report Designer or Label Designer. This lets your user modify a report, yet keeps them from making certain changes.
Layout objects have five protection modes you can set, and Field objects have an additional protection option. Bands have two protection modes you can set. The report itself has a variety of different protection modes you can set.
Protecting an Object
To protect a layout object in the Report Designer, select the Properties dialog box for the object. The Properties dialog box can be invoked from the Report menu after selecting the object from the right-click menu of the object, or by double-clicking the object. Figure 3 shows the Protection tab of the Properties dialog box for a field object. You can set the following five protection modes for layout objects:
- Object cannot be moved or resized prevents users from moving this layout object to a different position on the design surface and prevents users from resizing this object.
- Object cannot be edited prevents the user from making changes to the properties of this layout object.
- Object cannot be deleted prevents the user from deleting this object.
- Object cannot be selected prevents users from selecting this object. When this option is selected, the protection behaviors of Object cannot be moved or sized. Object cannot be edited and Object cannot be deleted are also imposed.
- Object is not visible in Designer prevents this object from appearing in the Report Designer in protected mode. When this option is selected, the protection behavior of the other four options is also imposed.
The Design-time caption portion of this dialog box only applies to Field objects. The literal string entered into this textbox is displayed in the Report Designer, instead of the Expression. This gives you the opportunity to display something that is user-friendly instead of a complicated expression.
Protecting a Band
To protect a band in the Report Designer, select the Properties dialog box for the band. The Properties dialog box can be invoked from the Edit Bands… option on the Report menu, from the right-click menu of the band, or by double-clicking the gray bar of the band. Figure 4 shows the Protection tab of the Properties dialog box for a band. You can set the following two protection modes for bands:
- Band cannot be edited prevents the Band Properties dialog box from being accessible to the user.
- Band cannot be resized prevents the user from resizing the band.
Protecting a Report
To set overall report protection, select the Report Properties dialog box. You can invoke this dialog box by selecting Properties from the Report menu or from the right-click menu of the report. Figure 5 shows the Protection tab of the Report Properties dialog box.
The top portion of this dialog box allows you to define which tabs of the Report Properties dialog box are unavailable to the user. For each of the selections made in this area, the applicable tab of the Report Properties dialog box is disabled. The Protection option is always checked and disabled. The Ruler/Grid option is disabled because the tab cannot be protected, although it appears on the dialog box so that the selections are consistent with the tabs on the Report dialog box.
The bottom portion of this dialog box allows you to define which menu options are unavailable to the user. For each of the selections in this area, the applicable menu option is disabled.
Honoring Protection Flags
To invoke protection during a Report Designer or Label Designer session, use the PROTECTED keyword, as shown in the following examples.
CREATE REPORT MyReport PROTECTED
MODIFY REPORT MyReport PROTECTED
CREATE LABEL MyLabel PROTECTED
MODIFY LABEL MyLabel PROTECTED
If the PROTECTED keyword is not used, the Report Designer functions as if no protection were applied to the layout objects.
UI Enhancements
Many changes have been made to the user interface to make designing reports easier and more intuitive. Menus have been overhauled, context menus have been changed, and new options have been added to the Report Designer toolbar. The Expression Builder dialog box and Expression Builder Options dialog box have new behaviors, and a few other miscellaneous user interface enhancements have been added to the Visual FoxPro 9 Report Writer.
Menus
The report menu system has been overhauled in Visual FoxPro 9 to accommodate new options. In addition, some options have been relabeled for clarity and some options have been repeated on several menus to allow easier access.
- The new Save As Class… option appears on the File menu.
- A new option has been added for the Report Designer Toolbar and horizontal lines have been added to separate the Grid Lines and Show Position options from the other options.
- Many changes appear on the Report menu, including relabeled options, new options, and the addition of the Print Preview option.
Context Menus
Existing context menus have been improved with additional items and are now more consistent with the dialog boxes they invoke. Items in the Report Designer that previously didn't have context menus now do.
- The Global Context menu has new options and one relabeled option.
- Invoke the new Band Context menu by right-clicking on the gray bar of any band.
- Invoke the new Layout Object context menu by right-clicking on any layout object.
Toolbar
The improved Report Designer toolbar, shown in Figure 6, has two new buttons: The Page Setup button and the Font button.
Expression Builder Dialog Box
A few changes have been made to the Report Expression dialog box, including a taller Expression for Field editing box, which allows more room for entering a report expression.
The Visual FoxPro 9 Report Writer includes one of the most often requested features: Multiple Detail Bands.
When the _REPORTBUILDER system variable is empty, the native behavior of the Expression Builder dialog box is specific. Only tables defined in the Data Environment are listed in the Fields list box. Tables opened outside of the Data Environment are not available in the list box.
When the _REPORTBUILDER system variable is set to ReportBuilder.app, the behavior of the Expression Builder dialog box is quite different. First of all, the Expression Builder defined in _GETEXPR is invoked instead of the native Expression Builder.
This Expression Builder dialog box has a combo box for choosing which table should be used when listing fields in the Fields list box. Only tables that are currently in use are listed in the combo box. This is an important point to remember because tables defined in the Data Environment are not automatically opened by the Report Designer, and do not automatically appear in the combo box.
This gives you the ability to control which tables are available to end-users when you allow them to modify reports within your application. You may have defined some tables in the Data Environment that you need, but to which you don't want user access. Because you have to specifically open the tables you want users to access, you can omit any tables you want to keep from them.
Expression Builder Options Dialog Box
The Field aliases option group is now enabled in the Expression Builder Options dialog box. This option group allows you to indicate whether or not you want the table alias added to the report expression when picking fields from the Expression Builder dialog box.
The Always add alias and Never add alias option buttons cause Visual FoxPro 9 to automatically add the table alias, or to not add the table alias, for all fields. The behavior of the Add non-selected alias only option button depends on the value of the _REPORTBUILDER system variable. If the _REPORTBUILDER system variable is empty, any field chosen from a table that is not the InitialSelectedAlias is prefixed with the table alias. Fields from the InitialSelectedAlias table are not prefixed with the table alias.
If the _REPORTBUILDER system variable is set to ReportBuilder.app, the Add non-selected alias only option uses slightly different logic. The currently selected alias is used instead of the InitialSelectedAlias for determining whether or not to prefix the field with the table alias.
In addition to selecting a field from the Expression Builder dialog box, dragging a field from the Data Environment to the Report Designer surface honors the setting of the Field aliases option group. Also, a new option exists on the Report tab of the Options dialog box to determine what the default Field aliases setting is for all newly created reports.
Mouse Cursor
The mouse cursor now changes to provide a visual cue when an object can be resized (see Figure 7.)
Multiple Selection Dialog Box
Visual FoxPro 9 has a Multiple Selection dialog box, which allows you to set the Protection and Print when properties for more than one layout object at a time. It also allows you to change any of the other properties of any individual layout object. To use this new feature, select more than one layout object, and then double-click any one of the objects to invoke the Multiple Selection dialog box, as shown in Figure 8.
The layout objects that were selected when this dialog box was invoked are listed in the first tab of this dialog box. To work with all layout objects defined in a report, use CTRL + A to select all layout objects before invoking this dialog box.
The Sort by option allows you to sort the list of layout objects by Type or Location within the report. The Remove from list button removes the selected layout objects from the list. Double-click on any item in the list and the applicable Properties dialog box for the individual object is invoked. The Properties tab of the Multiple Selection dialog box, shown in Figure 9, is used to change the properties of all the items listed in the Selection tab.
Select the Apply these protection settings to the selected objects checkbox to enable the Protection options. Select the Apply this condition to the selected objects upon saving checkbox to enable the Print when option. Change the Protection and Print when settings as needed, and then select OK to close the dialog box and apply the changes to all the layout objects listed on the first tab.
More Zoom Levels
The Preview window now has more Zoom levels, ranging from 10% up to 500%.
Layout Object Enhancements
A few improvements have been added for layout objects, including an option to control template characters, a trim mode for character expressions, and relative and absolute positioning.
Template Characters
The Field Properties dialog box has a new section for Template characters. The two available options are Overlay and Interleave. These determine how special characters are used in the format.
When the Overlay option is used, special characters are treated as part of the data and overlay any other specific character in a specified position. For example, when the Format expression is “999-999”, and the data contains “123456”, the report shows “123-56”. Notice that the “4” is replaced by the dash in the format expression.
When the Interleave option is used, the special character is inserted in between existing characters in the data. For example, when the Format expression is “999-999”, and the data contains “123456”, the report shows “123-456”. Notice that the dash is inserted between the “3” and the “4”.
Trim Mode for Character Expressions
Prior to Visual FoxPro 9, field objects were always trimmed to the nearest word when the text was too long. In Visual FoxPro 9, a new option on the Field Properties dialog box allows you to determine how the text is trimmed. The six trim options are:
- Default trimming uses the default behavior, which is the same as the Trim to nearest word, append ellipsis option. This behavior is similar to prior versions of Visual FoxPro, with the exception of appending the ellipsis.
- Trim to nearest character trims text to the last full character that fits in the defined area.
- Trim to nearest word trims text to the last full word that fits in the defined area.
- Trim to nearest character, append ellipsis trims text to the last full character that fits in the defined area, after an ellipsis is added to the text that prints.
- Trim to nearest word, append ellipsis trims text to the last full word that fits in the defined area, after an ellipsis is added to the text that prints.
- Filespec: Show inner path as ellipsis replaces the inner directories of a long path and filename with an ellipsis when the full text does not fit in the defined area.
Size and Position
Another new feature available on Layout objects is better control of the size and position of the object. When an object is added to the report, the values for From page top, From left, Height, and Width are automatically set. It's important to note that the From page top property is relative to the top of the page in the Report Designer, which means it takes into account the height of any gray bars above the object. Changing the From page top property may inadvertently move the object to another band.
Relative Positioning: The From page top property and the Height property work together to determine whether absolute positioning or relative positioning is used. When the From page top property is set to a value that falls within the Report Designer surface, and the value of the Height property is less than or equal to the height of the band in which the object is located, relative positioning is used. Relative positioning is needed for objects in bands other than the Page Header and Page Footer.
Absolute Positioning: When the From page top property is set to a value that falls outside the Report Designer surface, or the value of the Height property is greater than the height of the band in which the object is located, absolute positioning is used. Absolute positioning means the object is printed in exactly the same location on each and every page.
Absolute Positioning can be used to create a watermark on a report. Place a graphic image in the Page Header band, and set it to Scale contents, retain shape. Change the From page top property and the From left property to indicate the upper-left corner of where you want the watermark to begin. Change the Height and Width properties to indicate the overall size of the watermark, making sure not to extend beyond the printable margins of the printer.
Data Group Enhancements
A few enhancements have been made to Data Groups in the Visual FoxPro 9 Report Writer, including maximum Data Groups and horizontal columns.
Maximum Data Groups
The maximum number of Data Groups has been increased from 20 to 74. In actuality, the maximum of 74 was always true, but the user interface only allowed 20 Data Groups to be entered.
Horizontal Columns
Previously, reports with more than one column defined as horizontal with a Data Group wasted a lot of space. The first position (row 1, column 1) was left blank and data began in column 2 of row 1. Also, a blank band was wasted in between each set of Data Groups, as shown in Figure 10. Even if the height of the Data Group Header band is zero, Visual FoxPro still reserved the space, as shown in Figure 11.
In Visual FoxPro 9, the behavior of Data Groups and horizontal columns has been changed. When a new Data Group is encountered, it is printed in column 1 of the next full row. The remainder of this row is left blank and not used for printing details. The details belonging to the Data Group begin in column 1 of the row immediately after the row the Data Group is printed in, as shown in Figure 12. Also, no extra space is reserved if the height of the Data Group Header band is zero, as shown in Figure 13.
The new behavior, although avoiding the previously described situation, may break some existing reports. However, an added benefit of the new behavior is that the Data Group band can be stretched across all the columns, as shown in Figure 14.
Multiple-Detail Bands
The new multiple-detail band feature is one of the biggest, and most often requested, improvements. It allows you to process multiple child tables for each record in a parent table. An example of this type of report is shown in Figure 15.
Tables and Relationships
Understanding how the parent and child tables work together are key to understanding how to use this new feature. As an example of a Multiple-Detail scenario, assume you are writing the report shown in Figure 15. The database for this scenario is:
- The Customer table is the parent table and contains one record for each insurance customer.
- The Members table holds one record for each family member of the customer. The Members table is a child table of the Customer table.
- The Vehicles table holds one record for each vehicle insured by the customer. The Vehicles table is a child table of the Customer table.
- The Homes table holds one record for each home insured by the customer. The Homes table is a child table of the Customer table.
Driving the Report
One table is necessary to drive the report. In this example, the Customer table is the driving table. If you use the report's Data Environment to define the tables, set the InitialSelectedAlias property to this table. If you are using code to define the tables, make sure the Customer table is the current work area at the time the report runs.
The Target Alias
The target alias is the term used to describe which table is the driving table for a particular detail band. In this example, the Members table is the target alias for Detail 1 band, the Vehicles table is the target alias for Detail 2 band, and the Homes table is the target alias for Detail 3 band.
If no target alias is defined for a Detail band, the behavior it takes on is that of prior versions of Visual FoxPro. In other words, one detail band is processed per parent record. However, if you enter the name of the parent table as the target alias, you'll get very different results. For each record in the parent table, Visual FoxPro processes through all records in the entire parent table, printing each parent record in the Detail band. If you have a table with 10 parent records, and you set the target alias of a detail band to the parent table, the final report prints 10 sets of 10 records, or a total of 100 records.
Relationships
Relationships play a big part in how multiple-detail bands operate. Visual FoxPro uses the relationships between the parent table and the child tables to navigate through the records. You may use SET RELATION or SET SKIP to define these relationships. If you're opening the tables in the Data Environment, and relationships are already defined in the database, these relationships are honored.
If you're opening the tables in code, Listing 1 shows how to set up the tables for the Insurance Customer Listing shown in Figure 15.
Defining Multiple-Detail Bands
By default, new reports are created with one Detail band. Additional Detail bands are added through the Optional Bands dialog box. To invoke this dialog box, select Optional Bands… from the Report menu. This is the same dialog box that was formerly named Title/Summary.
Click the Add button to add another Detail band to the report. You may define up to 20 Detail bands for each report.
Defining the Target Alias
The target alias is assigned to a Detail band through the Detail dialog box. This dialog box can be invoked by selecting Edit Bands… from the Report menu and selecting the applicable Detail band. You can also invoke this dialog box by double-clicking the gray bar of the applicable Detail band.
The Target alias is an expression and you must wrap the table name in quotes. Once you have each target alias defined, the gray bars representing the Detail bands show the target alias.
When creating Multiple-Detail band reports, it's important to prefix field names with the applicable alias name. This prevents any confusion as to which table a field comes from.
Headers and Footers
Another enhancement of the Multiple-Detail band is the ability to add Headers and Footers to each Detail band. These are similar to Group Headers and Footers in some ways, yet different in others. For each parent record that is processed, the following occurs:
- The Detail 1 Header band is processed.
- The Detail 1 band is processed once for each child record in the associated target alias.
- The Detail 1 Footer band is processed.
- The Detail 2 Header band is processed.
- The Detail 2 band is processed once for each child record in the associated target alias.
- The Detail 2 Footer band is processed.
- The Detail 3 Header band is processed.
- The Detail 3 band is processed once for each child record in the associated target alias.
- The Detail 3 Footer band is processed.
- And so on .…
To turn on Detail Headers and Footers, place a check mark in the selection box for Detail Header/Footer in the Detail dialog box for each of the Detail bands. To help sort out the Detail bands from other bands, the triangle preceding the band name is solid and the other triangles are clear.
It's important to note that even if no detail records exist for a particular Detail band, the associated Detail Header and Detail Footer bands still print. If you do not want the Detail Header and Detail Footer bands under this condition, you may use the following Print when logic in each layout object defined in the bands to suppress their printing.
NOT EOF(<target alias>)
Be sure to place a check mark in the selection box for Remove line if blank for these layout objects as well.
Similar to Group Headers and Footers, Detail Headers and Footers have some of the same options.
- Start on a new column: Causes the Detail set to start on a new column of the report. Note that this option allows you to assign a Detail set to a specific column. If one Detail set has enough information to overflow the column, it is continued in the next column.
- Start on a new page: Makes the Detail set start on a new page.
- Reset page number to 1 for each detail set: With the Start on a new page option, resets the page number to 1 for each new Detail set.
- Start detail set on new page when less than: Helps prevent orphans. The detail set begins on a new page if the indicated amount of space is not available.
- Detail Header/Footer: Adds a Detail Header and Detail Footer band around this Detail band.
- Reprint detail header on each page: With the Detail Header/Footer option, makes the Detail Header band reprint whenever the Detail set overflows to a new page.
Report Variables and Calculations
With the introduction of Multiple-Detail bands, report variables and calculations have some new twists to them. You need to fully understand how they are processed in order to use them effectively. Otherwise, you may not get the results you're expecting.
The Reset at prompt on the Report Variables dialog box has been renamed Reset based on. This more clearly defines that the variable is reset based on the change in value of the selected option. In addition to renaming the control, if more than one Detail band is defined in the report, each Detail band is added to the drop-down list.
Selecting Detail n as the Reset based on value tells Visual FoxPro to only process this calculation when processing the detail records in the target alias of this Detail band. The report variable is not altered while records in other target aliases are being processed. This allows you to tie a report variable to one particular Detail band. The value of the report variable is not cleared until the Detail Header band of this same Detail set is processed for the next parent record.
If you chose a Reset based on value other than one of the Detail bands, the calculation is processed in several places. First, for each parent record, the calculation is applied. Next, the calculation is applied for each record in the target alias of the first Detail band. Then the calculation is applied for each record in the target alias of the second Detail band, and so on.
Tricks with Multiple-Detail Bands
The previous section showed how to print data from three different child tables on the same report. But a multiple detail band report doesn't necessarily have to have multiple child tables. The same child table can be used in more than one detail band.
Group Totals
Prior to Visual FoxPro 9, printing subtotals in the Data Group Header band (shown in Figure 16) was very difficult. The data had to be preprocessed to calculate the totals prior to running the report. With Visual FoxPro 9, no preprocessing is required. Follow these steps to create this report:
The above report definition tells the Visual FoxPro 9 Report Writer to process the Vehicles table twice for each customer in the Customer table. The first time, it calculates the total records and dollar amount for the customer and then prints them. The second pass of the Vehicles table prints the details. This process repeats for each customer in the Customer table.
Percentages
Another reporting concept is to show percentages of totals of each detail line, as the detail line prints. This can also be handled with Multiple-Detail bands, as shown in Figure 17. Follow these steps to create this report:
The above report definition tells the Visual FoxPro 9 Report Writer to process the Vehicles table twice for each customer in the Customer table. The first time totals the premium so it can be used in the second pass. The second pass of the Vehicles table prints the data for the customer, using the report variable that was calculated during the first detail band. This process repeats for each customer in the Customer table.
A Lot to Learn
The Visual FoxPro 9 Report Writer has many new features to help you create better reports. The new reusable data environments allow you to share data environments among reports. Various aspects of reports, such as objects and bands, can be protected. A better development experience has been created through the new user interface. The new object layout enhancements and data group enhancements offer more options than previously available. And finally, the new multiple-detail band feature opens up many more reporting options. Together, all these improvements and enhancements give you the ability to create more complex reports than ever before.
Listing 1: Setting the relationhipss between the parent and child tables.
*-- Open the child tables
USE Members IN 0 ORDER CustomerFK
USE Vehicles IN 0 ORDER CustomerFK
USE Homes IN 0 ORDER CustomerFK
*-- Open the parent table
SELECT 0
USE customer ORDER CustomerPK
*-- Set the relations between the parent and children
SET RELATION TO CustomerPK INTO Members
SET RELATION TO CustomerPK INTO Vehicles ADDITIVE
SET RELATION TO CustomerPK INTO Homes ADDITIVE
*-- Run the report
REPORT FORM Insurance PREVIEW
