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:

        
less
        

            
        
     
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:

        
foxpro
        

            
        
     
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:

        
css
        

            
        
     
GetAgeGroup(Student.Age)

Here is the code for that function:

        
foxpro
        

            
        
     
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