[Bug-12673] Perspective XY Chart Datasource Update Question

[Phil_B]

I am working with / learning the XY chart component. i have set it up to be a bar chart and bound the datasource to a dataset and it’s working for the most part. The data i am using is a tag history query with an hour interval and returning the Sum of the tag for the hour. the chart shows a rolling 24 hour period.

My problem occurs when the current hour changes. the bar chart will update all of the rows but doesn’t update the order of the bars to match the rows of the dataset returned with the history until i reload the view or webpage in the browser.

So my question is, is there a way to force the chart to re-render itself when the datasource value changes?

Thanks,

[Steve_Laubach]

Can you point me to documentation on the XY chart with how to set it up with historical tag charting? I’m looking in the help but looking for an example.

[osolorzano]

@Phil_B

My problem occurs when the current hour changes. the bar chart will update all of the rows but doesn’t update the order of the bars to match the rows of the dataset returned with the history until i reload the view or webpage in the browser.

I am having a hard time understanding what you meant by this, especially without seeing how your binding and XY chart are set up. If you don't mind, can you post or PM me a screenshot of your Tag History binding and your XY chart JSON? From the sound of your issue alone, it sounds more like a bug than a problem on your end, but I'd like to see it to make sure.

[Phil_B]

the data that is used on the bar chart reflected in the table seen below it. As for my binding i use setup a custom property with the expression now(60000) and a transform that uses the system.tag.querytaghistory to get my data i use a 1 hour interval looking at the last 24 hours in total.

to try to explain the issue better, the first bar on the chart is 13 which is right since it is noon and i am looking at the past 24 hours of data but when the time would change to 1:00 the first bar will remain 13. instead it should change to 14 since that will now be the first row of the dataset used to drive the chart

[Phil_B]

I figured it out by looking at a couple forums. what i did was drag a table onto the view and then in the datasource prop, there is “example”. bind your history dataset to that. then in the series prop find the data property and change the values inside it to match the rows of the dataset you bound to “example”

[osolorzano]

The documentation for this area is not completed. Even the UI for tag history was just implemented a few days ago. Here’s a simple rundown to at least get something on screen. It’s pretty much what Phil said, but with some pictures to help.

1. Bind to the “example” data source. It should look something like this if binding to only one tag’s history. After you do this, your XY chart should look mostly blank because it is not configured to handle the data coming in.
   

   

2. Change the XY chart’s props.series[0].data.y object so that it matches the alias you provided in the tag history binding.
   

   

At this point, your historical data should be showing. After this, I would suggest mostly exploring around the options in the XY chart to make it look like you want.

[Steve_Laubach]

Thank you.

I am working off a backup I restored that had a tag historian connection configured to a database I can’t get to from my current location. I disabled that connection since it’s faulted and made a new one pointing to a dev database at my current location.

I did some troubleshooting because things weren’t working as I expected. I discovered that tag historian didn’t make and of the tables in my database even though I’m connected as sa (MSSQL). The connection isn’t faulted in the configuration web site.

So in my case, the chart isn’t working because the historical DC stuff didn’t make it’s tables. I’m trying to figure out why that isn’t happening but haven’t found anything that looks amiss.

Can you try making a new historical data collection connection to some other database and see if it makes the tables for you? I’m trying to determine if it’s user error on my part or if I found a bug.

I’m using MSSQL Server 2014 using the sa account. The database server is not local to the Ignition server. The connection shows as valid.

[Phil_B]

I had a simliar issue a while back, where i had a good database connection but no tables were being created in it for history. alot of the stuff was being sent to store and forward quarantine. after some help from inductive i found that i needed a newer DB driver as the one i was using for PostgreSQL didnt work properly with the version of PostgreSQL i installed. maybe see is someone has posted about the driver for your case.

[Steve_Laubach]

Thank you for your screenshots (very helpful). It looks like I was doing it mostly right but I didn’t understand how to use the alias to link the data point to the series. I like that it’s simple.

My instance is still not working because the historical data collection is not creating the required tables. The original poster stated that he has had this problem in the past and it was fixed by updating his database drivers.

I found these instructions for downloading the latest database drivers
https://docs.inductiveautomation.com/display/DOC80/JDBC+Drivers+and+Translators

I ran the jar for Java 8 and it looks like I still don’t have my tables but maybe I have to reset something to make it happen. It’s also possible our security software in the office is blocking installation of the drivers. I tried to do a Java update and that definitely got blocked (the installer threw an angry error). The IT people I need to go through to look into that aren’t in the office so I can’t test that possibility.

Anyway, I wanted to include some of those details in case they are helpful to others.

[osolorzano]

I see what you are saying. I’ve tried to replicate using an expression binding that runs every minute, with an interval of an hour showing the sum of each 10 minute intervals. However, at every 10 minutes it properly moves the XY Chart over. Can you supply a screenshot of your expression binding and script?

[Phil_B]

I will get that for you right now. in the meantime i got this before and after from the previous hour change. i converted the bar chart to a line graph to better visualize it. You can see on the top image the line goes back across the chart. i hit reload on my web browser to grab the lower image. they are the same component on the same view.

[Phil_B]

