ColaReport - Demo 1

Home
FAQ
Examples
Documentation
Download
Feedback
Support

Demo 3 demo3.aspx.cs Complete Code Return to Examples

This example demonstrates how to create groups for summation and grand totals.

Sample report output:

Demo: demo3.aspx

Name	Invoice Date	Invoice Number	Invoice Total
ABC Computers	1/1/2003	1	$1,000.00
	1/1/2003	2	$700.00
	1/2/2003	3	$500.00
ABC Computers Subtotal			$2,200.00

Joe's Electronics	1/2/2003	4	$600.00
	1/2/2003	5	$300.00
Joe's Electronics Subtotal			$900.00

Total			$3,100.00

The code 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"]);

Note that since we later goup by CustomerName, we need to order by it here.

		
		//We later create a report "Group" on CustomerName.
		//It is therefore important to order by CustomerName here.
		sQuery = "SELECT c.CustomerName, i.InvoiceDate, i.InvoiceId, i.InvoiceTotal" +
			" FROM Customer c, Invoice i" +
			" WHERE c.customerid = i.customerid" +
			" ORDER BY c.CustomerName, i.InvoiceDate, i.InvoiceId";

		daRpt = new OleDbDataAdapter (sQuery, oConn);
		dsRpt = new DataSet();
		daRpt.Fill(dsRpt);


		// Define Report 
		oReport = new Report ();
		oReport.LinesPerPage = 33;

		oReport.Style.Add ("font-family", "Verdana");
		oReport.HeaderRowAttributes.Attributes["bgcolor"] = "#efefdf";

		oReport.FirstPageHeader.Text = "<h2>Demo: demo3.aspx</h2>";
		oReport.FirstPageHeader.Style.Add ("font-family", "Verdana");
		oReport.PageHeader.Text = "<h3>Demo: demo3.aspx</h3>";
		oReport.PageHeader.Style.Add ("font-family", "Verdana");

We create the CellColumns as we normally would.


		oCol = oReport.CreateCellColumn ("CustomerName", "Name"); 
		
		// Don't bother repeating it since we are grouping by it
		oCol.SuppressRepeatedValue = true; 

		oCol = oReport.CreateCellColumn ("InvoiceDate", "Invoice Date"); 
		oCol.StringFormat = "{0:d}"; // date format

		oCol = oReport.CreateCellColumn ("InvoiceId", "Invoice Number"); 

		// NOTE: Could also use oCol.Attributes["align"] = "right";
		oCol.HorizontalAlign = System.Web.UI.WebControls.HorizontalAlign.Right;

Note that we designate that InvoiceTotal should be summed. We also align it to the right, and tell it to format in currency format. {0:c} indicates taking the argument, and using a currency format for it.


		oCol = oReport.CreateCellColumn ("InvoiceTotal", "Invoice Total"); 
		oCol.SumColumn = true; //display totals for this column
		oCol.Attributes["align"] = "right";
		oCol.StringFormat = "{0:c}"; //currency format number

For the grand total at the bottom, we need to use the method Report.CreateTotalGroup. Note that the order in which we create Groups are outer to inner most. The grand total group is created first.


		// Create a group for the grand totals
		oGroup = oReport.CreateTotalGroup (true);
		oGroup.Cells["CustomerName"].Text = "Total";	
		oGroup.Cells["CustomerName"].Style.Add("font-weight", "bold");
		oGroup.Cells["InvoiceTotal"].Style.Add("font-weight", "bold");
		
		// NOTE: Could also use oGroup.Attributes["bgcolor"] = here
		oGroup.BackColor = System.Drawing.Color.FromArgb (249,249,200);

		oGroup.BlankLineAfterGroup = false;

We use Report.CreateGroup for creating Groups based on DataColumns, in our case, based on CustomerName.

		//Group data by CustomerName
		oGroup = oReport.CreateGroup ("CustomerName", true);
		oGroup.Cells["CustomerName"].StringFormat = "{0:g} Subtotal";

		// Make sure date column format is empty, otherwise it will try 
		// and display the Group value here
		oGroup.Cells["InvoiceDate"].StringFormat = "";

		oGroup.Style.Add("font-weight", "bold");
		
		oGroup.BackColor = System.Drawing.Color.FromArgb (249,249,249);

Finally, we bind the report.


		//Bind dataset
		oReport.Bind (dsRpt);

		//Set label text to report output
		lblReport.Text = oReport.Execute();
	}