xtrareports-devexpress-dot-xtrareports-dot-ui-3278326f.md
Displays multi-dimensional data in reports.
Namespace : DevExpress.XtraReports.UI
Assembly : DevExpress.XtraReports.v25.2.dll
NuGet Package : DevExpress.Reporting.Core
public class XRCrossTab :
XRControl,
IDataContainer,
IDataSourceAssignable,
IDataContainerBase,
IEffectiveDataContainer,
ISupportInitialize,
IComplexControl,
ICrossTabLayout
Public Class XRCrossTab
Inherits XRControl
Implements IDataContainer,
IDataSourceAssignable,
IDataContainerBase,
IEffectiveDataContainer,
ISupportInitialize,
IComplexControl,
ICrossTabLayout
The following members return XRCrossTab objects:
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.
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.
Use the Cross Tab’s DataSource and DataMember properties to bind this control to data.
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.
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.
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.
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.
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.
The following limitations apply when you design the Cross Tab layout:
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:
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.
Switch to Print Preview to see the Cross Tab populated with data. The following image illustrates Cross Tab elements:
The Cross-Tab Report Wizard allows you to create a new cross-tab report based on a template.
The Cross Tab calculates the following automatic 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.
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).
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.
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.
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:
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):
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";
}
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
Use the LayoutOptions property to adjust the order and layout of the cells in the CrossTab control.
|
Option
|
Description
| | --- | --- | |
|
Specify where to display column/row total headers:
| |
|
Specify the position of the column/row totals and column/row grand totals:
| |
|
Specifies what data the Cross Tab should display in the top left corner:
| |
|
Specifies how to position two or more data fields in the Cross Tab layout:
| |
|
Specifies how to display row headers:
|
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
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.
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:
Use the PrintOptions property to specify print options and define which Cross Tab elements to print.
|
Option
|
Description
| | --- | --- | |
|
Specifies how to print the Cross Tab content that does not fit on one page:
| |
|
Specifies the vertical distance between parts of the Cross Tab content in the AcrossThenDown print layout.
| |
|
Specifies when to print totals:
| |
|
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.
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.
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:
The following expression for the Text property of total and grand total cells displays negative values in parentheses:
Expressions are calculated in the report preview. The calculated appearance settings have the highest priority and override the cell appearance settings and style settings.
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.
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:
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;
}
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
Object MarshalByRefObject Component XRControl XRCrossTab
See Also