[Solved] Format column (currency vs percentage)

[Kurt Kluth]

Currently have this code working however I would like to take it a step further especially in the grouping footer as it will sum the total.  We have columns that are returned as "Decimal" however they could either be currency or a percentage.  Is there anyway of way to check the number that is being returned to I can either perform a Sum or Avg?

All of my percentages are returned from the database as .### (ie .435).

Also can I create a "dynamic" total line when they group.  Just a simple "Totals for {0}",  ({0} is what they selected to group on and anyway of making just the group footer bold?

Protected Sub _SearchResults_ColumnCreated(sender As Object, e As Telerik.Web.UI.GridColumnCreatedEventArgs) Handles _SearchResults.ColumnCreated
        If e.Column.DataType.Name = "Decimal" Then
            CType(e.Column, Telerik.Web.UI.GridBoundColumn).DataFormatString = "{0:N2}"
            CType(e.Column, Telerik.Web.UI.GridBoundColumn).Groupable = True

            CType(e.Column, Telerik.Web.UI.GridBoundColumn).Aggregate = Telerik.Web.UI.GridAggregateFunction.Sum
            e.Column.ItemStyle.HorizontalAlign = HorizontalAlign.Right
        End If

        'Specify the location of the Images
        e.Column.FilterImageUrl = "../images/Grid/Filter.gif"
        e.Column.SortAscImageUrl = "../images/Grid/SortAsc.gif"
        e.Column.SortDescImageUrl = "../images/Grid/SortDesc.gif"
    End Sub

[Eyup]

Hello Kurt,

Thank you for contacting us.

You can use a switch statement using the column UniqueName on ColumnCreated event to determine which aggregate you should use.

If this is not possible, you can grab the first item of the grid and traverse the columns:

Protected Sub RadGrid1_ItemDataBound(sender As Object, e As GridItemEventArgs)
    If TypeOf e.Item Is GridDataItem AndAlso e.Item.ItemIndex = 0 Then
        Dim dataItem As GridDataItem = TryCast(e.Item, GridDataItem)
        For Each col As GridColumn In dataItem.OwnerTableView.RenderColumns
            Dim numCol As GridNumericColumn = TryCast(col, GridNumericColumn)
            If numCol IsNot Nothing Then
                Dim value As Decimal = Decimal.Parse(dataItem(numCol).Text)
                Dim delta As Decimal = value * 100 - CInt(value * 100)
                Dim condition As Boolean = delta = 0

                numCol.Aggregate = If(condition, GridAggregateFunction.Sum, GridAggregateFunction.Avg)
            End If
        Next
    End If
End Sub

For dynamic configuration of the grouping expression, you can use the GroupsChanging event.

Hope this helps. Please give it a try and let me know if it works for you.

Regards,
Eyup
Telerik

[Kurt Kluth]

Eyup,

Thank you for your reply and the code example.  Using the UniqueName wasn't going to work as we have over 200 fields that are available for the user to select from and over time this list can just grow.

Also tried modifying it slightly to determine if it was a currency or should be considered a ratio.  The problem we ran into was some currency numbers would actually have a 0 value.  We couldn't check to see if the number was less than 0 because we could also have some currency columns be a negative number. 

We have decided not to implement group footer totals until we can come up with a better way to improve that logic.

Thank you

[Eyup]

Hi Kurt,

I'm glad that the provided directions were helpful.
If you happen to come up with a reliable solution for your specific data configuration, feel free to share it here with our community.

Regards,
Eyup
Telerik

[Kurt Kluth]

Eyup,

I tried the following code and while it worked on initial load when I performed a grouping it would break because it now had to create each item once again.  Not sure if there is something to check?  Or I only perform this call once. 

I was trying to count the number of decimal places because if it were greater than 2, I would know that the number was a ratio and not currency.

Protected Sub _SearchResults_ItemDataBound(sender As Object, e As GridItemEventArgs) Handles _SearchResults.ItemDataBound
     If TypeOf e.Item Is GridDataItem AndAlso e.Item.ItemIndex = 0 Then
         Dim dataItem As GridDataItem = TryCast(e.Item, GridDataItem)
         For Each col As GridColumn In dataItem.OwnerTableView.RenderColumns
             Dim numCol As GridNumericColumn = TryCast(col, GridNumericColumn)
             If numCol IsNot Nothing Then
                 If col.DataType.Name = "Decimal" Then
                     Dim numberAsString As String = Decimal.Parse(dataItem(numCol).Text)
                     Dim indexOfDecimalPoint As Integer = numberAsString.IndexOf(".")
                     Dim numberOfDecimals As Integer = _
                         numberAsString.Substring(indexOfDecimalPoint + 1).Length
                     If numberOfDecimals > 2 Then
                         numCol.Aggregate = GridAggregateFunction.Avg
                     Else
                         numCol.Aggregate = GridAggregateFunction.Sum
                     End If
                     numCol.DataFormatString = "{0:N2}"
                 End If
            End If
        Next
    End If
End Sub

[Eyup]

Hello Kurt,

I have created a sample RadGrid web site to test the provided snippet. Can you please run the attached application and instruct me the exact steps I need to follow to reproduce the issue?

Regards,
Eyup
Telerik

[Kurt Kluth]

Eyup,

 

Thank you for the example.  I have modified it slightly to reproduce the results that I am getting.  Take note of the following changes:

• Freight column is now 4 digits but will display as 2 client-side.
• ItemCost column was added and is only 2 digits

"Freight" column in this example will come from our database as 4 digits in the decimals place and the "itemCost" would be currency.  Making these changes would now cause the Freight column to sum the total rather than average.

Note: I was unable to attach a zip file (so see my modifications below)

ASPX page

<telerik:GridNumericColumn DataField="Freight" DataType="System.Decimal"
    FilterControlAltText="Filter Freight column" HeaderText="Freight"
    SortExpression="Freight" UniqueName="Freight" DataFormatString="{0:N2}">
</telerik:GridNumericColumn>
<telerik:GridNumericColumn DataField="ItemCost" DataType="System.Decimal"
    FilterControlAltText="Filter ItemCost column" HeaderText="Item Cost"
    SortExpression="ItemCost" UniqueName="ItemCost">
</telerik:GridNumericColumn>

Code Behind

column = New DataColumn()
column.DataType = Type.[GetType]("System.Decimal")
column.ColumnName = "ItemCost"
dataTable.Columns.Add(column)

    row("Freight") = (i + 1) + (i + 1) * 0.1 + (i + 1) * 0.001
    row("ItemCost") = (i + 1) + (i + 1) * 0.1 + (i + 1) * 0.01

[Kostadin]

Hello Kurt,

You are correct as the code OnItemDataBound event handler will be executed only for the first item in the grid. In your case both of the values of the first item are with only one digits before the decimal separator and in this case the aggregate will be sum. If you want to perform this check based on all items int the grid you have to save the largest number and set the aggregate based on it.

Regards,
Kostadin
Telerik