Print all values stored in a Data Key (pulled from MSSQL) as a list with no repeats on Report

Ignition
1

Hi all,

Background Info: I am currently working on a report on Ignition V8.1 Designer. On the report I am working on adding an overview page before each table. (image of overview page below) I am pulling data from their database (MSSQL) and using that for the table and the report overview table.

Problem: Right now on the report overview I would like to list out each value stored in the data key ‘Part’ as a list with commas between without repeats. Each key has multiple values stored to it, and the index is defined by the start and end date parameters, which I have set at (now - 12 hrs) and (now), so it refreshes with the time. I was wanting to do this in the simple table component circled in blue on the picture, but would also be open to use other components. Keep in mind the values and indexes may change so hardcoding a certain number of indexes is not an option.

Can anyone help with this or have any experience with this? Let me know if any clarification or information is needed. I am extremely new to Ignition so any help is greatly appreciated! Thanks for your time!

image
image665×826 60.8 KB

2

Here is some sample code to get the values of a column and create a string with unique values

# Sample dataset
sampleCols = ['Machine', 'Part', 't_stamp']
sampleData = [[1, 'abc', '2022-06-03 05:00:00'],
		      [1, 'abc', '2022-06-03 05:00:01'],
		      [1, 'def', '2022-06-03 05:00:02'],
		      [1, 'def', '2022-06-03 05:00:03'],
		      [1, 'ghi', '2022-06-03 05:00:04'],
		      [1, 'abc', '2022-06-03 05:00:05'],
		      [1, 'ghi', '2022-06-03 05:00:06'],
		      [1, 'xyz', '2022-06-03 05:00:07'],
		      [1, 'xyz', '2022-06-03 05:00:08'],
		      [1, 'pdq', '2022-06-03 05:00:09']
       ]

sampleDataSet = system.dataset.toDataSet(sampleCols,sampleData)

#----------------------------------------------#

dataIn = sampleDataSet # This can change to whatever your source is.

# Get column names
colNames = dataIn.getColumnNames()
        
# Find index of the 'Part' column
idx = colNames.indexOf('Part')

# Get the column values
partList = dataIn.getColumnAsList(idx)

# Converting to a set will return only unique values
uniquePartString = ', '.join(set(partList))

print partList
print uniquePartString

output:

[abc, abc, def, def, ghi, abc, ghi, xyz, xyz, pdq]
abc, def, ghi, xyz, pdq
>>>
1 Like
3

Psst:
https://files.inductiveautomation.com/sdk/javadoc/ignition81/8.1.16/com/inductiveautomation/ignition/common/Dataset.html#getColumnIndex(java.lang.String)

4

I can't keep up with all the new toys.

Also, it'd be nice if getColumnAsList('colName') was more widespread. But I digress...

5

Please?

The number of occurrences of dset.getColumnAsList(dset.getColumnIndex('foo')) in our scripts is very high.

6

Thank you everyone for the help! I have very minimal experience with coding so the sample code was awesome! Much appreciated!