ASPxGridView - How to calculate Total Summary based on other Summary values

Hi Nitesh,

Thank you for your message. We appreciate your patience while we are working on finding a solution to your inquiry.

Thanks,
Marion

Hi Nitesh,

You can calculate a custom summary using other summary values. Please review the How to calculate the total summary value based on values of other summaries example.

>> Then I need it to be so that when I enter a value in the textbox the summaries change accordingly via javascript.
The client-side ASPxGridView control and all summary calculation actions are execute on the server side.
You can refresh the grid on every text changing.

Thanks,
Ruslan

Thanks Ruslan,

That looks good. I will give that a try. I need some help though. How can I total only the rows where the isDisbursement falg is true. and then only the rows where it is false. This is what I have so far:
Am I on the right track?

Protected Sub gvCostings_CustomSummaryCalculate(sender As Object, e As DevExpress.Data.CustomSummaryEventArgs)
        Dim currRow As Integer = e.RowHandle
        If e.SummaryProcess = DevExpress.Data.CustomSummaryProcess.Start Then
            totalDisbursements = 0
        End If
        If e.SummaryProcess = DevExpress.Data.CustomSummaryProcess.Calculate Then
            If Convert.ToBoolean(gvCostings.GetRowValues(currRow, "isDisbursement")) = True Then

            End If
        End If
        If e.SummaryProcess = DevExpress.Data.CustomSummaryProcess.Finalize Then
            e.TotalValue = totalDisbursements
        End If
    End Sub

Hello Nitesh,

Thank you for your clarification.

The demonstrated approach looks correct, but it is impossible to calculate a summary of rows with isDisbursement=true and after that, the summary of rows with isDisbursement=false (or vice versa). ASPxGridView iterates through rows sequentially one by one. You need to simultaneously calculate these summaries (you can store their values in different local variables) while e.SummaryProcess = Calculate, combine them when e.SummaryProcess = Finalize, and assign the result to the e.TotalValue property.

Best regards,
Vladimir

Hi Guys,

I've made a bit of progress following your advise. Please have a look and let me know if I should change anything. This is how I add columns and summaryitems dynamically:

Private Sub PopulateCostings(ByVal strFileNumber As String)
        Dim fileID As Long = Convert.ToInt64(strFileNumber)
        Dim bl As New FileManDAL.FileManBL

        Me.gvCostings.Columns.Clear()

        Dim dt As New DataTable
        dt = bl.GetFileCostingsTable(fileID)

        For i = 1 To dt.Columns.Count - 1

            If i = 1 Then
                Dim lblColumn As GridViewDataColumn = New GridViewDataColumn
                lblColumn.FieldName = dt.Columns(i).ColumnName
                gvCostings.Columns.Add(lblColumn)
            Else
                Dim txtColumn As GridViewDataTextColumn = New GridViewDataTextColumn
                txtColumn.FieldName = dt.Columns(i).ColumnName
                txtColumn.DataItemTemplate = New myTextBoxTemplate
                gvCostings.Columns.Add(txtColumn)

                Dim si As ASPxSummaryItem = New ASPxSummaryItem
                si.FieldName = dt.Columns(i).ColumnName
                si.SummaryType = DevExpress.Data.SummaryItemType.Custom
                si.Tag = "Disbursements"
                si.ShowInColumn = dt.Columns(i).ColumnName
                si.DisplayFormat = "{0:c2}"
                gvCostings.TotalSummary.Add(si)

                Dim subt As ASPxSummaryItem = New ASPxSummaryItem
                subt.FieldName = dt.Columns(i).ColumnName
                subt.SummaryType = DevExpress.Data.SummaryItemType.Custom
                subt.Tag = "SubTotal"
                subt.ShowInColumn = dt.Columns(i).ColumnName
                subt.DisplayFormat = "{0:c2}"
                gvCostings.TotalSummary.Add(subt)

                Dim vat As ASPxSummaryItem = New ASPxSummaryItem
                vat.FieldName = dt.Columns(i).ColumnName
                vat.SummaryType = DevExpress.Data.SummaryItemType.Custom
                vat.Tag = "VAT"
                vat.ShowInColumn = dt.Columns(i).ColumnName
                vat.DisplayFormat = "{0:c2}"
                gvCostings.TotalSummary.Add(vat)

                Dim total As ASPxSummaryItem = New ASPxSummaryItem
                total.FieldName = dt.Columns(i).ColumnName
                total.SummaryType = DevExpress.Data.SummaryItemType.Custom
                total.Tag = "TOTAL"
                total.ShowInColumn = dt.Columns(i).ColumnName
                total.DisplayFormat = "{0:c2}"
                gvCostings.TotalSummary.Add(total)

            End If

        Next

        If (dt.Rows.Count > 0) Then
            gvCostings.Columns(1).Visible = False
            gvCostings.Columns(2).Visible = False
            gvCostings.Columns(3).Visible = False
            'Session("dt") = dt
            'Cache("cols") = grvCostings.Columns
            gvCostings.DataSource = dt
            gvCostings.DataBind()
        End If

    End Sub

