Application: Leak test where PLC readings are 5 ms apart.
Issue: Tags are created for each reading due to speed. Factory SQL creates a table with each of the tags as columns. I need to graph the value for each tag (Y-axis) for each 5 ms interval where the intervals are represented by the individual tags.
Carl: Need help writing a query that will return the value for each tag in 1 column for graphing purposes.
Thanx,
Keith
Keith,
This is a great question. You have a wide, 1-row table with your datapoints, but the chart component expects the more typical format of a 1-column, many-row table.
The technique that I would recommend is to add a custom property to the root container of your window of type Dataset. Name this property WideDS
Bind this dataset to a polling SQL query that retrieves your 5ms samples, Make sure that the columns are returned in order, that is, a column like SAMPLE1 comes before SAMPLE2.
We will then write an action script for the root container’s propertyChange event. This script will loop through the columns of the WideDS, and build a new DataSet that is 2-columns wide, one column for the timestamps (5ms apart) and one column for the sample values. The script will then set the Chart’s dataset to this new dataset.
I do have one question: How are you retrieving the initial timestamp of the sample? Is this stored and retrieved in the same table? If there isn’t an urgent rush, I’d rather write this script Monday morning when I get back to the office (I’m on the road) so that I can mock up this scenario and make sure to get it right.
In the meantime, maybe you could post the exact structure of this table (i.e. the column names and types).
Thanks,
Each of the columns represents a 5ms reading (60 columns). Since these are being represented by individual tags, and since the data is being rewritten each time and not logged, the actual time stamp is the tag/column designator. The table is 60 columns (readings at 5ms intervals) with 1 row of data (value for that particular reading) that is rewritten every 2 seconds. I need to graph this data by using the tags/columns as my X-Axis and the data as the Y value for each tag/column.
I can email you a sample graph in Excel if you want to see what I am trying to achieve.
Thanx,
Keith
[/img]
Keith,
Thanks for your patience on this one. I have mocked up this situation in our office, and the technique I suggested above works great. Here is how you can implement it.
[ol]
[li]Create a new window[/li]
[li]Add 2 custom properties to the window’s Root Container, one named wideDS and one named tallDS[/li]
[li]Bind wideDS to a SQL query that selects your 5-millisecond apart data, in order, such as: SELECT SAMPLE1, SAMPLE2, SAMPLE3, SAMPLE4 FROM MyTable
Make your binding poll every 2 seconds[/li]
[li]Select the Root Container and click on the Configure Actions button. select the propertyChange -> propertyChange event. Add the following script, which will turn the wideDS on its side and store it in tallDS.
if event.propertyName == 'wideDS':
wide = fpmi.db.toPyDataSet(event.newValue)
newData = []
row = wide[0]
sampleMillis = 0
for column in row:
# Add the value in this column as a row in the new dataset
newData.append([sampleMillis, column])
sampleMillis = sampleMillis + 5
event.source.setPropertyValue('tallDS', fpmi.db.toDataSet(["Millis", "Value"], newData))[/li]
[li]Add a chart component to your window[/li]
[li]Bind the chart’s Data property to a property binding, selecting Root Container.tallDS[/li]
[li]Select the Chart, and click on the Component Customizer. Add a new X-Axis, and make it a numeric axis (as opposed to a date axis). Go to the Dataset Properties tab, and assign your dataset to this new X-Axis.[/li]
[li]Configure any other chart properties that you’d like! (Axis titles, colors etc)[/li][/ol]
Thats it! Please let me know how this goes,
Script looks great and I have it all configured. Ran into 1 problem: When I try to bind the Chart’s Data property to Root Container.tallDS, I get an error.
The type of property you choose(Integer) is not assignment -compatible with the type of the property you are binding to (DataSet)
What do you think I am doing wrong?
Thanx
Keith
When you created the 2 custom properties on the Root Container (wideDS and tallDS), you must have made tallDS an Integer instead of a DataSet. Simply remove it and re-create it as a DataSet, and it should work fine.
Hope this helps,
Sorry for having to keep going back and forth on this.
No more errors - YEA!
I still do not get a graph. wideDS is updating correctly, but the tallDS is not showing any data. My expectation is that when I click on the icon to view the tallDS data, I should see 1 column with multiple rows of data.
Is there anything in the script that I need to customize for my tables? I did not notice anything. I am missing 1 more piece to the puzzle somewhere.
Thank you,
Keith
One thing to know is that action scripts only execute in preview mode. Click on the ‘run’ button in the toolbar and see if your tallDS dataset fills in.
Hope this helps,
Keith,
As per our recent phone conversation, here is a new version of the script that works with tables with any number of rows. It will make a dataset that can be charted such that each row of the original table will be a new pen in the chart.
The script is a little more complicated this time, but here it is:
[code]if event.propertyName == ‘wideDS’:
wide = fpmi.db.toPyDataSet(event.newValue)
newData = []
numCols = 0
if len(wide) > 0:
numCols = len(wide[0])
sampleMillis = 0
for colIdx in range(numCols):
# Add each column value as a row in the new dataset
newRow = [sampleMillis]
for rowIdx in range(len(wide)):
newRow.append(wide[rowIdx][colIdx])
newData.append(newRow)
sampleMillis = sampleMillis + 5
headers = [“Millis”]
for rowIdx in range(len(wide)):
headers.append(“Value %d” % (rowIdx+1))
event.source.setPropertyValue(‘tallDS’, fpmi.db.toDataSet(headers, newData))[/code]
Hope this helps,
Thank you so much for your help. The script works GREAT ! ! !
Let me know if you ever need a “Get out of Jail free” card. You guys are the greatest.
I will let you know how things develop. I will be presenting to the big bosses in 2 weeks.
Thank you,
Keith
No problem Keith, don’t hesitate to ask if any more questions come up.
We have purchased the software. Thank you so much for your help.
I need to add a calculation to the dataset. Each of the values from the PLC need to be manipulated by the equation below:
newvalue = (PLCvalue-2040)0.000542
This converts the PLC value to PSI. This is the value that needs to be represented on the graph.
Could you help me and let me know where in your Database on its side script this needs to be placed to return the corrct result?
Thank you,
Keith
Keith,
Take the line that looks like this: (this line is the line that takes the values out of the wide dataset, and puts them into a row of the new dataset)
newRow.append(wide[rowIdx][colIdx])… and change it to this:
plcVal = wide[rowIdx][colIdx]
newRow.append((plcVal-2040)*0.000542)
Hope this helps,
Works great.
Thanks again.
Keith
We are changing from MySQL to SQL Server 2005. I need help converting this script over.
What do you think?
Keith
Hmm, seeing as this script deals with the data after it has been retrieved from the database, I don’t see anything that would change when moving between database types.