Skip to main content

XRCrossTab Class

Displays multi-dimensional data in reports.

Namespace: DevExpress.XtraReports.UI

Assembly: DevExpress.XtraReports.v22.1.dll

Declaration

public class XRCrossTab :
    XRControl,
    IDataContainer,
    IDataSourceAssignable,
    IDataContainerBase,
    IEffectiveDataContainer,
    ISupportInitialize,
    IComplexControl,
    ICrossTabLayout

Remarks

The Cross Tab control allows you to display and analyze multi-dimensional data, such as summary statistics, surveys, balance sheets, and market research information.

Design:

Design

Preview:

Preview

Refer to the following tutorials for details on the Cross Tab control:

Create a Cross-Tab Report
Describes how to create a Cross Tab report with the Cross-Tab Report Wizard. The report is based on the Sales Summary Report demo.
Create a Balance Sheet
Shows how to use the Cross Tab control on the design surface. The tutorial is based on the Balance Sheet demo.

Add the Cross Tab to a Report

Drag the XRCrossTab item from the Toolbox onto a report’s Detail band.

Crosstab Control Drop from Toolbox

You cannot place the Cross Tab in another report control (XRTableCell or XRPanel) because its width cannot be determined at design time.

Bind the Cross Tab to Data

Use the Cross Tab’s DataSource and DataMember properties to bind this control to data.

  1. Click the Cross Tab’s smart tag.
  2. Expand the Data Source property’s drop-down list and click Add Report Data Source.
  3. Follow the steps in the invoked Data Source Wizard to configure a data source.

Crosstab DataSource Smart Tag

Note

If you place the Cross Tab control in the Detail band, ensure that a report’s DataSource property is not set. Otherwise, the Cross Tab data is printed as many times as there are rows in the report data source.

Define Cross Tab Fields

Drop data fields from the Field List onto cross-tab areas to define the control layout. The Cross Tab supports three field types (areas):

Rows (RowFields collection)
Display field values as row headers.
Columns (ColumnFields collection)
Display field values as column headers.
Data (DataFields collection)
Uses field values to calculate summaries at row and column intersections.

Tip

You can use calculated fields if the data source fields do not meet your requirements and you have to preprocess the data before the Crosstab control displays it.

Add Row and Column Fields

You can place two or more data fields in the same area to create a hierarchy. The values of the first field are displayed at the root level (first column/row). The values of the second field are grouped by the values of the first field and are displayed at the second level of the hierarchy (second column/row). The subsequent fields are organized in the same way.

  • Specify Row Fields

    |Drag and Drop Crosstab Row Fields

  • Specify Column Fields

    |Drag and Drop Crosstab Column Fields

Cross Tab cells marked with a database icon are bound to the dropped fields. The corresponding rows/columns are printed in the document as many times as there are field values in the data source. The upper left corner displays the headers for the row data fields.

Rows and columns are automatically added to the Cross tab to display the total values calculated for the added fields. The last row/column displays grand total values calculated for all rows/columns.

Add Data Fields

You can add two or more data fields and arrange them in two ways:

  • In a column, one under the other (field headers are displayed as row headers)

  • In a row, one after the other (field headers are displayed as column headers)

When the data area contains only one field, the field header is not displayed.

Use Unbound Fields

The Cross tab does not support unbound fields. However, you can add calculated fields with arbitrary content as row, column, or data fields to achieve the desired layout. You can also handle the XRCrossTabCell.BeforePrint event to display a desired value in a cell.

Consider Binding Limitations

The following limitations apply when you design the Cross Tab layout:

  • You cannot bind the top left corner, row/column totals, and row/column grand totals to data fields.
  • The Cross Tab has its own Parameters collection, and you cannot directly use report parameters in Cross Tab cell expressions. But you can map a cross tab parameter to a report parameter and use the cross tab parameter in expressions.
  • You can only bind Cross Tab cells to fields from the data source and data member assigned to the DataSource and DataMember properties.

Use HTML Formatting in Cells

You can use HTML-like markup in a cell’s Text and Format String properties to customize cell text. Set the cell’s AllowMarkupText property to True to enable markup support in this cell. For the list of supported tags and their description, refer to the following help topics:

