ASP.NET

Dynamic sum in GridView footer. RSS

17 replies

Last post Jan 30, 2011 09:38 AM by nikhilgupta

‹ Previous Thread|Next Thread ›

- Reply
nikhilgupta

Member

674 Points

204 Posts

Dynamic sum in GridView footer.

Jun 09, 2010 06:44 AM|LINK

I wanted to create CrossTab o/p as In Ms Access. tried couple of articles based upon coding in C# but in one way or other they could not give me final result. Then I posted here. muhammedsalimp almost resolved my query. But only problem I am left with is adding a TotalSum to the selective columns in GridView or In SQL.

It can be seen from the screen-shot that columns 1-7 does not require sum but columns after 7 all will require sum as they are the Items passed their now will vary from day to day.

Please help need to resolve soon.

"Mark As Answer" if my reply helped you.

Ravihimanshu

Member

599 Points

131 Posts

Re: Dynamic sum in GridView footer.

Jun 09, 2010 08:57 AM|LINK

i used sqlserver as i dont have MS access in my machine. Make changes as per your requirement. this is sample code. required result

Month Number of Injury Expenses

01-2003 4 $20

02-2003 5 $60

GrandTotal 9 $80

<asp:GridView ID="GridView1" runat="server" BackColor="White"
            BorderColor="#336666" BorderStyle="Double" BorderWidth="3px" CellPadding="4"
            GridLines="Horizontal" AutoGenerateColumns="False" DataSourceID="SqlDataSource1" ShowFooter="true">
            <RowStyle BackColor="White" ForeColor="#333333" />
            <FooterStyle BackColor="White" ForeColor="#333333" />
            <PagerStyle BackColor="#336666" ForeColor="White" HorizontalAlign="Center" />
            <SelectedRowStyle BackColor="#339966" Font-Bold="True" ForeColor="White" />
            <HeaderStyle BackColor="#336666" Font-Bold="True" ForeColor="White" />
            <Columns>
            <asp:BoundField DataField="DT_OF_INJURY" HeaderText="Date of Injury" FooterText="Grand Total" />
            <asp:TemplateField HeaderText="No. of Injuries" FooterStyle-Font-Bold="true">
            <ItemTemplate>
            <%# GetInjuryNbr(Int32.Parse(Eval("INJURY_NO").ToString())).ToString("N2") %>
            </ItemTemplate>
            <FooterTemplate>
            <%# GetInjuryTotal().ToString("N2") %>
            </FooterTemplate>


<FooterStyle Font-Bold="True"></FooterStyle>
            </asp:TemplateField>
                        <asp:TemplateField HeaderText="Cost" FooterStyle-Font-Bold="true">
            <ItemTemplate>
            <%# GetCost(decimal.Parse(Eval("COST").ToString())).ToString("N2") %>
            </ItemTemplate>
            <FooterTemplate>
            <%# GetCostTotal().ToString("N2") %>
            </FooterTemplate>


<FooterStyle Font-Bold="True"></FooterStyle>
            </asp:TemplateField>
            </Columns>
        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="Select * from injury">
</asp:SqlDataSource>

web. config (change as per your requirement)

<connectionStrings>
    <add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MyDatabase.mdf;Integrated Security=True;User Instance=True"
      providerName="System.Data.SqlClient" />
  </connectionStrings>

Code Behind

public int TotalInjuryNbr;
    public int GetInjuryNbr(int Price)
        {
        TotalInjuryNbr += Price;
        return Price;
        }
    public int GetInjuryTotal()
        {
        return TotalInjuryNbr;
        }

    public decimal TotalCost;
    public decimal GetCost(decimal cost)
        {
        TotalCost += cost;
        return cost;
        }
    public decimal GetCostTotal()
        {
        return TotalCost;
        }

hope this will help you

- Reply
nikhilgupta

Member

674 Points

204 Posts

Re: Dynamic sum in GridView footer.

Jun 09, 2010 01:48 PM|LINK

I am not using MS Access. I said i want O/p to be like MS Access CrossTab. I am using SQL Server 2008 & Visual Studio 2008.

U did not view the linked post. I can not use a function in the footer as i have some extra fields that doesnot need to be summed.

More over My normal SQL o/p is like :

Units | IRNo          | IndentNo           | IndentDate            | FromDate                    | ToDate                 | Item | Qty

1         123              xyz                      06/09/2010             06/01/2010                    06/15/2010             abc     10

1         123              xyz                      06/09/2010             06/01/2010                    06/15/2010             xyz       2

2         896              pqr                      06/09/2010             06/01/2010                    06/15/2010             abc      5

2         896              pqr                      06/09/2010             06/01/2010                    06/15/2010             xyz       9

But I want My result to be like :

Units | IRNo | IndentNo | IndentDate | FromDate | ToDate | abc| xyz| Itemn

1            x           x                     x                   x            x          10   2

2            x           x                     x                   x            x           5    9

SUM---------------------------------------------------------------------- 15 11

"Mark As Answer" if my reply helped you.
- Reply
Decker Dong ...

All-Star

118619 Points

18779 Posts

Re: Dynamic sum in GridView footer.

Jun 10, 2010 01:44 AM|LINK

