Could you point me to the right direction on how to fill power table with data from database. I can do that with simple SQL commands if single row in database corresponds to single row in table.
But now I have a row in database which must be displayed across multiple rows in table.
Each row corresponds to one production batch. And I want to display batch data.
Simplified version (there is ~200 columns) of row (a list of columns):
BATCH_ID, COMPONENT_1_NAME, COMPONENT_1_TARGET_KG, COMPONENT_1_ACTUAL_KG, COMPONENT2_NAME, COMPONENT_2_TARGET_KG, COMPONENT_2_ACTUAL_KG
I need it to display in table like that (row by row):
COMP1: NAME, TARGET_KG, ACTUAL_KG
COMP2: NAME, TARGET_KG, ACTUAL_KG
Create a named query to retrieve the data
Use that named query as the data binding
Assuming you’re talking about Vision - use the power table customiser to hide columns you don’t need, and rename columns.
So if I create SELECT query it returns data as a single row and binding that named query
displays single line in power table.
I need query which would return data formatted as two dimensional table and directly bind it to table or somehow format that data in table script (?)
It would be useful to see your query, and some potential rows of data. Unless there’s only one row of data in the database you should be getting more.
This looks to be a wide to tall format issue. Which means that you will need to do some scripting. As long as the db columns are consistently named (e.g. COMPONENT_X_NAME, it should be fairly simple to iterate through it.
mocked up named query:
Add a custom property to the table. I used ‘query’ and bound it to the named query:
Use a propertyChange script to transform the data:
if event.propertyName == 'query':
baseColNames = ['component_{}_name', 'component_{}_target_kg', 'component_{}_actual_kg']
dataIn = system.dataset.toPyDataSet(system.db.runNamedQuery('Query', {}))
headers = ['Comp', 'Name', 'Target', 'Actual']
data = []
for i in xrange(5):
newRow = [i+1]
for col in baseColNames:
newRow.append(dataIn[0][col.format(i+1)])
data.append(newRow)
event.source.data = system.dataset.toDataSet(headers, data)
3 Likes
