Best approach gathering data for report and building report

Ignition
1

I am new to Ignition, and working with SCADA in general, and would like some pointers on good ways to approach a report i would like to build.

In the ControlLogix for one of our paint lines i have accumulating counts (using DINT not COUNTER) for skid throughput per job and shift, that get reset at 2AM. Want to do history for those for previous week. Our IT department has not set us up with a SQL database to use yet, so limited to SQLite. Was originally going to do a transaction group to save values to DB at 1:30AM, after production has finished but before daily reset, but having trouble with getting timestamp to show as a date, and with figuring out how to structure the data in the table to arrive at the report view desired (shown in attached excel). Not sure if i can use tag history, since COS would have a new data point at every increment instead of just the total, and i cannot tell it to only store between 1:30AM-2AM. Maybe create a new set of DINTs in PLC that just get written to at that time with the daily total?

Also not sure it is even possible or how to go about designing the report to display like that in Ignition. Any pointers greatly appreciated. I am very limited on my knowledge of Python, so any solutions involving scripting i may have some trouble understanding.

Thank you in advance.

Chart basis.xlsx (15.2 KB)

1 Like
2

This is the approach I would recommend. Your end goal, I think, is a bar chart in the report that uses stacked bar charts to display multiple value columns per shift (2 per day), over a length of time. The TX group would store the 4 value columns along with: an identifier for the day + an identifier for the shift. (To add custom values to the TX group you can add expression tags/items that evaluate to the values you want, for example you could have an expression doing a calc to find the previous day if the group runs at 1:30 AM)

This may be possible to do without any Python, Python can be helpful after a query in reporting to alter the dataset to fit the chart format. With proper ordering I think you could go from TX group > that bar chart format, but I am not sure what the x-axis labels would look like.

3

Thank you for the response.

I did end up using transaction groups, but since SQLite does not like dates, i created a string expression tag storing ‘dateFormat(now(()-86400000), “MM/dd/YY”)’ and used that instead of transaction group timestamp.

Could not figure out the stacked bar in a single chart, a bit over my head at the moment, so did 2 seperate bar charts in report. Used a drop down selector bound to parameter for selecting different table to report, and a script in data source to change the table and rows in the queries. Not pretty, but it works.

Some of the reports are for hourly data over the week, and would like to have legend showing the hour, like ‘4:00AM’, but since legend is populated from column name, and SQL does not like numbers in column name and definitely no colons, having some trouble with that. Any ideas how to customize the legend?

4

SQLite is a toy, designed to store low-traffic configuration data. It is absolutely unsuitable as a historian. Use a real SQL database.

1 Like
5

Would love to, but at the mercy of our glorious I.T. department.

6

For customizing the legend with your current setup, I'd change the underlying data set after the query. This would likely be done via a simple Script data source that transforms the column names based on your needs, so scripting would be required: Scripting Data Source | Ignition User Manual

There are some pretty simple examples here, the report is expecting a dataset and you can interact with it and provide an altered dataset that your design expects instead.

7

Ah, I had used that article figuring out how to change parameters for my query, just didn’t click that I can use it for manipulating results as well.

I am still missing something, tried a few rows to see how it worked, using the following but displaying like pic:

def updateData(data, sample):
	#data['myKey']='Example'
	
	#build headers for new datasets
	header1 = ['Date', '4:00AM', '5:00AM', '6:00AM', '7:00AM', '8:00AM', '9:00AM']
	filteredDataset1 = []
	
	#get 1st shift results
	rawDataset1 = data['query1st'] .getCoreResults()
	
	#build new pydataset for 1st shift
	for row in range(rawDataset1.rowCount):
		valDate = rawDataset1.getValueAt(row, 'Day')
		val1 = rawDataset1.getValueAt(row, 'OT1')
		val2 = rawDataset1.getValueAt(row, 'OT2')
		val3 = rawDataset1.getValueAt(row, 'HR1')
		val4 = rawDataset1.getValueAt(row, 'HR2')
		val5 = rawDataset1.getValueAt(row, 'HR3')
		val6 = rawDataset1.getValueAt(row, 'HR4')
		filteredDataset1.append([valDate, val1, val2, val3, val4, val5, val6])
		
	#convert to standard dataset
	filteredDataset1 = system.dataset.toDataSet(header1, filteredDataset1)
	
	#create new data source with filtered dataset
	data['Chart 1st'] = filteredDataset1

Report
Report1594×878 91.5 KB

8

It looks like all of your columns are ending up with the same name. If I remember correctly, column names can not start with a number. I think it is changing the column names to AM_ instead.

9

Is there a way to make legend use a different list than header, so column names don't start with a number, but user gets to see a number as the identifier?

10

I don’t have any experience with it, but I found this forum post that might point in the right direction.

I think Paul is pointing to the Chart Scripting that is briefly mentioned in the documentation here: Report Charts | Ignition User Manual