Cross-tabulating ranges of values

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.

StepsTo cross-tabulate ranges of values:

  1. Determine the expression that results in the raw values being converted into one of a small set of fixed values.

    Each of those values will form a row or column in the crosstab.

  2. Specify the expression in the Columns or Rows box in the Crosstab Definition dialog box.

    You choose the box depending on whether you want the columns or rows to correspond to the range of values.

  3. In the Values column, apply the appropriate aggregate function to the expression.

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:

int(salary/10000) * 10000

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:

34000/10000 = 3.4
int(3.4) = 3
3 * 10000 = 30000

With this information you can build the crosstab. The following uses the Employee table in the ASA asademo 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.

    In the Rows box, use:

    int(salary/10000) * 10000

    In the Values box, use:

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

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

  4. Click OK.

    This is the result in the Design view:

    The sample Design view is divided vertically into four areas. The rightmost area is blank for all sections. At top is Header ( one ). In the leftmost area, the header shows the title Number of employees by department and salary, and the subtitile $30,000 includes up to $39,999. The next area is blank. The third area shows Total number of employees making the salary. Next is Header two. It has Salary in the lower part of the leftmost area, the truncated expression display ( @ in the lower part of the second, and Department I D at the top of the third. Next is Detail, with row _ column, val, and cross tab sum ( one ) in the three areas. Next is Summary. The leftmost area displays Total number of employees in the department and the second area shows the truncated expression sum ( val for. At bottom is the empty footer section.

    This is the crosstab at runtime:

    The sample crosstab is divided vertically into seven columns. At top left is the title Number of employees by department and salary and the subtitile $30,000 includes up to $39,999. The next five areas are blank. The last area shows Total number of employees making the salary. In the next row, Department I D spans the middle five columns, and under that, Salary is on the far left, the five columns under Department I D are labeled 100, 200, 300, 400, 500, and the last cell is blank. Next are nine rows under Salary for the ranges $20,000 through $130,000. The number of employees in each range are shown in each department column, with grand totals in the far right column. On the bottom line, the leftmost area displays Total number of employees in the department, and the next five columns display totals for each department.

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.

StepsTo display blank values in a crosstab as zero:

  1. Select the column you want to modify and click the Format tab in the Properties view.

  2. Replace [General] in the Format box with ###0;###0;0;0.

    The fourth section in the mask causes a null value to be represented as zero.




Copyright © 2007. Sybase Inc. All rights reserved. Creating static crosstabs