The Crosstab Filter

The Crosstab Filter element is used with datalayer elements to "pivot" columns of data into rows of data. It converts related rows of data into a single row with multiple columns.

The following topics discuss the Crosstab Filter element:

• Applying the Crosstab Filter to a Crosstab Table
• Applying the Crosstab Filter to a Chart
• Using Dynamic Crosstab Filters

About the Crosstab Filter

The Crosstab Filter element is available for use with all datalayer elements and is the functional opposite of the UnCrosstab Filter element. In operation, the element basically converts all rows in the datalayer which share a common identifying value into a single row with multiple columns that contain the column values from all the original rows.

It's a little easier to understand the filter's actions when you can see the effect limited to one set of related rows, as shown above.

This "pivot" action denormalizes the data on the fly and, among other applications, can be really useful when having to work with data imported from spreadsheets.

To configure a Crosstab Filter, you specify datalayer columns as the Crosstab Column, the Label Column, and the Value Column, and select a Value Function (Any, Average, Count, DistinctCount, Median, Mode, StdDev, or Sum).

When the filter runs, a row is generated in the resulting datalayer for each distinct value in the designated Label Column, and a column is generated for each distinct value found in the designated Crosstab Column. The columns or "cells" in the resulting datalayer are derived from the designated Value Column by adding, counting, averaging, etc. (depending on the Value Function chosen) all the values unique to each Crosstab Column and Label Column. You can also group and summarize Value Columns and Label Columns by adding Secondary Label Columns and Summary Rows to your Crosstab Filter.

Datalayer rows that initially have blank Label Column values or blank Crosstab Column values are not included by default in the resulting datalayer. To include these rows, add a Calculated Column child element to the datalayer before the Crosstab Filter, configure it to provide a non-blank value if a blank value is found, then use it as the Crosstab Column or Label Column.

Other datalayer filter elements you may choose to add to your definition must not be configured to affect the columns designated when configuring the Crosstab Filter.