Format column with autogenerated columns

[Kurt Kluth]

How can I go about formatting a column when AutoGenerateColumns="true"?

I have a recordset returned from the database and I needed to transpose the data which I have done successfully.  Now I would need to format that rows data to either currency or percentage.  The number of columns will be dynamic but the number of rows will be static.

Attached is an image of what my data looks like I need formatted.

<telerik:RadGrid ID="grdResults" runat="server">
    <ClientSettings>
        <Scrolling AllowScroll="true" UseStaticHeaders="True" SaveScrollPosition="true"></Scrolling>
        <Selecting AllowRowSelect="true" />
        <Resizing AllowColumnResize="true" />
    </ClientSettings>
    <MasterTableView AutoGenerateColumns="true" HeaderStyle-Width="100px">
        <Columns>
            <telerik:GridTemplateColumn UniqueName="RowNumber" Visible="false">
                <ItemTemplate>
                    <asp:Label ID="lblRowNumber" runat="server" />
                </ItemTemplate>
                <ItemStyle Font-Bold="true" Width="20px" />
                <HeaderStyle Width="50px" />
            </telerik:GridTemplateColumn>
            <telerik:GridTemplateColumn UniqueName="RowText">
                <ItemTemplate>
                    <asp:Label ID="lblRowText" runat="server" />
                </ItemTemplate>
                <ItemStyle Font-Bold="true" Width="20px" />
                <HeaderStyle Width="300px" />
            </telerik:GridTemplateColumn>
        </Columns>
    </MasterTableView>
</telerik:RadGrid>

 

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    oConn.Open()
    Dim ocmd As New SqlCommand("myStoredProc", oConn)
    ocmd.CommandType = CommandType.StoredProcedure

    With ocmd.Parameters
        .Add(New SqlParameter("@my_num", 95778))
    End With

    reader = ocmd.ExecuteReader

    If reader.HasRows Then
        Dim dt As DataTable = New DataTable()

        dt.Load(reader)
        'GetTransposedTable(dt)
        grdResults.DataSource = GetTransposedTable(dt)

        grdResults.DataBind()
    End If
End Sub

Function GetTransposedTable(ByVal dtOriginal As DataTable) As DataTable
    Dim dtReflection As New DataTable("Reflection")
    For i As Integer = 0 To dtOriginal.Rows.Count - 1
        dtReflection.Columns.Add(dtOriginal.Rows(i)(0))
    Next
    Dim row As DataRow
    For j As Integer = 1 To dtOriginal.Columns.Count - 1
        row = dtReflection.NewRow
        For k As Integer = 0 To dtOriginal.Rows.Count - 1
            row(k) = dtOriginal.Rows(k)(j)
        Next
        dtReflection.Rows.Add(row)
    Next

    Return dtReflection
End Function


Private Sub grdResults_ItemDataBound(sender As Object, e As GridItemEventArgs) Handles grdResults.ItemDataBound
    'Set Row Count
    If TypeOf e.Item Is Telerik.Web.UI.GridDataItem Then
        Dim rowCounter As Integer = 0
        Dim lblRowNumber As Label = TryCast(e.Item.FindControl("lblRowNumber"), Label)
        Dim lblRowText As Label = TryCast(e.Item.FindControl("lblRowText"), Label)

        rowCounter = grdResults.MasterTableView.PageSize * grdResults.MasterTableView.CurrentPageIndex
        lblRowNumber.Text = (e.Item.ItemIndex + 1 + rowCounter).ToString()

        If lblRowNumber.Text = 1 Then
            lblRowText.Text = "Total Assets"
        ElseIf lblRowNumber.Text = 2 Then
            lblRowText.Text = "Total Shares"
        ElseIf lblRowNumber.Text = 3 Then
            lblRowText.Text = "Net Capital"
        ElseIf lblRowNumber.Text = 4 Then
            lblRowText.Text = "Total Loans"
        ElseIf lblRowNumber.Text = 5 Then
            lblRowText.Text = "Allowance for Loan Losses"
        ElseIf lblRowNumber.Text = 6 Then
            lblRowText.Text = "Total Delinquency - Delinquent Loans (>2 mo)"
        ElseIf lblRowNumber.Text = 7 Then
            lblRowText.Text = "Net Capital Ratio"
        ElseIf lblRowNumber.Text = 8 Then
            lblRowText.Text = "Delinquency Ratio"
        ElseIf lblRowNumber.Text = 9 Then
            lblRowText.Text = "Operating Expenses - Annualized Operating Exp Ratio"
        ElseIf lblRowNumber.Text = 10 Then
            lblRowText.Text = "Net Income/(Loss) - Annualized ROA"
        ElseIf lblRowNumber.Text = 11 Then
            lblRowText.Text = "Net Charge-Offs/Avg. Loans - Annualized Net C/O"
        ElseIf lblRowNumber.Text = 12 Then
            lblRowText.Text = "Coverage Ratio (ALL-to-Delq)"
        ElseIf lblRowNumber.Text = 13 Then
            lblRowText.Text = "Interest Margin"
        ElseIf lblRowNumber.Text = 14 Then
            lblRowText.Text = "Other Income"
        ElseIf lblRowNumber.Text = 15 Then
            lblRowText.Text = "Provision for Loan Losses"
        End If


    End If
End Sub

 

 

[Kurt Kluth]

Added the following logic as a work around but would like to know how to format the data on the grid side.  Also knowing how to do that would allow to align the text as well.

Any suggestions?

 

If j <= 6 Then
    row(k) = String.Format("{0:c0}", dtOriginal.Rows(k)(j))
Else
    row(k) = String.Format("{0:p3}", dtOriginal.Rows(k)(j))
End If

[Eyup]

Hello Kurt,

First of all, please make sure that you are not using DataBind() to bind the grid. Performing complex grid operations such as Inserting, Deleting, Updating, Hierarchy relations, Grouping, Exporting, Paging, Sorting, Filtering, etc. require accommodating appropriate database operations.  Therefore, we suggest you to avoid Simple Databinding and strongly recommend the use of more advanced databinding methods, which automatically handle the aforementioned functions:
Declarative DataSource
Programmatic Data Binding

As for your initial requirement, you can achieve it using the ColumnCreated event handler:
http://docs.telerik.com/devtools/aspnet-ajax/controls/grid/columns/working-with-autogenerated-columns#customizing-auto-generated-columns

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

Regards,
Eyup
Telerik

[Kurt Kluth]

I have updated my databiding for the grid as I was also using it in conjunction with HTMLChart.