Forget Constructing Multiple Crystal Reports – Use Dynamic Sorts

Are you doing too much S O R T I N G  ? ?

No Need to Construct Multiple Reports - Use Dynamic Sorts!

Problem:  How do we get a report to change which field it uses to sort your data, depending upon a prompt or other formula? The old school method was to create copies of the report and set the sorting for each, separately. Then you would “Group” utility offers dynamic sort capabilities but based solely on that specific group field – not flexible enough!

Solution:

1. Create a parameter (asking the user at run-time how they want to sort) or a formula (if you already know how the data needs to sort based on _____).We created the parameter “Sort By” to let users pick the sort order at report run-time.  If you do this, be sure you set the possible values they can choose to prevent unexpected values being returned into your formula.
   
2. Create a new formula (e.g.: “sortfield”) and build logic into it that concatenates the potential sort fields into a single string. Your formula uses the sort parameter/formula (step 1) to determine in what order you concatenate the potential sort fields.Note - We found how Crystal handles null values in the potential sort fields can cause unwanted results so we built in logic to handle nulls a little more gracefully.  See the sample below (click picture to download text version).
3. Group your report by the “sortfield” and you’re done!!     You may want to leave the sort field displayed while you test, then hide the group section when ready to release your report for production use.

Lanette Felsch, RKL eSolutions Sage 100 Consultant also contributed to this blog.