ColaReport - Demo 6

When clicking a link in the report above, the user will see a detailed report like this:

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 later create a "group" on CustomerName.
		//It is therefore important to order by CustomerName here.
		sQuery = "SELECT c.CustomerId, c.CustomerName, c.City, c.State, sum(i.InvoiceTotal) as InvoiceTotal" +
			" FROM Customer c, Invoice i" +
			" WHERE c.customerid = i.customerid" +
			" GROUP BY c.CustomerId, c.CustomerName, c.City, c.State" +
			" ORDER BY c.CustomerName";

		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: demo6.aspx</h2>";
		oReport.FirstPageHeader.Style.Add ("font-family", "Verdana");
		oReport.PageHeader.Text = "<h3>Demo: demo6.aspx</h3>";
		oReport.PageHeader.Style.Add ("font-family", "Verdana");

We create a CellColumn for CustomerName.

The DefineFormat method is used to create the HTML link.

We specify the URL, using demo6a.aspx as the page, and passing in the CustomerId. {0:g} and {1:g} are used to pass in the parameters to the first and second arguments of the string, respectively, which are CustomerId and CustomerName.

Note that DefineFormat can take an arbitrary number of arguments to allow the developer to use any number desired.

This same technique being used for links can be used for < IMG tags to include images in the report.

		oCol = oReport.CreateCellColumn ("CustomerName", "Customer Name");

		// Define the contents as a format string that can be made up of any
		// number of data elements from the DataSet.
		oCol.DefineFormat ("<a href='demo6a.aspx?customerid={0:g}'>{1:g}</a>", "CustomerId", "CustomerName");

The other columns are then created.


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

A total Group is created.

		//Total Group
		
		oGroup = oReport.CreateTotalGroup (true);
		oGroup.Cells["State"].Text = "Total";	
		oGroup.Cells.ApplyAttributeToAll("font-weight", "bold");
		oGroup.BackColor = System.Drawing.Color.FromArgb (249,249,200);
		oGroup.BlankLineAfterGroup = false;

The report is bound to the DataSet and output.

		//Bind dataset
		oReport.Bind (dsRpt);

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

demo6a.aspx.cs

The code for the detail report that demo6.aspx.cs links to appears below

	private void Page_Load(object sender, System.EventArgs e)
	{
		Report oReport;
		CellColumn oCol;
		Group oGroup;
		DataSet dsRpt;
		string sQuery;
		string sCustomerid;

		OleDbConnection oConn;
		OleDbDataAdapter daRpt;

		// Get connection string
		oConn = new OleDbConnection (ConfigurationSettings.AppSettings["gsConn"]);

		// Used Request for passing customerid from demo6.
		// Instead, we could have just posted back to demo6, and chose
		// to output the detail report there instead of here.
		// It is broken out into its own file for simplicity.
		sCustomerid = Request["customerid"].ToString();

		//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" +
			" AND c.customerid = " + sCustomerid +
			" 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";
	
		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;

		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

		// 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;

		//Bind dataset
		oReport.Bind (dsRpt);

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