Pie Graph historical query

I am recording machine state number for a number of machines in the plant, 0-4 represent different machine states.
I have got this state number recorded via the Ignition Historian.
I can set up a query for a certain date range to display the machine center state over that time, with a Status Chart. This is quite nice, but not quite what i am after.

I want the data to be somehow grouped by state number, so that i can display a Pie Chart that clearly shows the percentage of time the machine spends in each state. Ideally the number of minutes as well. Something like this:

The problem is i can’t figure out how to get Ignition to ‘count’ the total minutes that the machine has been in each state, without doing some nasty work around.

I’ve had a look at all the different options for Sample Size and Return Format. For instance, for longer time periods it would be less taxing to return “On Change” values as opposed to “Natural”.
But i’m still not sure how to get a count of the number of same values for that dataset range. If i returned a sample with a 1minute interval, then i could determine the number of minutes the machine was in each state by counting the number of samples for each state number, but i’m not sure how to do that count.

I have a similar setup to you so have put together a couple of functions to do what you need, it is perhaps a nasty workaround but it works. May need further error checking, I’m going to start displaying this state pie chart myself so will update if I find any problems.

I have a simple state that is 0 - 3. I record the state number in the historical db. I then have a dataset tag containing the description of each state number.
0 Fault / Stopped
1 Running
2 Discharge High
3 Infeed Low

I have two functions, call the first to get the total minutes for each state over the desired period.

Call the second to substitute the state numbers for the descriptions.

def historyduration(tag,startdate,enddate):
	ColumnNames = ["t_stamp","code"]
	hds = system.tag.queryTagHistory(paths=[tag], startDate=startdate, endDate=enddate, columnNames=ColumnNames, aggregationMode="MinMax", returnFormat='Wide', noInterpolation=True, ignoreBadQuality = 1)
	ds = []
	dsheader = ['Code','Duration']
	#get number of seconds between each event change
	for row in range(hds.rowCount):
		#first row start time is start of query
		if row == 0:
			valtime_start = startdate
			valtime_end = hds.getValueAt(row,'t_stamp')
			valdiff = system.date.secondsBetween(valtime_start, valtime_end)
			valcode = hds.getValueAt(row,'code')
		#all other rows except last
		if (row > 0) and (row < hds.rowCount-1):
			valtime_start = hds.getValueAt(row,'t_stamp')
			valtime_end = hds.getValueAt(row+1,'t_stamp')
			valdiff = system.date.secondsBetween(valtime_start, valtime_end)
			valcode = hds.getValueAt(row,'code')
		#last row end time is end of query
		if row == hds.rowCount - 1:
			valtime_start = hds.getValueAt(row,'t_stamp')
			valtime_end = enddate
			valdiff = system.date.secondsBetween(valtime_start, valtime_end)
			valcode = hds.getValueAt(row,'code')
		#convert seconds to minutes	
		valdiff = (valdiff * 1.00) / 60 * 1.00
		ds.append([valcode,valdiff])
	ds2 = system.dataset.toDataSet(dsheader,ds)
	#sort by event code
	sds2 = system.dataset.sort(ds2,'Code')
	#lastvalue needs a value
	lastvalue = -1
	duration=0
	fds2 = []
	#group each like event together and add duration
	for row in range(sds2.rowCount):
		value = sds2.getValueAt(row,'Code')
		thisduration = sds2.getValueAt(row,'Duration')
		#if this is a new event, append last event details
		if value != lastvalue and lastvalue > -1:
			fds2.append([lastvalue,duration])
			duration = 0
		#if this is the last row add duration and append
		if row == sds2.rowCount - 1:
			duration = duration + thisduration
			fds2.append([value,duration])
		duration = duration + thisduration
		lastvalue = value
	return system.dataset.toDataSet(dsheader,fds2)
def codereplace(ds,descriptions):
	#substitute code for description in dataset
	nds = []
	ndsheader = ['State','Duration']
	for row in range(ds.rowCount):
		code = ds.getValueAt(row,'Code')
		duration = ds.getValueAt(row,'Duration')
		for row2 in range(descriptions.rowCount):
			if code == descriptions.getValueAt(row2,'Code'):
				description = descriptions.getValueAt(row2,'Description')
				nds.append([description,duration])
				break
	return system.dataset.toDataSet(ndsheader,nds)

Hope that helps.

Thanks for the reply. I’ve gone a bit of a different approach in the end, trying to use as much in-built Ignition functionality as possible.

I used a standard history query to get the data for the desired timeframe, and made sure it is interval based. This is plotted on a Status Chart to show the change over time.

Then on a property change script for the Status Chart i extracted each state as a seperate list, and counted how many were in the list. Because i am only after a percentage, it didn’t matter to me if i was counting hours or minutes.
The scripting wasn’t too nasty in the end, all i really needed was the count() method.

if event.propertyName == "data":
	# Get data from status chart
	data = event.source.data
	
	colData = []
	
	for row in range(data.rowCount):
		colData.append(data.getValueAt(row,"StateNum"))
	# end for
	
	# count states
	offCount = colData.count(0)
	availCount = colData.count(1)
	prodCount = colData.count(2)
	maintCount = colData.count(3)
	notAvailCount = colData.count(4)
	
	# Create dataset from counts
	headers = ["Label","Value"]
	rows = []
	rows.append(["Off",offCount])
	rows.append(["Available",availCount])
	rows.append(["Producing",prodCount])
	rows.append(["Maintenance",maintCount])
	rows.append(["Not Available",notAvailCount])
	
	pieData = system.dataset.toDataSet(headers,rows)
	event.source.parent.getComponent('Pie Chart').data = pieData

This data is then put into the Pie Chart, allowing the user to choose to display the results in terms of seconds, minutes or hours, depending on how long they are querying over.

1 Like