XRCrossTab Class

Displays multi-dimensional data in reports.

Namespace: DevExpress.XtraReports.UI

Assembly: DevExpress.XtraReports.v24.1.dll

NuGet Package: DevExpress.Reporting.Core

Declaration

C#

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

VB.NET

Public Class XRCrossTab
    Inherits XRControl
    Implements IDataContainer,
               IDataSourceAssignable,
               IDataContainerBase,
               IEffectiveDataContainer,
               ISupportInitialize,
               IComplexControl,
               ICrossTabLayout

Related API Members

The following members return XRCrossTab objects:

• XRCrossTabCell.CrossTab

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:

Preview:

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

Create a Cross-Tab Report in the Visual Studio Report Designer

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 in the Visual Studio Report Designer

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.

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.

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

  

• Specify 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:

• Supported Tags
• HTML-inspired Text Formatting

Format Field Values

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

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:

Use the Cross-Tab Wizard

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

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.

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.

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

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.

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:

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:

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.

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:

Then you can use the cross tab parameter in the filter criteria:

Display TimeSpan Values

The Cross Tab does not support the TimeSpan format for data fields and displays them as decimal values. If you want to display TimeSpan values in an XRCrossTab, you need to create and register a custom function that converts decimal values to a custom output type (TimeSpan):

C#

using DevExpress.XtraReports.Design;
using DevExpress.XtraReports.Expressions;
// ...
[VSDesignerCustomFunction]
public class ToTimeSpan : ReportCustomFunctionOperatorBase {
    static ToTimeSpan instance = new ToTimeSpan();
    public static void Register() {
        CustomFunctions.Register(instance);
    }
    public static void Unregister() {
        CustomFunctions.Unregister(instance.Name);
    }
    public override string FunctionCategory => "DateTime";
    public override int MinOperandCount => 1;
    public override int MaxOperandCount => 3;
    public override object Evaluate(params object[] operands) {
        try {
            var ticks = Convert.ToInt64(operands[0]);
            var result = TimeSpan.FromTicks(ticks);
            return result;
        } catch (Exception ex) {
            return TimeSpan.Zero;
        }
    }
    public override bool IsValidOperandType(int operandIndex, int operandCount, Type type) {
        if (operandIndex >= operandCount)
            return false;
        return type == typeof(int);
    }
    public override string Description => "ToTimeSpan(decimal value)\r\nConverts the value to TimeSpan";

    public override string Name => "ToTimeSpan";
}

VB.NET

Imports DevExpress.XtraReports.Design
Imports DevExpress.XtraReports.Expressions
'...
<VSDesignerCustomFunction>
Public Class ToTimeSpan
    Inherits ReportCustomFunctionOperatorBase

    Private Shared instance As New ToTimeSpan()
    Public Shared Sub Register()
        CustomFunctions.Register(instance)
    End Sub
    Public Shared Sub Unregister()
        CustomFunctions.Unregister(instance.Name)
    End Sub
    Public Overrides ReadOnly Property FunctionCategory() As String
        Get
            Return "DateTime"
        End Get
    End Property
    Public Overrides ReadOnly Property MinOperandCount() As Integer
        Get
            Return 1
        End Get
    End Property
    Public Overrides ReadOnly Property MaxOperandCount() As Integer
        Get
            Return 3
        End Get
    End Property
    Public Overrides Function Evaluate(ParamArray ByVal operands() As Object) As Object
        Try
            Dim ticks = Convert.ToInt64(operands(0))
            Dim result = TimeSpan.FromTicks(ticks)
            Return result
        Catch ex As Exception
            Return TimeSpan.Zero
        End Try
    End Function
    Public Overrides Function IsValidOperandType(ByVal operandIndex As Integer, ByVal operandCount As Integer, ByVal type As Type) As Boolean
        If operandIndex >= operandCount Then
            Return False
        End If
        Return type Is GetType(Integer)
    End Function
    Public Overrides ReadOnly Property Description() As String
        Get
            Return "ToTimeSpan(decimal value)" & vbCrLf & "Converts the value to TimeSpan"
        End Get
    End Property

    Public Overrides ReadOnly Property Name() As String
        Get
            Return "ToTimeSpan"
        End Get
    End Property
