Draw a chart based on data from a SQL named query

Hi All,
Am using Ignition 8.1.21 and i have spent 40 hrs of reading in inductive university and i have no clue of how to bind a Query with a Chart.

my ask is simple. I have a query below is screen print.
Query

and what i want is
needed

i have a date column in my dataset there is no way of mapping them in chart. Really frustrating

That seems unlikely as the current release is 8.1.16.

Your screen print doesn’t have a date field so there’s one problem.

Try this.

  1. In props.dataSources add a new dataSource array (or re-use the example dataSource).
  2. Bind that array to your query. You should now see that array get populated. Expand it and check that the data looks correct.
  3. Set props.series.0.data.source to your dataSource array name.
  4. Set props.series.0.data.source.x to your dataSource date/time column name.
  5. Set props.series.0.data.source.y to the name of the dataSource column you want displayed on this plot.
  6. props.series.0.data.source.xAxis should refer to the name set up in props.xAxes.0.name.
  7. props.series.0.data.source.yAxis should refer to the name set up in props.yAxes.0.name.

When you’re reporting back please make it clear whether or not your dataSource populates as expected from the binding. If you’re posting code then please use the </> code formatting button to format it properly.

Apologies missed the Date column in the previous screen print.

Here is the screen shot of the data set that am working on has a date column (Sample date)
Capture

I tried the exact steps that you explained me, but i was not able to replicate the same. Am fairly new to ignition itself.

Now how do i extract the first column of my table as my X axis and all other values needs to be a pen on the x- y axis.

Is there a step by step doc that explain starting from step zero. or can you help me over a call.

Thanks ,
Abi .

No, we don’t do support calls. (I don’t work for IA.) Problems should solved on the forum so that everyone can benefit.

Find your XY Chart in the project tree, right-click and paste it into your post here. Then select the whole lot and apply the </> code formatting / syntax highlighting. Then select the whole lot again and click the gear icon and apply the Hide Details formatting and you’ll get a neat listing similar to the “Summary” below. Then your readers can paste your code into their own designer and inspect your code.

