Crosstabs and Summations

Crosstabs present counts of two fields in a matrix, with one field as the rows and the other field as the columns of the matrix.

Summations present counts for one field. For example, total cases closed per office, total hours per timekeeper, and so on.

A report can have multiple summations and crosstabs in any order. They appear in the order they are added and you can't change that (without deleting and re-adding).

Add crosstabs and summations in the Additional Display Formats section.

In this Article:

• Examples
• Values to Count
• Subsections
• Bins
• Sorting

Examples

Click the Summations link in Additional Display Formats to add a new section to your report:

The Summations settings above would produce output like this on the report:

Click the Crosstabs link in Additional Display Formats to add a new section to your report:

The above settings would produce output like this:

Values to Count

Summations need a displayed numeric value to count. For a time report, this would typically be Time Spent. For a case report, you need a magic column that displays a "1" for each row. Your site might already have a "Case Count" or "Count for Totals" or "Row Count" field available in Add Columns. If not, go into "Advanced Add Columns". Under Method 2, expand the Fields list under the top level table and add a "New Literal Numeric Field". After you close that window, refresh the report data. Edit the new column, optionally change the Header to something like "Row Count", then put a "1" (without the quote marks) in the Expression box. Then "Apply and Refresh". You can now use that column to count in your summation(s) on the report.

Crosstabs can count a Database ID column directly, without needing to display it or aggregate it. The Value attribute in crosstabs can also Sum, Average, and provide a Unique Count of the value being counted. Unique Count can be useful on reports with repeated rows. For example, a report on time on cases will have one row per timeslip, and if displayed, the case number will be repeated on each row. If you want an accurate count of cases on that report, set the Value to Database ID (of the case) and the Operator to Unique Count. This will only count a case ID once, even it appears multiple times because a case has multiple timeslips.

Subsections

Summation row subsections are limited to fields that have a relationship defined within the database, for example Legal Problem Codes and Legal Problem Categories.

Crosstab subsections can be unrelated fields, for example Gender and Race as shown above.

Bins

Summations and crosstabs can use Bins to group numeric fields into rows or columns. A common use of this is with the Age at Intake field in the standard Age-Race crosstab report. Without bins, a field like Age at Intake would produce a row or column for each age in the report: 1, 2, 3, 4, and so on up to the oldest age returned by the report. To provide a compact and more useful report, you can enter the following text into either a row bin or column bin:

0-17,18-59,60+:60-999

This will group the results into 3 columns or rows. Used in a column bin, this would produce results like this:

The format for a bins statement is numeric ranges separated by commas. Each numeric range can be preceded by an optional label and colon. In the example, 60+:60-999, the "60+:" is the label shown and "60-999" is the numeric range of ages to put into that bin.

The 'upper' range in this example could be set to 60-100 instead of 60-999. If the data returned by the report contained an Age at Intake greater than 100, a new column would appear. This can be used to identify records that likely have an incorrect DOB.

Numeric ranges in bins are inclusive for the end of a range. For example, if a bin statement is "0-125,125-200", a case with a percentage of poverty of 125.00 would fall into the first bin, while a case at 125.01 would fall into the second bin. More explicit ranges like "0-125,125.01" are recommended since they are easier to read and don't require knowing how ambiguous ranges are interpreted.

You can't use open-ended ranges, like "60-".

999 was the old maximum value for a bin range. It is now 2,147,483,647.

Sorting

Crosstabs have built in sorting that you can't change.

Summations have an optional Sort setting. If your summation is not sorting as expected, the field is likely also sorted in the data table. Remove the sort on the data table by clicking the triangle by the column heading for that field, or deselect the Sort option in the Summation settings.