Tutorial 3-7. Dynamic Grouping Fields

In this tutorial we will base the groups on formulas. We will fix the previous report so that it shows either both groups or only one group. This gives the user the ability to choose how the type of report they want to see. There are three changes that need to be made. The first change is creating a parameter that prompts the user for the type of report to print. The second change is basing the two groups on a formula rather than a database field. The third change disables certain report objects (group header and footer, summary fields, etc.) so that only the appropriate ones are displayed when the report displays just one group.

Although formulas can give you advanced grouping options, be careful if you have a report that is hundreds of pages long and you want better performance. Using formulas as the grouping/sorting fields will incur a slight performance hit. Normally, this isn’t a problem. But for large scale reports that take a long time to run, this could be a factor.

1. Rename the previous report so that we can re-use it again after this tutorial. Select File > Save As and enter the name Sales – Multiple Groups.rpt.
2. Create a parameter to prompt the user for the type of report they want to print. Call the parameter Report Type. The prompt has two possible values: Country or Product. A screen shot of the parameters appears below.

This report has two groups that are both based on a database field. We need to create two new formulas that will be used for grouping instead. The first formula will return either the Country field or the Product Type Name field.

1. Create a new formula and name it MainGroup. Type in the following code:

if {?Report Type}="Country" then  {Customer.Country} else  {Product_Type.Product Type Name};

This formula looks at the parameter value and if it is equal to “Country” then the formula returns the Country field. If not, then we know that the report should only group on the Product Type Name field.

When using a formula to return the value to group on, each possible value must be the same data type. For example, you can’t have an If statement where the success portion returns a string data type and the failure portion returns a number data type.

1. Create a second formula and name it SubGroup. Type in the following code:

if {?Report Type}="Product" then "" else {Product_Type.Product Type Name}

This formula is for the secondary group field, which is optional. If the user only wants to group by product type, then this formula doesn’t need to return a value at all. Thus, it’s assigned an empty string as the grouping value. If the user chooses to sort by Country, then both fields will be grouped on and the Product Type Name field becomes the secondary group.

1. Now that the formulas have been created, modify the groups to replace the current fields with the new formulas. Right-click on Group Header #1 and select Change Group. In the drop-down list, select the Main Group formula. This sets the group to be based on that formula instead of the Country field.
2. Repeat the previous step to change Group Header #2 so that it uses the SubGroup formula.
3. Preview the report (selecting the Country parameter value) to see what it looks like. It should look identical to the original report because you’ve only added the logic to duplicate the existing fields. No formatting changes have been made yet.
4. Preview the report again and select the Product Type parameter value. There are two problems immediately noticeable.

See how there is too much space between the group header and the first detail record? There are also two sub-total rows instead of one. Even though Group 2 doesn’t have any data, the header and footers are still being printed. Printing an empty group creates unnecessary space on the report. The next step is to suppress these sections when only grouping on the Product Type field.

1. Let’s first disable the Group #2 Header when the report is only grouping on the Product Type. Right-click on the Group #2 Header section and select Section Expert.
2. On the Section Expert dialog box, look for the Suppress property and click on the Formula Workshop button to the right. Enter the following formula:

if {?Report Type}="Product" then True else False;

This formula checks to see which type of report is being printed by analyzing the {?Report Type} parameter. If the parameter value is “Product” then this is the Product Type only report and the group header should be suppressed. Otherwise False is returned and the group header is displayed.

1. Repeat the last two steps, but this time do it for the Suppress property of the Group #2 Footer. Make sure to enter the same formula exactly.
2. Preview the report and select the Product Type only report. It should look much better now because the header doesn’t have extra white space below it and there is only one sub-total.

This tutorial showed that creating groups with dynamic fields can be done by creating a parameter to prompt the user for the report type and then using this parameter in formulas to dynamically change the formatting of the underlying report objects.

Using a formula as the grouping field gives you a wide variety of ways to customize groups. Formulas can do complex calculations that aren’t possible when referencing simple database fields. For example, you can parse text out of an existing field to group on a sub-string. Formulas can examine the values of other fields and create a new value based upon a calculation. For example, a shipping report could calculate the area of a package and group it as Small, Medium or Large. There are an unlimited number of ways to use formulas to create custom groups.