Changing Fonts and Colors on a "Dynamic" Table based on value

[Scott]

I've created a "Dynamic" Table at run time based on a DataTable populated via code.  The DataTable is binding to the Table in the report in the ItemDataBinding Sub.

Private Sub tblSolution1_ItemDataBinding(ByVal sender As Object, ByVal e As System.EventArgs) Handles tblSolution1.ItemDataBinding
        Try
            Dim ds As New DataSet
            Dim dc As DataColumn
            Dim newDT As New DataTable
            'Changes datatable layout
            newDT = RestructureDataTable(MyDatatable)
            ds.Tables.Add(newDT)
            Dim processingTable As Telerik.Reporting.Processing.Table = sender

            Dim textboxGroup As Telerik.Reporting.TextBox
            Dim textBoxTable As Telerik.Reporting.TextBox
            Dim I As Integer
            I = 0

            tblSolution1.ColumnGroups.Clear()
            tblSolution1.Body.Columns.Clear()
            tblSolution1.Body.Rows.Clear()

            Dim tableGroupColumn As Telerik.Reporting.TableGroup
            Dim tableGroupRow As Telerik.Reporting.TableGroup

            For Each dc In ds.Tables(0).Columns
                tableGroupColumn = New Telerik.Reporting.TableGroup
                tblSolution1.Body.Columns.Add(New Telerik.Reporting.TableBodyColumn(Unit.Inch(0.5)))

                textboxGroup = New Telerik.Reporting.TextBox
                textboxGroup.Value = dc.ColumnName.ToString
                textboxGroup.Size = New SizeU(Unit.Inch(1.2), Unit.Inch(0.3))
                tableGroupColumn.ReportItem = textboxGroup
                tblSolution1.ColumnGroups.Add(tableGroupColumn)

                textBoxTable = New Telerik.Reporting.TextBox
                textBoxTable.Value = "=Fields." + dc.ColumnName
                textBoxTable.Style.BorderStyle.Default = Telerik.Reporting.Drawing.BorderType.Solid
                textBoxTable.Style.BorderWidth.Default = Unit.Pixel(1)

                tblSolution1.Body.SetCellContent(0, I, textBoxTable)
                I = I + 1
            Next
            tableGroupRow = New Telerik.Reporting.TableGroup
            tblSolution1.RowGroups.Add(tableGroupRow)
            tableGroupRow.Grouping.Add(New Telerik.Reporting.Data.Grouping)
            processingTable.DataSource = ds.Tables(0)

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

I want to change the font and color of the text in the Table based on the value of the cell.  Such as if the value is less than 50 the text will be green above 50 text will be red. 

Please advise on how I might accomplish this?

[Hrisi]

Hi Scott,

First you should create the correct Table definition. In your snippet all TextBox instances should also be added to the Table.Items collection (see the code snipet in How-To: Add groups to Table item and Crosstab item).

Conditional formatting can be added for every TextBox you want. Use the Report Designer to define the conditional formatting rule and see what code is generated in the code behind.

Best wishes,
Hrisi
the Telerik team

[Scott]

I'm still having difficulties  getting the conditional formatting to work.  Below is my code is there something I'm not doing correctly?

