locked
Calculate Avg Based On Condition RRS feed

  • Question

  • User-807418713 posted

    Hello

    This is my Gridview Data

    Arrival Date Name Sale To Pieces Meter Avg
    1-Oct-18 Top Quality John 10 50 5
    2-Oct-18 Best M/S Quality   100 600 6
    3-Oct-18 Low Quality   20 105 5.25
    3-Oct-18 Best M/S Quality   1000 5107 5.11
    4-Oct-18 Best M/S Quality   100 940 9.4
               
      Grand Total   1230 6802 5.54

    Here I Want To Calculate Average Grand Total Take Some Of M/S Pieces & Meter Then Meter / Pieces here 6647/1200 = 5.54

    how to do so using Gridview Row Databound..

    Thanks

    Wednesday, October 10, 2018 6:30 AM

Answers

  • User839733648 posted

    Hi Gopi.MCA,

    Just as PatriceSc has said, you could add .Contains() method in DataBound to check if Name contains M/S.

            protected void GridView1_DataBound(object sender, EventArgs e)
            {
                int piecesAmount = 0;
                int meterAmount = 0;
                for (int i = 0; i < GridView1.Rows.Count; i++)
                {
                    string pieces = ((Label)GridView1.Rows[i].FindControl("lblPieces")).Text;
                    string meter = ((Label)GridView1.Rows[i].FindControl("lblMeter")).Text;
                    string name = GridView1.Rows[i].Cells[1].Text;
                    if (name.Contains("M/S"))
                    {
                        piecesAmount += Convert.ToInt32(pieces);
                        meterAmount += Convert.ToInt32(meter);
                    }
                }
                Label piecesTotal = (Label)GridView1.FooterRow.FindControl("PiecesTotal");
                Label meterTotal = (Label)GridView1.FooterRow.FindControl("MeterTotal");
                piecesTotal.Text = piecesAmount.ToString();
                meterTotal.Text = meterAmount.ToString();
                Label avgTotal = (Label)GridView1.FooterRow.FindControl("AvgTotal");
                float avgtotal = float.Parse(meterTotal.Text) / float.Parse(piecesTotal.Text);
                avgTotal.Text = avgtotal.ToString();
                GridView1.FooterRow.Cells[1].Text = "Grand Total";
            }

    reslut:

    Best Regards,

    Jenifer

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 11, 2018 9:31 AM

