Tutorial 3-8. Dynamic Group Sorting

When creating a group, you have to specify the order to sort the data in (the default is ascending order). To make a report more flexible, you can let the user choose how to sort the report. For example, a person who wants to see the most recent sales activity would sort the report in descending order by date. But someone performing an audit would want to sort the date in ascending order so that they can see historical data first. Crystal Reports allows dynamic sorting by letting you specify a formula for the group sorting order.

Dynamic sorting is only allowed on group fields. Crystal Reports doesn’t allow modifying the sort order of detail records. How unfortunate!

1. Open the report Sales by Country and Product Type.rpt that you created in Tutorial 3-6.
2. Rename it to Sales with Custom Sorting.rpt.
3. Create a new parameter called Product Type Sort Order. This parameter prompts the user to sort the Product Type group in either ascending or descending order. The following screen shot shows how to set the parameter settings.

4. Now we want to modify the group sorting so that it uses a formula to determine the sorting order. Select Report > Group Expert to open the dialog box.
5. Select the Product Type Name field on the right and click the Options button. This opens the Change Group Options dialog box.
6. Click the checkbox Use a Formula as Group Sort Order. This enables the Formula button.
7. Click on the Formula button and enter the following formula:

If {?Product Type Sort Order} = "Ascending" crAscendingOrderElse crDescendingOrder;

This formula checks the value of the parameter and sets the sort order accordingly. Notice how the sort order uses the constants crAscendingOrder or crDescendingOrder. These are predefined constants by Crystal Reports.

8. Preview the report and select the different parameter values. You’ll see that the Product Type sort order changes based upon the parameter value.

This tutorial demonstrated how to integrate parameters and formulas together to customize group sorting. Certain problems that arise with the basic grouping features are overcome with advanced programming techniques.