Crosstabs provide users with the tools to nest multiple fields as rows or columns, select multiple measures and calculations, apply traffic lighting and field formatting on any data and produce tables of results. Charts are easily added alongside Crosstabs by selecting from a range of pre-defined chart types.

To access the Crosstab functionality, click Crosstab at the top of the screen.

There are six tabs to navigate when creating a Crosstab: Axes, Totals, Layout, Drill, Charting and Run Report.

To discard any changes and start again, click New Crosstab at the top of the screen.

As options are selected, a preview of the Crosstab is generated at the bottom of the screen.

Axes

In this tab, the fields for the rows and columns can be selected.

Use the drop-down list to select fields below the Available Rows and Available Columns headings to populate the Crosstab. All fields made available by the system administrator in the currently loaded Index are available.

To use a saved Query as a row or column, click the Saved option at the bottom of the drop-down list. Click … next to the Saved field then Select Reports to choose a Query. Check the box next to the desired queries to import and click the Selected Reports tab to review the selections. Untick any that are not required and click Add to Crosstab to import the checked reports.

To delete all selections for a row or column click None next to the drop-down list. To remove individual fields, click the X icon next to the relevant field.

Adding two or more row or column entries allows multiple fields to be nested into a single Crosstab. Selected entries can be ordered by dragging the entries into the required order, and the underlying data can be sorted by clicking the Arrow icon. The fields are sorted in ascending order when the arrow points upwards, and are sorted in descending order when pointing downwards.

Click the Visibility icon to hide an axis level from the report. The underlying logic will still be applied in the table as only the heading values are removed.

Rows/Columns Settings

Click the Cog icon to open the corresponding Row Settings and Column Settings popups, where a number of options are available without leaving the Axes tab.

With Default View selected, every level of the report will remain open, providing full visibility of the nested fields. By enabling Expandable Hierarchy, users are able to expand sections of the report while retaining the full report on-screen. With Drillable Hierarchy selected, users can instead view the entire report initially, and drill to a grouping of choice.

Enabling the Row Axis Labels or Column Axis Labels options will add the selected field names onto the report that can be amended by double-clicking the field name when running the report. Change the rotation of the Column Headers by selecting the relevant angle that you want to use. Add row numbers to the report by enabling the Show Row Numbers option.

Use the Row Totals and Column Totals radio buttons to select where the applicable total will be displayed on the report, with the first option hiding the respective total completely.

Enable the Do Not Indent Row Headings option to display a value for every nested row rather that grouping content.

Clicking Switch Axis will swap all the values currently selected as rows and columns.

For further field options, click … next to the relevant fields. If changes are made, the … icon will turn white. Three tabs are available for fields: Values, Groups and Range.

Values

This tab allows the selection of individual values within a field. By default, all values are loaded and displayed on the specified axis.

To select certain values, use the left and right arrows to move the desired fields from Available Values to Selected Values. Re-order fields by using the up and down arrow icons.

By default, only values that contain data will displayed in a Crosstab. Checking the Lock Values On Axis option will display headings even if there is no data present. The cells will appear blank but will populate if associated data is inserted into the Index.

The Exclude Selected Values option changes the function of the Selected Values column, omitting rather than displaying the selected fields.

When displaying data in nested rows, the Always Open option will result in permanent visibility for the field, even when the other fields are collapsed. Fields are collapsed when the Show Rows Hierarchically, Show Columns Hierarchically, Group Rows or Group Columns options are enabled at Axis level.

The Collapse to Drop List option is available for rows only and displayed row values in a drop list rather than the standard table view. 

To include values that are currently filtered out by a query, check the Ignore Current Query option.

At Row level, when Subtotals are enabled, the Hide Subtotals option will become available. Enable this to hide the subtotal output for a nested value. This provides greater control over the nested fields that will have subtotals displayed on the report.

The Value Prefix and Value Suffix radio buttons generate the value names as either a prefix or suffix to their respective values in the Crosstab and can be removed by using the None radio button. To display a custom prefix or suffix, enter the required text in the Axis Prefix and Axis Suffix boxes.

Advanced Options

Click the Advanced Options text to reveal the following configuration options.

It is also possible to replace values with images. Specify the image directory in the Image URL textbox, ensuring that the image names match the field names. Within the URL, %% will be replaced by the value. To specify an image that will be used whenever a match is not found, specify the image directory in the Missing Image URL textbox. Specify the image size, in pixels, with the Width and Height textboxes.

If the selected field contains a URL, enabling the URL option will allow end-users to click the link and view the information in a new browser tab.

