RadPivotGrid CustomCalculatedField In aggregateColumn

[Iñaki]

Good day,

I have a radpivotgridview with a custom calculated field, 2 questions:

First one I see in https://docs.telerik.com/devtools/winforms/controls/pivotgrid/calculated-fields to make the custom calculated field from just a value column named ExtendedPrice if I want column a from database div column b from database how could I implement that.

Second I have a calculated field like this:

Public Class TelerikPivotCalculatedFieldDirua
    Inherits CalculatedField
    Private denboraEuroakField As RequiredField
    Public Sub New()
        Me.Name = "TotalEuro"
        Me.denboraEuroakField = RequiredField.ForProperty("zenbatSeg")
    End Sub
    Protected Overrides Function RequiredFields() As IEnumerable(Of RequiredField)
        Return New List(Of RequiredField) From {denboraEuroakField}
    End Function
    Protected Overrides Function CalculateValue(aggregateValues As IAggregateValues) As AggregateValue
        Dim aggregateValue = aggregateValues.GetAggregateValue(Me.denboraEuroakField)
        If aggregateValue.IsError() Then
            Return aggregateValue
        End If
        Dim denboraEuroak As Integer = 0 '= aggregateValue.ConvertOrDefault(Of Integer)()
        denboraEuroak = CInt(aggregateValue.GetValue)
        If denboraEuroak >= 0 Then
            Dim gEurosSeg As Decimal = 0
            gEurosSeg = CDec(gEurosHora) / CDec(3600)
            Dim gGuztira As Decimal = 0
            gGuztira = CDec(denboraEuroak) * CDec(gEurosSeg)
            gGuztira = Math.Round(gGuztira, 2, MidpointRounding.AwayFromZero)
            Return New DoubleAggregateValue(gGuztira)
        End If
        Return Nothing
    End Function

End Class

In the vb file:

    Me.provider = New LocalDataSourceProvider()
        'Using rpgestadistica.PivotGridElement.DeferRefresh()
        Using provider.DeferRefresh()
            Dim calculatedField As New TelerikPivotCalculatedFieldDirua()
            calculatedField.Name = "TotalEuro"
            calculatedField.DisplayName = Func_nombre(3372)
            provider.CalculatedFields.Add(calculatedField)
        End Using
        'DirectCast(Me.rpgestadistica.DataProvider, LocalDataSourceProvider).CalculatedFields.Add(calculatedField)
        'rpgestadistica.DataProvider = provider
        Me.provider.RowGroupDescriptions.Add(New PropertyGroupDescription() With {.PropertyName = "globalNombre", _
                                                                                  .GroupComparer = New GroupNameComparer(), _
                                                                                  .CustomName = Func_nombre(3370)})
        Me.provider.RowGroupDescriptions.Add(New PropertyGroupDescription() With {.PropertyName = "tipogasin", _
                                                                                  .GroupComparer = New GroupNameComparer(), _
                                                                                  .CustomName = Func_nombre(160)})
        Me.provider.RowGroupDescriptions.Add(New PropertyGroupDescription() With {.PropertyName = "suscripcion", _
                                                                                  .GroupComparer = New GroupNameComparer(), _
                                                                                  .CustomName = Func_nombre(250)})
        Me.provider.RowGroupDescriptions.Add(New PropertyGroupDescription() With {.PropertyName = "razon", _
                                                                                  .GroupComparer = New GroupNameComparer(), _
                                                                                  .CustomName = Func_nombre(174)})
        Me.provider.AggregateDescriptions.Add(New PropertyAggregateDescription() With {.PropertyName = "total", _
                                                                                       .AggregateFunction = AggregateFunctions.Sum})
        Me.provider.AggregateDescriptions.Add(New PropertyAggregateDescription() With {.PropertyName = "denbora", _
                                                                                       .AggregateFunction = New TelerikPivotAggregateFunctionEstTiempo()})
        'Me.provider.AggregateDescriptions.Add(provider.CalculatedFields.Item(11))
        Me.provider.AggregateDescriptions.Add(New PropertyAggregateDescription() With {.PropertyName = "TotalEuro", _
                                                                                       .AggregateFunction = AggregateFunctions.Sum})

    'This last line appears like "error".
        Me.cargaridiomarpg()
        provider.AggregatesPosition = PivotAxis.Columns
        provider.AggregatesLevel = 2
        Me.provider.ItemsSource = ldt
        rpgestadistica.DataProvider = provider
        cargaridiomarpg()
        For Each rd As RadElement In Me.rpgestadistica.PivotGridElement.ColumnDescriptorsArea.Children
            Dim pgde As PivotGroupDescriptorElement = TryCast(rd, PivotGroupDescriptorElement)
            If Not IsNothing(pgde) Then
                Me.rpgestadistica.PivotGridElement.BestFitHelper.BestFitRowHeaders(pgde.Level)
            End If
        Next
        For Each element As RadElement In Me.rpgestadistica.PivotGridElement.RowDescriptorsArea.Children
            Dim pgde As PivotGroupDescriptorElement = TryCast(element, PivotGroupDescriptorElement)
            If Not IsNothing(pgde) Then
                Me.rpgestadistica.PivotGridElement.BestFitHelper.BestFitRowHeaders(pgde.Level)
            End If
        Next
        Me.rpgestadistica.PivotGridElement.BestFitHelper.BestFitColumns()

