private void button1_Click(object sender, EventArgs e) { using (XtraReport1 report = new XtraReport1()) { XRCrossTab crossTab = new XRCrossTab(); report.Detail.Controls.Add(crossTab); SqlDataSource ds = new SqlDataSource(new Access97ConnectionParameters(@"|DataDirectory|\nwind.mdb", String.Empty, String.Empty)); ds.Queries.Add(SelectQueryFluentBuilder .AddTable("Order Details") .SelectColumns( "UnitPrice", "Quantity", "OrderID" ) .Join("Products", "ProductID") .SelectColumns( "ProductName" ) .Filter("[Order Details].[OrderID] < 10250") .Build("SalesInfo")); crossTab.DataSource = ds; crossTab.DataMember = "SalesInfo"; crossTab.RowFields.Add(new CrossTabRowField() { FieldName = "ProductName" }); crossTab.ColumnFields.Add(new CrossTabColumnField() { FieldName = "OrderID" }); crossTab.DataFields.Add(new CrossTabDataField() { FieldName = "UnitPrice" }); crossTab.DataFields.Add(new CrossTabDataField() { FieldName = "Quantity" }); crossTab.DataFields.Add(new CrossTabDataField()); crossTab.GenerateLayout(); /* +---------------------+---------------------------------------+---------------------------------------+ | ProductName | [OrderID] | Grand total | | +-------------+------------+------------+-------------+------------+------------+ | | Unit Price | Quantity | Empty cell | UnitPrice | Quantity | Empty cell | +---------------------+-------------+------------+------------+-------------+------------+------------+ | [ProductName] | [UnitPrice] | [Quantity] | Empty cell | [UnitPrice] | [Quantity] | Empty cell | +---------------------+-------------+------------+------------+-------------+------------+------------+ | Grand Total | | | | | | | +---------------------+-------------+------------+------------+-------------+------------+------------+ */ //Adjust generated cells foreach (var c in crossTab.ColumnDefinitions) { //Enable auto-width for all columns c.AutoWidthMode = DevExpress.XtraReports.UI.AutoSizeMode.ShrinkAndGrow; } foreach (XRCrossTabCell c in crossTab.Cells) { if (c.DataLevel == 0 && c.RowIndex != 1) { //Adjust format string for the "UnitPrice" cells c.TextFormatString = "{0:c}"; } if (c.RowIndex == 0 && c.ColumnLevel == 0) { //Adjust format string for the "OrderID" cells c.TextFormatString = "Order {0}"; } if (c.DataLevel == 2 && c.RowIndex != 1) { //Set custom expression for empty data cells c.ExpressionBindings.Add(new ExpressionBinding("Text", "[UnitPrice] * [Quantity]")); c.Font = new Font(c.Font, FontStyle.Bold); c.TextFormatString = "{0:c}"; } if (c.DataLevel == 2 && c.RowIndex == 1) { //Set text for empty header cells c.Font = new Font(c.Font, FontStyle.Bold); c.Text = "Total"; } } // Assign styles to 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 }; report.ShowRibbonPreviewDialog(); } }
private XtraReport CreateReport() { // Create a blank report. XtraReport crossTabReport = new XtraReport() { VerticalContentSplitting = VerticalContentSplitting.Smart, HorizontalContentSplitting = HorizontalContentSplitting.Smart }; // Create a detail band and add it to the report. DetailBand detail = new DetailBand(); crossTabReport.Bands.Add(detail); // Create a cross tab and add it to the Detail band. XRCrossTab crossTab = new XRCrossTab(); detail.Controls.Add(crossTab); crossTab.PrintOptions.RepeatColumnHeaders = true; crossTab.PrintOptions.RepeatRowHeaders = true; // Create a data source Access97ConnectionParameters connectionParameters = new Access97ConnectionParameters(@"|DataDirectory|\nwind.mdb", "", ""); SqlDataSource ds = new SqlDataSource(connectionParameters); // Create an SQL query to access the SalesPerson view. SelectQuery query = SelectQueryFluentBuilder.AddTable("SalesPerson") .SelectColumn("CategoryName") .SelectColumn("ProductName") .SelectColumn("Country") .SelectColumn("Sales Person") .SelectColumn("Quantity") .SelectColumn("Extended Price").Build("SalesPerson"); ds.Queries.Add(query); // Bind the cross tab to data. crossTab.DataSource = ds; crossTab.DataMember = "SalesPerson"; // Generate cross tab's 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 = "Sales Person" }); crossTab.DataFields.Add(new CrossTabDataField() { FieldName = "Quantity" }); crossTab.DataFields.Add(new CrossTabDataField() { FieldName = "Extended Price" }); crossTab.GenerateLayout(); /* +----------------+---------------+-------------------------------+---------------------------+---------------------------+ | Category Name | Product Name | [Country] | Total [Country] | Grand total | | | +-------------------------------+ | | | | | [Sales Person] | | | | | +------------+------------------+----------+----------------+----------+----------------+ | | | Quantity | Extended Price | Quantity | Extended Price | Quantity | Extended Price | +----------------+---------------+------------+------------------+----------+----------------+----------+----------------+ | [CategoryName] | [ProductName] | [Quantity] | [Extended Price] | | | | | +----------------+---------------+------------+------------------+----------+----------------+----------+----------------+ | Total [CategoryName] | | | | | | | +--------------------------------+------------+------------------+----------+----------------+----------+----------------+ | Grand Total | | | | | | | +--------------------------------+------------+------------------+----------+----------------+----------+----------------+ */ //Adjust generated cells foreach (var c in crossTab.ColumnDefinitions) { //Enable 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) { //Adjust format string for the "Extended Price" cells c.TextFormatString = "{0:c}"; } } // Assign styles to 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); }