This is a migrated thread and some comments may be shown as answers.

RadPivotGrid CustomCalculatedField In aggregateColumn

5 Answers 100 Views
PivotGrid and PivotFieldList
This is a migrated thread and some comments may be shown as answers.
Iñaki
Top achievements
Rank 1
Iñaki asked on 29 Jan 2021, 07:20 AM

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

5 Answers, 1 is accepted

Sort by
0
Dess | Tech Support Engineer, Principal
Telerik team
answered on 02 Feb 2021, 11:04 AM
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

Virtual Classroom, the free self-paced technical training that gets you up to speed with Telerik and Kendo UI products quickly just got a fresh new look + new and improved content including a brand new Blazor course! Check it out at https://learn.telerik.com/.

0
Iñaki
Top achievements
Rank 1
answered on 03 Feb 2021, 07:55 AM

Good day Dess,

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

Regards,

Xabi

0
Iñaki
Top achievements
Rank 1
answered on 10 Feb 2021, 06:59 AM

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

0
Dess | Tech Support Engineer, Principal
Telerik team
answered on 12 Feb 2021, 11:37 AM

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

Virtual Classroom, the free self-paced technical training that gets you up to speed with Telerik and Kendo UI products quickly just got a fresh new look + new and improved content including a brand new Blazor course! Check it out at https://learn.telerik.com/.

0
Iñaki
Top achievements
Rank 1
answered on 11 Mar 2021, 04:32 PM

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

Tags
PivotGrid and PivotFieldList
Asked by
Iñaki
Top achievements
Rank 1
Answers by
Dess | Tech Support Engineer, Principal
Telerik team
Iñaki
Top achievements
Rank 1
Share this question
or