How do I solve this?
“INFO Perspective.Designer.BrowserConsole - components.PieChart: Data invalid. Each pie section object must have a label (string) and a value (number)”
My query returns a daset with nine values and there are nine colors defined
1 Like
Does the dataset have two columns?
No nine, this is the sql query
SELECT sum(S1) AS ‘A’,
sum(S4) AS ‘B’,
sum(S5) AS ‘C’,
sum(S8) AS ’ D’,
sum(S13) AS ‘E’,
sum(S20) AS ‘F’,
sum(S21) AS ’ G’,
sum(S22) AS ‘H’,
sum(S28) AS ‘I’
FROM mac
WHERE machinestatus.ID = ewon
In another project with vision module works
Ah. Gotcha. 
Is the extract order of the chart set to ‘By Row’?
I know there is this option in vision module, but I can’t find in perspective module.
Vision and Perspective components deal with incoming data in different ways, and so I’m not surprised that this might work in Vision but not Perspective. We’re working on certain components to make it easier to have a dataset just “work”, but sometimes we can’t do much because of the component.
In the case of the Pie Chart, it does require that that each data point have an object with a very specific shape (The objects MUST contain a property which is a String, and the object MUST contain a property which is a numeric value), so there is some “massaging” of data required.
Perform your Query binding as you did previously.
You will want to add a Script transform to this binding.
My Script Transform looks like this, where my SQL Query is returning multiple rows and I want the ‘name’ and ‘quantity’ values to be in my Pie Chart objects.
value_objects = []
py_value = system.dataset.toPyDataSet(value)
for row in py_value:
value_objects.append({'name': row['name'], 'quantity':row['quantity']})
return value_objects
In my Query, ‘name’ is a String, and ‘quantity’ is a numeric value (Int). When I am bound directly to the query, my binding does work, although I do currently see a warning due to receiving a string instead of an array of objects (this is something we’re working on addressing). the inclusion of the previously described transform removes the warning.
2 Likes
It might be useful to offer some standard, optimized transforms that provide this kind of compatibility layer.
Long-term (for Pie Chart), our goal is to remove the warning when the incoming data matches requirements because if your incoming Dataset is an array of many rows which contain only a String and a number, you should be able to use that without any transforms (which you can, but you’ll see a warning).
For some components (like Dropdown), any rows in the array must actually have the “label”/“value” keys. We can’t safely make any assumptions about what order a user’s query is returning values, and which should be label or value.
That being said, Transform Templates aren’t such a bad idea…
Ah, but a vision dropdown just uses the first two columns as value and label. Or just a single column as both. A simple transform that takes a dataset intended for a vision dropdown and yields a value/label list suitable for a Perspective dropdown would be a great help. Particularly for projects that will have both Vision and Perspective UIs (whether transitional or long-term).
@software.development So, for your single-row query result, the transform would be somewhat like:
labelList = value.getColumnNames()
valueList = list(system.dataset.toPyDataSet(value)[0])
valueObjects = [{'label':l, 'value':v} for l,v in zip(labelList, valueList)]
return valueObjects
1 Like
@cmallonee, I’m seeing the same error message @software.development described in the original post. I’ve been trying to apply your transform solution but keep getting the error.
I’ve written my query to return 2 columns. I want to use one of them for label and the other for value.
When I test the query, the results look good…
I have the data binding configured for a Return Format of dataset. However, I still get the Error_Exception. I tried your transform but couldn’t get it to work. But, if I’m being honest, I don’t quite follow how you’re using the dictionary on the “append” line (I’m still pretty new to python).
I’m trying to bind the data property shown below.
- What version of Perspective are you using? Newer versions 8.0.5 should accept datasets without any need for a transform, assuming the dataset is two columns where the first column is a string and the second a number.
- You could also try changing the Return Format in your Query Binding (dropdown in the upper right of the dialog) from dataset to json and skip the transform.
Any component which is displaying data combinations expects an array of objects, so my (now unnecessary due to updates to Perspective) transform was:
A. Making an empty list.
B. Storing the value of my dataset after I had converted it to a PyDataSet.
C. Looping through each row of the PyDataSet and making a new object comprised of the name and quantity columns of the PyDataSet and then appending this object to my list
D. After all rows had been converted, return the list.
Two things to check on your end:
- Your query should be absolutely fine, and you shouldn't even need to change the return format or do a transform. Have you saved since you wrote the query? I've encountered lots of users who write a query and then attempt to bind and while the Designer does some hand-wavey magic, the binding is actually going to return no value because the Named Query hasn't actually been saved, and so the Gateway isn't aware of it and therefore isn't going to return a value.
- Assuming you have saved, the
Totalvalue I see in your data looks like a running count, (1,2,3,4), when I expected14,111,379,791.
I appreciate you taking the time to explain these concepts to a newbie like me. Its one of the things that makes IA awesome!
I’m using Version 8.0.5 (b2019101516). Regarding the (1,2,3,4) vs (14,111,379,791), those were just placeholder values I had thrown in.
It looks like I fell victim to scenario #1 
. This morning, I opened up the project and the binding just worked (probably because of the saving when I closed it last night). It didn’t occur to me to save because every time I edited the named query it was previewed correctly in the biding window. I took that to mean that the gateway was using the modified NQ.
Yah, the Designer does some magic because it’s aware of the Named Query, but once the binding is saved it begins using values from the Gateway, which has no record of the Named Query. Once the save completes the values come through as expected. I’m glad you got it working.
What version of Perspective are you using? Newer versions 8.0.5 should accept datasets without any need for a transform, assuming the dataset is two columns where the first column is a string and the second a number.
This is broken for me in 8.0.16. "Array found where object expected" in the properties window and "cannot read property 'toString' of undefined" on the pie chart itself
query = """
SELECT area, estimated_duration
FROM shift_reports where username = ?
AND report_date = ?
"""
args = ['user', '2021-01-20']
self.getSibling("PieChart").props.data = system.db.runPrepQuery(query, args)
I’m having an issue when using JSON return format from a Named Query binding to a Perpspective Pie Chart. I’m seeing the following message but I’m not able to see a message in the log pertinent to this.
My query returns 2 colums. The first is a varchar and the second is an int.
1 Like
1 Like
Same problem… did you find a solution?
Hi @tecnoeletronic I was working on same and I have noticed it works best if one of them is string and other as value. like pic below:
I think it can be achieved, if you add a character (N, C, # etc.) before the code in your case
Yes! Thanks a lot!