Change specific row background on Perspective Table based upon int value in dataset

Greetings. I have a sql query that is returning performance values that I have linked to a Perspective Table. I’m trying to apply conditional formatting on a column that is hidden on the table but in the dataset called “Perf Int”. On the property I’ve set the following Expression:

if({[0][‘Perf Int’]}>=95,’#CCFFCC’,’#FFCCCC’)

This is changing the background color to green which is the first value, but when it falls below 95 it remains green. I’m sure that I need to loop it through the table, and I believe I’m just calling the column heading but I’m not sure how to bridge the gap between what I’ve got and where I need it to be at. Can anyone assist?


Thank you all in advance!

I think what’s happening with your expression is that your evaluating the first row of the dataset; in your screenshot its >=95 therefore all rows will be green.

You can achieve what you want by reading this post:

1 Like

Hey man I appreciate the reply. Unfortunately I’m still learning my way through all of this. Should I attach this as a scripting transform on the data itself, or should I use this as an expression on the row color background binding?


The end result will include a value and style object;

You’re basically iterating the dataset, keeping all original values, and appending a style object as well for each row.
Hope this helps.

1 Like

I appreciate the help but I told you I’m very green at this and learning. I’m getting a script error on line 5 and as far as I can tell my indents are correct?

def transform(self, value, quality, timestamp):
Transform the incoming value and return a result.

	self: A reference to the component this binding is configured on.
	value: The incoming value from the binding or the previous transform.
	quality: The quality code of the incoming value.
	timestamp: The timestamp of the incoming value as a java.util.Date
output_json = []
style_red = {"backgroundColor": "#FFCCCC"}
style_green = {"backgroundColor": "#CCFFCC"}
for row in range(value.getRowCount()):
    row_object = {}
    row_value = {}
    row_style = {}
    for col in range(value.getColumnCount()):    
        row_value[value.getColumnName(col)] = value.getValueAt(row, col)
        row_object['value'] = row_value
        if value.getColumnName(col) == 'Perf Int':
            if value.getValueAt(row, col) >= '95':
                row_style = style_red
            elif value.getValueAt(row, col) <= '94':
                row_style = style_green

        row_object['style'] = row_style
return value

Sounds like value is not a dataset perhaps? What does your data bind look like?

1 Like

You’ll want to return output_json and not value.

1 Like

This is how I’ve currently got it setup, without a transform, when I add the script transform is when I get the error

Try changing your Return Format to dataset.

1 Like

Set as dataset, it returns the values fine with no errors, but it also doesn’t apply the color with the script transform haha. I told you man I’m green!

In your named query your casting Perf Int as INTEGER.

if value.getValueAt(row, col) >= '95':

You’re comparing an integer to a string. Drop the quotes and you should be good.

1 Like

When I drop the quotes, I still get the following script error set on JSON return, and when I put to dataset it goes through without the background changing still:

Your return is still incorrect.

1 Like

I didn’t see that, my fault there. Even with change still got same script error of array.array object has no attribute “getRowCount”

That’s because you changed the return format from dataset.

1 Like

Bingo, got it my man, thank you for the help. I had changed back to JSON return, that was my issue at the end. Return dataset, and then return json on the script transform!

Thank you so much for the help my friend!

1 Like