Manipulating Report Groups

One of the requirements for the Xtreme Orders report is that the user be able to specify how the report is grouped. A combo box exists on the Print Report form that lets the user choose between Quarter and Customer grouping. In the RDC design window, this is normally accomplished by using the Change Group option from a pop-up menu to change the field a group is based on, as well as the sequence ( ascending or descending) that you want the groups to appear in. If the group is based on a date field, such as Order Date, you may also specify the range of dates (week, month, quarter, and so on) that make up the group. To familiarize yourself with various grouping options, refer to Chapter 3.

By being able to change these options from within an application at run time, you can provide great reporting flexibility to your end users. In many cases, you can make it appear that several different reports are available, depending on user input. In fact, the application will be using the same Report object, but grouping will be changed at run time according to user input.

Manipulating groups in the RDC object model requires a bit of navigation down the object hierarchy. Ultimately, the database or formula field a group is based on is specified by the GroupConditionField property of the Area object. A report contains an Areas collection consisting of an Area object for each area in a report. Consider an area of the report to be each individual main section of the report, such as the page header, details section, group footer, and so on. If there are multiple sections on the report, such as Details a and Details b sections, they are still part of the overall details area, and there is only one member of the Areas collection for them. The Areas collection has one advantage not shared by most other collections exposed by the RDC ”the index used to refer to an individual member of the collection can be a string value or a numeric value. This allows you to specify RH, PH, GH n, D, GF n, PF, or RF as index values, or use the one-based index number. Using the string values makes your code much easier to understand when working with the Areas collection, and you don t have to write extra looping code, like that shown earlier for formula manipulation.

Note  

The Areas and Sections collections are similar, but not identical. As shown in an example earlier in this chapter, in the section Changing Text Objects at Run Time, the Sections collection can also accept a string index; but this collection exposes individual sections, with indexes such as PHa for Page Header a and PHb for Page Header b. The Areas collection contains only one member for the Page Header area (referenced with a string index of PH), even if there are multiple page header sections, such as Page Header a and Page Header b.

However, once you navigate to the specific Area object to specify the GroupConditionField property, you are faced with additional work when approaching the RDC object model. You cannot simply supply a string value containing the actual field name (such as {Orders.Order Date} or {Customer.Customer Name }) to indicate which field you wish to base a group on. Instead, you must specify a DatabaseFieldDefinition object to the GroupConditionField property ”you can t just pass a string containing the field name.

The DatabaseFieldDefinition object is found quite deep in the object hierarchy ”there is one of these objects for each field contained in the DatabaseFieldDefinitions collection in a DatabaseTable object. And, multiple DatabaseTable objects are contained in the DatabaseTables collection of the Database object, contained inside the overall Report object. To make things even more complex, none of the collections just mentioned will accept a string index value ” you must specify a one-based index value. You can use the previously mentioned GetItemByName method with the DatabaseFieldDefinitions collection, but not with the DatabaseTables collection.

Although this sounds confusing and hard to navigate, you ll eventually be able to travel through the object hierarchy fairly quickly to find the database table and field you want to provide to the GroupConditionField property. Look at the following code from the Xtreme Orders sample application:

 'Set grouping 
 If cboGroupBy = "Quarter" Then 
 Report.Areas("GH").GroupConditionField = _ 
 Report.Database.Tables(1).Fields(5) 'Orders.Order Date 
 Report.Areas("GH").GroupCondition = crGCQuarterly 
 Else 
 Report.Areas("GH").GroupConditionField = _ 
 Report.Database.Tables(2).Fields(3) 'Customer.Customer Name 
 Report.Areas("GH").GroupCondition = crGCAnyValue 
 End If  'cboGroupBy = "Quarter" 

In this example, notice that the grouping is based on the user choice for the cboGroupBy combo box. In either case, the GroupConditionField property of the GH member of the Areas collection (the group header) is being set. If the user chooses to group by Order Date, the property is set to the fifth member of the Fields collection in the first member of the Tables collection. This indicates that the fifth field (Order Date) in the first table (Orders) will be used for grouping. If the user chooses to group by Customer, the third field (Customer Name) in the second table (Customer) will be used for grouping. As with other collections that don t allow string indexes, you can use quick Print statements in the Immediate window to search through different members of the collection. You can also look at the Database section of the Field Explorer view in the RDC design window to determine which index numbers to use to point to the correct tables and fields. The GetItemByName method can be used. Or, you can write a function that uses looping logic, described earlier, to search through the collections until you find a matching name value.

In addition to choosing the database field you want to use for the group, you may have to choose how the grouping occurs, particularly if you use a date or Boolean field as the group field. This is accomplished by setting the Area object s GroupCondition property, which can be set to an integer value or an RDC-supplied constant (see the explanation for the Area object in the online developer s help for specific constants and integer values). In the sample code shown previously, the group is set to break for every calendar quarter if Order Date grouping is set (hence, the crGCQuarterly constant that is provided). If Customer Name grouping is chosen , the crGCAnyValue constant is supplied, indicating that a new group will be created whenever the value of the group field changes.