so the binding is on the “example” prop and is set to and expression of “now(60000)” then i use a script transform to get the dataset i want. it has a lot of other columns that aren’t used in the graph because the same dataset is used with other components.

	basePath = self.custom.TagPath#system.tag.read("[default]LineTest/LineMori61/TagPath").value
	endTime = system.date.midnight(value)
	endTime = system.date.addHours(endTime,system.date.getHour24(value))
	startTime = system.date.addDays(endTime,-1)
	DBInfo = system.tag.queryTagHistory(paths=[basePath+"/dischargeCount",basePath+"/spoilageCount"], startDate=startTime, endDate=endTime, aggregationMode="Sum", returnFormat='Wide', columnNames=["t_stamp","Production","Spoilage"], intervalHours=1)
	FaultInfo = system.tag.queryTagHistory(paths=[basePath+"/fault"], startDate=startTime, endDate=endTime, aggregationMode="DurationOn", returnFormat='Wide', columnNames=["t_stamp","Fault"], intervalHours=1)
	RatedInfo = system.tag.queryTagHistory(paths=[basePath+"/rated_speed"], startDate=startTime, endDate=endTime, aggregationMode="Average", returnFormat='Wide', columnNames=["t_stamp","Rated"], intervalHours=1)


	hours = []
	faultDuration = []
	ratedSpeed = []
	for row in range(DBInfo.rowCount):
		hour = system.date.getHour24(DBInfo.getValueAt(row,"t_stamp"))
		hours.append(hour)
		fault = FaultInfo.getValueAt(row,"Fault")
		if fault == None:
			fault = 0
		faultDuration.append(fault)
		rated = RatedInfo.getValueAt(row,"Rated")
		if rated == None:
			rated = 0
		ratedSpeed.append(rated)
		
	DBInfo = system.dataset.addColumn(DBInfo,1,hours,"Hour",int)
	DBInfo = system.dataset.addColumn(DBInfo,4,faultDuration,"Faulted",int)	
	DBInfo = system.dataset.addColumn(DBInfo,5,ratedSpeed,"Rated Speed",float)	
	DBInfo = system.dataset.deleteRow(DBInfo,DBInfo.rowCount-1)
	
	target = []
	OEE_Q = []
	OEE_A = []
	OEE_P = []
	OEE = []
	for row in range(DBInfo.rowCount):
		OEE_Temp = 1.00
		hour = DBInfo.getValueAt(row,"Hour")
		rated = DBInfo.getValueAt(row,"Rated Speed")
		production = DBInfo.getValueAt(row,"Production")
		spoilage = DBInfo.getValueAt(row,"Spoilage")
		fault = DBInfo.getValueAt(row,"Faulted")
		target.append(3600.00/rated)
		if production == 0:
			OEE_Q.append(1.00)
			OEE_Temp = OEE_Temp*1.00
		else:
			OEE_Q.append(round(1.00 - spoilage/(production + spoilage),2))
			OEE_Temp = OEE_Temp*round(1.00 - spoilage/(production + spoilage),2)
		OEE_A.append(round((3600.00-fault)/3600.00,2))
		OEE_Temp = OEE_Temp*round((3600.00-fault)/3600.00,2)
		if target == 0:
			OEE_P.append(1.00)
			OEE_Temp = OEE_Temp*1.00			
		else:
			OEE_P.append(round(production/(3600.00/rated),2))
			OEE_Temp = OEE_Temp*round(production/(3600.00/rated),2)
		OEE.append(OEE_Temp)
	
	DBInfo = system.dataset.addColumn(DBInfo,6,target,"Target",float)	
	DBInfo = system.dataset.addColumn(DBInfo,7,OEE_Q,"Quality",float)	
	DBInfo = system.dataset.addColumn(DBInfo,8,OEE_A,"Availability",float)	
	DBInfo = system.dataset.addColumn(DBInfo,9,OEE_P,"Performance",float)	
	DBInfo = system.dataset.addColumn(DBInfo,10,OEE,"OEE",float)	
	
	return DBInfo

the reason i am using scripting to get tag history is because the tag path i am pulling isn’t in the tag browser and the history binding setting seemed to not like that.

[osolorzano]

I think I am currently running into the issue you are seeing, but I had to make a few assumptions about how your chart is set up based on your script. I noticed your dataset is adding a column named ‘Hour’ where you are taking the hour out of the t_stamp, are you using this for the X axis of your series? And if so, are you then running in category mode to display the data? When in this situation, I noticed the categories do not re-shuffle themselves based on the order of the data coming in from the dataset when the binding updates. In your case, if you are in hour 12, and hour 13 comes rolling along, instead of creating a new 13 category at the end it re-uses the existing 13 category. Does this sound to be what is happening to you?

[Phil_B]

you are correct on all your assumptions. That sounds like exactly what is happening.

[osolorzano]

Awesome. I’ll get that written up as an internal bug, it doesn’t sound like it should work that way. Thanks for finding this and helping me understand what was going on.

[Phil_B]

Thank you for all of your help as well,

[awalker]

This issue was fixed in the build that was uploaded today (4/19). Please let us know if you continue to see this issue.