Hello everyone,
I have a gridview which show the total of all works with respect to year and district and at the footer I am showing the total of all works(grand total). I need to show the subtotal also with respect to year I am not getting how can i do it. Please help me getting out of this. Below is my code. And please click the below url contains image to have a better understanding of my need.
Thanks in advance.
https://www.awesomescreenshot.com/image/2799121/0a235e860cbf827d76bc3a8e299902ec
using System;using System.Collections;using System.Configuration;using System.Data;using System.Linq;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.HtmlControls;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Xml.Linq;using System.Data.SqlClient;using System.Globalization;using System.Drawing;public partial class Home : System.Web.UI.Page{ public App_Lib AL = new App_Lib(); private decimal Totalalloc = (decimal)0.0; private decimal TotalExp = (decimal)0.0; private decimal Admamt = (decimal)0.0; decimal Page_Sum; public string Mon = string.Empty; private decimal allocttl = (decimal)0.0; private decimal woamt = (decimal)0.0; int works, comp, UP, NS, worksttl; protected void Page_Load(object sender, EventArgs e) { Session["Mon"] = DateTime.Now.AddMonths(-1).ToString("MMMM", new CultureInfo("en-GB")); }
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e) {
if (e.Row.RowType == DataControlRowType.DataRow) // if row type is DataRow, add ProductSales value to TotalSales works += Convert.ToInt16(DataBinder.Eval(e.Row.DataItem, "Total_Works"));
else if (e.Row.RowType == DataControlRowType.Footer) // If row type is footer, show calculated total value // e.Row.Cells[6].Text = "TOTAL"; e.Row.Cells[2].Text = works.ToString();
if (e.Row.RowType == DataControlRowType.DataRow) // if row type is DataRow, add ProductSales value to TotalSales comp += Convert.ToInt16(DataBinder.Eval(e.Row.DataItem, "Completed"));
else if (e.Row.RowType == DataControlRowType.Footer) // If row type is footer, show calculated total value // e.Row.Cells[6].Text = "TOTAL"; e.Row.Cells[3].Text = comp.ToString();
if (e.Row.RowType == DataControlRowType.DataRow) // if row type is DataRow, add ProductSales value to TotalSales UP += Convert.ToInt16(DataBinder.Eval(e.Row.DataItem, "Under_progress"));
else if (e.Row.RowType == DataControlRowType.Footer) // If row type is footer, show calculated total value // e.Row.Cells[6].Text = "TOTAL"; e.Row.Cells[4].Text = UP.ToString();
if (e.Row.RowType == DataControlRowType.DataRow) // if row type is DataRow, add ProductSales value to TotalSales NS += Convert.ToInt16(DataBinder.Eval(e.Row.DataItem, "Not_Started"));
else if (e.Row.RowType == DataControlRowType.Footer) // If row type is footer, show calculated total value // e.Row.Cells[6].Text = "TOTAL"; e.Row.Cells[5].Text = NS.ToString();
if (e.Row.RowType == DataControlRowType.DataRow) // if row type is DataRow, add ProductSales value to TotalSales Totalalloc += Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, "PLAN_ALLOC")); else if (e.Row.RowType == DataControlRowType.Footer) // If row type is footer, show calculated total value // e.Row.Cells[6].Text = "TOTAL"; e.Row.Cells[6].Text = Totalalloc.ToString();
if (e.Row.RowType == DataControlRowType.DataRow) // if row type is DataRow, add ProductSales value to TotalSales TotalExp += Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, "EXP")); else if (e.Row.RowType == DataControlRowType.Footer) // If row type is footer, show calculated total value // e.Row.Cells[6].Text = "TOTAL"; e.Row.Cells[7].Text = TotalExp.ToString(); }
int currentId = 0; decimal subTotal = 0; decimal total = 0; int subTotalRowIndex = 0; protected void OnRowCreated(object sender, GridViewRowEventArgs e) { subTotal = 0; if (e.Row.RowType == DataControlRowType.DataRow) { DataTable dt = (e.Row.DataItem as DataRowView).DataView.Table; int yearIndex = Convert.ToInt32(dt.Rows[e.Row.RowIndex]["sanc_year"]); total += Convert.ToDecimal(dt.Rows[e.Row.RowIndex]["Total_Works"]); if (yearIndex != currentId) { if (e.Row.RowIndex > 0) { for (int i = subTotalRowIndex; i < e.Row.RowIndex; i++) { subTotal += Convert.ToDecimal(GridView1.Rows[i].Cells[2].Text); } this.AddTotalRow("Sub Total", subTotal.ToString("N2")); subTotalRowIndex = e.Row.RowIndex; } currentId = yearIndex; } } }
private void AddTotalRow(string labelText, string value) { GridViewRow row = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Normal); row.BackColor = ColorTranslator.FromHtml("#F9F9F9"); row.Cells.AddRange(new TableCell[3] { new TableCell (), //Empty Cell new TableCell { Text = labelText, HorizontalAlign = HorizontalAlign.Right}, new TableCell { Text = value, HorizontalAlign = HorizontalAlign.Right } });
GridView1.Controls[0].Controls.Add(row); }
protected void OnDataBound(object sender, EventArgs e) { for (int i = subTotalRowIndex; i < GridView1.Rows.Count; i++) { subTotal += Convert.ToDecimal(GridView1.Rows[i].Cells[2].Text); } this.AddTotalRow("Sub Total", subTotal.ToString("N2")); this.AddTotalRow("Total", total.ToString("N2")); }}
USE [macro]
GO
/****** Object: StoredProcedure [dbo].[Distwise] Script Date: 09/08/2017 15:45:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Distwise]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
-- SET NOCOUNT ON;
SELECT Dist_Name,sanc_year,
SUM(case when Status_Type !=4 then 1 else 0 end) as Total_Works,
SUM(case when Status_Type =1 then 1 else 0 end) as Completed,
SUM(case when Status_Type =2 then 1 else 0 end) as Under_progress,
SUM(case when Status_Type =3 then 1 else 0 end) as Not_Started,
SUM(case when Status_Type !=4 then PLAN_ALLOC else 0 end )As PLAN_ALLOC,
SUM(case when Status_Type !=4 then EXP else 0 end )As EXP,
SUM(case when Status_Type !=4 then Release else 0 end )As Release
FROM WORKS
GROUP BY Dist_Name,sanc_year order by sanc_year,Dist_name
END
