/// <summary>
/// Illustrates using a GrapeCity OleDb object as a data source
///
/// </summary>
private void btnOleDb_Click(object sender, EventArgs e)
{
Cursor = Cursors.WaitCursor;
//OleDb Data Source object to use.
//
Data.OleDBDataSource oleDb = new Data.OleDBDataSource();
//Assign the database connection string from the sample Northwind access database
//
oleDb.ConnectionString = Properties.Resources.ConnectionString;
//Run the SQL command.
//
oleDb.SQL = "SELECT * FROM Invoices ORDER BY Customers.CompanyName, OrderID";
//Create the report and assign the data source
//
var rpt = new ActiveReports.SectionReport();
rpt.LoadLayout(XmlReader.Create(System.IO.Path.Combine("..\\..\\", Properties.Resources.ReportName)));
rpt.DataSource = oleDb;
//Run and view the report
//
arvMain.LoadDocument(rpt);
Cursor = Cursors.Arrow;
}
/// <summary>
/// DataInitialize Event
/// Use it to add custom fields to the report's Fields collection.
/// Custom fields can be added to a bound report (one that uses a Data Control to connect and retrieve records)
/// or an unbound report (one that does not depend on a data control to get its records).
/// </summary>
private void DrillThroughMain_DataInitialize(object sender, System.EventArgs eArgs)
{
//Connect to the sample database
Data.OleDBDataSource dS = new Data.OleDBDataSource();
dS.ConnectionString = Properties.Resources.ConnectionString;
dS.SQL = "Select * from customers order by customerID";
DataSource = dS;
}
public CategoryReport()
{
InitializeComponent();
Data.OleDBDataSource dS = new Data.OleDBDataSource();
dS.ConnectionString = Properties.Resources.ConnectionString;
dS.SQL = "Select Categories.CategoryID, categories.CategoryName, Avg(Products.UnitPrice) as AvgUnitPrice, Count(Categories.CategoryID) as ProductCount FROM Products, Categories WHERE Products.CategoryID = Categories.CategoryID GROUP BY Categories.CategoryID, categories.CategoryName;";
DataSource = dS;
}
/// <summary>
/// DataInitialize Event
/// Use it to add custom fields to the report's Fields collection.
/// Custom fields can be added to a bound report (one that uses a Data Control to connect and retrieve records)
/// or an unbound report (one that does not depend on a data control to get its records).
/// </summary>
private void ProductSalesByCategory_DataInitialize(object sender, System.EventArgs eArgs)
{
//Create dynamic datasource using sample database
Data.OleDBDataSource ds = new Data.OleDBDataSource();
ds.ConnectionString = Properties.Resources.ConnectionString;
ds.SQL = "SELECT DISTINCTROW Categories.CategoryName, Sum([Order Details Extended].ExtendedPrice) AS ProductSales FROM Categories INNER JOIN (Products INNER JOIN (Orders INNER JOIN [Order Details Extended] ON Orders.OrderID = [Order Details Extended].OrderID) ON Products.ProductID = [Order Details Extended].ProductID) ON Categories.CategoryID = Products.CategoryID WHERE (((Orders.OrderDate) Between #1/1/95# And #12/31/95#)) GROUP BY Categories.CategoryName ORDER BY Categories.CategoryName";
DataSource = ds;
}
public ProductsReport()
{
InitializeComponent();
Data.OleDBDataSource dS = new Data.OleDBDataSource();
dS.ConnectionString = Properties.Resources.ConnectionString;
dS.SQL = "Select Products.ProductName, Products.UnitPrice, Products.CategoryID, categories.CategoryName FROM Products, Categories WHERE Products.CategoryID = Categories.CategoryID Order By Categories.CategoryID;";
DataSource = dS;
}
private void rptNestedParent_ReportStart(object sender, EventArgs e)
{
rpt = new rptNestedChildMain();
Data.OleDBDataSource dS = new Data.OleDBDataSource();
dS.ConnectionString = Properties.Resources.ConnectionString;
dS.SQL = "Select * from employees";
DataSource = dS;
reportInfo1.FormatString = "Page {PageNumber} of {PageCount}";
}
private void rptHierarchicalMain_ReportStart(object sender, EventArgs e)
{
Data.OleDBDataSource dS = new Data.OleDBDataSource();
dS.ConnectionString = Properties.Resources.ShapeDataConnectionString;
dS.SQL = "SHAPE {SELECT CustomerID, CompanyName, Address FROM Customers} APPEND ({SELECT CustomerID, OrderID, Freight, OrderDate, EmployeeID FROM Orders} AS CustomerOrders RELATE CustomerID TO CustomerID)";
DataSource = dS;
reportInfo1.FormatString = "Page {PageNumber} of {PageCount}";
_rpt = new rptHierarchicalSub();
}
private void rptParamMain_ReportStart(object sender, EventArgs e)
{
_rpt = new rptParamSub();
Data.OleDBDataSource dS = new Data.OleDBDataSource();
dS.ConnectionString = Properties.Resources.ConnectionString;
dS.SQL = "Select * from suppliers order by country";
DataSource = dS;
reportInfo1.FormatString = "Page {PageNumber} of {PageCount}";
}
public Invoice()
{
InitializeComponent();
Document.Name = "Invoice ActiveReport";
Data.OleDBDataSource dS = new Data.OleDBDataSource();
dS.ConnectionString = Properties.Resources.ConnectionString;
dS.SQL = "SELECT * FROM Invoices ORDER BY Customers.CompanyName, OrderID";
DataSource = dS;
}
public rptNestedChildMain()
{
// ActiveReport designer support is required.
InitializeComponent();
Data.OleDBDataSource dS = new Data.OleDBDataSource();
dS.ConnectionString = Properties.Resources.ConnectionString;
dS.SQL = "Select Top 10 * from orders inner join [order details] on orders.orderID = [order details].orderID where orders.employeeID = <%employeeID%> Order By orders.OrderID";
DataSource = dS;
}
public rptMasterSub()
{
// ActiveReport designer support is required
InitializeComponent();
Data.OleDBDataSource dS = new Data.OleDBDataSource();
dS.ConnectionString = Properties.Resources.ConnectionString;
dS.SQL = "Select * from [order details] inner join products on [order details].productid = products.productID where [order details].orderID = <%OrderID%>";
DataSource = dS;
}
public DrillThrough2()
{
InitializeComponent();
//Connect to Sample Database
Data.OleDBDataSource dS = new Data.OleDBDataSource();
dS.ConnectionString = Properties.Resources.ConnectionString;
dS.SQL = "Select * from [order details] where orderID = <%orderID||1%> order by productid";
DataSource = dS;
}
public rptParamSub()
{
// ActiveReport designer support is required.
InitializeComponent();
Data.OleDBDataSource dS = new Data.OleDBDataSource();
dS.ConnectionString = Properties.Resources.ConnectionString;
dS.SQL = "SELECT * FROM products INNER JOIN categories ON products.categoryid = categories.categoryid WHERE products.supplierID = <%SupplierID%>";
DataSource = dS;
}
public rptNestedChildSub()
{
// ActiveReport designer support is required.
InitializeComponent();
Data.OleDBDataSource dS = new Data.OleDBDataSource();
dS.ConnectionString = Properties.Resources.ConnectionString;
dS.SQL = "Select * from customers where customerID = '<%CustomerID%>' ";
DataSource = dS;
}
private void Detail_Format(object sender, EventArgs e)
{
Data.OleDBDataSource subDS = new Data.OleDBDataSource();
subDS.ConnectionString = ((Data.OleDBDataSource)DataSource).ConnectionString;
subDS.SQL = "Select * from products where categoryID = " + _categoryID;
_rpt.DataSource = subDS;
ctlSubreport.Report = _rpt;
Detail.AddBookmark(txtCategoryName.Text);
}
public DrillThrough1()
{
InitializeComponent();
//Connect to Sample Database
Data.OleDBDataSource dS = new Data.OleDBDataSource();
dS.ConnectionString = Properties.Resources.ConnectionString;
dS.SQL = "Select * from orders where customerID = '<%customerID%>' order by orderdate";
DataSource = dS;
}
public rpt2DBar()
{
// Required for ActiveReport designer support.
InitializeComponent();
Data.OleDBDataSource dS = new Data.OleDBDataSource();
dS.ConnectionString = Properties.Resources.ConnectionString;
dS.SQL = "SELECT ShipCountry, SUM(Freight) AS Expr1 FROM Orders GROUP BY ShipCountry";
ChartControl.DataSource = dS;
}
private void rptBookmarkMain_ReportStart(object sender, EventArgs e)
{
_rpt = new rptBookmarkSub();
Data.OleDBDataSource dS = new Data.OleDBDataSource();
dS.ConnectionString = Properties.Resources.ConnectionString;
dS.SQL = "Select * from categories";
DataSource = dS;
reportInfo1.FormatString = "Page {PageNumber} of {PageCount}";
}
private void Detail_Format(object sender, EventArgs e)
{
//SQL statement to set the connection string for the data source of the subreport.
//
Data.OleDBDataSource subDS = new Data.OleDBDataSource();
subDS.ConnectionString = ((Data.OleDBDataSource)(DataSource)).ConnectionString;
subDS.SQL = "SELECT * FROM products WHERE categoryID = " + _categoryID;
_rpt.DataSource = subDS;
ctlSubreport.Report = _rpt;
//Sets the sub-report object to the subreport control.
//
}
/// <summary>
/// ReportStart Event
/// Use this event to initialize any objects or variables needed while running a report.
/// Also use this event to set any Subreport control objects to a new instance
/// of the report assigned to the Subreport control.
/// </summary>
private void Top10Products_ReportStart(object sender, System.EventArgs eArgs)
{
//Set row count to 0.
_iRow = 0;
chtProducts.Titles[1].Text = "Top 10 Products in 1997";
Data.OleDBDataSource ds = new Data.OleDBDataSource();
ds.ConnectionString = Properties.Resources.ConnectionString;
ds.SQL = "SELECT TOP 10 Products.ProductName, Sum([Order Details].[UnitPrice]*[Order Details].[Quantity]) AS Sales FROM Products INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID GROUP BY Products.ProductName ORDER BY Sum([Order Details].[UnitPrice]*[Order Details].[Quantity]) DESC";
DataSource = ds;
chtProducts.DataSource = ds;
chtProducts.ChartAreas[0].Axes["AxisY"].LabelFormat = "{0:$#,##0.00}";
}
/// <summary>
/// ReportStart Event
/// Use this event to initialize any objects or variables needed while running a report.
/// Also use this event to set any Subreport control objects to a new instance
/// of the report assigned to the Subreport control.
/// </summary>
private void Top10Customers_ReportStart(object sender, System.EventArgs eArgs)
{
//Set row count to 0.
_iRow = 0;
ChartControl1.Titles[1].Text = "Top 10 customers in 1997";
//Create dynamic datasource using sample database
Data.OleDBDataSource ds = new Data.OleDBDataSource();
ds.ConnectionString = Properties.Resources.ConnectionString;
ds.SQL = "SELECT TOP 10 Customers.CompanyName, Sum([UnitPrice]*[Quantity]) AS Sales FROM (Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID GROUP BY Customers.CompanyName ORDER BY Sum([UnitPrice]*[Quantity]) DESC";
DataSource = ds;
ChartControl1.DataSource = ds;
ChartControl1.ChartAreas[0].Axes["AxisY"].LabelFormat = "{0:$#,##0.00}";
}
/// <summary>
/// Illustrates using a GrapeCity OleDb object as a data source
///
/// </summary>
private void btnOleDb_Click(object sender, EventArgs e)
{
Cursor = Cursors.WaitCursor;
//OleDb Data Source object to use.
Data.OleDBDataSource oleDb = new Data.OleDBDataSource();
//Assign the database connection string from the sample Northwind access database
oleDb.ConnectionString = Properties.Resources.ConnectionString;
//Run the SQL command.
oleDb.SQL = "SELECT * FROM Invoices ORDER BY Customers.CompanyName, OrderID";
//Create the report and assign the data source
Invoice rpt = new Invoice();
rpt.DataSource = oleDb;
//Run and view the report
arvMain.LoadDocument(rpt);
Cursor = Cursors.Arrow;
}
/// <summary>
/// DataInitialize Event
/// Use it to add custom fields to the report's Fields collection.
/// Custom fields can be added to a bound report (one that uses a Data Control to connect and retrieve records)
/// or an unbound report (one that does not depend on a data control to get its records).
/// </summary>
private void ProductWeeklySales_DataInitialize(object sender, System.EventArgs eArgs)
{
//Assign report data source to the sample database
Data.OleDBDataSource ds = new Data.OleDBDataSource();
ds.ConnectionString = Properties.Resources.ConnectionString;
ds.SQL = "SELECT DISTINCTROW Categories.CategoryID, Categories.CategoryName, Products.ProductName, Orders.OrderDate, Sum([Order Details Extended].ExtendedPrice) AS ProductSales, Sum([Order Details Extended].Quantity) AS ProductUnits FROM Categories INNER JOIN (Products INNER JOIN (Orders INNER JOIN [Order Details Extended] ON Orders.OrderID = [Order Details Extended].OrderID) ON Products.ProductID = [Order Details Extended].ProductID) ON Categories.CategoryID = Products.CategoryID GROUP BY Categories.CategoryID, Categories.CategoryName, Products.ProductName, Orders.OrderDate ORDER BY Categories.CategoryName, Products.ProductName";
DataSource = ds;
//Add unbound Fields to Fields collection
Fields.Add("WkUnits");
Fields.Add("WkSales");
Fields.Add("MTDUnits");
Fields.Add("MTDSales");
Fields.Add("QTDUnits");
Fields.Add("QTDSales");
Fields.Add("PQTDSales");
Fields.Add("YTDUnits");
Fields.Add("YTDSales");
}
/// <summary>
/// Run the report with the supplied categoryName and assign it to the viewer control.
/// </summary>
/// <param name="categoryName">Category to run the report with.</param>
private void runCategoryReport(string categoryName)
{
try
{
Cursor tmp = Cursor;
Cursor = Cursors.WaitCursor;
CategoryProducts rpt = new CategoryProducts();
Data.OleDBDataSource dS = new Data.OleDBDataSource();
dS.ConnectionString = Properties.Resources.ConnectionString;
string sqlString = "Select Products.ProductName, Products.UnitPrice, Products.CategoryID, Categories.CategoryName";
sqlString += " FROM Products, Categories";
sqlString += " WHERE Products.CategoryID = Categories.CategoryID AND Categories.CategoryName = '";
sqlString += categoryName + "'";
dS.SQL = sqlString;
rpt.DataSource = dS;
arvMain.LoadDocument(rpt);
Cursor = tmp;
}
catch (ReportException ex)
{
MessageBox.Show(this, "Error fired while running the report: " + ex.Message, "Report Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void Invoice_ReportStart(object sender, EventArgs e)
{
Data.OleDBDataSource ds = (Data.OleDBDataSource)DataSource;
ds.SQL = "SELECT * FROM Invoices WHERE Country = 'USA'";
}