Grouping Values Together

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 +")

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")

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