Dashboard 2 Data Format

Dashboard
1

Having trouble converting payload for Dashboard 1 chart into the expected format for Dashboard 2.

[{"labels":["08 - 09","09 - 10","10 - 11","11 - 00","00 - 01","01 - 02","02 - 03","03 - 04","04 - 05","05 - 06","06 - 07","07 - 08"],"series":["Filter","Target"],"data":[[920,1108,787,1169,1264,814,1248,979,1001,1067,952,0],[1250,1250,1250,1250,1250,1250,1250,1250,1250,1250,1250,1250]]}]

This is the dashboard 1 output

image
image497×222 14.7 KB

My data comes from Sql Server DB and is formatted using a change node with jsonata expression
Example for the above payload

[
   {
       "labels":[**.HOUR],
       "series":["Filter","Target"],
       "data":[
           [**.FILTER],
            [**.TARGET]
            
       ]
    }
]

regards Gerry

2

You shouldn't need to covert the DB retunrd data for db2, just tell the chart where to look.
As you have not given an example of the db return at a guess
set series to json ["FILTER","TARGET"]
and x axis to key HOUR

3

Here's the flow if that helps

[
    {
        "id": "6548862726e9264b",
        "type": "ui_chart",
        "z": "57576b86c3754c2f",
        "name": "Hourly Previous",
        "group": "f504f60f6a6a975e",
        "order": 1,
        "width": 14,
        "height": 5,
        "label": "",
        "chartType": "line",
        "legend": "true",
        "xformat": "HH:mm:ss",
        "interpolate": "cubic",
        "nodata": "no data",
        "dot": false,
        "ymin": "0",
        "ymax": "2000",
        "removeOlder": 1,
        "removeOlderPoints": "",
        "removeOlderUnit": "3600",
        "cutout": 0,
        "useOneColor": false,
        "useUTC": false,
        "colors": [
            "#1f77b4",
            "#2ca02c",
            "#d62728",
            "#d62728",
            "#98df8a",
            "#d62728",
            "#ff9896",
            "#9467bd",
            "#c5b0d5"
        ],
        "outputs": 1,
        "useDifferentColor": false,
        "className": "",
        "x": 960,
        "y": 160,
        "wires": [
            []
        ]
    },
    {
        "id": "07dc739d0f3bc17c",
        "type": "function",
        "z": "57576b86c3754c2f",
        "name": "Stacked Bar",
        "func": "msg.ui_control = {\n     options: {\n        scales: {\n            xAxes: [{\n                stacked: false,\n                ticks: {\n                    fontColor: '#ffffff',\n                    fontSize: 8\n                }\n            }\n            ],\n            yAxes: [{\n                stacked: false,\n                ticks: {\n                    fontColor: '#ffffff'\n                }\n            }]\n        }\n    }\n}\nreturn msg;",
        "outputs": 1,
        "timeout": "",
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 770,
        "y": 160,
        "wires": [
            [
                "6548862726e9264b"
            ]
        ]
    },
    {
        "id": "93ef652db5d2dae0",
        "type": "change",
        "z": "57576b86c3754c2f",
        "name": "Hourly Outs",
        "rules": [
            {
                "t": "set",
                "p": "payload",
                "pt": "msg",
                "to": "[\t   {\t       \"labels\":[**.HOUR],\t       \"series\":[\"Filter\",\"Target\"],\t       \"data\":[\t           [**.FILTER],\t            [**.TARGET]\t       ]\t    }\t]",
                "tot": "jsonata"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 570,
        "y": 160,
        "wires": [
            [
                "07dc739d0f3bc17c",
                "851c5281e39ebf24"
            ]
        ]
    },
    {
        "id": "dd5e2f8c926d83e3",
        "type": "inject",
        "z": "57576b86c3754c2f",
        "name": "",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "[{\"SHIFT\":\"A\",\"HOUR\":\"08 - 09\",\"FILTER\":1028,\"COBRA\":0,\"HOBBES\":1028,\"TARGET\":1250,\"DEFICIT\":222,\"SURPLUS\":0,\"LOADER\":874},{\"SHIFT\":\"A\",\"HOUR\":\"09 - 10\",\"FILTER\":989,\"COBRA\":0,\"HOBBES\":989,\"TARGET\":1250,\"DEFICIT\":261,\"SURPLUS\":0,\"LOADER\":814},{\"SHIFT\":\"A\",\"HOUR\":\"10 - 11\",\"FILTER\":403,\"COBRA\":0,\"HOBBES\":403,\"TARGET\":1250,\"DEFICIT\":847,\"SURPLUS\":0,\"LOADER\":804},{\"SHIFT\":\"A\",\"HOUR\":\"11 - 00\",\"FILTER\":833,\"COBRA\":0,\"HOBBES\":833,\"TARGET\":1250,\"DEFICIT\":417,\"SURPLUS\":0,\"LOADER\":734},{\"SHIFT\":\"A\",\"HOUR\":\"00 - 01\",\"FILTER\":910,\"COBRA\":0,\"HOBBES\":910,\"TARGET\":1250,\"DEFICIT\":340,\"SURPLUS\":0,\"LOADER\":1153},{\"SHIFT\":\"A\",\"HOUR\":\"01 - 02\",\"FILTER\":901,\"COBRA\":0,\"HOBBES\":901,\"TARGET\":1250,\"DEFICIT\":349,\"SURPLUS\":0,\"LOADER\":1282},{\"SHIFT\":\"A\",\"HOUR\":\"02 - 03\",\"FILTER\":975,\"COBRA\":0,\"HOBBES\":975,\"TARGET\":1250,\"DEFICIT\":275,\"SURPLUS\":0,\"LOADER\":1233},{\"SHIFT\":\"A\",\"HOUR\":\"03 - 04\",\"FILTER\":896,\"COBRA\":998,\"HOBBES\":0,\"TARGET\":1250,\"DEFICIT\":354,\"SURPLUS\":0,\"LOADER\":1166},{\"SHIFT\":\"A\",\"HOUR\":\"04 - 05\",\"FILTER\":738,\"COBRA\":614,\"HOBBES\":124,\"TARGET\":1250,\"DEFICIT\":512,\"SURPLUS\":0,\"LOADER\":1010},{\"SHIFT\":\"A\",\"HOUR\":\"05 - 06\",\"FILTER\":386,\"COBRA\":472,\"HOBBES\":0,\"TARGET\":1250,\"DEFICIT\":864,\"SURPLUS\":0,\"LOADER\":686},{\"SHIFT\":\"A\",\"HOUR\":\"06 - 07\",\"FILTER\":630,\"COBRA\":582,\"HOBBES\":48,\"TARGET\":1250,\"DEFICIT\":620,\"SURPLUS\":0,\"LOADER\":357},{\"SHIFT\":\"A\",\"HOUR\":\"07 - 08\",\"FILTER\":548,\"COBRA\":602,\"HOBBES\":0,\"TARGET\":1250,\"DEFICIT\":702,\"SURPLUS\":0,\"LOADER\":734}]",
        "payloadType": "json",
        "x": 350,
        "y": 160,
        "wires": [
            [
                "93ef652db5d2dae0"
            ]
        ]
    },
    {
        "id": "851c5281e39ebf24",
        "type": "debug",
        "z": "57576b86c3754c2f",
        "name": "debug 71",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "statusVal": "",
        "statusType": "auto",
        "x": 840,
        "y": 260,
        "wires": []
    },
    {
        "id": "f504f60f6a6a975e",
        "type": "ui_group",
        "name": "D1 Flow",
        "tab": "48ea50b352b56f26",
        "order": 1,
        "disp": false,
        "width": "30",
        "collapse": false,
        "className": ""
    },
    {
        "id": "48ea50b352b56f26",
        "type": "ui_tab",
        "name": "D1 Flow",
        "icon": "dashboard",
        "disabled": false,
        "hidden": false
    }
]
4

Ok so I tried that. I'm getting the series legends but no data. I have labels as the key for x-axis. These are strings "08 - 09" "09 - 10" etc so maybe that causes an issue?

image
image919×530 12.5 KB

5

Seems to be an issue with the HOUR data as it is not a timestamp.
You may need to return a timestamp from the DB and then show a timescale chart and format the X axis to display the hour.

Unless someone more knowledgeable in DB2 knows how to display a liner chart with text labels.

6

I changed my payload to have a timestamp instead of text, but still no data.
This is my chart setup

image
image820×790 19.7 KB

Possibly my payload needs a flatter structure. I was hoping that my existing payloads would work with maybe an additional change node or function node. I'll continue to play around with the structure, but thanks for your input

regards Gerry

7

Make sure you reload the dashboard page after any change in the chart config. Ctrl-F5 is a good idea.

Can you share a sample (just a few records) of the output from your SQL request?

8

Payload from database

[{"SHIFT":"B","HOUR":"2024-12-11T08:00:00Z","FILTER":920,"COBRA":908,"HOBBES":12,"TARGET":1250,"DEFICIT":330,"SURPLUS":0,"LOADER":1300},{"SHIFT":"B","HOUR":"2024-12-11T09:00:00Z","FILTER":1108,"COBRA":1112,"HOBBES":0,"TARGET":1250,"DEFICIT":142,"SURPLUS":0,"LOADER":1233},{"SHIFT":"B","HOUR":"2024-12-11T10:00:00Z","FILTER":787,"COBRA":722,"HOBBES":65,"TARGET":1250,"DEFICIT":463,"SURPLUS":0,"LOADER":1394},{"SHIFT":"B","HOUR":"2024-12-11T11:00:00Z","FILTER":1169,"COBRA":1178,"HOBBES":0,"TARGET":1250,"DEFICIT":81,"SURPLUS":0,"LOADER":1316},{"SHIFT":"B","HOUR":"2024-12-11T12:00:00Z","FILTER":1264,"COBRA":1340,"HOBBES":0,"TARGET":1250,"DEFICIT":0,"SURPLUS":14,"LOADER":1477},{"SHIFT":"B","HOUR":"2024-12-11T13:00:00Z","FILTER":814,"COBRA":780,"HOBBES":34,"TARGET":1250,"DEFICIT":436,"SURPLUS":0,"LOADER":1638},{"SHIFT":"B","HOUR":"2024-12-11T14:00:00Z","FILTER":1248,"COBRA":1220,"HOBBES":28,"TARGET":1250,"DEFICIT":2,"SURPLUS":0,"LOADER":1523},{"SHIFT":"B","HOUR":"2024-12-11T15:00:00Z","FILTER":979,"COBRA":1020,"HOBBES":0,"TARGET":1250,"DEFICIT":271,"SURPLUS":0,"LOADER":1429},{"SHIFT":"B","HOUR":"2024-12-11T16:00:00Z","FILTER":1001,"COBRA":926,"HOBBES":75,"TARGET":1250,"DEFICIT":249,"SURPLUS":0,"LOADER":859},{"SHIFT":"B","HOUR":"2024-12-11T17:00:00Z","FILTER":1067,"COBRA":974,"HOBBES":93,"TARGET":1250,"DEFICIT":183,"SURPLUS":0,"LOADER":1146},{"SHIFT":"B","HOUR":"2024-12-11T18:00:00Z","FILTER":952,"COBRA":776,"HOBBES":176,"TARGET":1250,"DEFICIT":298,"SURPLUS":0,"LOADER":1142},{"SHIFT":"B","HOUR":"2024-12-11T19:00:00Z","FILTER":940,"COBRA":1066,"HOBBES":0,"TARGET":1250,"DEFICIT":310,"SURPLUS":0,"LOADER":970}]

Output from change node to chart

[{"series":["Filter","Target"],"labels":["2024-12-11T08:00:00Z","2024-12-11T09:00:00Z","2024-12-11T10:00:00Z","2024-12-11T11:00:00Z","2024-12-11T12:00:00Z","2024-12-11T13:00:00Z","2024-12-11T14:00:00Z","2024-12-11T15:00:00Z","2024-12-11T16:00:00Z","2024-12-11T17:00:00Z","2024-12-11T18:00:00Z","2024-12-11T19:00:00Z"],"data":[[920,1108,787,1169,1264,814,1248,979,1001,1067,952,940],[1250,1250,1250,1250,1250,1250,1250,1250,1250,1250,1250,1250]]}]
9

I believe that your change node is redundant for Dashboard 2 charts.

Feed the query output directly to a chart node like this

image
image668×510 22.4 KB

image
image504×144 7.96 KB

[{"id":"f067bb7edaea7365","type":"inject","z":"d696ba8a9c4044d5","name":"db output array","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"SHIFT\":\"B\",\"HOUR\":\"2024-12-11T08:00:00Z\",\"FILTER\":920,\"COBRA\":908,\"HOBBES\":12,\"TARGET\":1250,\"DEFICIT\":330,\"SURPLUS\":0,\"LOADER\":1300}, {\"SHIFT\":\"B\",\"HOUR\":\"2024-12-11T09:00:00Z\",\"FILTER\":1108,\"COBRA\":1112,\"HOBBES\":0,\"TARGET\":1250,\"DEFICIT\":142,\"SURPLUS\":0,\"LOADER\":1233}, {\"SHIFT\":\"B\",\"HOUR\":\"2024-12-11T10:00:00Z\",\"FILTER\":787,\"COBRA\":722,\"HOBBES\":65,\"TARGET\":1250,\"DEFICIT\":463,\"SURPLUS\":0,\"LOADER\":1394}, {\"SHIFT\":\"B\",\"HOUR\":\"2024-12-11T11:00:00Z\",\"FILTER\":1169,\"COBRA\":1178,\"HOBBES\":0,\"TARGET\":1250,\"DEFICIT\":81,\"SURPLUS\":0,\"LOADER\":1316}, {\"SHIFT\":\"B\",\"HOUR\":\"2024-12-11T12:00:00Z\",\"FILTER\":1264,\"COBRA\":1340,\"HOBBES\":0,\"TARGET\":1250,\"DEFICIT\":0,\"SURPLUS\":14,\"LOADER\":1477}, {\"SHIFT\":\"B\",\"HOUR\":\"2024-12-11T13:00:00Z\",\"FILTER\":814,\"COBRA\":780,\"HOBBES\":34,\"TARGET\":1250,\"DEFICIT\":436,\"SURPLUS\":0,\"LOADER\":1638}, {\"SHIFT\":\"B\",\"HOUR\":\"2024-12-11T14:00:00Z\",\"FILTER\":1248,\"COBRA\":1220,\"HOBBES\":28,\"TARGET\":1250,\"DEFICIT\":2,\"SURPLUS\":0,\"LOADER\":1523}, {\"SHIFT\":\"B\",\"HOUR\":\"2024-12-11T15:00:00Z\",\"FILTER\":979,\"COBRA\":1020,\"HOBBES\":0,\"TARGET\":1250,\"DEFICIT\":271,\"SURPLUS\":0,\"LOADER\":1429}, {\"SHIFT\":\"B\",\"HOUR\":\"2024-12-11T16:00:00Z\",\"FILTER\":1001,\"COBRA\":926,\"HOBBES\":75,\"TARGET\":1250,\"DEFICIT\":249,\"SURPLUS\":0,\"LOADER\":859}, {\"SHIFT\":\"B\",\"HOUR\":\"2024-12-11T17:00:00Z\",\"FILTER\":1067,\"COBRA\":974,\"HOBBES\":93,\"TARGET\":1250,\"DEFICIT\":183,\"SURPLUS\":0,\"LOADER\":1146}, {\"SHIFT\":\"B\",\"HOUR\":\"2024-12-11T18:00:00Z\",\"FILTER\":952,\"COBRA\":776,\"HOBBES\":176,\"TARGET\":1250,\"DEFICIT\":298,\"SURPLUS\":0,\"LOADER\":1142}, {\"SHIFT\":\"B\",\"HOUR\":\"2024-12-11T19:00:00Z\",\"FILTER\":940,\"COBRA\":1066,\"HOBBES\":0,\"TARGET\":1250,\"DEFICIT\":310,\"SURPLUS\":0,\"LOADER\":970}]","payloadType":"json","x":140,"y":160,"wires":[["196ca8e7af4ede55"]]},{"id":"196ca8e7af4ede55","type":"ui-chart","z":"d696ba8a9c4044d5","group":"77a183e6f60a2085","name":"Chart","label":"chart","order":1,"chartType":"line","category":"[\"FILTER\", \"TARGET\"]","categoryType":"json","xAxisLabel":"","xAxisProperty":"HOUR","xAxisPropertyType":"property","xAxisType":"time","xAxisFormat":"","xAxisFormatType":"auto","xmin":"","xmax":"","yAxisLabel":"","yAxisProperty":"payload","yAxisPropertyType":"msg","ymin":"","ymax":"","bins":10,"action":"replace","stackSeries":false,"pointShape":"circle","pointRadius":4,"showLegend":true,"removeOlder":1,"removeOlderUnit":"604800","removeOlderPoints":"","colors":["#0095ff","#ff0000","#ff7f0e","#2ca02c","#a347e1","#d62728","#ff9896","#9467bd","#c5b0d5"],"textColor":["#666666"],"textColorDefault":true,"gridColor":["#e5e5e5"],"gridColorDefault":true,"width":"4","height":"6","className":"","interpolation":"bezier","x":310,"y":160,"wires":[[]]},{"id":"41624380a6a26da9","type":"inject","z":"d696ba8a9c4044d5","name":"Clear","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[]","payloadType":"json","x":170,"y":120,"wires":[["196ca8e7af4ede55"]]},{"id":"77a183e6f60a2085","type":"ui-group","name":"Demo","page":"e81fa6389c042304","width":"12","height":"1","order":1,"showTitle":true,"className":"","visible":"true","disabled":"false","groupType":"default"},{"id":"e81fa6389c042304","type":"ui-page","name":"Demo","ui":"d45641ed6d39fea1","path":"/demo","icon":"home","layout":"grid","theme":"0d92c765bfad87e6","breakpoints":[{"name":"Default","px":"0","cols":"3"},{"name":"Tablet","px":"576","cols":"6"},{"name":"Small Desktop","px":"768","cols":"9"},{"name":"Desktop","px":"1024","cols":"12"}],"order":1,"className":"","visible":"true","disabled":"false"},{"id":"d45641ed6d39fea1","type":"ui-base","name":"This is my ui-base","path":"/dashboard","appIcon":"","includeClientData":true,"acceptsClientConfig":["ui-notification","ui-control"],"showPathInSidebar":false,"showPageTitle":true,"navigationStyle":"default","titleBarStyle":"default"},{"id":"0d92c765bfad87e6","type":"ui-theme","name":"Basic Blue Theme","colors":{"surface":"#4d58ff","primary":"#0094ce","bgPage":"#eeeeee","groupBg":"#ffffff","groupOutline":"#cccccc"},"sizes":{"pagePadding":"12px","groupGap":"12px","groupBorderRadius":"4px","widgetGap":"2px","density":"default"}}]

image
image629×421 19.8 KB

If you find that this example does not work for you, ensure that you have @flowfuse/node-red-dashboard v1.20.

10

Hi
Yes Dashboard version is 1.20
Set the hour to integer 8,9,10 etc. and x-axis type to Linear
Getting closer to my destination. Thanks for all the help.

regards Gerry

image
image944×515 18.3 KB