Create a report that shows how long a REAL value was above a setpoint each hour for the month

steven.gyllich · April 26, 2022, 9:02pm

I have a REAL value that I am logging in the Tag Historian. I would like to know how long the REAL value was above a certain value (lets say greater than 10) each hour for the last month. What would be the best approach to getting this data into a report?

nminchin · April 26, 2022, 9:09pm

You should be able to use system.tag.queryTagHistory to get your samples, then loop over those samples, if it’s greater than your target, get the difference between that sample’s time and the next one and add it to a running total

steven.gyllich · April 26, 2022, 9:27pm

I am fairly new to Ignition so you lost me at “You should be able to use system.tag.queryTagHistory…”.
Would this function be used in a custom script in the report designer? I have not done any scripting in Ignition as of yet so sample would be useful.

nminchin · April 26, 2022, 10:47pm

No worries. Yes, this would be in a script data source in the report.

https://docs.inductiveautomation.com/display/DOC81/system.tag.queryTagHistory

It’s actually a little bit involved. Bare with me…

nminchin · April 26, 2022, 11:44pm

Ok, I think I have it. Test this in the Designer Script Console.
Note: this is sampling at a rate of 1min, averaging any samples within that time. If you want to use raw samples without interpolation, i’ve commented out two params in the queryTagHistory arguments. You’ll also then need to then calculate the difference in time between the current sample and the next sample and add that to the total instead of just adding 1 minute.

Note 2: the resulting dataset is the number of minutes that the tag value was higher than the target for each hour

from copy import copy

targetValue = 880
paths = ['path/to/tag']
endDate = system.date.now()
#endDate = system.date.setTime(endDate, 23, 59, 59)

startDate = copy(endDate)
startDate.setDate(1) # set the day to the start of the month
startDate = system.date.setTime(startDate, 0, 0, 0) # set the time to 00:00:00

intervalMinutes = 1

samples = system.tag.queryTagHistory(paths = paths,
								   startDate = startDate,
								   endDate = endDate,
								   intervalMinutes = intervalMinutes,
								   aggregationMode = 'Average',
								   includeBoundingValues = True,
								   #noInterpolation = True,
								   #returnSize = -1
								   )

samplesPDS = system.dataset.toPyDataSet(samples)
print len(samplesPDS)
# first col is always 't_stamp', the rest are the paths you supplied without the tag provider name. Easier just to use the column array
# we remove the t_stamp column to just leave the tag path column(s)
tagPaths = system.dataset.getColumnHeaders(samplesPDS)[1:]

# this will be your resultset with your hourly times above your targetValue
hourlyTotals = {}
for i, sample in enumerate(samplesPDS):
	date = sample['t_stamp']
	this_hour = system.date.getHour24(date)
	# get the sample date without minutes or seconds (set them to 0)
	date = system.date.setTime(date, this_hour, 0, 0)
	
	# create the key for the current hourly date if it doesn't exist and prepopulate the tagPaths dict
	dummy = hourlyTotals.setdefault(date, {tagPath: 0 for tagPath in tagPaths})
	
	# for each tag, check if the value for the current sample is greater than the target, if so add the interval to the total
	for tagPath in tagPaths:
		if sample[tagPath] > targetValue:
			# add the intervalMinutes to the current value
			hourlyTotals[date][tagPath] += intervalMinutes

# convert the hourlyTotals from a dictionary to a dataset for use in a report
headers = ['t_stamp']
headers.extend(tagPaths)
data = []
# create the data array in the format: [[date 1, tag1AboveTargetMins], [date 2, tag1AboveTargetMins], ...]
for date, vals in hourlyTotals.items(): # note: the hourlyTotals dictionary is UNSORTED. We will need to sort this after
	row = [date]
	for tagPath in vals:
		row.append(vals[tagPath])
	data.append(row)

# sort the array by date (first column)
data.sort(key=lambda x:x[0])

# print out the contents of the rows for testing. comment this out for your report
for d in data: print d

ds = system.dataset.toDataSet(headers, data)

E.g.

josborn · April 26, 2022, 11:46pm

Another option is to use a tag history data source and a script data source. You can use the script data source to go thorough your history data source and do calculations and such. Nick’s example is better, not posting mine =X

steven.gyllich · April 27, 2022, 4:55pm

nminchin:

from java.util import Calendar

targetValue = 880
paths = ['path/to/tag']
endDate = system.date.now()
#endDate = system.date.setTime(endDate, 23, 59, 59)

# there might be a less round-about way to do this... but, to get the date of the start of the month:
startDate = Calendar.getInstance() # create a Calendar object
startDate.setTime(endDate) # set the date to the endDate
startDate.set(Calendar.DAY_OF_MONTH, 1) # set the day to the start of the month
startDate = startDate.getTime() # convert back to a java.util.Date
startDate = system.date.setTime(startDate, 0, 0, 0) # set the time to 00:00:00

intervalMinutes = 1

samples = system.tag.queryTagHistory(paths = paths,
								   startDate = startDate,
								   endDate = endDate,
								   intervalMinutes = intervalMinutes,
								   aggregationMode = 'Average',
								   includeBoundingValues = True,
								   #noInterpolation = True,
								   #returnSize = -1
								   )