End Class

Specify Layout Options

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

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.

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

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.

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

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

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.

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:

Preview:

Specify Print Options

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

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.

You can invoke the Styles Editor to change styles:

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.

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:

The result is shown in the image below:

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.

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:

C#

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;
}

VB.NET

Imports DevExpress.DataAccess.ConnectionParameters
Imports DevExpress.DataAccess.Sql
Imports DevExpress.XtraPrinting
Imports DevExpress.XtraReports.UI
Imports DevExpress.XtraReports.UI.CrossTab
' ...
Private Function CreateReport() As XtraReport
    ' Creates a blank report.
    Dim crossTabReport As XtraReport = New XtraReport() With {.VerticalContentSplitting = VerticalContentSplitting.Smart, .HorizontalContentSplitting = HorizontalContentSplitting.Smart}
    ' Creates a detail band and adds it to the report.
    Dim detail As DetailBand = New DetailBand()
    crossTabReport.Bands.Add(detail)
    ' Creates a cross tab and adds it to the Detail band.
    Dim crossTab As XRCrossTab = New XRCrossTab()
    detail.Controls.Add(crossTab)
    crossTab.PrintOptions.RepeatColumnHeaders = True
    crossTab.PrintOptions.RepeatRowHeaders = True
    ' Creates a data source.
    Dim connectionParameters As SQLiteConnectionParameters = New SQLiteConnectionParameters("|DataDirectory|\nwind.db", "")
    Dim ds As SqlDataSource = New SqlDataSource(connectionParameters)
    ' Creates an SQL query to access the SalesPerson view.
    Dim query As SelectQuery = 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() With {.FieldName = "CategoryName"})
    crossTab.RowFields.Add(New CrossTabRowField() With {.FieldName = "ProductName"})
    crossTab.ColumnFields.Add(New CrossTabColumnField() With {.FieldName = "Country"})
    crossTab.ColumnFields.Add(New CrossTabColumnField() With {.FieldName = "FullName"})
    crossTab.DataFields.Add(New CrossTabDataField() With {.FieldName = "Quantity"})
    crossTab.DataFields.Add(New CrossTabDataField() With {.FieldName = "ExtendedPrice"})
    crossTab.GenerateLayout()
' ...
    ' Adjusts the generated cells.
    For Each c In crossTab.ColumnDefinitions
        ' Enables auto-width for all columns.
        c.AutoWidthMode = DevExpress.XtraReports.UI.AutoSizeMode.GrowOnly
    Next

    For Each c As XRCrossTabCell In crossTab.Cells
        If c.DataLevel = 1 AndAlso c.RowIndex <> 2 Then
            ' Adjusts format string for the "Extended Price" cells.
            c.TextFormatString = "{0:c}"
        End If
    Next

    ' Assigns styles to the cross tab.
    crossTab.CrossTabStyles.GeneralStyle = New XRControlStyle() With {.Name = "Default", .Borders = BorderSide.All, .Padding = New PaddingInfo() With {.All = 2}}
    crossTab.CrossTabStyles.TotalAreaStyle = New XRControlStyle() With {.Name = "Data", .TextAlignment = TextAlignment.TopRight}
    crossTab.CrossTabStyles.DataAreaStyle = crossTab.CrossTabStyles.TotalAreaStyle
    crossTab.CrossTabStyles.HeaderAreaStyle = New XRControlStyle() With {.Name = "HeaderAndTotals", .BackColor = Color.WhiteSmoke}
    Return crossTabReport
End Function

View Example: Reporting for WinForms - Use XRCrossTab Control to Create Cross-Tab Report in Code

Implements

IScriptable

Inheritance

Object

MarshalByRefObject

Component

XRControl

XRCrossTab