Search code examples
crystal-reportsbusiness-objects

Crystal reports sort group-by in natural order

A crystal report I am maintaining has a group by section by team. For various reasons, including teams not always being numeric in the past, this is a string:

Team 1

Team 2

...

Team 10

Currently string sort always puts team 10 right after team 1, which is expected.
However, I need team 10 to be at the very end, after team 9.

I have tried going to Report->Group expert-> Right click the group -> Checkbox Use a Formula in group sort order. However, I can't find any good examples what sort of code is expected there. Ideally I want to either hardcode the order, or explode on the space and then cast the second part to an int and sort by it. I don't know enough Crystal syntax to do either.

Solution

  • Group on the following formula:

    local stringvar array team_array := Split({team}, " ");
team_array[1] + " " + ToText(val(team_array[2]), '00');

    "Team 1" is converted to "Team 01"