So I have a summaryItem for TotalDisbursements, one for Subtotal(totaldisbursements + total nondisbursements), one for vat(subtotal * 0.14), and one for total(subtotal + vat)

this is my CustomSummaryCalculate event:

Protected Sub gvCostings_CustomSummaryCalculate(sender As Object, e As DevExpress.Data.CustomSummaryEventArgs)
        Dim item As ASPxSummaryItem = CType(e.Item, ASPxSummaryItem)

        If e.SummaryProcess = DevExpress.Data.CustomSummaryProcess.Start Then
            totalDisbursements = 0
            totalNonDisbursements = 0
        End If
        If e.SummaryProcess = DevExpress.Data.CustomSummaryProcess.Calculate Then
            Dim isDisbursement As Boolean = Me.gvCostings.GetRowValues(e.RowHandle, New String() {"isDisbursement"})
            If isDisbursement Then
                totalDisbursements += If(IsDBNull(e.FieldValue), 0, e.FieldValue)
            Else
                totalNonDisbursements += If(IsDBNull(e.FieldValue), 0, e.FieldValue)
            End If

        End If
        If e.SummaryProcess = DevExpress.Data.CustomSummaryProcess.Finalize Then
            Select Case item.Tag
                Case "Disbursements"
                    e.TotalValue = totalDisbursements
                Case "SubTotal"
                    subTotal = totalDisbursements + totalNonDisbursements
                    e.TotalValue = subTotal
                Case "VAT"
                    Vat = (totalDisbursements + totalNonDisbursements) * 0.14
                    e.TotalValue = Vat
                Case "TOTAL"
                    e.TotalValue = subTotal + Vat
            End Select

        End If
    End Sub

I've given each summaryitem a tag, then I show totals based on the tag(not sure if this is the best method).

I've attached a screenshot of my end product. It is basically what I need.

My next step is entering values in the textboxes and refreshing the summary items accordingly. If I refresh the grid on every time I exit a textbox will this be bad practice?

Also, is there any way I can add a label to the first column like in attachment 2 that tells the user what total they are looking at e.g Total Disbursements, SubTotal, Vat.

Hello Nitesh,

Thank you for your reply.
We have prepared a sample project that illustrates a possible solution for a similar scenario. Please examine it and let us know if you need additional assistance.

Best regards,
Vladimir

thank you Vladimir,

That was very helpful. I'm 80% there. I'm working on the summaries at the moment. Using your method The total checked/uncked is perfect. The problem comes in on the SubTotal1. Since I add columns dynamically I think there are a few issues. I get the subtotal for coulmn 1 displayed in all the columns. Not sure how to fix this. Please see attached pic. If I fix this bug then my VAT and Total summaries will get fixed as well.

Hello Nitesh,

