The rest of the Report Wizard
By now you’re probably dying to know what all those skipped steps are and what other features the Report Wizard offers you. Well, hang on… you’re about to find out!
Step 2—Group Records
The second step of the wizard is called “Group Records.” It allows you to tell the wizard to group the records. The wizard automatically takes care of sorting the records by adding an index to the table based on the groups you’ve defined. Therefore, you must have exclusive use of the table at the time you’re creating the report. If you don’t, you’ll receive an error message that varies depending on whether the table is a free table or belongs to a database container. Free tables generate an “Unable to open table exclusive” error message. Contained tables generate the error message “The DBC containing the selected table was previously opened non-exclusively and the field(s) you chose for sorting are not in an existing index tag. Please select field(s) which already have an index tag or exit the wizard and reopen the DBC exclusively.”
I want to reemphasize what I just said. The Report Wizard may add a new index to your table based on the selected grouping. Months later when you look at your tables and database and are scratching your head because you don’t remember creating a particular index—the answer may be that you didn’t…
the Report Wizard did!
You may define up to three different levels of grouping with the wizard. You may also indicate special summary options such as subtotals for each group.
To follow along with this example, use the wizard to start a new single-table report. In Step 1, select the Customer table and select the following fields: Country, Customer_id, Max_order_amt, Min_order_amt, and Sales_region. Select the Next button to display the Step 2 dialog shown in Figure 6.
Select the Country field from the first drop-down combo box. This tells the wizard to sort and group the records based on the country. If you want additional levels of grouping, select the fields in the second and third drop-down combo boxes in a similar fashion. For example, you may want to see data grouped by Country, then State within each Country, then City within each State.
For this example, you’re grouping the records based on the entire contents of the Country field, but this isn’t the only choice you have available. The wizard also allows you to group the records based on the first letter, the first two letters, and up to the first five letters of the field.
This may be done by selecting the Grouping options… button, which displays the dialog shown in Figure 7. An example of when you would use this option is a customer phone list.
Similar to a phone book, you would group the records by the first letter of the name so you see a slight break in the report for each letter of the alphabet.
Figure 6. Step 2 of the Report Wizard allows you to choose up to three different levels of grouping as well as special summary options.
Figure 7. The wizard allows you to group records based on the entire field or just the first character, the second character, on up to the fifth character of the field.
Now that you have the records grouped by country, it’s time to put some special summary options on the report. Select the Summary Options… button to display the Summary Options dialog. Figure 8 shows the Summary Options dialog with all the necessary options selected to create the report shown in Figure 9.
Figure 8. The Summary Options dialog allows you to add special calculations to the report as well as choose how much information to print on the report.
For each field on the report, you may choose up to five different calculations.
• Sum: Checking this box provides a subtotal of this field for each group and a grand total of all the records at the end of the report. In addition to the sum, you may check the “Calculate percent of total for sums” check box. This tells the wizard to add an additional figure for each group subtotal. This figure is the percentage of the overall total that is represented by this group.
• Avg: Checking this box provides an average of this field for each group and an overall average of all the records at the end of the report.
• Count: Checking this box provides a count of the records in each group and a total count of all the records at the end of the report.
• Min: Checking this box provides the lowest value of this field for each group as well as the overall lowest value at the end of the report.
• Max: Checking this box provides the highest value of this field for each group as well as the overall highest value at the end of the report.
Figure 9. This report was created with the wizard using groups and summary options.
In addition to choosing calculations, you may also choose which lines appear on the report.
• Detail and Summary: This option prints the group description on a line by itself, followed by a line for each detail record, followed by a summary line for the special calculations chosen. If no calculations are chosen, a blank line is still printed after the details and before the start of the next group.
• Summary only: In my opinion, there’s a bug with how this option works. The title indicates that only the summary line for each group prints. However, that’s not always the case. If you’ve selected any fields other than the grouped fields, the output you get from this option is exactly the same as the output from the “No totals” option.
If you’ve only selected grouped fields, you get a summary version, but no special calculations appear. This option is probably meant to work in conjunction with the SUMMARY clause of the REPORT FORM command, which is discussed later in Chapter 9, “Running Reports.”
• No totals: This option is similar to the “Detail and Summary” option, with the exception that no summary line is printed after the end of a group. This means that if you’ve chosen any special calculations, you should not choose this option because it suppresses those calculations.
In my opinion, there’s a bug with how the “Summary only” option works, but officially, I don’t know whether Microsoft considers this to be a bug or a feature.