# Creating a Chart from MSQL-Database

**URL:** <https://discourse.nodered.org/t/creating-a-chart-from-msql-database/90122>\
**Category:** Dashboard\
**Created:** [12 August 2024 10:29 UTC](https://discourse.nodered.org/t/creating-a-chart-from-msql-database/90122 "2024-08-12T10:29:57Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![tmessers](https://avatars.discourse-cdn.com/v4/letter/t/df705f/32.png) [@tmessers](https://discourse.nodered.org/u/tmessers)\
**Post date:** [12 August 2024 10:29 UTC](https://discourse.nodered.org/t/creating-a-chart-from-msql-database/90122/1 "2024-08-12T10:29:57Z")

</div>

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:

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

---

<div class="post-metadata">

**Author:** ![raffaele](https://avatars.discourse-cdn.com/v4/letter/r/90db22/32.png) [@raffaele](https://discourse.nodered.org/u/raffaele)\
**Post date:** [12 August 2024 10:38 UTC](https://discourse.nodered.org/t/creating-a-chart-from-msql-database/90122/2 "2024-08-12T10:38:57Z")

</div>

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)

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

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [12 August 2024 10:41 UTC](https://discourse.nodered.org/t/creating-a-chart-from-msql-database/90122/3 "2024-08-12T10:41:22Z")

</div>

Hi, welcome to the forum.

Here is an existing working demo

> [@I need help, chart-ui from select sql server](https://discourse.nodered.org/t/i-need-help-chart-ui-from-select-sql-server/46633/31):
>
> 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](https://nodered.org/docs/user-guide/messages)) 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.

![BX00Cy7yHi](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/d/bd1f333a9e043b061b39917c328b0094d3e52d30.gif)

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

````auto
``` 
   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](https://discourse.nodered.org/t/how-to-share-code-or-flow-json/506)

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [12 August 2024 10:57 UTC](https://discourse.nodered.org/t/creating-a-chart-from-msql-database/90122/4 "2024-08-12T10:57:16Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [12 August 2024 11:01 UTC](https://discourse.nodered.org/t/creating-a-chart-from-msql-database/90122/5 "2024-08-12T11:01:18Z")

</div>

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](https://dashboard.flowfuse.com/nodes/widgets/ui-chart.html)

If you are still using the Angular node-red-dashboad then you have to use the format for data [here](https://github.com/node-red/node-red-dashboard/blob/master/Charts.md). You can also have the sql query return the data in the `[{"x":1234567891234", "y": 24.6}]` format using `AS key word` [syntax](https://www.w3schools.com/sql/sql_ref_as.asp).

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [12 August 2024 12:16 UTC](https://discourse.nodered.org/t/creating-a-chart-from-msql-database/90122/6 "2024-08-12T12:16:10Z")

</div>

There is an example of SQL database (not specifically MSSQL) to Dashboard v1 chart [here](https://discourse.nodered.org/t/convert-object-from-sqlite-to-use-in-line-graph/85906/2)

---

<div class="post-metadata">

**Author:** ![joepavitt](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/joepavitt/32/59722_2.png) [@joepavitt](https://discourse.nodered.org/u/joepavitt)\
**Post date:** [13 August 2024 09:11 UTC](https://discourse.nodered.org/t/creating-a-chart-from-msql-database/90122/7 "2024-08-13T09:11:50Z")

</div>

> [@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](https://dashboard.flowfuse.com/) - 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`:

 ![Screenshot 2024-08-13 at 10.11.25](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/a/4/a4eacfab741257fc70d07733f8f449d923a53094.png)

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

---

<div class="post-metadata">

**Author:** ![tmessers](https://avatars.discourse-cdn.com/v4/letter/t/df705f/32.png) [@tmessers](https://discourse.nodered.org/u/tmessers)\
**Post date:** [13 August 2024 21:36 UTC](https://discourse.nodered.org/t/creating-a-chart-from-msql-database/90122/8 "2024-08-13T21:36:58Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![tmessers](https://avatars.discourse-cdn.com/v4/letter/t/df705f/32.png) [@tmessers](https://discourse.nodered.org/u/tmessers)\
**Post date:** [13 August 2024 21:38 UTC](https://discourse.nodered.org/t/creating-a-chart-from-msql-database/90122/9 "2024-08-13T21:38:03Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [12 September 2024 21:38 UTC](https://discourse.nodered.org/t/creating-a-chart-from-msql-database/90122/10 "2024-09-12T21:38:52Z")

</div>

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