All replies

  • User753101303 posted

    Hi,

    My understanding is that you want to exclude row 1 and 3 from the grand total calculation but I'm not sure about your criteria. This is because you have less than x pieces ?

    IMO the code is clearer when you are working on source data (you are using ADO.NET or EF ?) rather than when binding a control to data and then taking data from your control rather than from the original data source.

    Wednesday, October 10, 2018 3:18 PM
  • User839733648 posted

    Hi Gopi.MCA,

    According to your description, I suggest that you could Calculate Average Grand Total in the DataBound Event better.

    I've made a sample on my side, and for more details, you could refer to the code below.

    .Aspx

               <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" ShowFooter="true" OnRowDataBound="GridView1_RowDataBound" OnDataBound="GridView1_DataBound">
                    <RowStyle HorizontalAlign="Center" />
                    <FooterStyle HorizontalAlign="Center" />
                    <Columns>
                        <asp:BoundField DataField="Arrival Date" HeaderText="Arrival Date" ItemStyle-Width="150">
                            <ItemStyle Width="150px"></ItemStyle>
                        </asp:BoundField>
                        <asp:BoundField DataField="Name" HeaderText="Name" ItemStyle-Width="150">
                            <ItemStyle Width="150px"></ItemStyle>
                        </asp:BoundField>
                        <asp:BoundField DataField="Sale To" HeaderText="Sale To" ItemStyle-Width="150">
                            <ItemStyle Width="150px"></ItemStyle>
                        </asp:BoundField>
                        <asp:TemplateField HeaderText="Pieces">
                            <ItemTemplate>
                                <asp:Label ID="lblPieces" runat="server" Text='<%# Eval("Pieces") %>'></asp:Label>
                            </ItemTemplate>
                            <FooterTemplate>
                                <asp:Label ID="PiecesTotal" runat="server"></asp:Label>
                            </FooterTemplate>
                            <ItemStyle Width="150px" />
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="Meter">
                            <ItemTemplate>
                                <asp:Label ID="lblMeter" runat="server" Text='<%# Eval("Meter") %>'></asp:Label>
                            </ItemTemplate>
                            <FooterTemplate>
                                <asp:Label ID="MeterTotal" runat="server"></asp:Label>
                            </FooterTemplate>
                            <ItemStyle Width="150px" />
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="Avg" >
                            <ItemTemplate>
                                <asp:Label ID="lblAvg" runat="server"></asp:Label>
                            </ItemTemplate>
                            <FooterTemplate>
                                <asp:Label ID="AvgTotal" runat="server"></asp:Label>
                            </FooterTemplate>                        
                            <ItemStyle Width="150px" />
                        </asp:TemplateField>
                    </Columns>
                </asp:GridView>

    Code-behind.

            protected void Page_Load(object sender, EventArgs e)
            {
                if (!this.IsPostBack)
                {
                    DataTable dt = new DataTable();
                    dt.Columns.AddRange(new DataColumn[5] { new DataColumn("Arrival Date"), new DataColumn("Name"), new DataColumn("Sale To"), new DataColumn("Pieces"), new DataColumn("Meter") });
                    dt.Rows.Add("1-Oct-18", "Top Quality", "John", "10", "50");
                    dt.Rows.Add("2-Oct-18", "Best M/S Quality", " ", "100", "600");
                    dt.Rows.Add("3-Oct-18", "Low Quality", " ", "20", "105");
                    dt.Rows.Add("3-Oct-18", "Best M/S Quality", " ", "1000", "5107");
                    dt.Rows.Add("4-Oct-18", "Best M/S Quality", " ", "100", "940");
                    GridView1.DataSource = dt;
                    GridView1.DataBind();
                }
            }
    
            protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
            {
                if (e.Row.RowType == DataControlRowType.DataRow)
                {
                    Label pieces = e.Row.FindControl("lblPieces") as Label;
                    Label meter = e.Row.FindControl("lblMeter") as Label;
                    Label avg = e.Row.FindControl("lblAvg") as Label;
                    float avgvalue = float.Parse(meter.Text) / float.Parse(pieces.Text);
                    avg.Text = avgvalue.ToString();
                }
            }
    
            protected void GridView1_DataBound(object sender, EventArgs e)
            {
                int piecesAmount = 0;
                int meterAmount = 0;
                for (int i = 0; i < GridView1.Rows.Count; i++)
                {
                    string pieces = ((Label)GridView1.Rows[i].FindControl("lblPieces")).Text;
                    string meter = ((Label)GridView1.Rows[i].FindControl("lblMeter")).Text;
                    piecesAmount += Convert.ToInt32(pieces);
                    meterAmount += Convert.ToInt32(meter);
                }
                Label piecesTotal=(Label)GridView1.FooterRow.FindControl("PiecesTotal");
                Label meterTotal = (Label)GridView1.FooterRow.FindControl("MeterTotal");
                piecesTotal.Text = piecesAmount.ToString();
                meterTotal.Text = meterAmount.ToString();
                Label avgTotal = (Label)GridView1.FooterRow.FindControl("AvgTotal");
                float avgtotal = float.Parse(meterTotal.Text) / float.Parse(piecesTotal.Text);
                avgTotal.Text = avgtotal.ToString();
                GridView1.FooterRow.Cells[1].Text = "Grand Total";
    
            }

    result:

    Best Regards,

    Jenifer

    Thursday, October 11, 2018 3:28 AM
  • User-807418713 posted

    Hello Jenifer

    Thanks For Your Reply

    But i Want to Show Avg Grand Total For Only  which have M/S  record Not All For All Data

    Please need code

    Thursday, October 11, 2018 7:26 AM
  • User753101303 posted

    With the code above you could add something such as :

    string name = ((Label)GridView1.Rows[i].FindControl("lblName")).Text;
    if(name.Contains("M/S")) {
    // sum
    }
    Thursday, October 11, 2018 8:00 AM
  • User839733648 posted

    Hi Gopi.MCA,

    Just as PatriceSc has said, you could add .Contains() method in DataBound to check if Name contains M/S.

            protected void GridView1_DataBound(object sender, EventArgs e)
            {
                int piecesAmount = 0;
                int meterAmount = 0;
                for (int i = 0; i < GridView1.Rows.Count; i++)
                {
                    string pieces = ((Label)GridView1.Rows[i].FindControl("lblPieces")).Text;
                    string meter = ((Label)GridView1.Rows[i].FindControl("lblMeter")).Text;
                    string name = GridView1.Rows[i].Cells[1].Text;
                    if (name.Contains("M/S"))
                    {
                        piecesAmount += Convert.ToInt32(pieces);
                        meterAmount += Convert.ToInt32(meter);
                    }
                }
                Label piecesTotal = (Label)GridView1.FooterRow.FindControl("PiecesTotal");
                Label meterTotal = (Label)GridView1.FooterRow.FindControl("MeterTotal");
                piecesTotal.Text = piecesAmount.ToString();
                meterTotal.Text = meterAmount.ToString();
                Label avgTotal = (Label)GridView1.FooterRow.FindControl("AvgTotal");
                float avgtotal = float.Parse(meterTotal.Text) / float.Parse(piecesTotal.Text);
                avgTotal.Text = avgtotal.ToString();
                GridView1.FooterRow.Cells[1].Text = "Grand Total";
            }

    reslut:

    Best Regards,

    Jenifer

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 11, 2018 9:31 AM