Sort an Array in Crystal Reports, with Multiple Options, Using a Custom Function

[JWiseman]

If you use arrays in Crystal Reports then you may wish to sort an array, perhaps for display purposes or to create a new sorted array to be consumed by other formulas.

Further below is a link to a sample report with a Custom Function that can sort your arrays using the following options:

1. Alpha ascending


2. Alpha descending


3. Length of array members ascending


4. Length of array members descending.

There is also an option to trim the array members before sorting. In the screenshot below, an alpha ascending sort option is used and the array values were not trimmed.

 

Here's how it works; the Custom Function creates a copy / worker array, loops through the new worker array, and finds the maximum or minimum value, according to the chosen sort option. That value is moved into a new output array and this step is repeated for the length of the array. This method keeps the total number of loop iterations to about twice the size of the array's length / ubound value, so there are no issues using larger arrays.

Instructions:

1. Download the report from here.


2. Try out the parameters for the sort options and trimming the array members to see if this is useful for your situation.


3. If it is useful, go to the Report Menu > Formula Workshop > Report Custom Functions and then right click on the ArraySort function to add to your repository. (If you are not using a CR Server or BOBJ environment, then you can copy the ArraySort function syntax to use in a new Custom Function on each applicable report.)


4. See the ArraySortCustomFunctionTest formula in the sample report as an example of calling the Custom Function in a formula.


5. Create a new formula in your report calling the function, but remember that the output of the Custom Function is a new sorted array; you will need to ensure that the formula output is not the array itself.

Questions:

Please feel free to post any questions or comments below. Also, if you're looking for any other sample reports, workarounds, etc. please let me know...I have a lot of them. You can also check out my older blog posts, many of which contain report samples. jamie.wiseman#content:blogposts