Creating a Chart from MSQL-Database

[tmessers]

Hi folks,

i hope you can help me.
I collect data in a mysql database.
When i read out data, the payload is in that format:

 array[99]
  [0 … 9]
     0: object
         date: "2024-02-10T23:00:00.000Z
         max_power: 0.52

    1: object
    2: object
    3: object
    4: object
    5: object
    6: object
    7: object
    8: object
    9: object
  [10 … 19]
  [20 … 29]
  [30 … 39]
  [40 … 49]
  [50 … 59]
  [60 … 69]
  [70 … 79]
  [80 … 89]
  [90 … 99]

I want to create a chart that displays the date on the x-axis and max_power on the y-axis.
What function or node do i need, to transform the data in a format that can be used by a chart node?
I read a lot about about that issue i this forum and others, but i did not get the conclusion.

Thanks for your help.

Greetings

Thomas

[raffaele]

I use MS SQL but the output of the connection node is the same.
I Work with the function node and use the following code (translate with your variables)

var data=[];
var series= ["max_power"];
var labels = [];
var labelX= "";
var labelY= "";

msg.payload.forEach(function(value) {
    data.push(value["max_power"]);
    labels.push(value['date']);
});

data=[data];

msg.payload=[{series,data,labels}];
msg.ui_control = {
    options:{
        scales:{
            yAxes: [{
                scaleLabel:{
                    display:true,
                    labelString: "Max Power"
                }
                            }],
            xAxes: [{
                //type: 'time',
                scaleLabel: {
                    display: true,
                    labelString: "Date"
                }
            }],
        }
    }
}

This work fine with chart in Dashboard 1.0.
Regards

[Steve-Mcl]

Hi, welcome to the forum.

Here is an existing working demo

I need help, chart-ui from select sql server

Here is a dynamic solution that works for most SQL data arrays... I have adapted it to show you how it can work for you... [image] [{"id":"a80817a3d567f813","type":"inject","z":"553814a2.1248ec","name":"Fake DB data","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"LocalCol\":\"2020-11-18 18:25:48.906\",\"PS3 Output Temp\":21.3},{\"LocalCol\":\"2020-11-18 18:16:23.957\",\"PS3 Output Temp\":21.6},{\"LocalCol\":…

---

Some notes on your posting.

How to copy data from node-red debug for posting on forum

There’s a great page in the docs (Working with messages : Node-RED) that will explain how to use the debug panel to find the right path/value for any data item.

Pay particular attention to the part about the buttons that appear under your mouse pointer when you over hover a debug message property in the sidebar.

How to post data/flows to the forum so that is is both readable and useable

In order to make code readable and usable it is necessary to surround your code with three backticks (also known as a left quote or backquote ```)

``` 
   code goes here 
```

You can edit and correct your post by clicking the pencil icon.

See this post for more details - How to share code or flow json

[jbudd]

Note that the two examples above are for the original dashboard node-red-dashboard.

There is a newer dashboard @flowfuse/node-red-dashboard.

If that's the version you installed you'll need to search the forum for dashboard-2 examples from within the last year. The ones above won't work.

[E1cid]

If you are using @flowforge-dashboad (Dashboard 2) then you do not need to format the sql return. Except I believe the timestamp has to be unix millisecond timestamp, which of course you can have the sql request return. Chart ui-chart | Node-RED Dashboard 2.0

If you are still using the Angular node-red-dashboad then you have to use the format for data here. You can also have the sql query return the data in the [{"x":1234567891234", "y": 24.6}] format using AS key word syntax.

[jbudd]

There is an example of SQL database (not specifically MSSQL) to Dashboard v1 chart here

[joepavitt]

@E1cid

If you are using @flowforge-dashboad (Dashboard 2) then you do not need to format the sql return. Except I believe the timestamp has to be unix millisecond timestamp, which of course you can have the sql request return

Just emphasising this - use Dashboard 2.0 - the original Dashboard is now deprecated.

It'll save you have to do any data transforms at all, as you can just use "Key Mapping" options on the ui-chart:

No need to modify your date structure, that should just work as you have it.

[tmessers]

Hallo Raffaele, you were right, it worked perfectly on Dashboard 1.0.Thanks

[tmessers]

Dear Steve, thanks for your recommendations on posting into the forum.