FAQ
Examples
Documentation
Download
Feedback
Support
This example demonstrates the usage of running total columns.
Sample report output:
Demo: demo5.aspx
Invoice Date Customer Name Invoice Number Invoice Total Running Total
1/1/2003 ABC Computers 1 $1,000.00 $1,000.00
1/1/2003 ABC Computers 2 $700.00 $1,700.00
1/1/2003 Subtotal $1,700.00 $1,700.00
1/2/2003 ABC Computers 3 $500.00 $2,200.00
1/2/2003 Joe's Electronics 4 $600.00 $2,800.00
1/2/2003 Joe's Electronics 5 $300.00 $3,100.00
1/2/2003 Subtotal $1,400.00 $3,100.00
Total $3,100.00 $3,100.00
The code for Page_Load appears below.
private void Page_Load(object sender, System.EventArgs e)
{
Report oReport;
CellColumn oCol;
Group oGroup;
DataSet dsRpt;
string sQuery;
OleDbConnection oConn;
OleDbDataAdapter daRpt;
// Get connection string
oConn = new OleDbConnection (ConfigurationSettings.AppSettings["gsConn"]);
We order by CustomerName first, since we want to group the data by CustomerName.
//We later create a "group" on CustomerName.
//It is therefore important to order by CustomerName here.
sQuery = "SELECT i.InvoiceDate, c.CustomerName, i.InvoiceId, i.InvoiceTotal" +
" FROM Customer c, Invoice i" +
" WHERE c.customerid = i.customerid" +
" ORDER BY i.InvoiceDate, i.InvoiceId";
daRpt = new OleDbDataAdapter (sQuery, oConn);
dsRpt = new DataSet();
daRpt.Fill(dsRpt);
// Define Report
oReport = new Report ();
oReport.Style.Add ("font-family", "Verdana");
oReport.HeaderRowAttributes.Attributes["bgcolor"] = "#efefdf";
oReport.FirstPageHeader.Text = "<h2>Demo: demo5.aspx</h2>";
oReport.FirstPageHeader.Style.Add ("font-family", "Verdana");
oReport.PageHeader.Text = "<h3>Demo: demo5.aspx</h3>";
oReport.PageHeader.Style.Add ("font-family", "Verdana");
Define regular CellColumns.
oCol = oReport.CreateCellColumn ("InvoiceDate", "Invoice Date");
oCol.StringFormat = "{0:d}";
oCol.Wrap = false;
oCol = oReport.CreateCellColumn ("CustomerName", "Customer Name");
oCol.Wrap = false;
oCol = oReport.CreateCellColumn ("InvoiceId", "Invoice Number");
oCol.Attributes["align"] = "right";
oCol = oReport.CreateCellColumn ("InvoiceTotal", "Invoice Total");
oCol.SumColumn = true; //display totals for this column
oCol.Attributes["align"] = "right";
oCol.StringFormat = "{0:c}"; //currency format
Now we use the CreateRunningTotalColumn method to create a CellColumn with a running total.
The first argument specifies a user-defined name, and the second a column header. The third argument indicates the expression to use for summation, in this case, InvoiceTotal. We could have also used other expressions like "InvoiceTotal * Tax" for example.
The final argument indicates that the starting value to use is 0. It is useful to have the flexibility to set the starting value to something else. For example, if doing an accounting application and reporting on a time frame for transactions of an account, a previous balance can be used as the initial value.
oCol = oReport.CreateRunningTotalColumn
("RunningTotal", "Running Total", "InvoiceTotal", 0);
oCol.Attributes["align"] = "right";
oCol.StringFormat = "{0:c}"; //currency format
A total Group is created for the report. Note that we include the RunningTotal column here. In this case, it is not "summed" but just displays the final running total value.
// Create a group for the grand totals
oGroup = oReport.CreateTotalGroup (true);
oGroup.Cells["InvoiceDate"].Text = "Total";
oGroup.Cells["InvoiceDate"].StringFormat = "";
oGroup.Cells["InvoiceDate"].Style.Add("font-weight", "bold");
oGroup.Cells["InvoiceTotal"].Style.Add("font-weight", "bold");
oGroup.Cells["RunningTotal"].Style.Add("font-weight", "bold");
oGroup.BackColor = System.Drawing.Color.FromArgb (249,249,200);
oGroup.BlankLineAfterGroup = false;
We also create a subgroup for InvoiceDate
//Group data by InvoiceDate
oGroup = oReport.CreateGroup ("InvoiceDate", true);
oGroup.Cells["InvoiceDate"].StringFormat = "{0:d} Subtotal";
oGroup.Cells["InvoiceDate"].Style.Add("font-weight", "bold");
Finally, we bind and output the report.
//Bind dataset
oReport.Bind (dsRpt);
//Set label text to report output
lblReport.Text = oReport.Execute();
}