Summary
[
  {
    "type": "ia.chart.xy",
    "version": 0,
    "props": {
      "xAxes": [
        {
          "name": "time",
          "label": {
            "enabled": true,
            "text": "Time",
            "color": ""
          },
          "visible": true,
          "tooltip": {
            "enabled": true,
            "text": "",
            "cornerRadius": 3,
            "pointerLength": 4,
            "background": {
              "color": "",
              "opacity": 1
            }
          },
          "inversed": false,
          "render": "date",
          "category": {
            "break": {
              "enabled": false,
              "startCategory": "",
              "endCategory": "",
              "size": 0.05
            }
          },
          "date": {
            "baseInterval": {
              "enabled": false,
              "timeUnit": "hour",
              "count": 1,
              "skipEmptyPeriods": false
            },
            "range": {
              "max": "",
              "min": "",
              "useStrict": false
            },
            "break": {
              "enabled": false,
              "startDate": "",
              "endDate": "",
              "size": 0.05
            },
            "inputFormat": "yyyy-MM-dd kk:mm:ss",
            "format": "M/d"
          },
          "value": {
            "range": {
              "max": "",
              "min": "",
              "useStrict": false
            },
            "logarithmic": false,
            "break": {
              "enabled": false,
              "startValue": 0,
              "endValue": 100,
              "size": 0.05
            },
            "format": "#,###.##"
          },
          "appearance": {
            "opposite": false,
            "inside": false,
            "labels": {
              "color": "",
              "opacity": 1
            },
            "grid": {
              "color": "",
              "opacity": 1,
              "dashArray": "",
              "minDistance": 60,
              "position": 0.5
            },
            "font": {
              "size": "",
              "weight": 500
            }
          }
        }
      ],
      "yAxes": [
        {
          "name": "process temp",
          "label": {
            "enabled": true,
            "text": "Process Temp",
            "color": ""
          },
          "visible": true,
          "tooltip": {
            "enabled": true,
            "text": "",
            "cornerRadius": 3,
            "pointerLength": 4,
            "background": {
              "color": "",
              "opacity": 1
            }
          },
          "inversed": false,
          "render": "value",
          "category": {
            "break": {
              "enabled": false,
              "startCategory": "",
              "endCategory": "",
              "size": 0.05
            }
          },
          "date": {
            "baseInterval": {
              "enabled": false,
              "timeUnit": "hour",
              "count": 1,
              "skipEmptyPeriods": false
            },
            "range": {
              "max": "",
              "min": "",
              "useStrict": false
            },
            "break": {
              "enabled": false,
              "startDate": "",
              "endDate": "",
              "size": 0.05
            },
            "inputFormat": "yyyy-MM-dd kk:mm:ss",
            "format": "M/d/yyyy HH:mm:ss"
          },
          "value": {
            "range": {
              "max": "",
              "min": "",
              "useStrict": false
            },
            "logarithmic": false,
            "break": {
              "enabled": false,
              "startValue": 0,
              "endValue": 100,
              "size": 0.05
            },
            "format": "#,###.##"
          },
          "appearance": {
            "opposite": false,
            "inside": false,
            "labels": {
              "color": "",
              "opacity": 1
            },
            "grid": {
              "color": "",
              "opacity": 1,
              "dashArray": "",
              "minDistance": null,
              "position": 0.5
            },
            "font": {
              "size": "",
              "weight": 500
            }
          }
        },
        {
          "name": "output temp",
          "label": {
            "enabled": true,
            "text": "Output Temp",
            "color": ""
          },
          "visible": true,
          "inversed": false,
          "tooltip": {
            "enabled": true,
            "text": "",
            "cornerRadius": 3,
            "pointerLength": 4,
            "background": {
              "color": "",
              "opacity": 1
            }
          },
          "render": "value",
          "category": {
            "break": {
              "enabled": false,
              "startCategory": "",
              "endCategory": "",
              "size": 0.05
            }
          },
          "date": {
            "baseInterval": {
              "enabled": false,
              "timeUnit": "hour",
              "count": 1,
              "skipEmptyPeriods": false
            },
            "range": {
              "max": "",
              "min": "",
              "useStrict": false
            },
            "break": {
              "enabled": false,
              "startDate": "",
              "endDate": "",
              "size": 0.05
            },
            "inputFormat": "yyyy-MM-dd kk:mm:ss",
            "format": "M/d/yyyy"
          },
          "value": {
            "range": {
              "min": "",
              "max": "",
              "useStrict": false
            },
            "logarithmic": false,
            "break": {
              "enabled": false,
              "startValue": 0,
              "endValue": 100,
              "size": 0.05
            },
            "format": "#,###.##"
          },
          "appearance": {
            "opposite": true,
            "inside": false,
            "labels": {
              "color": "",
              "opacity": 1
            },
            "grid": {
              "color": "",
              "opacity": 1,
              "dashArray": "4,4",
              "minDistance": null,
              "position": 0.5
            },
            "font": {
              "size": "",
              "weight": 500
            }
          }
        }
      ],
      "series": [
        {
          "name": "process temp",
          "label": {
            "text": "Process Temp"
          },
          "visible": true,
          "hiddenInLegend": false,
          "defaultState": {
            "visible": true
          },
          "data": {
            "source": "example",
            "x": "t_stamp",
            "y": "process_temp"
          },
          "xAxis": "time",
          "yAxis": "process temp",
          "zIndex": 0,
          "tooltip": {
            "enabled": true,
            "text": "{name}: [bold]{valueY}[/]",
            "cornerRadius": 3,
            "pointerLength": 4,
            "background": {
              "color": "",
              "opacity": 1
            }
          },
          "render": "line",
          "candlestick": {
            "open": {
              "x": "",
              "y": ""
            },
            "high": {
              "x": "",
              "y": ""
            },
            "low": {
              "x": "",
              "y": ""
            },
            "appearance": {
              "fill": {
                "color": "",
                "opacity": 1
              },
              "stroke": {
                "color": "",
                "opacity": 1,
                "width": 1
              },
              "stacked": false,
              "deriveFieldsFromData": {
                "fill": {
                  "color": "",
                  "opacity": ""
                },
                "stroke": {
                  "color": "",
                  "opacity": "",
                  "width": ""
                }
              },
              "heatRules": {
                "enabled": false,
                "max": "",
                "min": "",
                "dataField": ""
              }
            }
          },
          "column": {
            "open": {
              "x": "",
              "y": ""
            },
            "appearance": {
              "fill": {
                "color": "",
                "opacity": 1
              },
              "stroke": {
                "color": "",
                "opacity": 1,
                "width": 1
              },
              "stacked": false,
              "width": null,
              "height": null,
              "deriveFieldsFromData": {
                "fill": {
                  "color": "",
                  "opacity": ""
                },
                "stroke": {
                  "color": "",
                  "opacity": "",
                  "width": ""
                }
              },
              "heatRules": {
                "enabled": false,
                "max": "",
                "min": "",
                "dataField": ""
              }
            }
          },
          "line": {
            "open": {
              "x": "",
              "y": ""
            },
            "appearance": {
              "connect": true,
              "tensionX": 1,
              "tensionY": 1,
              "minDistance": 0.5,
              "stroke": {
                "width": 3,
                "opacity": 1,
                "color": "",
                "dashArray": ""
              },
              "fill": {
                "opacity": 0,
                "color": ""
              },
              "bullets": [
                {
                  "enabled": false,
                  "render": "circle",
                  "width": 10,
                  "height": 10,
                  "label": {
                    "text": "{value}",
                    "position": {
                      "dx": 0,
                      "dy": 0
                    }
                  },
                  "fill": {
                    "color": "",
                    "opacity": 1
                  },
                  "stroke": {
                    "color": "",
                    "opacity": 1,
                    "width": 1
                  },
                  "rotation": 0,
                  "tooltip": {
                    "enabled": true,
                    "text": "{name}: [bold]{valueY}[/]",
                    "cornerRadius": 3,
                    "pointerLength": 4,
                    "background": {
                      "color": "",
                      "opacity": 1
                    }
                  },
                  "deriveFieldsFromData": {
                    "fill": {
                      "color": "",
                      "opacity": ""
                    },
                    "stroke": {
                      "color": "",
                      "opacity": "",
                      "width": ""
                    },
                    "rotation": ""
                  },
                  "heatRules": {
                    "enabled": false,
                    "max": 100,
                    "min": 2,
                    "dataField": ""
                  }
                }
              ]
            }
          },
          "stepLine": {
            "open": {
              "x": "",
              "y": ""
            },
            "appearance": {
              "connect": true,
              "tensionX": 1,
              "tensionY": 1,
              "minDistance": 0.5,
              "stroke": {
                "width": 3,
                "opacity": 1,
                "color": "",
                "dashArray": ""
              },
              "fill": {
                "opacity": 0,
                "color": ""
              },
              "bullets": [
                {
                  "enabled": true,
                  "render": "circle",
                  "width": 10,
                  "height": 10,
                  "label": {
                    "text": "{value}",
                    "position": {
                      "dx": 0,
                      "dy": 0
                    }
                  },
                  "fill": {
                    "color": "",
                    "opacity": 1
                  },
                  "stroke": {
                    "color": "",
                    "opacity": 1,
                    "width": 1
                  },
                  "rotation": 0,
                  "tooltip": {
                    "enabled": true,
                    "text": "{name}: [bold]{valueY}[/]",
                    "cornerRadius": 3,
                    "pointerLength": 4,
                    "background": {
                      "color": "",
                      "opacity": 1
                    }
                  },
                  "deriveFieldsFromData": {
                    "fill": {
                      "color": "",
                      "opacity": ""
                    },
                    "stroke": {
                      "color": "",
                      "opacity": "",
                      "width": ""
                    },
                    "rotation": ""
                  },
                  "heatRules": {
                    "enabled": false,
                    "max": 100,
                    "min": 2,
                    "dataField": ""
                  }
                }
              ]
            }
          }
        },
        {
          "name": "output temp",
          "label": {
            "text": "Output Temp"
          },
          "visible": true,
          "hiddenInLegend": false,
          "defaultState": {
            "visible": true
          },
          "data": {
            "source": "example",
            "x": "t_stamp",
            "y": "output_temp"
          },
          "xAxis": "time",
          "yAxis": "output temp",
          "zIndex": 0,
          "tooltip": {
            "enabled": true,
            "text": "{name}: [bold]{valueY}[/]",
            "cornerRadius": 3,
            "pointerLength": 4,
            "background": {
              "color": "",
              "opacity": 1
            }
          },
          "render": "line",
          "candlestick": {
            "open": {
              "x": "",
              "y": ""
            },
            "high": {
              "x": "",
              "y": ""
            },
            "low": {
              "x": "",
              "y": ""
            },
            "appearance": {
              "fill": {
                "color": "",
                "opacity": 1
              },
              "stroke": {
                "color": "",
                "opacity": 1,
                "width": 1
              },
              "stacked": false,
              "deriveFieldsFromData": {
                "fill": {
                  "color": "",
                  "opacity": ""
                },
                "stroke": {
                  "color": "",
                  "opacity": "",
                  "width": ""
                }
              },
              "heatRules": {
                "enabled": false,
                "max": "",
                "min": "",
                "dataField": ""
              }
            }
          },
          "column": {
            "open": {
              "x": "",
              "y": ""
            },
            "appearance": {
              "fill": {
                "color": "",
                "opacity": 1
              },
              "stroke": {
                "color": "",
                "opacity": 1,
                "width": 1
              },
              "stacked": false,
              "width": null,
              "height": null,
              "deriveFieldsFromData": {
                "fill": {
                  "color": "",
                  "opacity": ""
                },
                "stroke": {
                  "color": "",
                  "opacity": "",
                  "width": ""
                }
              },
              "heatRules": {
                "enabled": false,
                "max": "",
                "min": "",
                "dataField": ""
              }
            }
          },
          "line": {
            "open": {
              "x": "",
              "y": ""
            },
            "appearance": {
              "connect": true,
              "tensionX": 1,
              "tensionY": 1,
              "minDistance": 0.5,
              "fill": {
                "opacity": 0,
                "color": ""
              },
              "stroke": {
                "width": 3,
                "opacity": 1,
                "color": "",
                "dashArray": ""
              },
              "bullets": [
                {
                  "enabled": false,
                  "render": "circle",
                  "width": 10,
                  "height": 10,
                  "label": {
                    "text": "{value}",
                    "position": {
                      "dx": 0,
                      "dy": 0
                    }
                  },
                  "fill": {
                    "color": "",
                    "opacity": 1
                  },
                  "stroke": {
                    "color": "",
                    "opacity": 1,
                    "width": 1
                  },
                  "rotation": 0,
                  "tooltip": {
                    "enabled": true,
                    "text": "{name}: [bold]{valueY}[/]",
                    "cornerRadius": 3,
                    "pointerLength": 4,
                    "background": {
                      "color": "",
                      "opacity": 1
                    }
                  },
                  "deriveFieldsFromData": {
                    "fill": {
                      "color": "",
                      "opacity": ""
                    },
                    "stroke": {
                      "color": "",
                      "opacity": "",
                      "width": ""
                    },
                    "rotation": ""
                  },
                  "heatRules": {
                    "enabled": false,
                    "max": 100,
                    "min": 2,
                    "dataField": ""
                  }
                }
              ]
            }
          },
          "stepLine": {
            "open": {
              "x": "",
              "y": ""
            },
            "appearance": {
              "connect": true,
              "tensionX": 1,
              "tensionY": 1,
              "minDistance": 0.5,
              "fill": {
                "opacity": 0,
                "color": ""
              },
              "stroke": {
                "width": 3,
                "opacity": 1,
                "color": "",
                "dashArray": ""
              },
              "bullets": [
                {
                  "enabled": true,
                  "render": "circle",
                  "width": 10,
                  "height": 10,
                  "label": {
                    "text": "{value}",
                    "position": {
                      "dx": 0,
                      "dy": 0
                    }
                  },
                  "fill": {
                    "color": "",
                    "opacity": 1
                  },
                  "stroke": {
                    "color": "",
                    "opacity": 1,
                    "width": 1
                  },
                  "rotation": 0,
                  "tooltip": {
                    "enabled": true,
                    "text": "{name}: [bold]{valueY}[/]",
                    "cornerRadius": 3,
                    "pointerLength": 4,
                    "background": {
                      "color": "",
                      "opacity": 1
                    }
                  },
                  "deriveFieldsFromData": {
                    "fill": {
                      "color": "",
                      "opacity": ""
                    },
                    "stroke": {
                      "color": "",
                      "opacity": "",
                      "width": ""
                    },
                    "rotation": ""
                  },
                  "heatRules": {
                    "enabled": false,
                    "max": 100,
                    "min": 2,
                    "dataField": ""
                  }
                }
              ]
            }
          }
        }
      ],
      "dataSources": {
        "example": [
          {
            "t_stamp": "Tue Aug 07 2018",
            "process_temp": 63,
            "output_temp": 38
          },
          {
            "t_stamp": "Tue Aug 08 2018",
            "process_temp": 59,
            "output_temp": 39
          },
          {
            "t_stamp": "Tue Aug 09 2018",
            "process_temp": 62,
            "output_temp": 41
          },
          {
            "t_stamp": "Tue Aug 10 2018",
            "process_temp": 63,
            "output_temp": 37
          },
          {
            "t_stamp": "Tue Aug 11 2018",
            "process_temp": 48,
            "output_temp": 31
          },
          {
            "t_stamp": "Tue Aug 12 2018",
            "process_temp": 54,
            "output_temp": 42
          },
          {
            "t_stamp": "Tue Aug 13 2018",
            "process_temp": 49,
            "output_temp": 38
          },
          {
            "t_stamp": "Tue Aug 14 2018",
            "process_temp": 69,
            "output_temp": 34
          },
          {
            "t_stamp": "Tue Aug 15 2018",
            "process_temp": 73,
            "output_temp": 56
          },
          {
            "t_stamp": "Tue Aug 16 2018",
            "process_temp": 61,
            "output_temp": 32
          },
          {
            "t_stamp": "Tue Aug 17 2018",
            "process_temp": 73,
            "output_temp": 62
          }
        ]
      }
    },
    "meta": {
      "name": "XYChart"
    },
    "position": {
      "basis": "375px"
    },
    "custom": {}
  }
]

I get what you mean, thanks for inputs, i was able to configure the XY axis and render the value - made some good progress there.

now i would like to add labels to my data points which i tried reading materials not sure what am missing here image and the corresponding configuration is image

All i want is 4 and 10 instead of “Totals”, would you be able to help me?

Try changing text: Totals to text: {value}.

that helps a lot mate, Thanks for all you Inputs.