# \[Question\] Pie chart series

**URL:** <https://forum.inductiveautomation.com/t/question-pie-chart-series/20934>\
**Category:** Ignition Early Access\
**Created:** [November 19, 2018, 10:49am UTC](https://forum.inductiveautomation.com/t/question-pie-chart-series/20934 "2018-11-19T10:49:47Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![software.development](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/software.development/32/6688_2.png) [@software.development](https://forum.inductiveautomation.com/u/software.development)\
**Post date:** [November 19, 2018, 10:49am UTC](https://forum.inductiveautomation.com/t/question-pie-chart-series/20934/1 "2018-11-19T10:49:47Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [November 19, 2018, 12:29pm UTC](https://forum.inductiveautomation.com/t/question-pie-chart-series/20934/2 "2018-11-19T12:29:03Z")

</div>

Does the dataset have two columns?

---

<div class="post-metadata">

**Author:** ![software.development](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/software.development/32/6688_2.png) [@software.development](https://forum.inductiveautomation.com/u/software.development)\
**Post date:** [November 19, 2018, 12:41pm UTC](https://forum.inductiveautomation.com/t/question-pie-chart-series/20934/3 "2018-11-19T12:41:54Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [November 19, 2018, 12:48pm UTC](https://forum.inductiveautomation.com/t/question-pie-chart-series/20934/4 "2018-11-19T12:48:34Z")

</div>

Ah. Gotcha. 🙂

Is the extract order of the chart set to ‘By Row’?

---

<div class="post-metadata">

**Author:** ![software.development](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/software.development/32/6688_2.png) [@software.development](https://forum.inductiveautomation.com/u/software.development)\
**Post date:** [November 19, 2018, 12:56pm UTC](https://forum.inductiveautomation.com/t/question-pie-chart-series/20934/5 "2018-11-19T12:56:28Z")

</div>

I know there is this option in vision module, but I can’t find in perspective module.

---

<div class="post-metadata">

**Author:** ![cmallonee](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/cmallonee/32/6416_2.png) [@cmallonee](https://forum.inductiveautomation.com/u/cmallonee)\
**Post date:** [November 19, 2018, 3:25pm UTC](https://forum.inductiveautomation.com/t/question-pie-chart-series/20934/6 "2018-11-19T15:25:01Z")

</div>

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.

```auto
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.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [November 19, 2018, 3:27pm UTC](https://forum.inductiveautomation.com/t/question-pie-chart-series/20934/7 "2018-11-19T15:27:22Z")

</div>

It might be useful to offer some standard, optimized transforms that provide this kind of compatibility layer.

---

<div class="post-metadata">

**Author:** ![cmallonee](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/cmallonee/32/6416_2.png) [@cmallonee](https://forum.inductiveautomation.com/u/cmallonee)\
**Post date:** [November 19, 2018, 3:40pm UTC](https://forum.inductiveautomation.com/t/question-pie-chart-series/20934/8 "2018-11-19T15:40:43Z")

</div>

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…

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [November 19, 2018, 4:35pm UTC](https://forum.inductiveautomation.com/t/question-pie-chart-series/20934/9 "2018-11-19T16:35:06Z")

</div>

> [@cmallonee](#):
>
> 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.

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).

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [November 19, 2018, 9:07pm UTC](https://forum.inductiveautomation.com/t/question-pie-chart-series/20934/10 "2018-11-19T21:07:29Z")

</div>

@software.development So, for your single-row query result, the transform would be somewhat like:

```python
labelList = value.getColumnNames()
valueList = list(system.dataset.toPyDataSet(value)[0])

valueObjects = [{'label':l, 'value':v} for l,v in zip(labelList, valueList)]

return valueObjects

```

---

<div class="post-metadata">

**Author:** ![mmurphy](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmurphy/32/11430_2.png) [@mmurphy](https://forum.inductiveautomation.com/u/mmurphy)\
**Post date:** [October 29, 2019, 1:17am UTC](https://forum.inductiveautomation.com/t/question-pie-chart-series/20934/11 "2019-10-29T01:17:08Z")

</div>

@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.  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/8/822b09166a24e898c34838ee0edd34bc578a38a8.png)

When I test the query, the results look good…  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/d/db1292e5d382343f6e76e0eca5ba3f99063ef9f4.png)

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).

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/3/3c2dc643b3ef33ce5960051330cb849f5db3dca5.png)

I’m trying to bind the data property shown below.  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/2/2253f5afab60024d6a66e9fce7e4059173e906c1.png)

---

<div class="post-metadata">

**Author:** ![cmallonee](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/cmallonee/32/6416_2.png) [@cmallonee](https://forum.inductiveautomation.com/u/cmallonee)\
**Post date:** [October 29, 2019, 2:36pm UTC](https://forum.inductiveautomation.com/t/question-pie-chart-series/20934/12 "2019-10-29T14:36:37Z")

</div>

1. 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.
2. 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.
3. 

> [@mmurphy](#):
>
> I don’t quite follow how you’re using the dictionary on the “append” line (I’m still pretty new to python).

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:

1. 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.
2. Assuming you _have_ saved, the `Total` value I see in your data looks like a running count, (`1`,`2`,`3`,`4`), when I expected `14`,`111`,`379`,`791`.

---

<div class="post-metadata">

**Author:** ![mmurphy](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmurphy/32/11430_2.png) [@mmurphy](https://forum.inductiveautomation.com/u/mmurphy)\
**Post date:** [October 29, 2019, 3:24pm UTC](https://forum.inductiveautomation.com/t/question-pie-chart-series/20934/13 "2019-10-29T15:24:14Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![cmallonee](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/cmallonee/32/6416_2.png) [@cmallonee](https://forum.inductiveautomation.com/u/cmallonee)\
**Post date:** [October 29, 2019, 3:26pm UTC](https://forum.inductiveautomation.com/t/question-pie-chart-series/20934/14 "2019-10-29T15:26:14Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![chasondeshotel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/chasondeshotel/32/20032_2.png) [@chasondeshotel](https://forum.inductiveautomation.com/u/chasondeshotel)\
**Post date:** [January 21, 2021, 4:53pm UTC](https://forum.inductiveautomation.com/t/question-pie-chart-series/20934/15 "2021-01-21T16:53:26Z")

</div>

> 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

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/9/e99186e4b74f3e418dbfb885d1909b5c02a2707e.png)

```auto
	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)

```

---

<div class="post-metadata">

**Author:** ![BenRicketts](https://avatars.discourse-cdn.com/v4/letter/b/c37758/32.png) [@BenRicketts](https://forum.inductiveautomation.com/u/BenRicketts)\
**Post date:** [April 30, 2021, 7:55pm UTC](https://forum.inductiveautomation.com/t/question-pie-chart-series/20934/16 "2021-04-30T19:55:09Z")

</div>

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.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/d/3/d3a63bfb878896e043fac904ab29832317748247.png)

My query returns 2 colums. The first is a varchar and the second is an int.

---

<div class="post-metadata">

**Author:** ![BenRicketts](https://avatars.discourse-cdn.com/v4/letter/b/c37758/32.png) [@BenRicketts](https://forum.inductiveautomation.com/u/BenRicketts)\
**Post date:** [April 30, 2021, 7:57pm UTC](https://forum.inductiveautomation.com/t/question-pie-chart-series/20934/17 "2021-04-30T19:57:37Z")

</div>

Here is an image of the binding I am using.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/c/0c74509a9f56c5376513afe69e83d0673c253dc5.png)  
any help would be appreciated!

Ignition version 8.1

---

<div class="post-metadata">

**Author:** ![tecnoeletronic](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/tecnoeletronic/32/50975_2.png) [@tecnoeletronic](https://forum.inductiveautomation.com/u/tecnoeletronic)\
**Post date:** [January 19, 2022, 10:34am UTC](https://forum.inductiveautomation.com/t/question-pie-chart-series/20934/18 "2022-01-19T10:34:46Z")

</div>

Same problem… did you find a solution?

---

<div class="post-metadata">

**Author:** ![shabir.ahmed](https://avatars.discourse-cdn.com/v4/letter/s/a88e4f/32.png) [@shabir.ahmed](https://forum.inductiveautomation.com/u/shabir.ahmed)\
**Post date:** [January 20, 2022, 12:10pm UTC](https://forum.inductiveautomation.com/t/question-pie-chart-series/20934/19 "2022-01-20T12:10:09Z")

</div>

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:  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/a/4/a448a61ebef12da6316194fc8a3ae68cb2129da4.png)  
I think it can be achieved, if you add a character (N, C, # etc.) before the code in your case

---

<div class="post-metadata">

**Author:** ![tecnoeletronic](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/tecnoeletronic/32/50975_2.png) [@tecnoeletronic](https://forum.inductiveautomation.com/u/tecnoeletronic)\
**Post date:** [January 20, 2022, 12:45pm UTC](https://forum.inductiveautomation.com/t/question-pie-chart-series/20934/20 "2022-01-20T12:45:29Z")

</div>

Yes! Thanks a lot!

[Next page](https://forum.inductiveautomation.com/t/question-pie-chart-series/20934.md?page=2)