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

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

9 Answers 410 Views
General Discussions
This is a migrated thread and some comments may be shown as answers.
Scott
Top achievements
Rank 2
Scott asked on 09 Sep 2010, 08:05 PM
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?

9 Answers, 1 is accepted

Sort by
0
Hrisi
Telerik team
answered on 10 Sep 2010, 02:51 PM
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
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
0
Scott
Top achievements
Rank 2
answered on 13 Sep 2010, 03:54 PM
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.
0
Scott
Top achievements
Rank 2
answered on 14 Sep 2010, 09:17 PM
Any thing?
0
Hrisi
Telerik team
answered on 17 Sep 2010, 01:16 PM
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
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
0
Scott
Top achievements
Rank 2
answered on 20 Sep 2010, 01:17 PM
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.
0
Scott
Top achievements
Rank 2
answered on 20 Sep 2010, 01:50 PM
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.
0
Hrisi
Telerik team
answered on 22 Sep 2010, 05:29 PM
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
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
0
Scott
Top achievements
Rank 2
answered on 22 Sep 2010, 06:13 PM
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

0
Scott
Top achievements
Rank 2
answered on 22 Sep 2010, 07:23 PM
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
Tags
General Discussions
Asked by
Scott
Top achievements
Rank 2
Answers by
Hrisi
Telerik team
Scott
Top achievements
Rank 2
Share this question
or