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

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?

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

1 Like

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.

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…

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.

image

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

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

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 “”.

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:

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

Did that work?

No that did not work.

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.

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

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__’.

image

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

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

Preview result:
image

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)

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.

1 Like

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.