Reporting and Graph

General
1

Hi
I have two basic requirements which I am not able to do it. I will list my issues in points.

  1. I want to create a report. I can log data from various sensors to MSSQL and from MSSQL to excel file using node-red successfully. I want to filter the data based on sensor tags and save it separate sheets of the excel file. For example : Sensor 1 values with time stamp should be filtered and saved to sheet 1 and Sensor 2 values with timestamp should be filtered and saved to sheet 2 from MSSQL and so on. I can filter the data from MSSQL but I am not able to create different sheets for different tags.

  2. I want to create a real time graph between temperature and pressure. Both the parameters is a real time parameters. I do not want to have time in the X-axis.

If I am able to create these two things the major task of my project will be complete. Please help me to achieve this.

2

I don't think there is an easy way of creating separate sheets in an excel file with the currently available nodes.

Point 2 you asked the same question 13 days ago, and received a reply that explained how to do it Graph or chart for real time variables

I moved your post into the #general as it wasn't a feature request

3

Rather than trying to do this directly with Excel, it is very likely to be easier to use 2 separate CSV files and then create an Excel workbook that uses PowerQuery to combine the CSV files automatically.

Otherwise you are going to need something that is able to programmatically control Excel.

You have a couple of options:

  • Create an Excel workbook that queries MSSQL - this is by far the easiest approach.
  • Call out to PowerShell from Node-RED on a device with Excel installed. You can easily use powershell to query MSSQL and build the results into Excel.

You could, of course, do this with Node-RED and Dashboard alone. We would need to understand more about the data and how you want it to look in order to be able to help further.

You could also do this charting in Excel of course. You could also probably use something like Grafana.

4

I checked Grafana and it looks interesting. Thank you for that.

The graph which I want to look is a real time graph between temperature and pressure and both these data are coming from PLC. I am checking the behavior of the system in real time.

5

Well real-time means different things to different people of course. To get closest to real real-time, you should receive the data direct from the devices rather than via a db. If I had the choice of architecture, I would probably choose to send from the devices direct to MQTT and use Node-RED to both display the dashboard and forward to a db for historic data using InfluxDB as the data store as this is better than MSSQL for time series data.

6

Currently I am receiving data directly from my PLC devices and then I am storing it to MSSQL. But node-red doesn't give any option to plot the real time data for two different parameters( pressure vs Temperature)

7

Ah, sorry, I misunderstood.

There are several ways you could do that depending on what you already have in use.

If you are using Dashboard for other things, you can add custom code via the template node so if you can find a suitable chart library, that is easily included.

If you aren't using Dashboard, you might find it easier to use uibuilder to create a single page display. Again, using a chart library.

8

I haven't tried it, but it sounds like the flow linked in this discussion might work for you: Has anyone created a scatter/bubble plot?