locked
Calc totals from GridView columns RRS feed

  • Question

  • User1510859543 posted

    I have a GridView that I need to calculate totals for 3 columns and display in a footer at bottom of a GridView.  Someone gave me a javascript function a few years ago but it does not always work.  I need to total the txtPartCost (2nd column below), The txtVendorListPrice (3rd column) and the LblGP label control value (4th column).  The txtPartCost can change by user changing or by changing the RadioButtonList value (that I have a calculation for.  Below is my page markup and would like either a javascript function to do this or a jquery function.  Thanks.

        <asp:Panel ID="PanelLines" runat="server">
            <asp:GridView ID="gvROLines" runat="server" AutoGenerateColumns="False" 
                DataSourceID="SqlselROLinesOrder" ShowFooter="True" 
                DataKeyNames="LineID" CssClass="grid9" AllowSorting="True">
                <Columns>
                    <asp:TemplateField SortExpression="PartStatus">
                        <HeaderTemplate>
                            <table>
                                <tr>
                                    <td>
                                        <strong>Skip&nbsp;Ord&nbsp;Rcvd&nbsp;Ret&nbsp;Dmg</strong>
                                    </td>
                                </tr>
                            </table>
                        </HeaderTemplate>
                        <ItemTemplate>
                            <asp:RadioButtonList ID="rblPartStatus" runat="server" 
                                SelectedValue='<%# Bind("PartStatus") %>' 
                                RepeatDirection="Horizontal" onclick="costchg(this,1)" 
                                TabIndex="-1" CellSpacing="4" CellPadding="4">
                                <asp:ListItem Value="0" Text=""></asp:ListItem>
                                <asp:ListItem Value="3" Text=""></asp:ListItem>
                                <asp:ListItem Value="4" Text=""></asp:ListItem>
                                <asp:ListItem Value="5" Text="" Enabled="False"></asp:ListItem>
                                <asp:ListItem Value="7" Text="" Enabled="False"></asp:ListItem>
                            </asp:RadioButtonList>
                            <asp:Label ID="LblPartStatusOld" runat="server" Text='<%# Eval("PartStatus") %>' CssClass="Hide"></asp:Label>
                        </ItemTemplate>
                        <HeaderStyle HorizontalAlign="Center" Font-Bold="False" Width="150"></HeaderStyle>
                        <ItemStyle HorizontalAlign="Left" Width="150" />
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Cost" ItemStyle-HorizontalAlign="Right" ItemStyle-BackColor="#99FFCC" 
                        FooterStyle-BackColor="#99FFCC" ItemStyle-Width="80" HeaderStyle-HorizontalAlign="NotSet" FooterStyle-HorizontalAlign="Right">
                        <HeaderTemplate>
                            <strong>Cost</strong>
                        </HeaderTemplate>
                        <ItemTemplate>
                            <asp:TextBox ID="txtPartCost" runat="server"  
                                Text='<%# Bind("PartCost", "{0:n2}") %>' CssClass="txtright" Width="72" onchange="costchg(this,2)">
                            </asp:TextBox>
                            <asp:RequiredFieldValidator ID="RequiredFieldValidator4" runat="server" 
                                ErrorMessage="Cost cannot be blank" Display="Dynamic" ControlToValidate="txtPartCost" SetFocusOnError="True">
                            </asp:RequiredFieldValidator>
                            <asp:FilteredTextBoxExtender ID="FilteredTextBoxExtender4" runat="server" 
                                TargetControlID="txtPartCost" ValidChars="0123456789.">
                            </asp:FilteredTextBoxExtender>
                            <asp:CompareValidator ID="CompareValidator2" runat="server" 
                                ErrorMessage="Cost must be a valid amount." 
                                Type="Double" 
                                Operator="DataTypeCheck" 
                                ControlToValidate="txtPartCost" Display="Dynamic">
                            </asp:CompareValidator>
                            <asp:Label ID="LblPartCostOld" runat="server" Text='<%# Eval("PartCost", "{0:n2}") %>' CssClass="Hide"></asp:Label>
                            <asp:TextBox ID="txtLineID" runat="server" Text='<%# Eval("LineID") %>' CssClass="Hide"></asp:TextBox>
                        </ItemTemplate>
                        <FooterTemplate>
                            <asp:Label ID="LblTotalPartCost" runat="server" Text="" Font-Bold="True" ClientIDMode="Static"></asp:Label>
                        </FooterTemplate>
                        <FooterStyle HorizontalAlign="Right" BackColor="#99FFCC"></FooterStyle>
                        <ItemStyle HorizontalAlign="Right" BackColor="#99FFCC" Width="80px"></ItemStyle>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="List Price" ItemStyle-HorizontalAlign="Right" 
                            ItemStyle-Width="100" HeaderStyle-HorizontalAlign="NotSet" FooterStyle-HorizontalAlign="Right">
                         <HeaderTemplate>
                            <strong>List Price</strong>
                        </HeaderTemplate>
                       <ItemTemplate>
                            <asp:TextBox ID="txtVendorListPrice" runat="server"  Text='<%# Eval("VendorListPrice", "{0:n2}") %>' 
                                    CssClass="txtright" Width="72" onchange="listchg(this);">
                            </asp:TextBox>
                            <asp:FilteredTextBoxExtender ID="FilteredTextBoxExtender5" runat="server" 
                                TargetControlID="txtVendorListPrice" ValidChars="0123456789.">
                            </asp:FilteredTextBoxExtender>
                            <asp:Label ID="LblVendorListPriceOld" runat="server" Text='<%# Eval("VendorListPrice", "{0:n2}") %>' CssClass="Hide"></asp:Label>
                        </ItemTemplate>
                        <FooterTemplate>
                            <asp:Label ID="LblTotalList" runat="server" Text="0.00" Font-Bold="True"></asp:Label>
                        </FooterTemplate>
                        <FooterStyle HorizontalAlign="Right"></FooterStyle>
                        <ItemStyle HorizontalAlign="Right" Width="100px"></ItemStyle>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="GP">
                        <ItemTemplate>
                            <asp:Label ID="LblGP" runat="server" Text='<%# Eval("GP", "{0:p0}") %>'></asp:Label>
                        </ItemTemplate>
                        <FooterTemplate>
                            <asp:Label ID="LblTotalGP" runat="server" Text="0%" Font-Bold="True" ClientIDMode="Static"></asp:Label>
                        </FooterTemplate>
                        <FooterStyle HorizontalAlign="Right"></FooterStyle>
                        <ItemStyle Width="36px" HorizontalAlign="Right"></ItemStyle>
                    </asp:TemplateField>
                </Columns>
                <HeaderStyle BackColor="#99CCFF" HorizontalAlign="Center" /> 
            </asp:GridView>
        </asp:Panel>
    

    Friday, August 16, 2019 5:31 PM

All replies

  • User-2054057000 posted

    The procedure to Calc totals from GridView columns is:

    1. Give the column some css class like 'total'.

    2. Now $("total") will give you all these column as an array.

    3. You loop through them using jQuery Each function like shown below:

    $(".total").each(function (index) {
          // do the addition
    });

    4. Finally, show it on the footer of the GridView.

    Refer: https://www.aspsnippets.com/Articles/Calculate-Row-Total-and-Grand-Total-in-ASPNet-GridView-using-jQuery.aspx

    Sunday, August 18, 2019 10:43 AM
  • User288213138 posted

    Hi dlchase,

    According to your description, I made a demo for your reference.

    You can get the sum of the Textbox values by loop through them using the each() method.

    And if you want to change txtPartCost by the value of radiobutton, you can use the change () method.

    The code:

    <script>
            $(document).ready(function () {
                txtPartCost();
                txtVendorListPriceTotal();
                LblGP();
                var radios = $("[id*=rblPartStatus] input[type=radio]");
                radios.change(function () {
                    
                    $('#Label1').text($(this).val() + $("#Label1").text());
                    
                });
    
            });
       
            function txtPartCost() {
                
                var val = 0;
                $('.t1').each(function () {
    
                    val = val + parseFloat($(this).val());
    
                });
                $('#Label1').text(val);
                rvalue = 0;
                val = 0;
            };
    
            function txtVendorListPriceTotal() {
                var val = 0;
                $('.t2').each(function () {
    
                    val = val + parseFloat($(this).val());
                });
                $('#Label2').text(val);
                val = 0;
            };
            function LblGP() {
                var val = 0;
                $('.t3').each(function () {
    
                    val = val + parseFloat($(this).val());
                });
                $('#Label3').text(val);
                val = 0;
            };
        </script>
    <div>
                <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" ShowFooter="True" DataSourceID="SqlDataSource1">
                     <Columns>                  
                        <asp:TemplateField HeaderText="Values">
                            <ItemTemplate>
                                <asp:RadioButtonList ID="rblPartStatus" runat="server" RepeatDirection="Horizontal" CssClass="items">
                                <asp:ListItem Value="0" Text=""></asp:ListItem>
                                <asp:ListItem Value="3" Text=""></asp:ListItem>
                                <asp:ListItem Value="4" Text=""></asp:ListItem>
                                <asp:ListItem Value="5" Text="" Enabled="False"></asp:ListItem>
                                <asp:ListItem Value="7" Text="" Enabled="False"></asp:ListItem>
                            </asp:RadioButtonList>
                            </ItemTemplate>
                            
                        </asp:TemplateField>
    
                        <asp:TemplateField HeaderText="txtPartCost">
                            <ItemTemplate>
                                <asp:TextBox ID="TextBox1" runat="server" Text='<%# Eval("txtPartCost") %>' CssClass="t1"></asp:TextBox>
                            </ItemTemplate>
                            <FooterTemplate>
                                Total :<asp:Label ID="Label1" runat="server" ClientIDMode="Static"></asp:Label>
                            </FooterTemplate>
                        </asp:TemplateField>
    
                        <asp:TemplateField HeaderText="txtVendorListPrice ">
                            <ItemTemplate>
                                <asp:TextBox ID="TextBox2" runat="server" Text='<%# Eval("txtVendorListPrice") %>' CssClass="t2"></asp:TextBox>
                            </ItemTemplate>
                            <FooterTemplate>
                                Total :<asp:Label ID="Label2" runat="server" ClientIDMode="Static"></asp:Label>
                            </FooterTemplate>
                        </asp:TemplateField>
    
                        <asp:TemplateField HeaderText="LblGP">
                            <ItemTemplate>
                                <asp:TextBox ID="TextBox3" runat="server" Text='<%# Eval("LblGP") %>' CssClass="t3"></asp:TextBox>
                            </ItemTemplate>
                            <FooterTemplate>
                                Total :<asp:Label ID="Label3" runat="server" ClientIDMode="Static"></asp:Label>
                            </FooterTemplate>
                        </asp:TemplateField>
                    </Columns>
                              
                </asp:GridView>
    

    The result:

    Best regards,

    Sam

    Monday, August 19, 2019 10:12 AM
  • User1510859543 posted

    In your function txtPartCost you refer to a variable named rvalue.  What is this?

    Monday, August 19, 2019 1:23 PM
  • User288213138 posted

    Hi dlchase,

    That's a variable that I defined, but it doesn't do anything.

    Best regards,

    Sam

    Monday, August 19, 2019 2:55 PM
  • User1510859543 posted

    This is really close to exactly what I need.  The radio button click is suppose to calculate the txtPartCost based on the txtVendorListPrice.Text times the discount percent (which I forgot to include) in a Label control inside a FormView above the GridView.  The user can either enter the cost or click the radiobutton which calcs the cost. The Label control is named LblDiscount (see below)

    <asp:Label ID="LblDiscount" runat="server" Text='<%# Format((Eval("Discount") / 100),"Percent") %>' />

    Monday, August 19, 2019 6:45 PM
  • User288213138 posted

    Hi dlchase,

    According to your description, I couldn’t understand your requirement clearly.

    Do you mean you want to calculate the value of txtVendorListPrice by clicking radiobutton?

    If this is your requirement, I suggest you could try to below codes:

    <script>
            $(document).ready(function () {
                
                txtVendorListPriceTotal();
                LblGP();
                var radios = $("[id*=rblPartStatus] input[type=radio]");
                radios.change(function () {
                    var a = $("#FormView1_LblDiscount").text();
                    var t1 = $("#GridView1_TextBox2_0").val();
                    var t2 = $("#GridView1_TextBox2_1").val();
                   var t3 = $("#GridView1_TextBox2_2").val();
                    var c1 = parseFloat(a) * parseFloat(t1);
                    var c2 = parseFloat(a) * parseFloat(t2);
                    var c3 = parseFloat(a) * parseFloat(t3);
                    if ($(this).val() == 0) {                    
                        $('#GridView1_TextBox1_0').val(c1);
                        $('#Label1').text(c1); 
                    }
                    else if ($(this).val() == 3) {
                        
                        $('#GridView1_TextBox1_1').val(c2);
                        $('#Label1').text(c1+c2); 
                    }
                    else if ($(this).val() == 4) {
                        
                        $('#GridView1_TextBox1_2').val(c3);
                        $('#Label1').text(c1 + c2+c3); 
                    }              
                });
    
            });
           
            function txtVendorListPriceTotal() {
                var val = 0;
                $('.t2').each(function () {
    
                    val = val + parseFloat($(this).val());
                });
                $('#Label2').text(val);
                val = 0;
            };
            function LblGP() {
                var val = 0;
                $('.t3').each(function () {
    
                    val = val + parseFloat($(this).val());
                });
                $('#Label3').text(val);
                val = 0;
            };
        </script>
    <div>        
                FormView:<asp:FormView ID="FormView1" runat="server" DataSourceID="SqlDataSource2">               
                    <ItemTemplate>
                        Discount:
                        <asp:Label ID="LblDiscount" runat="server" Text='<%# Bind("Discount") %>' />
                    </ItemTemplate>                                                              
                </asp:FormView>                                                     
                <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:CaseTestConnectionString %>" SelectCommand="SELECT * FROM [Test40]"></asp:SqlDataSource>
                                 
                <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" ShowFooter="True" DataSourceID="SqlDataSource1">
                     <Columns>                  
                        <asp:TemplateField HeaderText="Values">
                            <ItemTemplate>
                                <asp:RadioButtonList ID="rblPartStatus" runat="server" RepeatDirection="Horizontal" CssClass="items">
                                <asp:ListItem Value="0" Text="0"></asp:ListItem>
                                <asp:ListItem Value="3" Text="3"></asp:ListItem>
                                <asp:ListItem Value="4" Text="4"></asp:ListItem>
                                <asp:ListItem Value="5" Text="5" Enabled="False"></asp:ListItem>
                                <asp:ListItem Value="7" Text="7" Enabled="False"></asp:ListItem>
                            </asp:RadioButtonList>
                            </ItemTemplate>
                            
                        </asp:TemplateField>
    
                        <asp:TemplateField HeaderText="txtPartCost">
                            <ItemTemplate>
                                <asp:TextBox ID="TextBox1" runat="server" CssClass="t1"></asp:TextBox>
                            </ItemTemplate>
                            <FooterTemplate>
                                Total :<asp:Label ID="Label1" runat="server" ClientIDMode="Static"></asp:Label>
                            </FooterTemplate>
                        </asp:TemplateField>
    
                        <asp:TemplateField HeaderText="txtVendorListPrice ">
                            <ItemTemplate>
                                <asp:TextBox ID="TextBox2" runat="server" Text='<%# Eval("txtVendorListPrice") %>' CssClass="t2"></asp:TextBox>
                            </ItemTemplate>
                            <FooterTemplate>
                                Total :<asp:Label ID="Label2" runat="server" ClientIDMode="Static"></asp:Label>
                            </FooterTemplate>
                        </asp:TemplateField>
    
                        <asp:TemplateField HeaderText="LblGP">
                            <ItemTemplate>
                                <asp:TextBox ID="TextBox3" runat="server" Text='<%# Eval("LblGP") %>' CssClass="t3"></asp:TextBox>
                            </ItemTemplate>
                            <FooterTemplate>
                                Total :<asp:Label ID="Label3" runat="server" ClientIDMode="Static"></asp:Label>
                            </FooterTemplate>
                        </asp:TemplateField>                   
                    </Columns>                        
                </asp:GridView>                           
                <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:CaseTestConnectionString %>" SelectCommand="SELECT * FROM [Test37]"></asp:SqlDataSource>                            
            </div>
    

    The result:

    Best regards,

    Sam

    Tuesday, August 20, 2019 9:32 AM