Private Sub tblSolution1_ItemDataBinding(ByVal sender As Object, ByVal e As System.EventArgs) Handles tblSolution1.ItemDataBinding
        Try
            Dim ds As New DataSet
            Dim dc As DataColumn
            Dim I As Integer
            Dim newDT As New DataTable
            newDT = RestructureDataTable(MyDatatable)
            ds.Tables.Add(newDT)
            Dim processingTable As Telerik.Reporting.Processing.Table = sender

            Dim textboxGroup As Telerik.Reporting.TextBox
            Dim textBoxTable As Telerik.Reporting.TextBox
            I = 0

            Dim FormattingRule1 As Telerik.Reporting.Drawing.FormattingRule = New Telerik.Reporting.Drawing.FormattingRule
            Dim FormattingRule2 As Telerik.Reporting.Drawing.FormattingRule = New Telerik.Reporting.Drawing.FormattingRule
            Dim FormattingRule3 As Telerik.Reporting.Drawing.FormattingRule = New Telerik.Reporting.Drawing.FormattingRule
            Dim FormattingRule4 As Telerik.Reporting.Drawing.FormattingRule = New Telerik.Reporting.Drawing.FormattingRule

            tblSolution1.ColumnGroups.Clear()
            tblSolution1.Body.Columns.Clear()
            tblSolution1.Body.Rows.Clear()

            Dim tableGroupColumn As Telerik.Reporting.TableGroup
            Dim tableGroupRow As Telerik.Reporting.TableGroup

            tableGroupRow = New Telerik.Reporting.TableGroup

            For Each dc In ds.Tables(0).Columns
                tableGroupColumn = New Telerik.Reporting.TableGroup
                tblSolution1.Body.Columns.Add(New Telerik.Reporting.TableBodyColumn(Unit.Inch(0.5)))

                textboxGroup = New Telerik.Reporting.TextBox
                textboxGroup.Value = dc.ColumnName.ToString
                textboxGroup.Size = New SizeU(Unit.Inch(1.2), Unit.Inch(0.3))
                tableGroupColumn.ReportItem = textboxGroup
                tblSolution1.ColumnGroups.Add(tableGroupColumn)

                textBoxTable = New Telerik.Reporting.TextBox
                textBoxTable.Value = "=Fields." + dc.ColumnName
                textBoxTable.Style.BorderStyle.Default = Telerik.Reporting.Drawing.BorderType.Solid
                textBoxTable.Style.BorderWidth.Default = Unit.Pixel(1)

                FormattingRule1.Filters.AddRange(New Telerik.Reporting.Data.Filter() {New Telerik.Reporting.Data.Filter("=Fields." + dc.ColumnName, Telerik.Reporting.Data.FilterOperator.GreaterOrEqual, "95")})
                FormattingRule1.Style.BackgroundColor = Color.LightGreen
                FormattingRule2.Filters.AddRange(New Telerik.Reporting.Data.Filter() {New Telerik.Reporting.Data.Filter("=Fields." + dc.ColumnName, Telerik.Reporting.Data.FilterOperator.GreaterOrEqual, "90")})
                FormattingRule2.Style.BackgroundColor = Color.Yellow
                FormattingRule3.Filters.AddRange(New Telerik.Reporting.Data.Filter() {New Telerik.Reporting.Data.Filter("=Fields." + dc.ColumnName, Telerik.Reporting.Data.FilterOperator.GreaterOrEqual, "1")})
                FormattingRule3.Style.BackgroundColor = Color.Orange
                FormattingRule4.Filters.AddRange(New Telerik.Reporting.Data.Filter() {New Telerik.Reporting.Data.Filter("=Fields." + dc.ColumnName, Telerik.Reporting.Data.FilterOperator.LessThan, "1")})
                FormattingRule4.Style.BackgroundColor = Color.Red

                textBoxTable.ConditionalFormatting.AddRange(New Telerik.Reporting.Drawing.FormattingRule() {FormattingRule1, FormattingRule2, FormattingRule3, FormattingRule4})

                tblSolution1.Body.SetCellContent(0, I, textBoxTable)
                tableGroupRow.ReportItem = textBoxTable
                I = I + 1
            Next
            tblSolution1.RowGroups.Add(tableGroupRow)
            tableGroupRow.Grouping.Add(New Telerik.Reporting.Data.Grouping)
            processingTable.DataSource = ds.Tables(0)

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

When the report is displayed its still has the default background color and also default text color of the table which is displayed in the attached jpg.

[Scott]

Any thing?

[Hrisi]

Hi Scott,

In your filter expressions you should use expressions and left and right hand side should have the same type. Unfortunately in your code you pass strings as right hand side expression. For example "95" is not an expression and its value is a string. You should use equal sign prior the number, so the expression would look like "=95".

Kind regards,
Hrisi
the Telerik team

[Scott]

Adding in a = in front of the 95 like you suggested resulted in the following error:

Cannot perform '>=' operation on system.string and system.int32.

Edited I had one column that also contained a string in the grid.  I changed the code to reflect the following:

If dc.DataType.Name = "Double" Then
                    FormattingRule1.Filters.AddRange(New Telerik.Reporting.Data.Filter() {New Telerik.Reporting.Data.Filter("=Fields." + dc.ColumnName, Telerik.Reporting.Data.FilterOperator.GreaterOrEqual, "=95")})
                    FormattingRule1.Style.BackgroundColor = Color.LightGreen

                    FormattingRule2.Filters.AddRange(New Telerik.Reporting.Data.Filter() {New Telerik.Reporting.Data.Filter("=Fields." + dc.ColumnName, Telerik.Reporting.Data.FilterOperator.GreaterOrEqual, "=90")})
                    FormattingRule2.Style.BackgroundColor = Color.Yellow

                    FormattingRule3.Filters.AddRange(New Telerik.Reporting.Data.Filter() {New Telerik.Reporting.Data.Filter("=Fields." + dc.ColumnName, Telerik.Reporting.Data.FilterOperator.GreaterOrEqual, "=1")})
                    FormattingRule3.Style.BackgroundColor = Color.Orange

                    FormattingRule4.Filters.AddRange(New Telerik.Reporting.Data.Filter() {New Telerik.Reporting.Data.Filter("=Fields." + dc.ColumnName, Telerik.Reporting.Data.FilterOperator.LessThan, "=1")})
                    FormattingRule4.Style.BackgroundColor = Color.Red

                    textBoxTable.ConditionalFormatting.AddRange(New Telerik.Reporting.Drawing.FormattingRule() {FormattingRule1, FormattingRule2, FormattingRule3, FormattingRule4})
                End If

When making this change its not doing the conditional formatting based on the seperate text boxes, but rather if one of the criteria is meet then that row will be changed.  Its not taking into account all of the formatting rules.

The only rule that is working is the one that meets the red criteria.

[Scott]

It appears that it only does the formatting if all the values in the row meet the conditioning criteria.  I want each text box to be its own individual criteria where a row may have a red box, a green box, Yellow Box, and Orange box.  Ect.

See the attached image of the current results.

[Hrisi]

Hi Scott,

1. " It appears that it only does the formatting if all the values in the row meet the conditioning criteria..."
   You can use FormattingRule.StopIfTrue to brake this.
2. "I want each text box to be its own individual criteria where a row may have a red box, a green box, Yellow Box, and Orange box.  Ect."
   Each Report item (including TextBox) has it's own ConditionalFormatting collection and it is up to you to use right formatting rule for each textbox.
   

And finally I want to remind you of my suggestion in the first reply: "Use the Report Designer to define the conditional formatting rule and see what code is generated in the code behind"

Sincerely yours,

Hrisi
the Telerik team

[Scott]

Hi Hrisi,

  I did use your suggestion in the beginning to figure out how multiple formatting rules can be associated to a text box.  As show in my code.  When adding the ConditionalFormatting range it doesn't seem to be catching on all values.  Only one row will highlight red where all the criteria is added meets that condition for the row.  I've added StopIfTrue = True to each formatting rule I've used.  As shown in my screen shot on my earlier post only one row is catching and its not doing the individual boxes.

As I loop through the code and create a new TextBox I add the four formatting rules to that text box, and then place that textbox into the Table.Body.SetCellContent field.

I'm still struggling to figure out why its only working for the one row that meets the singular criteria to make it red, and the other textboxes where the row's have mixed values and should be displayed with mixed coloring does not use the conditional formatting.

-Scott

[Scott]

I figured it out.  Here is the corrected Code.

