A Cross Tabulation (often abbreviated as "crosstab") is a data table that displays the joint distribution of two or more variables simultaneously. Sometimes called "pivot tables", they make it easy to sort, count, and total their data. The Logi Crosstab Table element makes it easy to implement this kind of table.
The Logi Info Crosstab Table element is a dynamic, data-driven reporting component that consists of the following three datalayer column types:
- Crosstab (also called Header) Column- Creates a new column in the crosstab table for each unique value. The column value appears in the table header.
- Label Column- creates a new row in the crosstab table for each unique value. The column value appears in the first (left-most) column of the row.
- Value Column- displays a value in each cell at the intersection of the Crosstab columns and Label rows (excluding the header row and first column).
Here's how each of these data column types appear in a crosstab table:
Each value displayed in the Value Column cells is the result of an aggregation, performed on the original data from the specified Value Column. The example above displays years for the Crosstab Column, employee names for the Label Column and a sum of corresponding subtotals for the Value Columns. So, when the year is 2013 and the employee is Nancy Davolio, the sum of all corresponding subtotals is $35,764.51.
Note that, to prevent creation of unmanageable tables, numeric type columns are not available for use in a Crosstab table as the Crosstab (Header) or the Label columns.
The following functions are available for aggregating Value Columns:
- Any - Displays a value from any of the rows. This can be used when the specified Value Column data is a string that isn't appropriate for aggregation; for example, when there's just one record to represent each crosstab cell.
- Average - Returns the average of all corresponding records in the specified Value Column.
- Count - Returns the total number of corresponding records in the specified Value Column.
- DistinctCount - Returns the total number of unique corresponding records in the specified Value Column.
- Max - Returns the maximum value in the specified Value Column.
- Median - Returns the value that separates the higher half of all values in the specified Value Column from the lower half.
- Min - Returns the minimum value in the specified Value Column.
- Mode - Returns the value that occurs the most frequently in records in the specified Value Column.
- StdDev - (Standard Deviation) Returns a simple measure of the variability of data in records in the specified Value Column. A low standard deviation indicates that the values tend to be very close to each other, while a high standard deviation indicates that the values are "spread out" over a large range.
- Sum - Returns the sum of all corresponding records in the specified Value Column
Columns with null values are excluded from aggregations by default. If you want to include them instead, create the constant rdCalculationsIncludeNulls in your _Settings definition and set it to True. Note that this will affect all calculations throughout the application.
What about differences between columns, either as values or as percentages? The Crosstab Comparison element can display these differences in a variety of ways. The element is discussed in detail in a later section.
The fastest and easiest way to create a crosstab table is to use the Crosstab Table Wizard in Logi Studio. Here's how:
- As shown above, in a definition in Logi Studio, select an element and either click the Crosstab Table item in the main menu's Wizards tab, or right-click the element and select Element Wizards, and then select Add a Crosstab Table from the secondary menu.
- A series of dialog boxes, shown above, will be displayed. These all relate to the retrieval of the data. Make appropriate selections for your application and click Next to move to the next dialog box.
The wizard will display a model crosstab table in a dialog box, where you can adjust it, if desired. If the table is just as you want it, click Next to exit the wizard.
In order to prevent creation of unmanageable tables, numeric type columns are not available for use as the Header Values or Label Values columns.
The Max Rows option at the bottom of the dialog box controls the maximum number of rows that will be retained in the table's datalayer.
When you exit the wizard, it will finish inserting all the elements for the table, properly configured, into your report definition. Only the table itself will be added, not any of the configuration controls you've just worked with. Preview your application and you should see your new table.
You can create a crosstab table in Logi Studio manually by adding the appropriate elements to a report definition. Every crosstab table uses the following minimum components:
- One Crosstab Table element
- One or more DataLayer elements with a Crosstab Filter element to retrieve and shape the data
- One or more Crosstab Table Label Column elements to display the Label column data
- One or more Crosstab Table Value Columns element to display the Value column data
The elements above define the most basic crosstab table. Other elements, such as Condition Filters, Sort Filters, and Calculated Columns, can be used to manipulate the data in the datalayer in the same way they would be used for any data table or chart.
If you use a Sort Filter beneath your datalayer, its effect on the table display may be different depending on its placement before (above) or after (below) the Crosstab Filter element. This is particularly relevant if your data returns some zero values, and you may want to experiment with this element in different locations in your definition.
The Crosstab Table Label Column element includes the Scope Row Header attribute. When set to True, the HTML output for data cells will be modified to improve Section 508 accessibility, creating a column which includes header information for each row, changing the <TD> tags to <TH Scope="Row"> tags.
A number of elements that are available for use with regular data tables, such as Header Row, Interactive Paging, and More Info Row, can also be used with crosstab tables. See our Data Table documentation for information about using these elements.
Instead of using the wizard, developers can manually build crosstab tables using two types of crosstab table columns: Label Columns and Value Columns. Crosstab tables have one Label Column by default; the number of Value Column cells is determined by the number of Crosstab Column values.
Here's how to add a Crosstab Table Label column:
- As shown above, in Logi Studio, select the parent Crosstab Table element and add a Crosstab Table Label Column element beneath it. Configure any of its optional attributes.
- Then, add a Label element beneath the new column and use a regular @Data token to reference values from a column returned by the datalayer.
Next, we want to add Crosstab Table Value Columns elements to create the Value columns for the crosstab table. Value columns contain the results of the aggregations of the data from the specified column.
But the results of the aggregations do not appear in the datalayer under any of its column names, so how is it referenced? Special @Data tokens allow you to access Crosstab column and Value data. Here are the tokens:
These special tokens are:
- @Data.rdCrosstabColumn~ - Returns the values in the specified Crosstab Column
- @Data.rdCrosstabValue~ - Returns the aggregated values in the specified Value Column
- @Data.rdCrosstabValCount~ - Returns the number of corresponding records from the Value Column used to calculate the current aggregate value if the aggregation is Sum, Count, or Average.
Let's add a Value Column and set its attributes using the special tokens:
- Beneath the Crosstab Table element, add a Crosstab Table Value Columns element, as shown above.
- We want to use data in the column headers, so set the element's Column Header attribute to one of the special tokens: @Data.rdCrosstabColumn~.
- Add a Label element beneath the Crosstab Table Value Columns element and set its Caption attribute to @Data.rdCrosstabValue~, as shown above, to display the aggregated crosstab values.
The developer can configure Crosstab Table columns so that they can be rearranged and/or resized at runtime by the user.
As shown above, when the Crosstab Table element's Draggable Columns attribute is set to True, a dotted icon appears in the column headers when the mouse is hovered over their left side and the cursor changes to left-right arrows. The entire column can then be dragged left or right and dropped into a new position. The new column arrangement will be "remembered" for the duration of the current user session. Draggable Columns cannot be used when there are custom header rows, with
columns that span multiple columns, in use. The default value for Draggable Columns is False.
When the Resizable Columns attribute is set to True, a dotted icon appears in the column headers when the mouse is hovered over their right side and the cursor changes shape. The icon can then be dragged left or right, contracting or expanding the width of the column. The new column width will be "remembered" for the duration of the current user session. The default value for Resizable Columns is False.
Resizable Columns cannot be used when there are custom header rows with columns that span multiple columns in use.
It's not uncommon to need to include the difference, either as a value or as a percentage, between two or more Value Columns in a crosstab table. There are two approaches to creating this information.
The Crosstab Comparison Element
The Crosstab Comparison element makes it easy to visually understand the differences between values in crosstab columns:
Each column is compared with the previous column and an indicator arrow or colored background is shown if the value increased or decreased. The amount of increase or decrease can be displayed as a numeric and/or percentage difference.
As shown above, comparisons between columns can be indicated by colored arrows and percentages or literal values, within each column cell or in tooltips that appear when the mouse is hovered on a value. Instead of the colored arrows, you can display a colored cell background, covering a spectrum, to indicate the differences.
The unique attributes of the Crosstab Comparison element are:
Comparison Show Tooltips
Enables display of the numerical difference in a tooltip when the mouse is hovered over a crosstab value. Display options include Percent, Value, All, or the default, None.
Comparison Show Values
Enables display of the numerical difference in the crosstab cells. Display options include Percent, Value, All, or the default, None. The Format attribute can be used to format the displayed numbers.
Specifies how differences will be indicated visually. Options include None, ColorSpectrum, and the default, Arrows.
Specifies a format for values or percentages displayed. For more information, see Format Data.
High Color Value
When ColorSpectrum has been selected as the Comparison Style, specifies the color to be used for the highest value in the numeric range. Default: Green
Low Color Value
When ColorSpectrum has been selected as the Comparison Style, specifies the color to be used for the lowest value in the numeric range. Default: Red
Medium Color Value
When ColorSpectrum has been selected as the Comparison Style, specifies the color to be used for the value in the middle of the numeric range.
If the Crosstab Table element's Draggable Columns attribute has been set to True, and the Crosstab Comparison element is in use, when columns are dragged to change their order, their comparison values will be automatically updated.
Because the calculations made by this element do not affect the table's datalayer, it's not possible to summarize or sort the results.
Calculating Comparisons Using Tokens
The second approach to Value Column comparisons allows other calculations, such as comparisons to current dates or to external values, and is accomplished entirely within the Label element used to present the results.
In the example above, a Crosstab Table Label Column element ("colDifference") has been added with a Label element beneath it. The Label element's Caption attribute includes a formula that relies on the fact that crosstab table value columns can be individually addressed using the tokens @Data.rdCrosstabValue-0~, @Data.rdCrosstabValue-1~, ...@Data.rdCrosstabValue-n~.
To display the difference between two values, for example, the Label caption would use this formula:
=@Data.rdCrosstabValue-1~ - @Data.rdCrosstabValue-0~
And, we can add the same element again to display the percentage difference between two values. The child Label element beneath it would use this formula in its Caption attribute:
=IIF(@Data.rdCrosstabValue-1~ - @Data.rdCrosstabValue-0~ < 1, (@Data.rdCrosstabValue-1~ - @Data.rdCrosstabValue-0~)/@Data.rdCrosstabValue-0~, (@Data.rdCrosstabValue-1~/@Data.rdCrosstabValue-0~) - 1 )
Both Label elements need to have their Format attributes set properly to display the numeric value correctly.
With both of these difference calculations included, the resulting crosstab table looks like the example shown above. Again, because the calculations do not affect the table's datalayer, it's not possible to summarize or sort the results.
Sorting and Summarizing Columns
As mentioned earlier, you can apply a Sort Filter element beneath your datalayer, before or after the Crosstab Filter, to sort the actual data. In addition, you can use sorting elements within the table columns.
The sorting and summary features for Crosstab Table Label Columns are functionally similar to those for data table columns: Sort and Data Column Summary child elements are added beneath them.
However, because of its special dynamic-column nature, the Crosstab Table Value Columns element has its own special sort element: the Crosstab Value Column Sort element is used to sort by value columns.
When using a standard Data Column Summary element to summarize crosstab Value columns, set its Data Column attribute to rdCrosstabColumn, as shown above.
A standard Summary Row element is added to create a row across the bottom of the crosstab table to display the summarized value for each column with a Data Column Summary element.
And the results look like the example shown above. By default, the Summary Row element's columns inherit the formatting and alignment of the value column above them.
For more precise control of the summary row, you may use Column Cell elements (beneath Crosstab Table Label Columns) and/or Crosstab Table Summary Column elements (beneath Crosstab Table Value Columns) to construct the row. These allow you to span rows and columns and set custom alignment and formatting.
A crosstab table does a great job of aggregating data and displaying it in a manner that makes it easy to see its progression or differences. There are times, however, when it's useful to be able to examine the detail data that the crosstab filter used to create the value in each column cell. The Group Drillthrough element provides this functionality.
In the example above, a Group Drillthrough element has been added beneath a Crosstab Table Value Columns element. All of the element's attributes, other than ID, are optional but the example shows a custom caption and export button selections have been entered. More information about the attributes can be found in the element's Element
The resulting crosstab table is shown above. When the cursor is hovered over a value cell, a drill-through icon is displayed.
When the icon is clicked, a report containing all of the relevant detail data, like the example shown above, is automatically generated and displayed.
The Group Drillthrough element's attributes allow you to customize the icon image that appears in the crosstab table and certain aspects of the detail report. You can also include Drillthrough Column elements beneath the Group Drillthrough element; they allow you to set the number columns that will appear in the report and to customize their appearance.
A Crosstab Filter element is required to convert, or "pivot", data retrieved into a datalayer for use in crosstab tables. As discussed in the first section of this topic, in configuring this filter you specify three columns in the datalayer: the Crosstab Column, the Label Column, and the Value Column. You must also choose an aggregate function to apply to records from the Value Column.
The filter groups the data based on the designated Crosstab Column. If the Crosstab Column contains numerous distinct records, many Crosstab Value columns may be generated and the table can become quite wide. This can be controlled using the optional Maximum Crosstab Columns attribute, which limits the number of Crosstab Value columns that appear in the table.
Similarly, the Maximum Crosstab Rows attribute sets the maximum number of crosstab rows created by the Crosstab Filter. In some cases this number should be limited so that the table does not become too large in memory. The default limit is 10,000 rows.
Extra Label Columns
One Crosstab Table Label Column may not be sufficient to display all the necessary information in the table.
As shown above, developers can add Extra Crosstab Label Column elements beneath a Crosstab Filter element in order to add additional columns from the datalayer as Label columns.
To display the data for these extra label columns, an @Data token is used but the token identifier matches the ID of the Extra Crosstab Label Column element, not the datalayer column it represents, as shown above.
Summarizing Across a Row
In an earlier section we saw that you can summarize crosstab column data (a vertical summary) and display the totals in a summary row at the bottom. You can also summarize crosstab row data (a horizontal summary) and show row totals in a separate column.
As shown above, the Crosstab Row Summary Column element can be added beneath a Crosstab Filter element to generate new columns in the filtered datalayer that contain aggregations of every value in each crosstab row.
You can specify an aggregation of Average, AverageOfAllRows, Count, CountOfAllRows,Max (v12.7), Min (v12.7), StdDev, or Sum. For example, to count the number of value columns, set this attribute to Count; in the image above there are value columns for 2013, 2014, and 2015, so the value calculated will be 3.
Additional Crosstab Table Label Column elements, such as "colRowTotal" above, can be added as needed to display the summarized results. The @Data token used to retrieve the data uses the ID of the Crosstab Row Summary Column element. So if, in the example above, the Crosstab Row Summary Column element was given an ID of "crsTotal", then @Data.crsTotal~ would be used in a Label element to display the data.
Similarly, a Data Column Summary element ("dcsTotal") can be added, configured to aggregate the "crsTotal" column, in order to total the new column vertically and include it in the Summary row at the bottom of the table.
Crosstab Filters can also be used with the datalayers that provide data for charts and gauges, however, they're designed for use with Crosstab Table elements and may not provide full functionality with other elements.
The Include Condition Attribute
The Crosstab Filter element has an Include Condition attribute and if the value of this attribute is left blank or contains a formula that evaluates to True, the Crosstab Filter element is applied to the datalayer. If the value evaluates to False, the filter is ignored and does not affect the datalayer. This powerful feature allows developers to dynamically determine if the datalayer will be manipulated or not.
Before adding elements to a definition, it's a good idea to first plan the table (or chart) that you're trying to produce. A simple sketch can often provide clarity and save you from having to re-work your definition later. Look at the crosstab table layout model shown below:
The table is limited to three years of data, groups products by company, provides summarized data for each year in several categories and for all products, and summarizes across all years as well. With this layout in hand, we can proceed to build the table.
The following examples demonstrate how to use a variety of elements to produce this table. The complete, downloadable definition for this crosstab table is available in the Crosstab Table Sample Application on DevNet.
As discussed on the previous page, the Crosstab Filter element determines the basic structure of the table:
The definition starts with a Crosstab Table element (no attributes except an element ID need be set), a datalayer to retrieve the data, and a Crosstab Filter element to format the data. In the filter attributes shown above, the table will have a label column for the Product Names, be grouped horizontally (crosstab) by Order Year, and the initial values will be the sum of the order Quantity.
A Crosstab Table Label Column element ("colProductName") and a Crosstab Table Value Columns element ("colQuantity") are added to the definition to display the data. Note that a special token is needed to access the Value column data: @Data.rdCrosstabValue~. The token identifier uses the reserved word "rdCrosstabValue" and the token is used in the Caption attribute of a Label element beneath the Crosstab Table Value Columns element.
Referring to our crosstab layout model, the seven elements added so far provide the portions highlighted in yellow above.
If we preview our definition at this point, the crosstab table looks like the example shown above. Now let's add another Label column to display the names of the suppliers.
In the previous page, the Extra Crosstab Label Column element was discussed. As shown above, one of these elements is added beneath the Crosstab Filter and set to use the CompanyName datalayer column to create the "Supplier" column. A Sort Filter is added to the datalayer and set to sort on the new Supplier column to get things into order by Supplier.
To display the new column, another Crosstab Table Label Column element ("colSupplier") and a Label element to display the data are added. Finally, adding a Hide Duplicates element results in a hierarchical look by hiding the supplier name in consecutive rows.
The result is the addition of the Supplier column, highlighted in yellow above in the crosstab layout model.
And, in a Preview, the table now looks like the image shown above.
You will need to apply formatting to various elements, such as Crosstab Table Value Columns and Column Cells, to align the data properly. This example uses the ThemeAlignRight style class to do this.
By default, the Crosstab Filter produces a single value for each column and row combination. In order to generate additional values for the Total Sales data for our crosstab table, we need to use an Extra Crosstab Value Column element.
As shown above, this element simply identifies a column from the datalayer ("OrderTotals") and the aggregation to be applied to it.
A Crosstab Table Value Columns element ("colOrderTotal") is also added to display the values. Note that a special token is needed to access that data: @Data.rdCrosstabValue-ODTotal~. The token identifier combines the reserved word "rdCrosstabValue", then a dash "-", and then the ID of the element beneath the Crosstab Filter that represents the value column ("ODTotal"). This token is used in the Caption attribute of a Label element beneath
Table Value Columns element we added.
Next, we'll repeat the previous steps in order to add an Average Unit Price column, this time using an Extra Crosstab Calculated Column element. In the example shown above, the average unit price is calculated using this element. The complete formula in this example is:
which divides the Order Total by the Quantity. The Order Total value is accessed using the same token discussed earlier and used to display the order total values; while the Quantity value is accessed using a similar token representing the original Value column defined in the Crosstab Filter.
The elements added and configured in this step add the columns highlighted above.
And, in a Preview, the table now looks like the image shown above.
Our plan calls for adding three columns in the table that summarize row values ((horizontal summary). This is done by adding Crosstab Row Summary Column elements to the Crosstab Filter:
The first of these elements refers to the original Value column defined in the Crosstab Filter attributes, Quantity, so all that is required is that the element be added beneath the filter, be given an ID, and its summarizing function be set to Sum, as shown above.
The second element of this type is summarizing an Extra Crosstab Value Column ("ODTotal") so it needs to include that elements's ID, as highlighted in yellow above, in the Extra Crosstab Value Column ID attribute.
Three Crosstab Table Label Columns are also added to display the summarized values. The first two reference their data using Label elements and standard tokens @Data.crsSumQuantity~ and @Data.crsSumODTotal~. The third one divides the other two using the formula =@Data.crsSumODTotal~/@Data.crsSumQuantity~to derive its value.
Referring to our layout model, the addition of the Crosstab Row Summary Column elements adds the columns highlighted in yellow above.
And, in a Preview, the table now looks like the image shown above.
The header row ties the crosstab value columns together and provides data-driven identification for them as well. Before proceeding, look at the header on our plan: it consists of a blank area that spans two columns, three areas that will contain data (the year number) and span three value columns each, and an area labeled "Total" that spans three columns.
We'll start by adding a Header Row element to the crosstab table, as shown above, then "build-up" the row using a combination of elements. Set the Header Row element's required ID and its Header Position attribute to Top.
Next, we add a Column Cell element that will be a "spacer" or placeholder; its attributes are set to span the two columns beneath it. It contains nothing.
Next, add a Crosstab Table Header Column element, with a required ID and configured to span three columns. Beneath it add a Label element and set its Caption attribute value to the token @Data.rdCrosstabColumn~. It will display the actual data from the datalayer column identified as the Crosstab Column in the Crosstab Filter's attributes (in our example, this is "OrderYear").
Finally, add a second Column Cell element and configure it to span three columns, as shown above. Beneath it add a Label element with the word "Total" as its Caption attribute value.
The elements added in this step create the header row, highlighted in yellow above.
And the resulting table, with header, looks like the image above.
The creation of a Summary Row at the bottom of a table with summarizing column values is a bit like creating the header row, in that it's built up column-by-column.
The first step in this process is to add Data Column Summary elements beneath the columns to be summarized. The columns that show the average Unit Price do not need these elements as their summarized value can be derived by dividing the summarized values of two other columns.
Note that the special column names, rdCrosstabValue-ODTotal and rdCrosstabValue, discussed earlier in the section on Extra Crosstab Value Columns, are used in the Data Column Summary elements' attributes for the two extra value columns, as shown above.
The IDs of the Data Column Summary elements ("sumODTotal", "sumQuantity", etc.) will be used in the next step with an @Data token to identify the summarized data.
Similarly, Summary Row elements are then added beneath Crosstab Label Column elements that display the row (horizontal) totals for each row.
In the example above, a Summary Row element has been added beneath the crosstab table. Like the Header Row, it will contains elements that "build up" the columns needed for the summaries. Column Cell elements are used for non-crosstab value columns and Crosstab Table Summary Column elements are used for the value columns.
The first Column Cell element is set to span two columns (it will be underneath the Supplier and Product columns) and contains a New Line element and a Label element with a Caption of "Totals:".
Next, three Crosstab Table Summary Columns have been added. The first two are used to display the summarized OrderTotal and Quantity value columns. Each contains a New Line and a Label element beneath it, as shown above. The Label elements' Caption attributes are set to the @Data token for the related Data Column Summary column.
The third Crosstab Table Summary Column is just a placeholder and needs no child elements.
Finally, three Column Cell elements are added, as shown above, to display the two summary totals for the row Order Total and Quantity summary columns and a final spacer column.
And the resulting crosstab table, with header and summary rows, looks like the image above.