Value of Crosstab SubTotals for rows and columns

[Elio Alexander]

I have a problem with crosstab sub totals, i have a table with 2 column groups (2012 and 2013) and another two row groups (Dolares and Soles),  i already managed to fill my table but what i need to do next is to calculate the totals. i have a column named "Diferencia" where i want the diference between 2013 TOTAL and 2012TOTAL, and i have a row at the bottom where i want the next formula ((Total of Dolares)*2.8 + (Total of soles)).

The columns and row groups are generated automatically...

This is something that is driving me crazy, i spent an entire day trying to figure out how to achive this but without any results, and thanks in advanced for you help

[IvanY]

Hi Elio,

Basically when you need to sum all the fields and show a total you would use an aggregate function like Sum. However since your example is more complex than a simple aggregate you will have to implement your own aggregate. Your aggregate will have to sum all the fields from a particular group, for example the 2012 group. It has to do the same for 2013 too and then finally you can subtract the two sums. For more information on how to implement a custom aggregate please check the User Aggregate Functions help article.

A simpler approach would be to recalculate the fields and display them in the total textbox instead of an aggregate function. With sql for example you can do this.

Regards,
IvanY
Telerik

[Elio Alexander]

Thank you for responding my question, i figure out how to do this and it was exactly as you told me to do it... i needed to make my own aggregate and add some variables to perform a simple if clause.

=Total_en_Soles(Fields.Moneda,Fields.Diezmo, Parameters.taza_cambio.Value) 

and the function is...

Public Sub Accumulate(ByVal values As Object()) Implements IAggregateFunction.Accumulate
  Try
            ' The aggregate function expects one parameter
            Dim value1 As Object = values(0)
            Dim value2 As Object = values(1)
            Dim value3 As Object = values(2)

            ' null values are not aggregated
            If value1 Is Nothing Or value2 Is Nothing Or value3 Is Nothing Then
                Return
            End If
            ' The actual accumulation
            'If Me.result.Length > 0 Then
            'result += ", "
            'End If
            If value1.ToString = "Soles" Or value1.ToString = "" Then
                Me.result += value2
            Else
                Me.result += (CDbl(value2) * CDbl(value3))
            End If
        Catch ex As Exception
            MsgBox(Err.Description)
        End Try