Stacked barchart binding per column

Hello all,

So first off I’m new to Ignition and haven’t done much programming in python in years; so I may be missing something simple here!

So I have table in mySQL that I am logging the states of machines to and that I can query through a stored to get how long a machine was in that particular state per day.

Example data retrieved via the store procedure is:

id  name             seconds
1   Idle             2616
2   Off              7200
3   Processing       6720
4   Starting Up      3314
5   Shutting Down    9226

(note that the seconds don’t add up to a full day as this is for so far though today…)

Simple. Binding it to a pie chart works really well and unlike alot of other platforms is really easy.

But what happens if I want to bind that same data against a stacked bar chart, each column representing a day so that for example an operations manager can view machine utilisation for the past 8 days?

Easy you think - and so did I but that thought lasted for about 30 seconds, hopefully someone else can show me the error of my ways…

So just backtracking for a moment on why the data is represented as it is and not, as would be easier to just have the first column with the date followed by one column per state with one row per day. To allow the same tables to hold data for multiple different types of machines the states that are returned aren’t necessarily the same on every machine, though they are currently artificially limited to 7 and at least with the pie chart just passing it 5 or 7 it’s happy. So for that reason I can’t just pivot the table round and all’s done.

So to over come this I thought thats no issue at all; drag a chart in and bind indervidually against each of the 8 data sets under the example data. The stored procedure gets called 8 times but thats fine as its quite an efficient query with little data that wont be, in the greater scheme of things be called often. What I’m stuck with is how I do that; I suspect it comes down to not having enough experiance with Ignition and my python skills being very rusty (and thats being super kind about them…).

The image below ilistrates what I’m trying (and failing) to do:

Now I know I can’t simply bind the same data because it would bind an array of 7 objects as such:

So the question is can someone help me with how I would reformat the data using a script transform; (as I assume thats what I need for this?!) I have spent a while trying to figure it out myself and I’ll be honest I have struggled… Even a top level overview on how you lot would do it with some pointers would be great as i can give it a go from there…

Thanks in advance!

1 Like

Are you able to edit your stored procedure? If so I would change this to take a start and end date, then group the data by day and return multiple lines of data along with the date.

Yes I am able to edit the stored procedure.

I assume you mean do something like as follows:

date          idle      off     processing     starting up    shutting down   
2020-04-24    2616      7200    6720           3314           9226
2020-04-23    2616      7200    6720           3314           9226
2020-04-22    2616      7200    6720           3314           9226
...

Unfortunatly I can’t do that as for some assets the states are different such as for a different machine I may have only 3 states, or 5 states but with some of them being different. As far as I know there is no way to pivot with different headings without hard coding the procedure for each of the posiable states. I should maybe clarify that there is a second table with a list of states that is looked up to convert the interger state to the text of the state. So in effect there could be an infinate number of different states dependant on the differnt types of machine.

So maybe ‘can’t’ is the wrong word, but ‘strongly wanting to avoid’ maybe better…

I store all my equipment state changes in a single table, with the states stored directly as plain text.

This allows me to query the following to get the utilization, no matter how many modes are used for a certain piece of equipment:

SELECT
	mode as Mode,
	SecondsElapsed,
	ROUND(SecondsElapsed * 100 / SUM(SecondsElapsed) OVER (), 1) as Percent,
	TIME_FORMAT(SEC_TO_TIME(SecondsElapsed),'%Hh %im') as TimeStringShort,
	seconds_to_string(SecondsElapsed) as TimeStringLong
	
FROM	(
	SELECT 
		mode,
		SUM(TIMESTAMPDIFF(SECOND, startDate, endDate)) as SecondsElapsed
	
	FROM	(
		#'Select all events which take place during the time period'
		(SELECT
			mode,
			event_start as startDate,
			event_end as endDate
		FROM 
			process_unit_history
		WHERE
			process_unit_id = :process_unit_id
			AND event_start > :begin
			AND event_end < :end
		)

		UNION

		#'Select all events which started during the time period, but havent ended yet'
		(SELECT
			mode,
			IF(NOW() > event_start, event_start, NOW()) as startDate,
			IF(NOW() > :end, :end, NOW()) as endDate
		FROM 
			process_unit_history
		WHERE
			process_unit_id = :process_unit_id
			AND event_start > :begin
			AND event_start < :end
			AND event_end is Null
		)

		UNION
		
		#'Select all events which started earlier than the time period, but ended during the time period.'
		(SELECT
			mode,
			:begin as startDate,
			event_end as endDate
		FROM 
			process_unit_history
		WHERE
			process_unit_id = :process_unit_id
			AND event_start < :begin
			AND event_end < :end
			AND event_end > :begin
		LIMIT 1
		)

		UNION

		#'Select all events which started earlier than the time period, but ended after the time period.'
		(SELECT
			mode,
			:begin as startDate,
			:end as endDate
		FROM 
			process_unit_history
		WHERE
			process_unit_id = :process_unit_id
			AND event_start < :begin
			AND event_end > :end
		LIMIT 1
		)

		UNION

		#'Select all events which started earlier than the time period, and havent ended yet.'
		(SELECT
			mode,
			IF(NOW() > :begin, :begin, NOW()) as startDate,
			IF(NOW() > :end, :end, NOW()) as endDate
		FROM 
			process_unit_history
		WHERE
			process_unit_id = :process_unit_id
			AND event_start < :begin
			AND event_end is Null
		LIMIT 1
		)
	) as events

	GROUP BY mode

) as durations

