subtraction in GridView footer rowi have the following dataCategoryTotal Revenue15000Expenses7000i obtain this data by using the following union all query private void BindProfit con new SqlDbConnect conSqlQuery34select ATypesumCredit 39Total39 from tblSalesTransaction as pt inner join tblGroupAccount as ga on ptAccountIDgaAccountID inner join tblGroup as g on gGroupIDgagroupID

Subtract Group item value and display in GridView Footer using C# and VB.net in ASP.Net

smile

SOLVED CLOSED ACTIVE ANSWERED
User: smile
Posted: on May 10, 2020 05:41 AM
Forum: Data Controls
Answer: 1
Views: 3070

subtraction in GridView footer row

i have the following data:

Category	Total
Revenue	15000
Expenses	7000

i obtain this data by using the following union all query

    private void BindProfit()
    {
        con = new SqlDbConnect();
        con.SqlQuery(@"select A_Type,sum(Credit) 'Total' from tblSalesTransaction as pt
                        inner join tblGroupAccount as ga on pt.AccountID=ga.AccountID
                        inner join tblGroup as g on g.GroupID=ga.groupID
                        inner join tblAccountType as at on g.TypeID=at.TypeID where A_Type='Revenue' and Credit > 0 group by A_Type
                        union all
                        select A_Type,sum(Debit) 'Total' from tblPayTransaction as pt
                                                inner join tblGroupAccount as ga on pt.AccountID=ga.AccountID
                                                inner join tblGroup as g on g.GroupID=ga.groupID
                                                inner join tblAccountType as at on g.TypeID=at.TypeID where A_Type='Expenses' and Debit > 0 group by A_Type;
                        ;");
        // con.Cmd.Parameters.Add(new SqlParameter("@Cat", this.ddlHead.SelectedItem.ToString()));
        adapt.SelectCommand = con.Cmd;
        DataTable pro = new DataTable();
        adapt.Fill(pro);
        if (pro.Rows.Count > 0)
        {
            GridView7.Visible = true;
            GridView7.DataSource = pro;
            GridView7.DataBind();

            GridView7.FooterRow.Cells[0].Text = "Revenu - Expenses = Proft:";
            GridView7.FooterRow.Cells[0].Font.Bold = true;
            GridView7.FooterRow.Cells[0].BackColor = System.Drawing.Color.YellowGreen;
            GridView7.FooterRow.Cells[0].HorizontalAlign = HorizontalAlign.Right;

            //decimal totalDis = sTable.AsEnumerable().Sum(row => row.Field<decimal>("Debit"));
            decimal totalDis = pro.AsEnumerable().Sum(row => row.Field<decimal?>("Total")).Value;
            GridView7.FooterRow.Cells[1].Text = totalDis.ToString();
            GridView7.FooterRow.Cells[1].HorizontalAlign = HorizontalAlign.Center;
            GridView7.FooterRow.Cells[1].Font.Bold = true;
            GridView7.FooterRow.Cells[1].BackColor = System.Drawing.Color.Yellow;


        }
        else
        {
            GridView7.Visible = false;
            Response.Write("No Record Found");
            return;
        }
        con.conClose();
    }

and html is

                                <asp:GridView ID="GridView7" runat="server" AllowPaging="false"  CssClass="table table-striped table-bordered table-hover"
                                    BackColor="LightGoldenrodYellow" BorderColor="Tan" BorderWidth="0px" CellPadding="6" RowStyle-Wrap="false" HeaderStyle-Wrap="false"
                                    AutoGenerateColumns="false" Font-Names="Arial" Font-Size="Small" GridLines="None" ShowFooter="true"
                                    ForeColor="black" >
                                    <Columns>
                                        <asp:BoundField ItemStyle-Width="70px" DataField="A_Type" HeaderText="Category" />
                                        <asp:BoundField ItemStyle-Width="75px" DataField="Total" HeaderText="Total" />                                                      
                                    </Columns>
                                    <FooterStyle BackColor="Tan" />
                                    <PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue" HorizontalAlign="Center" />
                                    <SelectedRowStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" />
                                    <HeaderStyle BackColor="Tan" Font-Bold="True" HorizontalAlign="Left" />
                                    <AlternatingRowStyle BackColor="PaleGoldenrod" />
                                    <RowStyle HorizontalAlign="Left" />
                                    </asp:GridView>

but i want to get data in the following shape from the above union all query.

Category	Total
Revenue	15000
Expenses	7000
Revenu - Expenses = Proft:	8000

how to get solution???

Download FREE API for Word, Excel and PDF in ASP.Net

dharmend...

0 1

Reply
ANSWER
User: dharmendr
Replied: on May 10, 2020 06:00 AM
Modified: on May 11, 2020 03:05 AM
Report

Hi smile,

Refer below sample.

HTML

<asp:GridView ID="GridView7" runat="server" AutoGenerateColumns="false" ShowFooter="true">
    <Columns>
        <asp:BoundField DataField="A_Type" HeaderText="Category" />
        <asp:BoundField DataField="Total" HeaderText="Total" />
    </Columns>
</asp:GridView>

Code

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        System.Data.DataTable pro = new System.Data.DataTable();
        pro.Columns.Add("A_Type", typeof(string));
        pro.Columns.Add("Total", typeof(decimal));
        pro.Rows.Add("Revenue", 15000);
        pro.Rows.Add("Expenses", 7000);

        if (pro.Rows.Count > 0)
        {
            GridView7.Visible = true;
            GridView7.DataSource = pro;
            GridView7.DataBind();

            GridView7.FooterRow.Cells[0].Text = "Revenu - Expenses = Proft:";
            GridView7.FooterRow.Cells[0].Font.Bold = true;
            GridView7.FooterRow.Cells[0].BackColor = System.Drawing.Color.YellowGreen;
            GridView7.FooterRow.Cells[0].HorizontalAlign = HorizontalAlign.Right;

            decimal revenue = pro.AsEnumerable().Where(x => x["A_Type"].ToString() == "Revenue").Sum(row => row.Field<decimal?>("Total")).Value;
            decimal expenses = pro.AsEnumerable().Where(x => x["A_Type"].ToString() == "Expenses").Sum(row => row.Field<decimal?>("Total")).Value;
            decimal totalDis = revenue - expenses;

            GridView7.FooterRow.Cells[1].Text = totalDis.ToString();
            GridView7.FooterRow.Cells[1].HorizontalAlign = HorizontalAlign.Center;
            GridView7.FooterRow.Cells[1].Font.Bold = true;
            GridView7.FooterRow.Cells[1].BackColor = System.Drawing.Color.Yellow;
        }
        else
        {
            GridView7.Visible = false;
            Response.Write("No Record Found");
            return;
        }
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not IsPostBack Then
        Dim pro As System.Data.DataTable = New System.Data.DataTable()
        pro.Columns.Add("A_Type", GetType(String))
        pro.Columns.Add("Total", GetType(Decimal))
        pro.Rows.Add("Revenue", 15000)
        pro.Rows.Add("Expenses", 7000)

        If pro.Rows.Count > 0 Then
            GridView7.Visible = True
            GridView7.DataSource = pro
            GridView7.DataBind()
            GridView7.FooterRow.Cells(0).Text = "Revenu - Expenses = Proft:"
            GridView7.FooterRow.Cells(0).Font.Bold = True
            GridView7.FooterRow.Cells(0).BackColor = System.Drawing.Color.YellowGreen
            GridView7.FooterRow.Cells(0).HorizontalAlign = HorizontalAlign.Right

            Dim revenue As Decimal = pro.AsEnumerable().Where(Function(x) x("A_Type").ToString() = "Revenue").Sum(Function(row) row.Field(Of Decimal?)("Total")).Value
            Dim expenses As Decimal = pro.AsEnumerable().Where(Function(x) x("A_Type").ToString() = "Expenses").Sum(Function(row) row.Field(Of Decimal?)("Total")).Value
            Dim totalDis As Decimal = revenue - expenses

            GridView7.FooterRow.Cells(1).Text = totalDis.ToString()
            GridView7.FooterRow.Cells(1).HorizontalAlign = HorizontalAlign.Center
            GridView7.FooterRow.Cells(1).Font.Bold = True
            GridView7.FooterRow.Cells(1).BackColor = System.Drawing.Color.Yellow
        Else
            GridView7.Visible = False
            Response.Write("No Record Found")
            Return
        End If
    End If
End Sub

Screenshot

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Subtract Group item value and display in GridView Footer using C# and VB.net in ASP.Net

Company

Explore

Follow us

Contact Us