XRCrossTab Class
Displays multi-dimensional data in reports.
Namespace: DevExpress.XtraReports.UI
Assembly: DevExpress.XtraReports.v24.2.dll
NuGet Package: DevExpress.Reporting.Core
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:
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.
- Click the Cross Tab’s smart tag.
- Expand the Data Source property’s drop-down list and click Add Report Data Source.
- 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:
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
):
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";
}
Specify Layout Options
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:
|
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.
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 |
---|---|
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.
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:
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;
}