Thank you for your reply.
Please make sure that you correctly initialize the ASPxSummaryItem.ShowInColumn property of any SummaryItem by the field value of the dynamically created column. If this does not help, would you please modify my sample or provide your own to demonstrate this issue to us?

Best regards,
Vladimir

Hi Vladimir,

I'm sure I've set the property correctly. Since I get my data by exceuting a stored procedure the number of columns that come after the isDisbursments column can vary. I've attached a sample project. Please have a look and let me know where I am going wrong. The first two summaries are correct. From the subtotal onwards I have issues.

Also please help me get the refreshing of the grid working in my sample. When a value changes in any of the textboxes I need to refresh the summaries. I'm a bit worried about this though because if I have a grid with a lot of data it could take a long time to refresh.

Thanks again

Hello Nitesh,

Thank you for sending me your sample.

I have examined it and noticed that the Total summary set is created every time a new column is appended to ASPxGridVIew. As a result, each column displays Total Summaries (see the attached video). I have modified your sample to display the Summaries only in the last column footer. You can use a similar approach to display the summaries in any appropriate column footer.

Best regards,
Vladimir

Hi again,

Thank you for your time and effort. The problem is, I am wanting a summary for each column. It needs to work on a per column basis as in the picture I've attached, hence the method I tried. I've used my own custom gridview for that but I want to upgrade to the devexpress grid. Please let me know if it is possible

Hello Nitesh,

Thank you for your clarification.

I have slightly modified the project previously sent to achieve the desired result. Please refer to the recent attachment and let me know whether or not this solution meets your requirements.

Best regards,
Vladimir

wow, you are awesome. This is perfect. Exactly what I need. Can I please ask one more favour. I now need to refresh the summaries everytime a user changes a textbox value. Please help me get it right. Once I do this my task will be complete.

Hello Nitesh,

Thank you for your reply.

Please look at our answer on 4/23/2012 8:02:10 AM (PST). It contains a sample project that demonstrates how to accomplish this task. The main idea is the following: when any of ASPxTextBoxes is changed, its client-side value is saved in the 'hf' ASPxHiddenField. On the server side, saved values are restored from ASPxHiddenField. After that, you need to save the updated data to a database and bind ASPxGridView with this data.

New summaries will be calculated with the updated data and valid values will be displayed.

Best regards,
Vladimir

Hi Vladimir,