To save any changes, click Apply or to discard any changes, click Close. To revert any changes to the default values, click Reset.

Groups

The Groups option allows multiple row or column values to be grouped together.

To create a group, use the left and right arrows to move the desired fields from Available Values to Selected Values, type in a group name and click Add. To edit a group, click the … button next to the relevant group. To remove a group, click the X icon.

The Collapse To Drop List option creates a drop list of groups when the Crosstab is run. Individual values are also included if the Show un-grouped as Individuals option is checked.

Please note that the Ignore Current Query option must be enabled prior to editing a group, and cannot be toggled when the ... icon is clicked for a group.

Ungrouped values can be displayed as either their original value if the Show un-grouped as Individuals option is checked or can be grouped together as ‘Others’ if the Show un-grouped as Others option is checked.

When displaying both groups and individual values, the Sort Groups option forces the values to sort alphanumerically.

Groups can also be created automatically with the Auto Create Groups option. Specify the number of Groups or Values per group and click Create Groups to automate the process.

To save any changes, click Apply or to discard any changes, click Close. To reset any changes to the default values, click Reset.

Range

The Range option can be used for any numeric or date fields to display a customised number of entries.

Enter the required starting point in the From box, the ending point in the To box and the required increment in the Increment box.

Totals

In this tab, users can configure how the data is displayed using one or more different aggregations.

As well as returning results as a Count, it is also possible to Sum, Average or leverage a wide range of mathematical functions on specific numeric fields.

Options

Under the Options heading, there are a number of options that become available when the reporting structure meets certain criteria.

The Accounting Format option, enabled by default, uses brackets to symbolise negative values.

Subtotals

Turn on Subtotals within the report by enabling the Include Subtotals in Report option. Please note that the subtotal options are only available when using nested values within the Axes tab.

Enable the Force Subtotals in Report option to display subtotals against rows where there are no nested values. To display subtotals where the axis level has been hidden in the Axes tab, enable the Show Hidden Sub-totals option.

Replace text In calculations

This option will appear when there are either Row Totals or totals that use the Calculation Builder. Simply enter data to Find and Replace, select the total(s) to which this will apply from the Totals drop list and click the Replace button. 

Aggregation

Use the Total Label text box to customise the display name for the total. A label is not required when running a Crosstab with a single total, but can still be specified if a description of the values is required.

If more than one Total is created, a default name in the following format will be generated: <Total Type> (Measure).

Total Types

Use the Total Type drop-down list to select the total to be performed.

Count

The default total type is a Count. This provides a count of rows based on any rows/columns selected.

Measure

Within the Total Type drop-down list, there are a range of mathematical functions, such as Average and Sum, which can be applied to any numerical fields in the Measure drop-down list. Numerical fields that can be used as a Measure are configured by the administrator.

Standard Calculation

To perform calculations on Index fields using the specified Total Type rather than a direct cell calculation, use the calculation as a Measure. After selecting a Total Type other than Count, Measure will appear as an option.

Cell/Total Calculation

To perform calculations based on existing totals, use Cell calculations. Select Calculation from the Total Type drop-down list and click the … icon to access the Calculation Builder.

String Calculation

This allow the user to provide detailed tooltips on existing totals or provide textual output in the Crosstab. Text must be written in double quotes e.g. "Hello World". String calculations can be split across multiple lines by using "\n". For example:

Customer_Segment + "\n" + "\n" + "Sales | £" + 'Sales' + "\n" + "Discounts | £" + 'Discount'

Would return:

ConsumerSales | £3,063,611.13
Discounts | £82.29

Dynamic Views

If the administrator has configured Dynamic Views for the Index, they will appear in the Dynamic View drop-down list. These can be used with any total type and any measures that they have been configured for or against a default count. This will result in a distinct count of the field in the Dynamic View.

For more information regarding the calculation functionality, please refer to the Calculation Builder chapter.

Row Total and Row Total Calculations will automatically populate the Measure option, and a second drop-down list allowing the selection of specific rows will be available. To select all rows, click the All button and to deselect any rows click the None button. Available rows will be displayed with the following options:

Use the Show Value As drop-down list to customise the displayed output of the total. The following options are available:

Use the Value Format drop-down list to select specific integer, decimal and date formats.

By clicking New Total, it is possible to add multiple aggregations to a single Crosstab.

Presentation

Once the aggregation options have been set, there are multiple options for customising how results are displayed.