Format Field Values

Use a cell’s TextFormatString property to format output data.

Crosstab Cell Format String Property

You can also use the NullValueText property to specify the text shown when a data field’s value is null or empty.

Preview the Report

Switch to Print Preview to see the Cross Tab populated with data. The following image illustrates Cross Tab elements:

Cross Tab Field Structure

Use the Cross-Tab Wizard

The Cross-Tab Report Wizard allows you to create a new cross-tab report based on a template.

Cross Tab Report Wizard

Cross Tab Report Wizard in Action

Calculate Totals

The Cross Tab calculates the following automatic totals:

  • Row Totals — against outer row fields.
  • Row Grand Totals — against all rows.
  • Column Totals — against outer column fields.
  • Column Grand Totals — against all columns.

Cross Tab Totals

You can use the layout options to move rows and columns that display total values.

If you want to hide certain totals, select any cell in the row/column and disable the XRCrossTabCell.RowVisible/XRCrossTabCell.ColumnVisible property, or bind a Boolean expression to these properties.

Change the Summary Type

The Cross Tab summarizes values of data fields and displays the results at the intersection of the corresponding rows and columns.

Use the SummaryType property to specify the summary function calculated for the specified data field.

Cross Tab Data Field Summary Type

Use the SummaryDisplayType property to display the contribution of results to other cell values (for example, as a percentage of grand total values).

Cross Tab Data Field Summary Display Type

Sort Data

The Cross Tab displays row and column field values in ascending order. Use the SortOrder property to change the current sort order. Set this property to None to keep the same order as records in the Cross Tab’s data source.

Cross Tab Sort Order Designer

You can use the SortBySummaryInfo property to arrange row/column field values based on grand total values.

Group Data

The Cross Tab displays unique values of column and row fields, and does not group their values by default, as the following images illustrate:

Design:

Cross Tab Group Default

Preview:

Cross Tab Group Default Preview

Use the GroupInterval property to combine original field values into categories (groups). You can group DateTime values by year, month, quarter, day, or hour. The following images show the Order Date data grouped by year:

Design:

Cross Tab Group by Year

Preview:

Cross Tab Group by Year Preview

To group numeric values, set the GroupInterval property to Numeric and use the GroupIntervalNumericRange property.

Filter Data

Use the Cross Tab’s FilterString property to invoke the FilterString Editor and specify the filter criteria.

Cross Tab Filter Data

You can add report parameters to the Cross Tab’s Parameters collection and use report parameters in the filter criteria.

Specify Layout Options

Use the LayoutOptions property to adjust the order and layout of the cells in the CrossTab control.

Cross Tab Layout Options

Option

Description

ColumnTotalHeaderPosition

RowTotalHeaderPosition

Specify where to display column/row total headers:

  • In the same row/column as the values of the row/column fields on which the totals are calculated (Inner).
  • Spanned across two rows/columns (Outer).

ColumnTotalsPosition

RowTotalsPosition

Specify the position of the column/row totals and column/row grand totals:

  • After column/row field values (AfterData).
  • Before column/row field values (BeforeData).

CornerHeaderDisplayMode

Specifies what data the Cross Tab should display in the top left corner:

  • Split the corner into columns and display the row field names (RowFieldNames).
  • Split the corner into rows and display the column field names (ColumnFieldNames).
  • Do not split the corner and do not display any text (None).

DataFieldLayout

Specifies how to position two or more data fields in the Cross Tab layout:

  • In a row, one after the other (InRow).
  • In a column, one below the other (InColumn).

HierarchicalRowLayout

Specifies how to display row headers:

  • In tree view, one below the other (true).
  • In one line (false).

Hide Rows and Columns

The XRCrossTabCell.RowVisible and XRCrossTabCell.ColumnVisible properties allow you to hide specific row(s) and column(s). You can select the bottom right cell and disable row and column visibility to hide grand totals. At design time, the Cross Tab control paints invisible cells with a hatch brush.

The following image shows the CrossTab control with a Grand Total cell whose RowVisible and ColumnVisible properties are set to false.

