Cross-tabulating ranges of values – PB Docs 2018

You can build a crosstab where each row tabulates a range of
values, instead of one discrete value, and you can make each column in
the crosstab correspond to a range of values.

For example, in cross-tabulating departmental salary
information, you might want one row in the crosstab to count all
employees making between $30,000 and $40,000, the next row to count
all employees making between $40,000 and $50,000, and so on.

Example

This is best illustrated with an example.

You want to know how many employees in each department earn
between $30,000 and $40,000, how many earn between $40,000 and
$50,000, how many earn between $50,000 and $60,000, and so on. To do
this, you want a crosstab where each row corresponds to a $10,000
range of salary.

The first step is to determine the expression that, given a
salary, returns the next smaller salary that is a multiple of $10,000.
For example, given a salary of $34,000, the expression would return
$30,000, and given a salary of $47,000, the expression would return
$40,000. You can use the Int function to accomplish this, as
follows:

That expression divides the salary by 10,000 and takes the
integer portion, then multiplies the result by 10,000. So for $34,000,
the expression returns $30,000, as follows:

With this information you can build the crosstab. The following
uses the Employee table in the PB Demo DB:

1. Build a crosstab and retrieve the dept_id and salary
   columns.

2. In the Crosstab Definition dialog box, drag the dept_id
   column to the Columns box.

3. Drag the salary column to the Rows box and to the Values box
   and edit the expressions.

4. In the Rows box, use:

   PowerBuilder

   int(salary/10000) * 10000

   1	int(salary/10000) * 10000

5. In the Values box, use:

   PowerBuilder

   count(int(salary/10000) * 10000 for crosstab)

   1	count(int(salary/10000) * 10000 for crosstab)

   For more on providing expressions in a crosstab, see Using expressions.

6. Click OK.

   This is the result in the Design view:

   

   This is the crosstab at runtime:

   

   You can see, for example, that 2 people in department 400
   and 5 in department 500 earn between $20,000 and $30,000.

Displaying blank values as zero

In the preceding crosstab, several of the cells in the grid are
blank. There are no employees in some salary ranges, so the value of
those cells is null. To make the crosstab easier to read, you can add
a display format to fields that can have null values so that they
display a zero.