The Value Prefix and Value Suffix textboxes allows the addition of text to the values shown in the table, such as £, $, €. These options do not apply to row totals.

By default, totals using dynamic views show the distinct count for the row or column specified in the dynamic view, not the total of all of the cells shown. Similarly, when filtering to a ‘Top Ten’ type report, the total of all values is displayed rather than just the top ten. By using the Calculate Column Total From Sum of Displayed Values option, all displayed values will also be summed. When using this option you cannot drill on the column total.

The Apply Selected Total Type option forces the row or column to perform the Total Type aggregation on displayed values rather than on the underlying data. This is used in conjunction with the Calculate Column Total From Sum of Displayed Values option.

Hiding specific totals will depend on how the Crosstab is configured. To hide the total for an individual measure, use the Hide Row Total option. This is only relevant if there are both rows and columns added to the Crosstab. If there are no columns, all totals are classed as row totals. To hide any total created in the Totals tab, use the Hide Total In Table option.

Use the Suppress Row Total option to remove row totals for Crosstabs with multiple rows. Subtotals are still displayed when this option is used.

To hide the actual value, use the Suppress Total in Table option. This is useful when used in conjunction with conditional formatting to provide only a colour or image when conditions are met.

The Show Blank As drop-down list allows the customisation of how blank totals are displayed.

Row Calculations

This tab will only appear if a placeholder row, without any logic attached, has been created. Row calculations allow calculations between the rows added to the Crosstab.

To create a placeholder row, return to the Axes tab and select Saved from the Available Rows drop-down list. Click the … icon and rather than clicking Select Reports, type a name into the textbox below and click Add. This will create a row with no associated data. Enable the Lock Values on Axis option and click Apply to return to the configuration.

Navigate back to the Totals tab and the Row Calculations tab will now be visible between the default Presentation and Filter tabs. Select the newly created placeholder from the Row Group drop-down list and click the … icon below the Row Calculation heading to open the Calculation Builder, where the row calculations can be entered.

Sort

This tab allows rows and columns to be sorted either ascending or descending using the Row Sort and Column Sort options. The sorting is applied in a hierarchical structure when nested rows or columns and multiple totals are used.

Filter

This tab allows users to filter the Crosstab to only show the top or bottom number of values (or percent of rows/columns). 

Use the Filter drop-down list to select a portion of the data, the Type drop-down list to select either between row, column or cell filtering and enter the required number of values in the Filter Values to Filter text box.

To apply a Threshold to the row total you need to use the Greater Than/Less Than/Equal To options. This is designed to be used when the Crosstab uses Columns and therefore has a combination of table values and row values.

Threshold

This tab allows the search results to be narrowed down to only those that meet a specified condition. Select the relevant option from the Condition drop-down list and complete the equation in the following text box.

The Mandatory checkbox is used if thresholds have been set against multiple totals. Without Mandatory being set, a total may be impacted by the threshold from another total, causing the row appear even if the threshold has been breached.

To apply a threshold to the row total (when using columns and therefore have totals in the table) you need to use the Greater Than/Less Than/ Equal To options in the Filter tab.

Conditional Formatting

This tab allows the setup of specific formatting when customisable conditions are met.

Click New Condition to reveal the options, choose a Source Value from the drop-down list and add a label using the Condition Label textbox if required.

To remove added formatting, click the X icon. To copy a set of conditional formatting, click the Copy icon.

To restrict the formatting to certain area of the report or to a chart, use the Scope drop-down list to select one or more options. Click the Match Parameters drop-down list to set a parameter and complete the numerical equation in the textbox below.

Under the Match Style heading is an example how the current settings will display a total when it matches the parameters set. Click Edit Style to customise the formatting. 

Use the Font and Font Size drop-down lists to specify changes to the text appearance and the Style options to further format matched entries. Change the Text Colour and Background Colour by typing in the hex code or by selecting a colour from the pop-up colour picker. Use the Horizontal Alignment and Vertical Alignment options to control where the value is positioned in the cell. Please note that when the Scope drop-down list is set to Chart, the Font options will not have any impact.

Click the … icon under the Image heading to specify an image file to be displayed. To remove the image, click the Clear button and to specify the Image Alignment use the corresponding radio buttons. There are also options to control the Image Size. Select Original to make no changes to the source image, or select Row Height to dynamically fill the cell size as controlled by the height of the row itself. To specify an exact size, select the Fixed Size option and enter the size, in pixels, in the resulting textboxes. Click Close to save any changes made.

