Wednesday, 16 January 2013

GRIDVIEW FOOTER RUNNING TOTAL

In many scenarios we need to display Running total as well as Grand total in GridView footer. In this post i will try to explain in an easy way that how we can display running total & Grand total in a GridView footer combindly. To explain this solution using an example here i use a sales order report. The report contains all sales order amount in a tabular way. I will use a GridView to display sales order amount and use GridView footer to display Running total & Grand total. Let we have a customer table with id and name column plus an orders table with OrderID,CustomerID,OrderDate & Amount. Now our goal is to show all customers order with amount as well as page wise running total & grand total. Note that running total is necessary when you enable paging in a GridView where as Grand total is always you can consider.

The output will be:

Fig: Page 1 runnig total

When you click on another page within GridView then you will get that page Running total with Grand total also.

Fig: Page 2 runnig total

To accomplish the above example please find the HTML markup code for your aspx page from below:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="gvgrandtotl.aspx.cs" Inherits="gvgrandtotl" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Gridview</title>
</head>

<body>

<form id="form1" runat="server">

<div>

<asp:GridView runat="server" ID="gvEdit" AllowPaging="true" PageSize="5" ShowFooter="true" OnPageIndexChanging="gvEdit_PageIndexChanging" OnRowDataBound="gvEdit_RowDataBound">
<Columns>
<asp:BoundField DataField="Code" HeaderText="Order Code">
</asp:BoundField>
<asp:BoundField DataField="Name" HeaderText="Name">
</asp:BoundField>

<asp:TemplateField HeaderText="Date" FooterStyle-BorderWidth="1px" FooterStyle-BorderColor="maroon">
<ItemTemplate>
<asp:Label ID="lblDate" runat="server" Text='<%# Convert.ToDateTime(Eval("Date")).ToString("dd-MM-yy")%>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:Label ID="lbltxtRTotal" runat="server">Running Total:</asp:Label><br />
<asp:Label ID="Label1" runat="server">Grand Total:</asp:Label><br />
</FooterTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="Amount" FooterStyle-BorderWidth="1px" FooterStyle-BorderColor="maroon">
<ItemTemplate>
<asp:Label ID="lblAmount" runat="server" Text='<%# Eval("Amount").ToString()%>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:Label ID="lblRTotal" runat="server"></asp:Label><br />
<asp:Label ID="lblGTotal" runat="server"></asp:Label>
</FooterTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>

</div>

</form>

</body>
</html>

To accomplish the above example please find the server side code for your aspx page from below:
using System;
using System.Data;
using System.Configuration;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;

public partial class gvgrandtotl : System.Web.UI.Page
{


decimal RunningTotal = 0;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
string connectionString = ConfigurationManager.ConnectionStrings["TestConnection"].ConnectionString;
DataTable dt = new DataTable();
SqlConnection conn = new SqlConnection(connectionString);
using (conn)
{
SqlDataAdapter ad = new SqlDataAdapter(
"SELECT OrderID Code,B.Name,OrderDate Date,Amount from "+
"orders A INNER JOIN customer B ON A.Customerid=B.ID", conn);
ad.Fill(dt);
}

decimal GrandTotal = 0;
foreach (DataRow oRow in dt.Rows)
GrandTotal += Convert.ToDecimal(oRow["Amount"]);

ViewState["GrandTotal"] = GrandTotal;

gvEdit.DataSource = dt;
gvEdit.DataBind();
ViewState["dt"] = dt;
}
}


protected void gvEdit_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
gvEdit.PageIndex = e.NewPageIndex;
gvEdit.DataSource = (DataTable)ViewState["dt"];
gvEdit.DataBind();
}


protected void gvEdit_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
RunningTotal += Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, "Amount"));

if (e.Row.RowType == DataControlRowType.Footer)
{
((Label)e.Row.FindControl("lblRTotal")).Text = RunningTotal.ToString();
((Label)e.Row.FindControl("lblGTotal")).Text = ViewState["GrandTotal"].ToString();
}
}
}

No comments:

Post a Comment

Note: only a member of this blog may post a comment.