Please fill the datatable with the sql statement below first:

select *, count(distinct id) from (select id,a,IsNull(b,0),(a+b) as TotalCost from xxx) group by id

--Please use this to fill with DataSet or DataTable, attentions should be called here that b can be Null.

Then please use this:

1) Make sure that the property of your Gridview is enabled to set true.

2) Do something like this in the Page_Load event:

GridView1.FooterRow.Cells[0-based index].Text = dt.Compute("Sum(TotalCost)").ToString();

The result is just want you want.
- Reply
nikhilgupta

Member

674 Points

204 Posts

Re: Dynamic sum in GridView footer.

Jun 10, 2010 07:51 AM|LINK

Guys you are not understanding My Problem, please visit http://forums.asp.net/t/1565406.aspx. This is my actual post. Here

in this post I resolved half on my problem using T-SQL but now i need to resolve the other half from GridView_RowDataBound events point

of view.

I really appreciate all the help, but it should help me in resolving the problem.

"Mark As Answer" if my reply helped you.

sarathi125

Star

13599 Points

2691 Posts

Re: Dynamic sum in GridView footer.

Jun 10, 2010 08:56 AM|LINK

Hi,

If you want to sum the column means make a separate method and call it in the RowDataBound Event Like this.

protected void SumTotal()
    {
        double totamount = 0;
        TextBox txtTotal = (TextBox)gvInvoice.FooterRow.FindControl("txtFColumn3");
        foreach (GridViewRow gvr in gvInvoice.Rows)
        {
            Label lblAmount = (Label)gvr.Cells[2].FindControl("lblColumn3");
            totamount = totamount + Convert.ToDouble(lblAmount.Text);
        }
        txtTotal.Text = totamount.ToString();
    }

Parthasarathi,
Remember to click Mark as Answer on the post that helps to others.

My Blog :MyAspSnippets

nikhilgupta

Member

674 Points

204 Posts

Re: Dynamic sum in GridView footer.

Jun 10, 2010 10:43 AM|LINK

I really thank people who are posting here, but i request them to first visit connecting post. Some text from that post

IssueTable
IssueId	UnitId	TotalStr	ItemId	IssueQty	Dated
1	1	10	1	10	6/5/2010
2	1	10	2	5	6/5/2010
3	1	10	3	50	6/5/2010
4	2	25	1	5	6/5/2010
5	2	25	3	10	6/5/2010

Now I want O/p to be like :

Units	TotalStr	ItemName-1	ItemName-2	ItemName-3	ItemName-n
UnitName-1	10	IssueQty	IssueQty	0	IssueQty
UnitName-2	25	0	IssueQty	0	IssueQty
UnitName-n	n	IssueQty	0	IssueQty	0
Total	SUM	SUM	SUM	SUM	SUM

Units will vary from day to day & so will Items.

Please see this http://forums.asp.net/t/1565406.aspx post to understand properly & answer in right way.

"Mark As Answer" if my reply helped you.

- Reply
nikhilgupta

Member

674 Points

204 Posts

Re: Dynamic sum in GridView footer.

Jun 11, 2010 08:19 AM|LINK

For those who are still understanding my problem here is the quick view. The following is a result of

http://forums.asp.net/t/1565406.aspx post. What i am left is to sum different items that are dynamic & will vary from day to day in gridview.

I hope some 1 will help me in solution. plz read the other post for exact problem.

"Mark As Answer" if my reply helped you.
- Edited by nikhilgupta on Jun 11, 2010 08:21 AM
- Edited by nikhilgupta on Jun 11, 2010 08:23 AM
- Reply
superguppie

All-Star

48225 Points

8679 Posts

Re: Dynamic sum in GridView footer.

Jun 11, 2010 11:43 AM|LINK
Ok, I haven't read the previous post. But let me try to say what I think that last picture means.
You want some Footer Cells of your GridView to hold the sum of the data Cells in the same column. If so, then maybe this post will be of help.

Superguppie.
Please remember to click “Mark as Answer” on the post that helps you. This can be beneficial to other community members reading the thread.
When all you've got is a Hammer, Every Problem looks like a Nail. Michael Swain.
- Reply
nikhilgupta

Member

674 Points

204 Posts

Re: Dynamic sum in GridView footer.

Jun 12, 2010 04:16 PM|LINK

Hi superguppie ,you are absolutely right. Your post is very good i just want to know how to store whole column in footer

when till GridView.DataBind() we dont know how many columns are there.

What ever idea i have & even that's not working i.e. declare some array but will not know about its size for that can use

fixed-non-computable columns(int FNC = 7), set computable columns(int CC) =

Total Gridview columns(Not known till databound) - FNC, then use RowDataBound for DataRow to add cell values

(for loop CC times in array, Then add array to corresponding footer cells.

I hope u understood what i am saying, otherwise will write some psudo-code so that some1 can help writing code.

"Mark As Answer" if my reply helped you.
- Edited by nikhilgupta on Jun 12, 2010 04:17 PM

Prev Next

1 2 Last

‹ Previous Thread|Next Thread ›

Dynamic sum in GridView footer. RSS

17 replies

nikhilgupta