Using the Alerting features, users can set alerts that can be used to drive whether a report schedule is or is not sent, based on whether the condition is met. To set an alert, enable the Alert option and enter the required title in the Name textbox.

Tooltips

Tooltips allow customisable text to appear when the cursor hovers over a particular total, giving end users more information if required.

Enter the desired text in the Default Tooltip textbox. To create individual tooltips for each aggregation, click New Tooltip under the Aggregation Tooltips heading. Select the function using the Tooltip drop-down list and the total types that it will apply to using the Scope drop-down list. To remove a Scope selection, click the X icon next to the corresponding selection.

To create another tooltip, click New Tooltip. To remove a tooltip, click the X icon next to the corresponding selection.

Linking

This tab will only appear if the Drill Through to Records option in the main Linking tab is removed. When this tab appears, users can set different totals to drill down to different reports.

Click Select Report to open the saved reports window and click the checkbox next to the desired report to be linked.

To remove a linked report, either uncheck it from the drop-down list or click the X icon next to the relevant report. 

Layout

Table

The table options allow results table to be presented in a variety of customisable ways.

Header

The header options allow labels to be added to the top of a report. The following labels can be added to the left, middle or right of the report:

Footer

Footers behave in the same way as headers but are displayed at the bottom of the report. Refer to the Headers section for more information.

Custom Labels

Labels can be renamed from the default values if required. To edit, use the textbox under the corresponding label. The User Field textboxes allow custom text to be displayed in a report.

Style

By default, Crosstabs are displayed using a printer-friendly, plain layout. The styling options allow complete customisation over the appearance.

The Theme drop-down list contains a number of colour schemes that will apply to the entire Crosstab. Disable this option by choosing Default from the drop-down list.

Select a specific section of the Crosstab to customise using the Area drop-down list. The Top Left option allows the use of an image or separate formatting of the empty space displayed at the top left of a Crosstab while all other options refer to specific values or locations. Click the Reset button to delete any changes for the currently selected Area, or click Reset All to remove all styling changes.

Select an option from the Copy drop-down list to quickly duplicate changes across multiple Areas. First select from the Area drop-down list the section to be styled, then select from the Copy drop-down list the section to be copied.

For further styling options, click the preview at the bottom of the screen and select the relevant area from the subsequent popup menu. Select Table to style the entire report, or select from the Odd/Even Data Columns and Odd/Even Data Rows. Click a Total value to add specific styling, and select Row Axis Labels or Column Axis Labels, when enabled, to style this area of the report. Font and Font Size are controlled using their own drop-down lists, and the Style checkboxes allow further customisation of displayed values. Use the Text Alignment radio buttons to specify the position of the text/numbers within cells. Change the Text Colour and Background Colour by either typing in the hex code or by selecting a colour from the pop-up colour picker.

Drill

Options

Enabling the Drill on Headers option allows users to subset data by clicking on the column/row header. Once enabled, the drill action for row and column headings can be controlled separately using the relevant Drill Row/Column Headings checkbox.

With the Drill on Values option enabled, users are able to drill down into the underlying data that constitutes a total by clicking a value in the report. Disable this option to remove this functionality from the report.

Display Fields

This option allows the configuration of displayed fields and the order of the Query columns that appear after drilling through on the report. Move fields between the Available and Selected boxes with the left and right arrows and rearrange the Selected box with the Up and Down arrows.

Linking

The Linking options are used to add further controls to the drill through action as well as allowing users to drill though from one report to another.

To add a report, click Select Reports to open the saved reports window, click the checkbox next to the desired report to be linked and click Add to Crosstab. To remove a linked report, click the X icon next to the relevant report.

Links can be displayed as either a drop list, list or buttons and are specified via the Display Drill Through Options As drop-down list. 

If the Drill Through to Records option is removed and no saved reports are selected, another Drill tab will appear in the Totals tab. 

Linking cannot be used with in conjunction with Dynamic Views. The only available drill through option is Drill Through to Records.

Charting

Please refer to the Charting section for a detailed breakdown of each chart type and their requirements.

Run Report

Click the Run Report tab to generate the Crosstab using all of the previously configured options.

When viewing a Crosstab, drill through to the underlying records by clicking on any value.

To save a Crosstab, click the Save icon.

To return to the previous screen, click the Undo button.

If a Crosstab is deemed too large to be generated in a timely manner, a dialogue box will appear with the option to Run as Background Task. If using this option for multiple reports, they will be queued for completion. A progress bar indicating how many reports have been completed is located next to the Undo icon. Clicking this icon allows access to the completed Crosstabs.