How to break data array into separate charts with data and labels

General
1

I am new to node-red and looking for some assistance here.

I am running a SQL statement to get two fields returned from a table: customer (string) and storage size (integer).
select trim(CUSTOMER) as "CUSTOMER",DISK_SPACE_USED from stats where run_date >= current date

I return 46 data sets into 1 array. I would like to loop through the array and have it create an output for each customer that I could then flow to a donut chart that would label the chart with the customer name and the data in the chart would be the storage size percentage (without me having to manually create each one).

Is this possible and if so what would it look like?

Right now I have it grabbing a specific customer and was going to create a function node for each customer if I had to but I would prefer it to be more dynamic if possible.
cust = "TEST";
var value = 0;
msg.payload.forEach(function(row) {
if (row.CUSTOMER == cust) {
value = parseInt(row.DISK_SPACE_USED);
}
});

return {payload:value};

image

image
image509Ă—612 7.55 KB

2

Are there 46 donuts on the dashboard or are you going to have a method of selecting the one you are interested in?

3

Colin,
I was thinking I would have to create 46 donut graphs and have 46 outputs from the function and link them. Since I am trying to get the label of the donut and the value to populate automatically it wouldn't matter the order.

4

I think I would convert your array to an object, something like (untested)

answer = {}
for (let i=0; i< msg.payload.length) {
  answer[msg.payload[i].CUSTOMER] = msg.payload.DISK_SPACE_USED
}
msg.payload = answer
return msg

Then you can feed that to all the gauges and in the Value format field put
msg.payload["this customer"]
Then the gauge will select the value for "this customer". At least I think you can do that, I haven't got time to check it as it is late. If I haven't got it quite right then I can have a better look in the morning.

5

Thanks for the suggestion Colin. When I tried this it broke my flow and wouldn't load the flow or ui. I had to backdoor my flow and remove the code. Maybe I did something wrong.

6

Why not use the ui-template node and create the gauges all dynamically, something like below.
The payload array is injected by the inject node. The function creates a html page with javascript. and the ui-template node displays all dounuts.

