Sometimes, you want values grouped together. For example, rather than displaying individual ages, such as 25, 26, 27, and so on, you want them grouped, such as "Under 20," "20 - 29," "30 - 39," and so on. To do that, create a formula using an ICASE statement that determines what to display if the value falls into a certain group. For example:

ICASE(Student.Age < 20, " Under 20", 
    Student.Age < 30, "20 - 29", 
    Student.Age < 40, "30 - 39", 
    "40 +")

While this expression appears over several lines for easy of reading, it should be entered on one line.

Note the space at the start of " Under 20"; this ensures it sorts before "20 - 29". Because the expression checks for the numbers from lowest to highest, it doesn’t have to check for ranges such as BETWEEN(Student.Age, 20, 29); the earlier comparison eliminated lesser values. Also notice there is no final condition: that means "any other values", which is displayed as "40+".

Here's another example: this groups car manufacturer by country:

ICASE(INLIST(Auto.Make, "Ford", "GM"), "U.S.", 
    INLIST(Auto.Make, "BMW", "Volkswagen"), "Germany", 
    "Other")

While this expression appears over several lines for easy of reading, it should be entered on one line.

Note that if there are a lot of groups or a lot of values in each group, the expression may be longer than 255 characters, which is the maximum length of an expression. In that case, create a function that does the work and call the function from the formula. For example, here is the expression for a formula handling age groups:

GetAgeGroup(Student.Age)

Here is the code for that function:

lparameters tnAge
do case
    case tnAge < 20
        return " Under 20"
    case tnAge < 30
        return "20 - 29"
    case tnAge < 40
        return "30 - 39"
    case tnAge < 50
        return "40 - 49"
    case tnAge < 60
        return "50 - 59"
    case tnAge < 70
        return "60 - 69"
    case tnAge < 80
        return "70 - 79"
    case tnAge < 90
        return "80 - 89"
    otherwise
        return "90+"
endcase