GROUP BY mode

ORDER BY Percent DESC

Then I call this named query for each day that I want to display on my stacked bar chart, using a script transform to dynamically generate the dataSource and series parameters.

Thats pretty much what I’m doing :+1: Only differance being for now mines as seconds while I find out if they want to include shifts…

Can I ask how you do the script transform? Thats the bit I’m massively struggling with.

To generate XYChart.props.dataSources.history:

# Load the process unit history for the configured process unit ID.
processUnitID = self.view.params.process_unit_id
        
# Controlling parameters
dateStart = system.date.fromMillis(self.view.params.dateStart)
dateEnd = system.date.fromMillis(self.view.params.dateEnd)
dateDays = system.date.daysBetween(dateStart, dateEnd)

# For each day in the history range, load the statistics from that day
# After loading the statistics from that day, build the format required by the chart
jsonResults = []
for day in range(dateDays + 1):
    currentStartDate = system.date.addDays(dateStart, day)
    currentEndDate = system.date.addDays(dateStart, day + 1)
    
    # Named Query: "Process Units/History/Usage History Unit"
    # Parameters:	process_unit_id	(Int4)	- ID of the process unit to store history for.
    #				begin (DateTime)		- Begining time to select history.
    #				end (DateTime)			- End time to select history.            
    params = {
        'process_unit_id': processUnitID,
        'begin': currentStartDate,
        'end': currentEndDate
    }  
    query = 'Process Units/History/Usage History Unit'  
    queryResults = system.db.runNamedQuery('global', query, params)
    
    # Turn the query results in a json list for easy charting.
    # Loop through the query results, and extract all the modes and percentages.
    currentResults = {}
    for row in range(queryResults.rowCount):
        percent = queryResults.getValueAt(row, "Percent")

        # Remove all spaces from the mode name
        mode = queryResults.getValueAt(row, "Mode").replace(' ', '')
        
        # If the percentage is Null, set it to zero.
        # If the percentage is not null, divide it by 100
        if percent is None:
            percent = 0
        else:
            percent = percent/100
        currentResults[mode] = percent
        
    # After extracting all the modes, create a key that represents the date of the historical data.
    currentResults['date'] = str(system.date.getMonth(currentStartDate)+1) +  '/' + str(system.date.getDayOfMonth(currentStartDate))
        
    # Append the current results, and the repeat for the rest of the remaining days.
    jsonResults.append(currentResults)

# After looping through the days, return the resultset.	
return jsonResults

To generate XYChart.props.series:

# Load the process unit history for the configured process unit ID.
processUnitID = self.view.params.process_unit_id

# Controlling parameters
dateStart = system.date.fromMillis(self.view.params.dateStart)
dateEnd = system.date.addDays(system.date.fromMillis(self.view.params.dateEnd), 1)

# Named Query: 'Process Units/History/Usage History Unit'   
# Parameters:	process_unit_id	(Int4)	- ID of the process unit to read history for.
#				begin (DateTime)		- Begining time to select history.
#				end (DateTime)			- End time to select history.             
params = {
    'process_unit_id': processUnitID,
    'begin': dateStart,
    'end': dateEnd
}
query = 'Process Units/History/Usage History Unit'
        
# Run the named query 
queryResults = system.dataset.sort(system.db.runNamedQuery('global', query, params), "Mode")

# Turn the query results in a json list for easy charting.
# Loop through the query results, and extract all the modes and percentages.
# For each mode, create a series.
jsonResults = []
for row in range(queryResults.rowCount):

    currentSeries = {}
    
    percent = queryResults.getValueAt(row, "Percent")
    modeWS = queryResults.getValueAt(row, "Mode")
    mode = modeWS.replace(' ','')
    time = queryResults.getValueAt(row, "SecondsElapsed")
    currentSeries = self.createSeries(modeWS, modeWS, 'history', 'date', mode, 'time', 'percent')

    # Append the current results, and the repeat for the rest of the remaining days.
    jsonResults.append(currentSeries)

# After looping through the days, return the resultset.	
return jsonResults

Where self.createSeries() is a custom method on the XYChart that builds the style of series I’m looking for:

