Troubleshooting

Group on a Formula

Sometimes when creating a report, the involved database might not natively contain the data that is specified as requiring a group; that is, the required grouping elements will need to be based on some criteria that is not available in the database. A typical example of this problem occurs when a report design calls for a grouping by a demographic range, such as age or income bracket. In the case of age, the database might have an Age field but the business case calls for some form of market analysis by the age ranges 019, 2034, 3549, 5064, and 65 and above. A formula called Date Range similar to that displayed below could solve the reporting problem.

   Select {@Age}        Case Is < 19 :            "0-19"        Case Is > 19, Is < 34 :            "20-34"        Case Is > 34, Is < 49 :            "35-49"        Case Is > 50, Is < 64 :            "50-64"        Case Is > 64:            "65+"

For the record, this sample is based on the Employee table from the Xtreme Sample Database and uses another formula called @Age that resolves to

   (Today - {Employee.Birth Date}) / 365

In situations such as these, it can be useful to group on a formula as discussed in the next section.