See Attach file 1, CustomAggregatev1:

If I rightClick and deattach TiempoEuro column and reattach it. I see it ok:

See Attach file 2 and Attach file 3.

How can I achive it like in attach file 3 from the beginning instead of having errors.

Thanks in advance,

Xabi

[Dess | Tech Support Engineer, Principal]

Hello, Xabi, 

I believe that the following KB article will be useful for achieving a more custom calculation (A/B) for the specific requirement that you have: https://docs.telerik.com/devtools/winforms/knowledge-base/add-weighted-average-in-pivotgrid 

Note that this is just a sample approach and it may not cover all possible cases. Feel free to modify and extend it in a way which meets the exact goal that you have. 

As to the second question that you have, please note that in the referred tutorial, the custom field is added to the LocalDataSourceProvider.CalculatedFields after the RadPivotGrid.DataProvider is set, then the RowGroupDescriptions, ColumnGroupDescriptions, etc and the ItemsSource are setup. Please follow the same approach as the one demonstrated in the example while initializing the pivot.  

I hope this information helps. If you need any further assistance please don't hesitate to contact me. 

Regards,
Dess | Tech Support Engineer, Sr.
Progress Telerik

[Iñaki]

Good day Dess,

Thanks for the answer I will try and comment if I see anything.

Regards,

Xabi

[Iñaki]

Good day,

Could you attach an example of the second point. I would like to fill the radpivotgrid with data after clicking in a button and refresh the data when reclicking the button.

Thanks in advance,

Xabier

[Dess | Tech Support Engineer, Principal]

Hello, Xabier,  

I have attached a sample project for your reference. The achieved result is illustrated in the gif file.

Please give it a try and see how it works on your end. 

Should you have further questions please let me know.

Regards,
Dess | Tech Support Engineer, Sr.
Progress Telerik

[Iñaki]

Good afternoon,

I think I have got it.

I will post my answer here aswell if there is more people like me.

Me.provider.AggregateDescriptions.Add(New PropertyAggregateDescription() With {.PropertyName = "TotalEuro", _
                                                                                       .AggregateFunction = AggregateFunctions.Sum}) -> In this line if your aggregateDescriptions is a calculated field -> Dim calculatedField As New TelerikPivotCalculatedFieldDirua() you cannot use the aggregateFunction of SUM if you want to use it you need to create your aggregate function of "myFunction" for instance and it will work at least it worked for my case.

 

Have a nice day and thanks.

The same happens in the attached project if you change this lines: you will get error instead of values:

If you change:

Lines 53-57:

dataProvider.AggregateDescriptions.Add(New PropertyAggregateDescription() With {
            .PropertyName = "MyCalculatedField",
            .AggregateFunction = New MyAggregateFunction(),
            .StringFormat = "N2"
        })

with

        dataProvider.AggregateDescriptions.Add(New PropertyAggregateDescription() With {
            .PropertyName = "MyCalculatedField",
            .AggregateFunction = AggregateFunctions.Sum,
            .StringFormat = "N2"
        })

It will not work.

Have a nice day, and thanks for the tips and the work.

Xabier