samplesPDS = system.dataset.toPyDataSet(samples)
print len(samplesPDS)
# first col is always 't_stamp', the rest are the paths you supplied without the tag provider name. Easier just to use the column array
# we remove the t_stamp column to just leave the tag path column(s)
tagPaths = system.dataset.getColumnHeaders(samplesPDS)[1:]

# this will be your resultset with your hourly times above your targetValue
hourlyTotals = {}
for i, sample in enumerate(samplesPDS):
	date = sample['t_stamp']
	this_hour = system.date.getHour24(date)
	# get the sample date without minutes or seconds (set them to 0)
	date = system.date.setTime(date, this_hour, 0, 0)
	
	# create the key for the current hourly date if it doesn't exist and prepopulate the tagPaths dict
	dummy = hourlyTotals.setdefault(date, {tagPath: 0 for tagPath in tagPaths})
	
	# for each tag, check if the value for the current sample is greater than the target, if so add the interval to the total
	for tagPath in tagPaths:
		if sample[tagPath] > targetValue:
			# add the intervalMinutes to the current value
			hourlyTotals[date][tagPath] += intervalMinutes

# convert the hourlyTotals from a dictionary to a dataset for use in a report
headers = ['t_stamp']
headers.extend(tagPaths)
data = []
# create the data array in the format: [[date 1, tag1AboveTargetMins], [date 2, tag1AboveTargetMins], ...]
for date, vals in hourlyTotals.items(): # note: the hourlyTotals dictionary is UNSORTED. We will need to sort this after
	row = [date]
	for tagPath in vals:
		row.append(vals[tagPath])
	data.append(row)

# sort the array by date (first column)
data.sort(key=lambda x:x[0])

# print out the contents of the rows for testing. comment this out for your report
for d in data: print d

ds = system.dataset.toDataSet(headers, data)

Thanks for this sample, it does exactly what I was looking for. I had no idea it was going to be that involved.

steven.gyllich · April 27, 2022, 8:59pm

So in the script console it shows me the data that I am looking for. Once I put this script into a datasource script in a report I no longer get the data. The timestamp shows the correct time/date but the data shows “”.

nminchin · April 27, 2022, 9:06pm

I think you have to add the resultant dataset to a new key in the data dictionary which is provided by the reporting updateData function.

data['your_key'] = ds

In this case, you’ll also need to change the name of the ‘data’ list throughout my script as well to something else so it doesn’t overwrite that variable provided by the reporting function

Ie change these names to something else (sorry, on phone) :

From the user manual:

steven.gyllich · April 27, 2022, 9:23pm

Yes I did all that. Changed the data variable to dataArray and passed the dataset
data[‘myData’] = ds.

nminchin · April 27, 2022, 9:25pm

Did that work?

steven.gyllich · April 27, 2022, 9:27pm

No that did not work.

steven.gyllich · April 27, 2022, 9:32pm

One think I am noticing and this may not be an issue:
My path for my tag is [’[default]30 ESP/ESP TR1/KVDC1/PV’]
In the Report designer under data sources, the key name is @ ESPESP TR1KVDC1PV__@

Would this cause an issue with reading the data from the dataset? The name starts with a space character, has removed the 30 from the start of the tag path and added a couple of underscores at the end.

nminchin · April 27, 2022, 9:47pm

Can you see any keys in the sample data in the my Data key?
I can have a look in an hour

steven.gyllich · April 27, 2022, 10:10pm

If I print the ‘headers’ in the script console I get:
[‘t_stamp’, u’30 ESP/ESP TR1/KVDC1/PV’]

I would have to assume that report designer does not like leading digits or “/” in the data source key names so that is why it truncates to ’ ESPESP TR1KVDC1PV__’.

nminchin · April 27, 2022, 11:20pm

Can you show how you’re trying to use the data in your report?

steven.gyllich · April 27, 2022, 11:59pm

Nothing too fancy at the moment. Just dragged the “myData” data source onto the report page and added the columns.

Preview result:

nminchin · April 28, 2022, 12:03am

Ah, so you are getting the timestamps which is good, but not the tag values. It could be as you said the starting numbers (generally not recommended to start any variables, including tags, with numbers) and the preceding space. You could rename those in the script.

Change this part:

targetValue = 880
paths = ['path/to/tag']
pathAliases = ['betterName'] # <---ADD THIS

and this part:

headers = ['t_stamp']
headers.extend(pathAliases) # CHANGED FROM headers.extend(tagPaths)

steven.gyllich · April 28, 2022, 3:31pm

That was it. So note to self for the next project avoid tagnames that begin with a number.
Thanks for all your help. Scripting has come along way since the basic scripting language of Wonderware Intouch back in the 90’s. Looks like I have some learning to do.

pturmel · April 28, 2022, 3:34pm

I recommend avoiding tag names that can't be used as python identifiers as-is. So no punctuation other than underscores, no spaces, no leading digits. IA can't impose such a rule at this late date, unfortunately.