private void CreateDepartmentsWorks()
{
Access97ConnectionParameters connectionParameters = new Access97ConnectionParameters(@"|DataDirectory|\AppData\Departments.mdb", "", "");
ConnectionWrapper cw = InitConnection("DepartmentsConnection", connectionParameters);
DataSourceWrapper dsw = DataSourceWrapper.Create();
dsw.Name = "Departments";
dsw.Connection = cw;
QueryWrapper qwSalesPerson = QueryWrapper.Create();
qwSalesPerson.OwnerDataSource = dsw;
qwSalesPerson.Name = "Departments";
qwSalesPerson.Sql = "select * from Departments";
dsw.Queries.Add(qwSalesPerson);
this.DataSources.Add(dsw);
}
private void BindToData()
{
// Create a data source with the required connection parameters.
Access97ConnectionParameters connectionParameters =
new Access97ConnectionParameters("../../Data/nwind.mdb", "", "");
SqlDataSource ds = new SqlDataSource(connectionParameters);
// Create a query to access fields of the Products data table.
SelectQuery query = SelectQueryFluentBuilder
.AddTable("Products")
.SelectColumns("CategoryID", "ProductName")
.Build("Products");
// Add a query parameter to be used as a criterion for data source level data filtering.
// In this example the query parameter has the Expression type and contains
// a simple expression that references a value of a report parameter named "catID".
QueryParameter parameter = new QueryParameter()
{
Name = "catID",
Type = typeof(Expression),
Value = new Expression("[Parameters.catID]", typeof(System.Int32))
};
query.Parameters.Add(parameter);
query.FilterString = "CategoryID = ?catID";
ds.Queries.Add(query);
// Assign the data source to the report.
this.DataSource = ds;
this.DataMember = "Products";
// Bind report controls to appropriate data fields depending on the report's data binding mode.
if (Settings.Default.UserDesignerOptions.DataBindingMode == DataBindingMode.Bindings)
{
xrLabel1.DataBindings.Add("Text", ds, "Products.CategoryID");
xrLabel2.DataBindings.Add("Text", ds, "Products.ProductName");
}
else
{
xrLabel1.ExpressionBindings.Add(new ExpressionBinding("BeforePrint", "Text", "[CategoryID]"));
xrLabel2.ExpressionBindings.Add(new ExpressionBinding("BeforePrint", "Text", "[ProductName]"));
}
}
private void CreateNorthwind()
{
DataConnectionParametersBase connectionParameters = new Access97ConnectionParameters(@"|DataDirectory|\AppData\nwind.mdb", "", "");
ConnectionWrapper cw = InitConnection("NorthwindConnection", connectionParameters);
DataSourceWrapper dsw = DataSourceWrapper.Create();
dsw.Name = "Northwind";
dsw.Connection = cw;
QueryWrapper qwCategories = QueryWrapper.Create();
qwCategories.OwnerDataSource = dsw;
qwCategories.Name = "Categories";
qwCategories.Sql = "select * from Categories";
dsw.Queries.Add(qwCategories);
QueryWrapper qwProducts = QueryWrapper.Create();
qwProducts.OwnerDataSource = dsw;
qwProducts.Name = "Products";
qwProducts.Sql = "select * from Products";
dsw.Queries.Add(qwProducts);
this.DataSources.Add(dsw);
}
protected void Application_Start(object sender, EventArgs e)
{
string dbFileName = Server.MapPath("~/App_Data/DashboardDataSet.xml");
DataBaseDashboardStorage dataBaseDashboardStorage = new DataBaseDashboardStorage();
ASPxDashboardDesigner.Storage.SetDashboardStorage(dataBaseDashboardStorage);
Access97ConnectionParameters accessParams = new Access97ConnectionParameters();
accessParams.FileName = Server.MapPath("~/App_Data/nwind.mdb");
DashboardSqlDataSource sqlDataSource = new DashboardSqlDataSource("SQL Data Source", accessParams);
TableQuery customerReportsQuery = new TableQuery("CustomerReports");
customerReportsQuery.AddTable("CustomerReports").SelectColumns("CompanyName", "ProductName", "OrderDate", "ProductAmount");
sqlDataSource.Queries.Add(customerReportsQuery);
DataSourceInMemoryStorage dataSourceStorage = new DataSourceInMemoryStorage();
dataSourceStorage.RegisterDataSource("sqlDataSource1", sqlDataSource);
ASPxDashboardDesigner.Storage.SetDataSourceStorage(dataSourceStorage);
ASPxDashboardDesigner.Storage.ConfigureDataConnection += Storage_ConfigureDataConnection;
}
private SqlDataSource BindToData()
{
// Create a data source with the required connection parameters.
Access97ConnectionParameters connectionParameters =
new Access97ConnectionParameters("../../nwind.mdb", "", "");
SqlDataSource ds = new SqlDataSource(connectionParameters);
// Create an SQL query to access the Products table.
CustomSqlQuery query = new CustomSqlQuery();
query.Name = "customQuery";
query.Sql = "SELECT * FROM Products";
// Add the query to the collection and return the data source.
ds.Queries.Add(query);
// Make the data source structure displayed
// in the Field List of an End-User Report Designer.
ds.RebuildResultSchema();
return(ds);
}
private XtraReport CreateReport()
{
Access97ConnectionParameters parameters = new Access97ConnectionParameters(@"|DataDirectory|\nwind.mdb", "", "");
SqlDataSource ds = new SqlDataSource(parameters);
SelectQuery query = SelectQueryFluentBuilder.AddTable("Products").SelectAllColumns().Build("Products");
ds.Queries.Add(query);
ds.RebuildResultSchema();
XtraReport report = new XtraReport()
{
DataSource = ds,
DataMember = query.Name
};
float actualPageWidth = report.PageWidth - (report.Margins.Left + report.Margins.Right);
int colCount = 3;
float colWidth = actualPageWidth / colCount;
XRTable tableDetail = new XRTable()
{
HeightF = 25f,
WidthF = actualPageWidth
};
tableDetail.BeginInit();
XRTableRow detailRow = new XRTableRow();
detailRow.WidthF = tableDetail.WidthF;
tableDetail.Rows.Add(detailRow);
XRTableCell detailCell = new XRTableCell()
{
WidthF = colWidth
};
detailCell.ExpressionBindings.Add(new ExpressionBinding("BeforePrint", "Text", "[ProductName]"));
detailRow.Cells.Add(detailCell);
detailCell = new XRTableCell()
{
WidthF = colWidth
};
detailCell.ExpressionBindings.Add(new ExpressionBinding("BeforePrint", "Text", "[QuantityPerUnit]"));
detailRow.Cells.Add(detailCell);
detailCell = new XRTableCell()
{
WidthF = colWidth
};
detailCell.ExpressionBindings.Add(new ExpressionBinding("BeforePrint", "Text", "[UnitPrice]"));
detailRow.Cells.Add(detailCell);
tableDetail.EndInit();
report.Bands.Add(new DetailBand()
{
HeightF = 25f
});
report.Bands[BandKind.Detail].Controls.Add(tableDetail);
return(report);
}
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);
}
public XtraReport CreateDataGroupingReport()
{
// Create a report.
XtraReport report = new XtraReport();
// Create a data source with the required connection parameters.
Access97ConnectionParameters connectionParameters =
new Access97ConnectionParameters("../../nwind.mdb", "", "");
SqlDataSource ds = new SqlDataSource(connectionParameters);
CustomSqlQuery query = new CustomSqlQuery();
query.Name = "customQuery";
query.Sql = "SELECT * FROM CategoryProducts";
ds.Queries.Add(query);
ds.RebuildResultSchema();
// Assign the data source to the report.
report.DataSource = ds;
report.DataMember = "customQuery";
// Create a detail band and add it to the report.
DetailBand detail = new DetailBand {
HeightF = 40
};
report.Bands.Add(detail);
// Create a group header band and add it to the report.
GroupHeaderBand ghBand = new GroupHeaderBand {
HeightF = 40
};
report.Bands.Add(ghBand);
// Create a group field and assign it to the group header band.
GroupField groupField = new GroupField("CategoryName");
ghBand.GroupFields.Add(groupField);
// Create new labels.
XRLabel labelGroup = new XRLabel {
ForeColor = System.Drawing.Color.Blue
};
XRLabel labelDetail = new XRLabel {
LocationF = new System.Drawing.PointF(30, 0)
};
// Specify labels' bindings depending on the report's data binding mode.
if (Settings.Default.UserDesignerOptions.DataBindingMode == DataBindingMode.Bindings)
{
labelGroup.DataBindings.Add("Text", null, "customQuery.CategoryName");
labelDetail.DataBindings.Add("Text", null, "customQuery.ProductName");
}
else
{
labelGroup.ExpressionBindings.Add(new ExpressionBinding("BeforePrint", "Text", "[CategoryName]"));
labelDetail.ExpressionBindings.Add(new ExpressionBinding("BeforePrint", "Text", "[ProductName]"));
}
// Add these labels to the report's bands.
ghBand.Controls.Add(labelGroup);
detail.Controls.Add(labelDetail);
return(report);
}
