Report and code example

This is an example of a report similar to Northwind database Invoice report. The report uses demo Northwind MS Access database. This is a report template, created in RTF editor:

Selection query for this report was created with query builder and attached to the report with alias "Qr". Three report variables are used in report: Freight, ExtPrice and s_ExtPrice. Variables Freight and ExtPrice are declared with VAR() function. Third variable s_ExtPrice was created automatically by SUM function used in the scan cycle.

The results of the report query is a simple plain table. At the beginning of the report before scan cycle, data fields a printed from the first record of the query results. Inside the scan cycle all the records are printed, which were returned with the query results. After the scan cycle the query cursor is at the eof position, so any data fields will return an empty string, if they are inserted in report. Totals and subtotals are printed using calculated report variables.

This is a query diagram:

In the demo project user may select a customer from a list. In the second table are orders of that customer. The report prints an invoice for selected order:

The report generates an output document, editable and ready for printing:

The next components are used for the report:

ekRtf1 - EkRtf report component;

cn - OleDbConnection, used for connecting to Northwind MS Access database;

oleDbCmd - OleDbCommand which executes select query for the report.

Selection query and program code.

C# example:

private void btnReport_Click(object sender, EventArgs e)
{
cn.Open();
oleDbCmd.CommandText =
"SELECT Customers.Address, "+
" Customers.City, "+
" Customers.Region, "+
" Customers.PostalCode, "+
" Customers.Country, "+
" Customers.CompanyName, "+
" Orders.OrderID, "+
" Orders.CustomerID, "+
" Orders.EmployeeID, "+
" Orders.OrderDate, "+
" Orders.RequiredDate, "+
" Orders.ShippedDate, "+
" Orders.ShipVia, "+
" Shippers.CompanyName AS Shp_CompanyName, "+
" Employees.LastName, "+
" Employees.FirstName, "+
" Products.ProductName, "+
" [Order Details].ProductID, "+
" [Order Details].UnitPrice, "+
" [Order Details].Quantity, "+
" [Order Details].Discount, "+
" Orders.Freight, "+
" Orders.ShipName, "+
" Orders.ShipAddress, "+
" Orders.ShipCity, "+
" Orders.ShipRegion, "+
" Orders.ShipPostalCode, "+
" Orders.ShipCountry "+
" FROM (((((Orders INNER JOIN "+
" Customers ON Orders.CustomerID = Customers.CustomerID) INNER JOIN "+
" Shippers ON Orders.ShipVia = Shippers.ShipperID) INNER JOIN "+
" [Order Details] ON Orders.OrderID = [Order Details].OrderID) INNER JOIN "+
" Employees ON Orders.EmployeeID = Employees.EmployeeID) INNER JOIN "+
" Products ON [Order Details].ProductID = Products.ProductID) "+
" WHERE (Orders.OrderID = @OrderID) " +
" ORDER BY Orders.CustomerID, Orders.OrderID";

oleDbCmd.Parameters.Clear();
oleDbCmd.Parameters.Add("OrderID", OleDbType.Integer);
Int32 OrderID;
OrderID = (Int32)((DataRowView)BindingContext[dataGridView2.DataSource].Current)["OrderID"];
oleDbCmd.Parameters["OrderID"].Value = OrderID;
OleDbDataReader reader = oleDbCmd.ExecuteReader();
reader.Read();

ekRtf1.ReportData.Add(reader, "Qr"); //Attach reader to the report with alias "Qr"
ekRtf1.OutFile = "Order_" + OrderID.ToString()+".doc";
ekRtf1.ExecuteOpen();
ekRtf1.ReportData.Clear();

reader.Close();
cn.Close();
}

Pascal example:

procedure TWinForm3.btnReport_Click(sender: System.Object; e: System.EventArgs);
var OrderID:Int32;
reader : OleDbDataReader;
begin
cn.Open();
oleDbCmd.CommandText :=
'SELECT Customers.Address, '+
' Customers.City, '+
' Customers.Region, '+
' Customers.PostalCode, '+
' Customers.Country, '+
' Customers.CompanyName, '+
' Orders.OrderID, '+
' Orders.CustomerID, '+
' Orders.EmployeeID, '+
' Orders.OrderDate, '+
' Orders.RequiredDate, '+
' Orders.ShippedDate, '+
' Orders.ShipVia, '+
' Shippers.CompanyName AS Shp_CompanyName, '+
' Employees.LastName, '+
' Employees.FirstName, '+
' Products.ProductName, '+
' [Order Details].ProductID, '+
' [Order Details].UnitPrice, '+
' [Order Details].Quantity, '+
' [Order Details].Discount, '+
' Orders.Freight, '+
' Orders.ShipName, '+
' Orders.ShipAddress, '+
' Orders.ShipCity, '+
' Orders.ShipRegion, '+
' Orders.ShipPostalCode, '+
' Orders.ShipCountry '+
' FROM (((((Orders INNER JOIN '+
' Customers ON Orders.CustomerID = Customers.CustomerID) INNER JOIN '+
' Shippers ON Orders.ShipVia = Shippers.ShipperID) INNER JOIN '+
' [Order Details] ON Orders.OrderID = [Order Details].OrderID) INNER JOIN '+
' Employees ON Orders.EmployeeID = Employees.EmployeeID) INNER JOIN '+
' Products ON [Order Details].ProductID = Products.ProductID) '+
' WHERE (Orders.OrderID = @OrderID) ' +
' ORDER BY Orders.CustomerID, Orders.OrderID';

oleDbCmd.Parameters.Clear();
oleDbCmd.Parameters.Add('OrderID', OleDbType.Integer);

OrderID := Int32(DataRowView(BindingContext[DataGrid2.DataSource].Current)['OrderID']);
oleDbCmd.Parameters['OrderID'].Value := OrderID;

reader := oleDbCmd.ExecuteReader();
reader.Read();

ekRtf1.ReportData.Add(reader, 'Qr'); //Attach reader to the report with alias "Qr"
ekRtf1.OutFile := 'Order_' + OrderID.ToString()+'.doc';
ekRtf1.ExecuteOpen();
ekRtf1.ReportData.Clear();

reader.Close();
cn.Close();
end;

See more examples at demo sections of EkRtf web site.

Group

Creating report template

Links

Creating report template