Cross Tab with Hidden Grand Totals

In the preview, rows and columns related to grand totals are hidden:

Cross Tab with Hidden Grand Totals Preview

You can bind an expression to the RowVisible or ColumnVisible properties to hide totals, hide data rows or columns in a group, or hide empty rows/columns.

View Example: How to Hide Cells, Rows and Columns That Meet Certain Conditions

Adjust Width and Height

Drag the Cross Tab’s handlers to change its size. You can also adjust the size of individual rows and columns.

Cross Tab Adjust Control Size

Use a cell’s ColumnAutoWidthMode property to specify whether and how to change the cell’s width to fit its content.

Cross Tab Auto Width Mode

Use a cell’s RowAutoHeightMode property to specify whether and how a cell’s height changes to fit its content.

Cross Tab Auto Height Mode

Column widths are changed before row heights.

Adjust Header Text

Double-click any header cell to invoke the in-place editor that allows you to enter text.

Cross Tab Cell Inplace Editor

The Angle property allows you to rotate the text in a cell. The following images show the header cell with the text rotated to 90 degrees.

Design:

|Cross Tab Cell Angle Property

Preview:

Cross Tab with Hidden Grand Totals Preview

Specify Print Options

Use the PrintOptions property to specify print options and define which Cross Tab elements to print.

Cross Tab Print Options

Option

Description

PrintLayout

Specifies how to print the Cross Tab content that does not fit on one page:

  • On the next page (AcrossOnly);
  • On the same page, below the previous content (AcrossThenDown).

AcrossThenDownOffset

Specifies the vertical distance between parts of the Cross Tab content in the AcrossThenDown print layout.

PrintTotalsForSingleValues

Specifies when to print totals:

  • For any field value, even if it contains one nested value (true);
  • For the field values that contain two or more nested values (false).

RepeatRowHeaders

RepeatColumnHeaders

Specify whether to repeat row/column headers when the Cross Tab content is split horizontally/vertically, or print them only once.

Note

If the Cross Tab content does not fit on one page, set the report’s VerticalContentSplitting and HorizontalContentSplitting properties to Smart to move entire columns/rows that do not fit to the next page. Otherwise, these rows/columns are split across two pages.

Customize the Appearance

When the Cross Tab control is created, it has four predefined report styles that are accessible with the CrossTabStyles property.

Cross Tab Styles Property

You can invoke the Styles Editor to change styles:

Cross Tab Style  Editor

Use the GeneralStyle property to specify common appearance settings that apply to all Cross Tab cells.

Use the HeaderAreaStyle, DataAreaStyle, and TotalAreaStyle properties to customize appearance settings of specific areas shown below.

Cross Tab Style Areas

If the area appearance is not specified, its settings are inherited from the general style.

You can explicitly specify appearance settings of each Cross Tab cell. Individual settings have a higher priority over style settings.

Apply Conditional Formatting

Expression bindings allow you to change the appearance of a cell conditionally. You can use the GroupRowIndex and GroupColumnIndex variables to identify group indexes in expressions. For more information on expressions, review the following help topic: Data Binding Modes.

The following expression specifies the alternating AntiqueWhite back color for odd and even rows:

Iif(([Arguments.GroupRowIndex]%2 == 0),'AntiqueWhite', ?)

Assign this expression to the data field cell:

Cross Tab Alternating Back Color Expression

The result is shown in the image below:

Cross Tab Alternating Back Color Preview

Expressions for totals and grand totals use the same field syntax (in this example, [UnitPrice]) to refer to the total values.

  • The following expression for the BackColor property of total and grand total cells highlights negative values:

    Iif([UnitPrice] < 0,  'Red', ?)
    
  • The following expression for the Text property of total and grand total cells displays negative values in parentheses:

    Iif([UnitPrice] < 0,  FormatString('({0})', [UnitPrice]), [UnitPrice])
    

Expressions are calculated in the report preview. The calculated appearance settings have the highest priority and override the cell appearance settings and style settings.

Convert From the Pivot Grid

You can convert an XRPivotGrid control to the Cross Tab control. Click the Pivot Grid’s smart tag and select Convert to Cross Tab.