Try
            Dim ds As New DataSet
            Dim dc As DataColumn
            Dim I As Integer
            Dim newDT As New DataTable
            newDT = RestructureDataTable(MyDataTable.copy)
            ds.Tables.Add(newDT.Copy)
            Dim processingTable As Telerik.Reporting.Processing.Table = sender

            Dim textboxGroup As Telerik.Reporting.TextBox
            Dim textBoxTable As Telerik.Reporting.TextBox
            I = 0

            tblSolution1.ColumnGroups.Clear()
            tblSolution1.Body.Columns.Clear()
            tblSolution1.Body.Rows.Clear()

            Dim tableGroupColumn As Telerik.Reporting.TableGroup
            Dim tableGroupRow As Telerik.Reporting.TableGroup

            tableGroupRow = New Telerik.Reporting.TableGroup

            For Each dc In ds.Tables(0).Columns
                tableGroupColumn = New Telerik.Reporting.TableGroup
                tblSolution1.Body.Columns.Add(New Telerik.Reporting.TableBodyColumn(Unit.Inch(0.5)))

                Dim FormattingRule1 As Telerik.Reporting.Drawing.FormattingRule = New Telerik.Reporting.Drawing.FormattingRule
                Dim FormattingRule2 As Telerik.Reporting.Drawing.FormattingRule = New Telerik.Reporting.Drawing.FormattingRule
                Dim FormattingRule3 As Telerik.Reporting.Drawing.FormattingRule = New Telerik.Reporting.Drawing.FormattingRule
                Dim FormattingRule4 As Telerik.Reporting.Drawing.FormattingRule = New Telerik.Reporting.Drawing.FormattingRule

                'Filter for Rule1
                FormattingRule1.Filters.AddRange(New Telerik.Reporting.Data.Filter() {New Telerik.Reporting.Data.Filter("=Fields." + dc.ColumnName, Telerik.Reporting.Data.FilterOperator.GreaterOrEqual, "=95")})
                FormattingRule1.Style.BackgroundColor = Color.LightGreen
                FormattingRule1.StopIfTrue = True

                'Filter for Rule2
                FormattingRule2.Filters.AddRange(New Telerik.Reporting.Data.Filter() {New Telerik.Reporting.Data.Filter("=Fields." + dc.ColumnName, Telerik.Reporting.Data.FilterOperator.GreaterOrEqual, "=90")})
                FormattingRule2.Style.BackgroundColor = Color.Yellow
                FormattingRule2.StopIfTrue = True

                'Filter for Rule3
                FormattingRule3.Filters.AddRange(New Telerik.Reporting.Data.Filter() {New Telerik.Reporting.Data.Filter("=Fields." + dc.ColumnName, Telerik.Reporting.Data.FilterOperator.GreaterOrEqual, "=1")})
                FormattingRule3.Style.BackgroundColor = Color.Orange
                FormattingRule3.StopIfTrue = True

                'Filter for Rule
                FormattingRule4.Filters.AddRange(New Telerik.Reporting.Data.Filter() {New Telerik.Reporting.Data.Filter("=Fields." + dc.ColumnName, Telerik.Reporting.Data.FilterOperator.LessThan, "=1")})
                FormattingRule4.Style.BackgroundColor = Color.Red
                FormattingRule4.StopIfTrue = True

                textboxGroup = New Telerik.Reporting.TextBox
                textboxGroup.Value = dc.ColumnName.ToString
                textboxGroup.Size = New SizeU(Unit.Inch(1.2), Unit.Inch(0.3))
                tableGroupColumn.ReportItem = textboxGroup
                tblSolution1.ColumnGroups.Add(tableGroupColumn)

                textBoxTable = New Telerik.Reporting.TextBox
                textBoxTable.Value = "=Fields." + dc.ColumnName
                textBoxTable.Style.BorderStyle.Default = Telerik.Reporting.Drawing.BorderType.Solid
                textBoxTable.Style.BorderWidth.Default = Unit.Pixel(1)

                If dc.DataType.Name = "Double" Then
                    textBoxTable.ConditionalFormatting.AddRange(New Telerik.Reporting.Drawing.FormattingRule() {FormattingRule1, FormattingRule2, FormattingRule3, FormattingRule4})
                End If

                tblSolution1.Body.SetCellContent(0, I, textBoxTable)
                I = I + 1
            Next
            processingTable.DataSource = ds.Tables(0)


        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

Thanks,
-Scott