Table Conditional Formating

Ignition
1

Hello,

Hoping this will be a quick answer. But, I’m building a report and want to highlight certain rows based on the Time/Date. But, I still need to see All of the data. Think of it like highlighting Values in a peak period. The formatting is based 1. on the day of the week and 2. on the time of day.

For example: If I wanted to highlight rows on “Wednesday” after 10, but the table looks like the following.

image

image

image
image580×620 19.8 KB

I’m sure I’m referencing my time of day value wrong, but I’m also unsure if my nested condition is correct either. It must be evaluating as “True” because all of the cells are highlighted.

Thank you in advance!!

2

You can't compare a date against a string and an integer. You'll need to create separate parameters that store the day of the week string and hour integer for comparison.

image
image454×186 13.3 KB

image
image479×174 12.5 KB

3

my “t_stamp” value is coming from my Historical Tag Query, so it’s not a report parameter. Does that change things?

4

Yes, you can add a script Data Source:

def updateData(data, sample):
	data['dayOfWeek'] = system.date.format(data['t_stamp'], 'EEEEE')
	data['hour'] = system.date.format(data['t_stamp'], 'HH')

If it's nested under a query data object, it might be something like:
data['queryData']['t_stamp'].

5

I think I’m missing something….

My Tag Historian Query

image
image280×325 9.5 KB

I’m assuming I need to replace the “data” in the system.date.format() to my Historian data source? So it would look like data['dayOfWeek'] = system.date.format(Total_demand_kW['t_stamp'], "EEEEE").

But it doesn’t recognize Total_demand_kW that as a global name

Leaving it as “data” gives me an updateDataKeyError for the t_stamp

6

Try:
system.date.format(data['Total_Demand_kW']['t_stamp'], 'EEEE')

Whatever you see under "Data Sources" in the Key Browser should be available as a value within the data dict.

image
image150×148 5.03 KB

7

Uhm, you have to iterate through your query result and construct a new one.....

data['Total_Demand_kW'] will be a QueryResult object, which contains a dataset.

8

Oh, right. Have to add the hour and day of week as new columns to that dataset...

9

Lots of discussions with QueryResults. Look for the ones with system.dataset.toDataSet() and/or DatasetBuilder().

10

As a tangent, I'd probably add a row to the dataset that defined whatever condition(s) you want to express as formatting.

That is - add a column that contains either null or "OutOfRange" or whatever, based on your full business logic (expressed in Python code within your script datasource). Then give the report row 'alternate' versions based on that column, and only deal with the presentation within those alternate rows.

You'll save yourself a lot of grief down the road if the business logic ever has to change and you don't have to click between a hundred table rows and cells trying to find all the key expressions you set up.