XRPivotGrid Convert to Cross Tab

For more information, review the following class description: XRPivotGrid Class.

Example

The following code sample creates a new SqlDataSource, creates a report with the XRCrossTab control at runtime, and binds the Cross Tab control to data:

Cross Tab Report Created in Code

using DevExpress.DataAccess.ConnectionParameters;
using DevExpress.DataAccess.Sql;
using DevExpress.XtraPrinting;
using DevExpress.XtraReports.UI;
using DevExpress.XtraReports.UI.CrossTab;
using System;
using System.Drawing;
using System.Windows.Forms;
// ...
private XtraReport CreateReport() {
    // Creates a blank report.
    XtraReport crossTabReport = new XtraReport() {
        VerticalContentSplitting = VerticalContentSplitting.Smart,
        HorizontalContentSplitting = HorizontalContentSplitting.Smart
    };

    // Creates a detail band and adds it to the report.
    DetailBand detail = new DetailBand();
    crossTabReport.Bands.Add(detail);

    // Creates a cross tab and adds it to the Detail band.
    XRCrossTab crossTab = new XRCrossTab();
    detail.Controls.Add(crossTab);
    crossTab.PrintOptions.RepeatColumnHeaders = true;
    crossTab.PrintOptions.RepeatRowHeaders = true;

    // Creates a data source.
    SQLiteConnectionParameters connectionParameters = new SQLiteConnectionParameters(@"|DataDirectory|\nwind.db", "");
    SqlDataSource ds = new SqlDataSource(connectionParameters);

    // Creates an SQL query to access the SalesPerson view.
    SelectQuery query = SelectQueryFluentBuilder.AddTable("SalesPerson")
                .SelectColumn("CategoryName")
                .SelectColumn("ProductName")
                .SelectColumn("Country")
                .SelectColumn("FullName")
                .SelectColumn("Quantity")
                .SelectColumn("ExtendedPrice").Build("SalesPerson");
    ds.Queries.Add(query);

    // Binds the cross tab to data.
    crossTab.DataSource = ds;
    crossTab.DataMember = "SalesPerson";

    // Generates cross tab fields.
    crossTab.RowFields.Add(new CrossTabRowField() { FieldName = "CategoryName" });
    crossTab.RowFields.Add(new CrossTabRowField() { FieldName = "ProductName" });
    crossTab.ColumnFields.Add(new CrossTabColumnField() { FieldName = "Country" });
    crossTab.ColumnFields.Add(new CrossTabColumnField() { FieldName = "FullName" });
    crossTab.DataFields.Add(new CrossTabDataField() { FieldName = "Quantity" });
    crossTab.DataFields.Add(new CrossTabDataField() { FieldName = "ExtendedPrice" });
    crossTab.GenerateLayout();
// ...
    // Adjusts the generated cells.
    foreach(var c in crossTab.ColumnDefinitions) {
        // Enables auto-width for all columns.
        c.AutoWidthMode = DevExpress.XtraReports.UI.AutoSizeMode.GrowOnly;
    }

    foreach(XRCrossTabCell c in crossTab.Cells) {
        if(c.DataLevel == 1 && c.RowIndex != 2) {
            // Adjusts format string for the "Extended Price" cells.
            c.TextFormatString = "{0:c}";
        }
    }


    // Assigns styles to the cross tab.
    crossTab.CrossTabStyles.GeneralStyle = new XRControlStyle() { 
        Name = "Default",
        Borders = BorderSide.All,
        Padding = new PaddingInfo() { All = 2 }                
    };
    crossTab.CrossTabStyles.DataAreaStyle = crossTab.CrossTabStyles.TotalAreaStyle = new XRControlStyle() {
        Name = "Data",
        TextAlignment = TextAlignment.TopRight
    };
    crossTab.CrossTabStyles.HeaderAreaStyle = new XRControlStyle() {
        Name = "HeaderAndTotals",
        BackColor = Color.WhiteSmoke
    };
    return crossTabReport;
}

View Example: How to use the XRCrossTab control to create a cross-tab report

See Also