[{"id":"a7d1f1c4.c8cb1","type":"inject","z":"57675e72.26a0d8","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"customer\":\"john\",\"size\":23},{\"customer\":\"sarah\",\"size\":47},{\"customer\":\"tom\",\"size\":34}]","payloadType":"json","x":80,"y":2460,"wires":[["422318c8.3f3c","819c37d1.394e8"]]},{"id":"422318c8.3f3c","type":"function","z":"57675e72.26a0d8","name":"","func":"let html = \"\";\nlet script = `<script>\n    `;\n\n\nfor(let i=0; i < msg.payload.length; i++){\n    let vals = [100-msg.payload[i].size,msg.payload[i].size]\n    html += `<canvas id=\"canvas${i}\" width=150 height=150 ></canvas>\\n`;\n    script += `var canvas=document.getElementById(\"canvas${i}\");\n    var ctr=canvas.getContext(\"2d\")\n    var colors=['gray','skyblue'];\n    values=[${vals[0]},${vals[1]}];\n    labels=['name','${msg.payload[i].customer}'];\n    dmbChart(ctr,70,70,60,10,values,colors,labels,1);`\n}\nmsg.template= html+script+\n`function dmbChart(ctx,cx,cy,radius,arcwidth,values,colors,labels,selectedValue){\n    var tot=0;\n    var accum=0;\n    var PI=Math.PI;\n    var PI2=PI*2;\n    var offset=-PI/2;\n    ctx.lineWidth=arcwidth;\n    for(var i=0;i<values.length;i++){tot+=values[i];}\n    for(var i=0;i<values.length;i++){\n        ctx.beginPath();\n        ctx.arc(cx,cy,radius,\n            offset+PI2*(accum/tot),\n            offset+PI2*((accum+values[i])/tot)\n        );\n        ctx.strokeStyle=colors[i];\n        ctx.stroke();\n        accum+=values[i];\n    }\n    var innerRadius=radius-arcwidth-3;\n    ctx.beginPath();\n    ctx.arc(cx,cy,innerRadius,0,PI2);\n    ctx.fillStyle=colors[selectedValue];\n    ctx.fill();\n    ctx.textAlign='center';\n    ctx.textBaseline='bottom';\n    ctx.fillStyle='white';\n    ctx.font=(innerRadius)+'px verdana';\n    ctx.fillText(values[selectedValue],cx,cy+innerRadius*.9);\n    ctx.font=(innerRadius/4)+'px verdana';\n    ctx.fillText(labels[selectedValue],cx,cy-innerRadius*.25);\n}\n</script>`;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":310,"y":2480,"wires":[["a0b72f46.f0133","6bd8925c.1728b4"]]},{"id":"a0b72f46.f0133","type":"ui_template","z":"57675e72.26a0d8","group":"8b5cde76.edd58","name":"","order":0,"width":0,"height":0,"format":"","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":true,"templateScope":"local","x":570,"y":2460,"wires":[[]]},{"id":"6bd8925c.1728b4","type":"debug","z":"57675e72.26a0d8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"template","targetType":"msg","statusVal":"","statusType":"auto","x":500,"y":2540,"wires":[]},{"id":"8b5cde76.edd58","type":"ui_group","z":"","name":"","tab":"8f03e639.85956","order":1,"disp":true,"width":"6","collapse":false},{"id":"8f03e639.85956","type":"ui_tab","z":"","name":"Home","icon":"dashboard","disabled":false,"hidden":false}]
7

It works ok for me

image

[{"id":"253472e5.9048ae","type":"inject","z":"2320900f.56c54","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{\"customer 1\": 7.3, \"customer 2\": 1.0}","payloadType":"json","x":90,"y":900,"wires":[["c2cb0f2c.789298","98a0db7b.e71498"]]},{"id":"c2cb0f2c.789298","type":"ui_gauge","z":"2320900f.56c54","name":"","group":"903a6ab8.4f4ca8","order":1,"width":0,"height":0,"gtype":"gage","title":"gauge","label":"units","format":"{{msg.payload[\"customer 1\"]}}","min":0,"max":10,"colors":["#00b500","#e6e600","#ca3838"],"seg1":"","seg2":"","x":320,"y":900,"wires":[]},{"id":"98a0db7b.e71498","type":"ui_gauge","z":"2320900f.56c54","name":"","group":"903a6ab8.4f4ca8","order":1,"width":0,"height":0,"gtype":"gage","title":"gauge","label":"units","format":"{{msg.payload[\"customer 2\"]}}","min":0,"max":10,"colors":["#00b500","#e6e600","#ca3838"],"seg1":"","seg2":"","x":310,"y":980,"wires":[]},{"id":"903a6ab8.4f4ca8","type":"ui_group","name":"Home","tab":"e2e6f4f5.56f91","order":1,"disp":false,"width":"6","collapse":false},{"id":"e2e6f4f5.56f91","type":"ui_tab","name":"Homet","icon":"dashboard","order":3,"disabled":false,"hidden":false}]
8

Colin,
Thank you. I will try this out and respond. From my initial look at this I believe this is what I was looking for but would like to test it.

9

Colin,
I think I am very close based on your suggestion; however, how do I grab specific values from the array? When I do {{msg.payload["1"]}} for the Label I get the whole array's values. I have tried various methods to get the value of that array but I'm missing something. Can you help here? Thank you.

For this graph I would like to grab value 0 of array 0 for the value and value 0 of array 1 for the label.

image
image829Ă—668 16 KB

10

I was able to work it out. This displays the value 0 of both arrays. Thanks for your help @Colin!!

**Function Node:**
var m = {};

m.data=[];
m.labels=[];
var datatemp=[]; 
msg.payload.forEach(function(row) {
        datatemp.push(parseFloat(row.DISK_SPACE_USED));
        m.labels.push(row.CUSTOMER);
});

m.data.push(datatemp);

return {payload:[m.data,m.labels]};

**Donut Graph node:**
Label: {{msg.payload[1][0]}}
Value format: {{msg.payload[0][0][0]}}

image
image830Ă—567 13.9 KB

1 Like
11

@E1cid Thank you for the reply. This would more than likely work but I wanted to try to stick with the system supplied node graphs.

12

You shouldn't need the parseFloat, I think. Assuming that the field is numeric in the database it should return a number not a string. Try it without and see.

13

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.