Sorry for the delayed response. I've tried but I can't get this to work because of the dynamic columns.Is there a way to do something similar to this. I used this method on my previous attempt using the asp.net gridview. On the row_databound event I check the disbursement flag. If it is false I assign the textbox a class of "calculate". if it is true I assign the textbox a class "calculated". Then I pass the textbox id and Index(which is the column index) to a doTotal javascript function.

  If Not IsDBNull(DataBinder.Eval(e.Row.DataItem, "isDisbursement")) Then
                intDisbursement = Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "isDisbursement"))

                If (intDisbursement = 0) Then

                    For j As Integer = 4 To e.Row.Cells.Count - 1
                        Dim index As String = e.Row.Cells(j).Attributes("index").ToString
                        For Each ctl As Control In e.Row.Cells(j).Controls
                            If TypeOf ctl Is TextBox Then
                                Dim txtbox As TextBox = CType(ctl, TextBox)
                                txtbox.Attributes.Add("OnChange", "doTotal(" & txtbox.ClientID & "," & index & ")")
                                txtbox.Attributes.Add("Class", "calculate")
                                txtbox.Attributes.Add("style", "font-family:Verdana;font-size:10px;width: 150px;")
                                CostingList.Add(New CostingRowColumn(e.Row.RowIndex, j, If(String.IsNullOrEmpty(txtbox.Text), 0.0, Convert.ToDecimal(txtbox.Text))))

                                Dim isTimeSheet As Boolean = Convert.ToBoolean(DataBinder.Eval(e.Row.DataItem, "isTimeSheet"))

                                If isTimeSheet Then
                                    txtbox.Attributes.Add("readonly", "readonly")
                                End If
                            End If
                        Next

                        'If Not IsDBNull(e.Row.DataItem("invoiceid")) Then
                        ' e.Row.Cells(j).Attributes.Add("colid", DataBinder.Eval(e.Row.DataItem, "invoiceid"))
                        'End If
                    Next

                Else

                    For j As Integer = 4 To e.Row.Cells.Count - 1
                        Dim index As String = e.Row.Cells(j).Attributes("index").ToString
                        For Each ctl As Control In e.Row.Cells(j).Controls
                            If TypeOf ctl Is TextBox Then
                                Dim txtbox As TextBox = CType(ctl, TextBox)
                                txtbox.Attributes.Add("OnChange", "doTotal(" & txtbox.ClientID & "," & index & ")")
                                txtbox.Attributes.Add("Class", "calculated")
                                txtbox.Attributes.Add("style", "font-family:Verdana;font-size:10px;width: 150px;")
                                DisbursementList.Add(New CostingRowColumn(e.Row.RowIndex, j, If(String.IsNullOrEmpty(txtbox.Text), 0.0, Convert.ToDecimal(txtbox.Text))))

                            End If
                        Next

                        'If Not IsDBNull(e.Row.DataItem("invoiceid")) Then
                        ' e.Row.Cells(j).Attributes.Add("colid", DataBinder.Eval(e.Row.DataItem, "invoiceid"))
                        'End If
                    Next

                    e.Row.BackColor = Drawing.Color.IndianRed

                    'If Not IsDBNull(DataBinder.Eval(e.Row.DataItem, "value")) Then
                    ' _TotalDisbursements += Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, _
                    ' "value"))
                    'End If
                End If
            End If

In the doTotal javascript function I search for all textboxes in the grid with the respective classes, either "calculate" or "calculated" using the passed through index and manipulate the grid values accordingly using javascript

 function doTotal(txtid, index)
        {
            var txtTotal = 0.00;
            var txtTotalD = 0.00;
            //alert($(txtid).attr('id').split("_")[7]);
            var invoicetxtId = $(txtid).attr('id').split("_")[7];
            
            var invid = invoicetxtId;
            //alert(invid);
            $("table[id*=grvCostings] input[type=text][id*=txt_"+ invid + "][class=calculate]").each(function (index, value) {
                var val = (value.value=='0')?0.00:value.value;
                val = (value.value=='')?0.00:value.value;
                //val = val.replace(",", ".");
                txtTotal = MathRound(parseFloat(txtTotal) + parseFloat(val));
            });

            $("table[id*=grvCostings] input[type=text][id*=txt_" + invid + "][class=calculated]").each(function (index, value) {
                var val = (value.value == '0') ? 0.00 : value.value;
                val = (value.value == '') ? 0.00 : value.value;
                //alert(val);
                //val = val.replace(",", ".");
                txtTotalD = MathRound(parseFloat(txtTotalD) + parseFloat(val));
            });
            
           var subTotal = txtTotalD + txtTotal;
 
            $(".totald_" + index.toString()).text(txtTotalD.toFixed(2).toString());
            $(".subtotal_" + index.toString()).text(subTotal.toFixed(2).toString());

            $(".subtotal_" + index.toString()).formatCurrency({ symbol: "R ", region: "en-ZA" });
            $(".totald_" + index.toString()).formatCurrency({ symbol: "R ", region: "en-ZA" });

            var vat = subTotal.toFixed(2) * 0.14;
            
            //alert('vat' + vat);
            var Total = subTotal + vat;
            
            $(".VAT_" + index.toString()).text(vat.toFixed(2).toString());
            $(".Total_" + index.toString()).text(Total.toFixed(2).toString());

            $(".VAT_" + index.toString()).formatCurrency({ symbol: "R ", region: "en-ZA" })
            $(".Total_" + index.toString()).formatCurrency({ symbol: "R ", region: "en-ZA" })
        }