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

Format column (currency vs percentage)

7 Answers 484 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Kurt Kluth
Top achievements
Rank 1
Kurt Kluth asked on 06 Dec 2013, 02:57 PM
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

7 Answers, 1 is accepted

Sort by
0
Eyup
Telerik team
answered on 11 Dec 2013, 02:40 PM
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
If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to the blog feed now.
0
Kurt Kluth
Top achievements
Rank 1
answered on 13 Dec 2013, 08:27 PM
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
0
Eyup
Telerik team
answered on 18 Dec 2013, 08:12 AM
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
If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to the blog feed now.
0
Kurt Kluth
Top achievements
Rank 1
answered on 19 Dec 2013, 08:35 PM
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
0
Eyup
Telerik team
answered on 24 Dec 2013, 02:51 PM
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
If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to the blog feed now.
0
Kurt Kluth
Top achievements
Rank 1
answered on 02 Jan 2014, 05:57 PM

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


0
Kostadin
Telerik team
answered on 07 Jan 2014, 01:29 PM
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
If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to the blog feed now.
Tags
Grid
Asked by
Kurt Kluth
Top achievements
Rank 1
Answers by
Eyup
Telerik team
Kurt Kluth
Top achievements
Rank 1
Kostadin
Telerik team
Share this question
or