Have you ever been asked by a customer to create a report, but they want to be able to control how it’s sorted and grouped? You probably created the report for the first sorting option, and then copied the report and changed a few things for the second sorting option. Although this solves the problem at hand, maintenance becomes double the work whenever you have to change something because two reports have to be changed. Wouldn’t it be nice to create just one report that’s smart enough to sort and group both ways?
Consider a report of Employees (I’m getting really bored with the Customer table). You want to be able to sort and group it by either the Country or Group_ID fields. The only thing different between the two reports is how it’s grouped and whether you print the Country or the Group ID in the Detail band. The Employee report shown in Figure 9 is sorted and grouped by Group ID. The Employee report shown in Figure 10 is sorted and grouped by Country. Yet both reports were run from the exact same report definition.
Figure 9. This Employee report is sorted and grouped by the Group ID field.
Figure 10. This Employee report is sorted and grouped by the Country field.
I bet you’d like to know how I did this. Well, it’s actually quite simple (like most things in life), once you know the secret. I started by creating a program that prompts the user for the way he or she wants to see the report. The program then opens the Employee table and creates a temporary index based on the user’s choice. It also sets some private variables for the report to use. It then runs the report, closes the table, and removes the temporary index. The following code is included in the source code available with this book, and is called Employee.PRG.
PRIVATE pcGroupExpr, pcGrpTitle, pcColTitle, pcColField, pcSortBy LOCAL lcIndex
lcIndex = ''
*-- Get the user's sort option pcSortBy = 'G'
CLEAR
@ 10,10 SAY 'Sort by [G]roup ID or [C]ountry: ' ; GET pcSortBy ;
PICTURE '!' ;
VALID pcSortBy $ 'GC' ;
MESSAGE 'Invalid - enter G or C' READ
*-- Open the table
USE DATA\employee
*-- Sort the table and set the private variables
*-- based on the selected sort option lcIndex = SYS(2015) + '.idx'
INDEX ON group_id + country + last_name TO (lcIndex) GOTO TOP
CASE pcSortBy = 'C' && by Country pcGroupExpr = 'country'
pcGrpTitle = 'Country: ' pcColTitle = 'Group ID' pcColField = 'group_id'
INDEX ON country + group_id + last_name TO (lcIndex) GOTO TOP
ENDCASE
*-- Print the report
REPORT FORM reports\Employee TO PRINTER PROMPT PREVIEW
*-- Close the table and erase the index SELECT employee
USE
ERASE (lcIndex)
Next, I turned my attention to defining the report. The key pieces to this trick are the expression for the Data Group, the expression used in the Group Header, the expression used for the first column of data, and the expression used for the heading of the first column of data. Do you see a pattern here? Expressions are the key to all of this. The report definition, Employee.FRX, is included in the source code available with this book.
The Data Group
If you were defining a static Data Group, you’d probably set the expression of the Data Group to a field in the table, such as Group_ID. However, in this situation, we don’t know what field should be controlling the Data Group because it’s determined when the user makes a choice in the calling program. So how in the world can we tell this Report definition to change the Data Group based on what the user chooses?
The trick is to use VFP’s EVALUATE() command. Look at the sample code again and you’ll notice that I set a private variable, pcGroupExpr, to the name of the field that should control the Data Group. Combine this variable with the EVALUATE() command as shown here and the Data Group suddenly becomes dynamic:
EVALUATE(pcGroupExpr)
Cool trick, don’t you think? Any valid VFP expression can be used, including the EVALUATE() command. Thus, the end result is that this expression returns either the Group_ID or the Country of the current record.
The Group Header
Now that the Data Group is defined, you need to put an object in the Group Header. Add a Field object using the following as the expression.
pcGrpTitle + EVALUATE(pcGroupExpr)
This uses two of the private variables defined in the calling program to print the value of the field that controls the Data Group preceded by a descriptive label.
The first column of data
Now that you have the Data Grouping taken care of, you need to turn your attention to what prints in the Detail band. The first column on the report needs to be the opposite field of what the report is grouped by. For example, if the report is sorted and grouped by Group_ID, the first column needs to be Country. Or if the report is sorted and grouped by Country, the first column needs to be Group_ID.
This is done by using one of the private variables defined in the calling program as follows.
EVALUATE(pcColField)
Now for the label associated with that column, you have a problem. You can’t use a Label object because Label objects are static. The information can’t be changed. I supposed you could add two Label objects, one for Group ID and one for Country, and change their Print When logic to conditionally print one or the other, but I don’t like to add more objects than necessary. Instead of using a Label object, use a Field object in the Page Header band with the following expression.
PcColTitle
Remember, Field objects can contain any expression and don’t necessarily have to be used to print fields from tables.
Easy maintenance
This may all seem like a lot of work right now, but down the road, this may save you time. I don’t know about you, but my customers hardly ever leave things alone. There’s always just one more change to make to the report. Using this technique, you’ll only have to make that change in one place because there’s only one report definition, not two.