Hey all,
I have set up a transaction that runs once per day and collects downtimes and places each downtime into a Column. Every row in the table is a days worth of times, which is what I wanted to do. Here is what the table looks like:
Now I want to make a downtime report but the charts on the report don’t seem to like this table format, I can sum all of the columns but I’m having a hard time getting it to display correctly in the report. How do I sum the columns and then turn them into rows? so instead of:
E-stop 1 E-stop 2 E-stop 3 E-stop 4 etc…
1 2 1 4
2 3 5 2
I would like:
E-stop 1 3
E-stop 2 5
E-stop 3 6
E-stop 4 6
I’m no SQL guru and I can’t figure out how to accomplish this task. Any help or tips would be appreciated, thanks!
This isn’t well supported in SQL, but you can write a simple script to do this for you. Something like:
rawData = system.db.runQuery("SELECT sum(Minutes_Estop__1_Active), sum(Minutes_Estop__2_Active), sum(Minutes_Estop__3_Active), ...")
header = ["E-stop", "sum"]
newData = []
newData.append([ "E-stop 1", rawData[0][0] ])
newData.append([ "E-stop 2", rawData[0][1] ])
newData.append([ "E-stop 3", rawData[0][2] ])
finishedData = system.dataset.toPyDataSet(header, newData)
Cool, thank you!
I put that code into the property change event handler on a date selector but now I’m getting some kind of date format error.
With this code:
start = event.source.date
stop = event.source.parent.getComponent('FlowStop').date
rawData = system.db.runQuery("SELECT SUM(Minutes_Estop__10_Active), SUM(Minutes_Estop__11_Active),SUM(Minutes_Estop__1_Active), SUM(Minutes_Estop__2_Active),SUM(Minutes_Estop__3_Active), SUM(Minutes_Estop__4_Active),SUM(Minutes_Estop__5_Active), SUM(Minutes_Estop__6_Active),SUM(Minutes_Estop__7_Active), SUM(Minutes_Estop__8_Active),SUM(Minutes_Estop__9_Active), SUM(Minutes_Hydraulic_Timeout_Active),SUM(Minutes_Landing_Conveyor_Down), SUM(Minutes_Left_Loin_Puller_Down),SUM(Minutes_Right_Loin_Puller_Down) FROM Cutfloor_EandD WHERE t_stamp >= '" + start + "' and t_stamp <= '" + stop + "'")
I get this error:
Traceback (innermost last):
File “event:propertyChange”, line 3, in ?
TypeError: add nor radd defined for these operands
Ignition v7.2.8 (b178)
Java: Sun Microsystems Inc. 1.6.0_24
I know this is somekind of typecase error, so I tried making the start date a string by doing str(start), but then I get a different error:
Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Gateway Error 500: Conversion failed when converting datetime from character string.
So now I’m stuck again… I can’t figure out how to pass the date from a date selection into the SQL query. 
It’s saying that you cannot add a string to a date. Try using python to stuff the date into the string like this:
start = event.source.date
stop = event.source.parent.getComponent('FlowStop').date
rawData = system.db.runQuery("SELECT SUM(Minutes_Estop__10_Active), SUM(Minutes_Estop__11_Active),SUM(Minutes_Estop__1_Active), SUM(Minutes_Estop__2_Active),SUM(Minutes_Estop__3_Active), SUM(Minutes_Estop__4_Active),SUM(Minutes_Estop__5_Active), SUM(Minutes_Estop__6_Active),SUM(Minutes_Estop__7_Active), SUM(Minutes_Estop__8_Active),SUM(Minutes_Estop__9_Active), SUM(Minutes_Hydraulic_Timeout_Active),SUM(Minutes_Landing_Conveyor_Down), SUM(Minutes_Left_Loin_Puller_Down),SUM(Minutes_Right_Loin_Puller_Down)
FROM Cutfloor_EandD WHERE t_stamp >= '%s' and t_stamp <= '%s'" %(start, stop))
Tried that, same thing. It looks like it’s actually a MSSQL problem. For some reason it doesn’t like the datetime coming from the date property:
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting datetime from character string.
This is what comes up in the error dialog box for the where clause:
WHERE t_stamp >= ‘Sun Aug 28 08:49:38 EDT 2011’ and t_stamp <= ‘Wed Aug 31 08:49:38 EDT 2011’, IgnitionDB, )
Doesn’t look any different to me than using that propery binding in a SQL query…
Yeah, the problem is that you get a format like ‘Sun Aug 28 08:49:38 EDT 2011’ out of your start and end date components. You can convert it to this format before putting it into the SQL query: ‘2011-08-28 08:49:38’. The easiest way to do this is to create a dynamic property (string) and use the dateFormat() expression function.
