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

**URL:** <https://forum.inductiveautomation.com/t/create-a-report-that-shows-how-long-a-real-value-was-above-a-setpoint-each-hour-for-the-month/58897>\
**Category:** Ignition\
**Created:** [April 26, 2022, 9:02pm UTC](https://forum.inductiveautomation.com/t/create-a-report-that-shows-how-long-a-real-value-was-above-a-setpoint-each-hour-for-the-month/58897 "2022-04-26T21:02:45Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![steven.gyllich](https://avatars.discourse-cdn.com/v4/letter/s/bc79bd/32.png) [@steven.gyllich](https://forum.inductiveautomation.com/u/steven.gyllich)\
**Post date:** [April 26, 2022, 9:02pm UTC](https://forum.inductiveautomation.com/t/create-a-report-that-shows-how-long-a-real-value-was-above-a-setpoint-each-hour-for-the-month/58897/1 "2022-04-26T21:02:45Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [April 26, 2022, 9:09pm UTC](https://forum.inductiveautomation.com/t/create-a-report-that-shows-how-long-a-real-value-was-above-a-setpoint-each-hour-for-the-month/58897/2 "2022-04-26T21:09:56Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![steven.gyllich](https://avatars.discourse-cdn.com/v4/letter/s/bc79bd/32.png) [@steven.gyllich](https://forum.inductiveautomation.com/u/steven.gyllich)\
**Post date:** [April 26, 2022, 9:27pm UTC](https://forum.inductiveautomation.com/t/create-a-report-that-shows-how-long-a-real-value-was-above-a-setpoint-each-hour-for-the-month/58897/3 "2022-04-26T21:27:53Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [April 26, 2022, 10:47pm UTC](https://forum.inductiveautomation.com/t/create-a-report-that-shows-how-long-a-real-value-was-above-a-setpoint-each-hour-for-the-month/58897/4 "2022-04-26T22:47:41Z")

</div>

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

[https://docs.inductiveautomation.com/display/DOC81/system.tag.queryTagHistory](https://docs.inductiveautomation.com/display/DOC81/system.tag.queryTagHistory)

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

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [April 26, 2022, 11:44pm UTC](https://forum.inductiveautomation.com/t/create-a-report-that-shows-how-long-a-real-value-was-above-a-setpoint-each-hour-for-the-month/58897/5 "2022-04-26T23:44:15Z")

</div>

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

```auto
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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/0/80283811699c4b59b9f61c99c0571abbf2aa23ee.png)

---

<div class="post-metadata">

**Author:** ![josborn](https://avatars.discourse-cdn.com/v4/letter/j/4bbf92/32.png) [@josborn](https://forum.inductiveautomation.com/u/josborn)\
**Post date:** [April 26, 2022, 11:46pm UTC](https://forum.inductiveautomation.com/t/create-a-report-that-shows-how-long-a-real-value-was-above-a-setpoint-each-hour-for-the-month/58897/6 "2022-04-26T23:46:04Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![steven.gyllich](https://avatars.discourse-cdn.com/v4/letter/s/bc79bd/32.png) [@steven.gyllich](https://forum.inductiveautomation.com/u/steven.gyllich)\
**Post date:** [April 27, 2022, 4:55pm UTC](https://forum.inductiveautomation.com/t/create-a-report-that-shows-how-long-a-real-value-was-above-a-setpoint-each-hour-for-the-month/58897/7 "2022-04-27T16:55:01Z")

</div>

> [@nminchin](#):
>
> ```auto
> 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.

---

<div class="post-metadata">

**Author:** ![steven.gyllich](https://avatars.discourse-cdn.com/v4/letter/s/bc79bd/32.png) [@steven.gyllich](https://forum.inductiveautomation.com/u/steven.gyllich)\
**Post date:** [April 27, 2022, 8:59pm UTC](https://forum.inductiveautomation.com/t/create-a-report-that-shows-how-long-a-real-value-was-above-a-setpoint-each-hour-for-the-month/58897/8 "2022-04-27T20:59:01Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [April 27, 2022, 9:06pm UTC](https://forum.inductiveautomation.com/t/create-a-report-that-shows-how-long-a-real-value-was-above-a-setpoint-each-hour-for-the-month/58897/9 "2022-04-27T21:06:15Z")

</div>

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) :

 ![Screenshot_20220428-064826_Chrome](https://us1.discourse-cdn.com/inductiveautomation/original/3X/a/b/ab1c2d105e6287d6e39f98455c57b3dea2be0dfd.jpeg)

From the user manual:

 ![Screenshot_20220428-063721_Chrome](https://us1.discourse-cdn.com/inductiveautomation/original/3X/7/2/72dd8733c26f952258e5fed1f39903ee9cf16181.jpeg)

---

<div class="post-metadata">

**Author:** ![steven.gyllich](https://avatars.discourse-cdn.com/v4/letter/s/bc79bd/32.png) [@steven.gyllich](https://forum.inductiveautomation.com/u/steven.gyllich)\
**Post date:** [April 27, 2022, 9:23pm UTC](https://forum.inductiveautomation.com/t/create-a-report-that-shows-how-long-a-real-value-was-above-a-setpoint-each-hour-for-the-month/58897/10 "2022-04-27T21:23:38Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [April 27, 2022, 9:25pm UTC](https://forum.inductiveautomation.com/t/create-a-report-that-shows-how-long-a-real-value-was-above-a-setpoint-each-hour-for-the-month/58897/11 "2022-04-27T21:25:06Z")

</div>

Did that work?

---

<div class="post-metadata">

**Author:** ![steven.gyllich](https://avatars.discourse-cdn.com/v4/letter/s/bc79bd/32.png) [@steven.gyllich](https://forum.inductiveautomation.com/u/steven.gyllich)\
**Post date:** [April 27, 2022, 9:27pm UTC](https://forum.inductiveautomation.com/t/create-a-report-that-shows-how-long-a-real-value-was-above-a-setpoint-each-hour-for-the-month/58897/12 "2022-04-27T21:27:10Z")

</div>

No that did not work.

---

<div class="post-metadata">

**Author:** ![steven.gyllich](https://avatars.discourse-cdn.com/v4/letter/s/bc79bd/32.png) [@steven.gyllich](https://forum.inductiveautomation.com/u/steven.gyllich)\
**Post date:** [April 27, 2022, 9:32pm UTC](https://forum.inductiveautomation.com/t/create-a-report-that-shows-how-long-a-real-value-was-above-a-setpoint-each-hour-for-the-month/58897/13 "2022-04-27T21:32:30Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [April 27, 2022, 9:47pm UTC](https://forum.inductiveautomation.com/t/create-a-report-that-shows-how-long-a-real-value-was-above-a-setpoint-each-hour-for-the-month/58897/14 "2022-04-27T21:47:23Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![steven.gyllich](https://avatars.discourse-cdn.com/v4/letter/s/bc79bd/32.png) [@steven.gyllich](https://forum.inductiveautomation.com/u/steven.gyllich)\
**Post date:** [April 27, 2022, 10:10pm UTC](https://forum.inductiveautomation.com/t/create-a-report-that-shows-how-long-a-real-value-was-above-a-setpoint-each-hour-for-the-month/58897/15 "2022-04-27T22:10:12Z")

</div>

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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/4/0/40b7d9474878926efc56600e70de4f3582e8bc24.png)

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [April 27, 2022, 11:20pm UTC](https://forum.inductiveautomation.com/t/create-a-report-that-shows-how-long-a-real-value-was-above-a-setpoint-each-hour-for-the-month/58897/16 "2022-04-27T23:20:10Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![steven.gyllich](https://avatars.discourse-cdn.com/v4/letter/s/bc79bd/32.png) [@steven.gyllich](https://forum.inductiveautomation.com/u/steven.gyllich)\
**Post date:** [April 27, 2022, 11:59pm UTC](https://forum.inductiveautomation.com/t/create-a-report-that-shows-how-long-a-real-value-was-above-a-setpoint-each-hour-for-the-month/58897/17 "2022-04-27T23:59:54Z")

</div>

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

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/a/8/a8d54d09640ceb00020c2587e5d1f2998141f16f.png)

Preview result:  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/a/e/aecdefeeaf584daf05a3a9c68d80ccacfad39a64.png)

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [April 28, 2022, 12:03am UTC](https://forum.inductiveautomation.com/t/create-a-report-that-shows-how-long-a-real-value-was-above-a-setpoint-each-hour-for-the-month/58897/18 "2022-04-28T00:03:34Z")

</div>

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:

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

```

and this part:

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

```

---

<div class="post-metadata">

**Author:** ![steven.gyllich](https://avatars.discourse-cdn.com/v4/letter/s/bc79bd/32.png) [@steven.gyllich](https://forum.inductiveautomation.com/u/steven.gyllich)\
**Post date:** [April 28, 2022, 3:31pm UTC](https://forum.inductiveautomation.com/t/create-a-report-that-shows-how-long-a-real-value-was-above-a-setpoint-each-hour-for-the-month/58897/19 "2022-04-28T15:31:27Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [April 28, 2022, 3:34pm UTC](https://forum.inductiveautomation.com/t/create-a-report-that-shows-how-long-a-real-value-was-above-a-setpoint-each-hour-for-the-month/58897/20 "2022-04-28T15:34:57Z")

</div>

> [@steven.gyllich](#):
>
> avoid tagnames that begin with a number.

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.

[Next page](https://forum.inductiveautomation.com/t/create-a-report-that-shows-how-long-a-real-value-was-above-a-setpoint-each-hour-for-the-month/58897.md?page=2)