def createSeries(name, title, source, dataXAxis, dataYAxis, chartXAxis, chartYAxis):
		return  {
		  "name": name,
		  "sequencedInterpolation": False,
		  "label": {
		    "text": title
		  },
		  "visible": True,
		  "hiddenInLegend": False,
		  "defaultState": {
		    "visible": True
		  },
		  "data": {
		    "source": source,
		    "x": dataXAxis,
		    "y": dataYAxis
		  },
		  "xAxis": chartXAxis,
		  "yAxis": chartYAxis,
		  "zIndex": 0,
		  "tooltip": {
		    "enabled": True,
		    "text": "{name}: [bold]{valueY}[/]",
		    "cornerRadius": 3,
		    "pointerLength": 4,
		    "background": {
		      "color": "",
		      "opacity": 1
		    }
		  },
		  "render": "column",
		  "candlestick": {
		    "open": {
		      "x": "",
		      "y": ""
		    },
		    "high": {
		      "x": "",
		      "y": ""
		    },
		    "low": {
		      "x": "",
		      "y": ""
		    },
		    "appearance": {
		      "fill": {
		        "color": "",
		        "opacity": 1
		      },
		      "stroke": {
		        "color": "",
		        "opacity": 1,
		        "width": 1
		      },
		      "stacked": False,
		      "deriveFieldsFromData": {
		        "fill": {
		          "color": "",
		          "opacity": ""
		        },
		        "stroke": {
		          "color": "",
		          "opacity": "",
		          "width": ""
		        }
		      },
		      "heatRules": {
		        "enabled": False,
		        "max": "",
		        "min": "",
		        "dataField": ""
		      }
		    }
		  },
		  "column": {
		    "open": {
		      "x": "",
		      "y": ""
		    },
		    "appearance": {
		      "fill": {
		        "color": "",
		        "opacity": 1
		      },
		      "stroke": {
		        "color": "",
		        "opacity": 1,
		        "width": 0
		      },
		      "stacked": True,
		      "deriveFieldsFromData": {
		        "fill": {
		          "color": "",
		          "opacity": ""
		        },
		        "stroke": {
		          "color": "",
		          "opacity": "",
		          "width": ""
		        }
		      },
		      "heatRules": {
		        "enabled": False,
		        "max": "",
		        "min": "",
		        "dataField": ""
		      }
		    }
		  },
		  "line": {
		    "open": {
		      "x": "",
		      "y": ""
		    },
		    "appearance": {
		      "connect": True,
		      "tensionX": 1,
		      "tensionY": 1,
		      "minDistance": 0.5,
		      "stroke": {
		        "width": 3,
		        "opacity": 1,
		        "color": "",
		        "dashArray": ""
		      },
		      "fill": {
		        "color": "",
		        "opacity": 0
		      },
		      "bullets": [
		        {
		          "enabled": False,
		          "render": "circle",
		          "width": 10,
		          "height": 10,
		          "label": {
		            "text": "{value}",
		            "position": {
		              "dx": 0,
		              "dy": 0
		            }
		          },
		          "tooltip": {
		            "enabled": True,
		            "text": "{name}: [bold]{valueY}[/]",
		            "cornerRadius": 3,
		            "pointerLength": 4,
		            "background": {
		              "color": "",
		              "opacity": 1
		            }
		          },
		          "fill": {
		            "color": "",
		            "opacity": 1
		          },
		          "stroke": {
		            "color": "",
		            "width": 1,
		            "opacity": 1
		          },
		          "rotation": 0,
		          "deriveFieldsFromData": {
		            "fill": {
		              "color": "",
		              "opacity": ""
		            },
		            "stroke": {
		              "color": "",
		              "opacity": "",
		              "width": ""
		            },
		            "rotation": ""
		          },
		          "heatRules": {
		            "enabled": False,
		            "max": 100,
		            "min": 2,
		            "dataField": ""
		          }
		        }
		      ]
		    }
		  },
		  "stepLine": {
		    "open": {
		      "x": "",
		      "y": ""
		    },
		    "appearance": {
		      "connect": True,
		      "tensionX": 1,
		      "tensionY": 1,
		      "minDistance": 0.5,
		      "stroke": {
		        "width": 3,
		        "opacity": 1,
		        "color": "",
		        "dashArray": ""
		      },
		      "fill": {
		        "color": "",
		        "opacity": 0
		      },
		      "bullets": [
		        {
		          "enabled": True,
		          "render": "circle",
		          "width": 10,
		          "height": 10,
		          "label": {
		            "text": "{value}",
		            "position": {
		              "dx": 0,
		              "dy": 0
		            }
		          },
		          "tooltip": {
		            "enabled": True,
		            "text": "{name}: [bold]{valueY}[/]",
		            "cornerRadius": 3,
		            "pointerLength": 4,
		            "background": {
		              "color": "",
		              "opacity": 1
		            }
		          },
		          "fill": {
		            "color": "",
		            "opacity": 1
		          },
		          "stroke": {
		            "color": "",
		            "width": 1,
		            "opacity": 1
		          },
		          "rotation": 0,
		          "deriveFieldsFromData": {
		            "fill": {
		              "color": "",
		              "opacity": ""
		            },
		            "stroke": {
		              "color": "",
		              "opacity": "",
		              "width": ""
		            },
		            "rotation": ""
		          },
		          "heatRules": {
		            "enabled": False,
		            "max": 100,
		            "min": 2,
		            "dataField": ""
		          }
		        }
		      ]
		    }
		  }
		}

Here’s the JSON for the view: Usage Chart.json (21.6 KB)

@ben.d.musson

A massive thank you for this. It seems what I was missing was the binding on series (that in hind sight seems obvious!)

1 Like