Separating Chart Data into a single line per unique value

Grafana Dashboards
1

Grafana version 9.4.3

A line chart displaying a single line for each variable, chosen by a filter selection

I have a line chart displaying the battery voltage of a large number of AGV’s over a selectable time period, with filters to select the AGV system and number. The data table shows the time stamp in column 1, the AGV number in column 2 and the voltage in column 3.

When the data is displayed as a chart, the time filter function correctly, but all the AGV values are combined into a single line. as I filter the selection, to an individual AGV, the data is correct, but as a group, the combined data is not relevant.

A single chart showing multiple lines per AGV, displaying the voltage over time

SELECT
TimeDate, AGV,
cast(BattVoltage AS real) as ‘value’,
AGV as ‘metric’
FROM AGV.dbo.AGVbattery
WHERE $__timeFilter(TimeDate) AND Program <> 0 AND Program in ($Program) AND AGV in ($AGV)
ORDER BY TimeDate desc

No errors shown, just not the correct visualisation for the data

Thank you

2

Please share a screenshot

3

image
image1678×386 35.7 KB

image
image1841×503 48.3 KB

image
image1502×1024 75.1 KB

the first image is the current chart, with dynamic selection, but showing the the four selection as one data line. the second image is a chart driven off four fixed value queries but shows how i would like to see the data.

1 Like
4

Hit the query inspector for the first the 1st image query and what does it show for the generated sql query?

servers = [‘test1’, ‘test2’]
String to interpolate: ‘${servers:singlequote}’
Interpolation result: “‘test1’,‘test2’”

5

Hi, please see image for inspection. the data all looks correct, it just seems to plot all the data as one series, instead of a single instance per result.

image
image942×521 39.7 KB

1 Like
6

Maybe try the transformation tab and then choose prepare time series and choose time frame?

Also change the below from Table to Time Series

image

and change your query to

Select TimeDate as time.
 AGV as metric,
cast(BattVoltage as real) as value

--etc
1 Like
7

SELECT TimeDate as time, AGV as ‘metric’, cast(BattVoltage AS real) as ‘voltage’,

FROM AGV.dbo.AGVbattery

WHERE $__timeFilter(TimeDate) AND Program <> 0 AND Program in ($Program) AND AGV in ($AGV)

ORDER BY TimeDate desc

i tried this, but now i get an error. sytax FROM

8

as ‘voltage’, <----

FROM

1 Like
9

perfect, error fixed…

still no data separation though ?

image
image1502×847 69.3 KB

10

:point_up: did you try this?

1 Like
11

I did :+1:, but could not get it to work as intended, I guess due to an error with my initial query table. I have got it working now, displaying exactly the information I would like to see. Thank you.

Is it possible for the chart filter at the base of the chart to be customised? although the data is now correct, it says Voltage (number of AGV) which looks confusing to the end user.

1 Like
12

You can rename the legend values using the overrides.

13

what is your final query look like?

14

I have tried to use the override function, but this replaces the variable with a fixed value, for example it previously said Voltage 123, Voltage 456, Voltage 789 etc… and with override it now says AGV, AGV, AGV, etc…
I am trying to get the combination.
AGV 123, AGV 456, AGV 789 etc…

hope that makes sense.

15

works perfectly on my pc :laughing:

image
image987×224 6.98 KB

16

SELECT TimeDate as time, AGV as ‘AGV’, cast(BattVoltage AS real) as ‘Voltage’

FROM AGV.dbo.AGVbattery

WHERE $__timeFilter(TimeDate) AND Program <> 0 AND Program in ($Program) AND AGV in ($AGV)

ORDER BY TimeDate desc

with the transform Wide Time Series

17

look at the query marked as solution,

18

Hi. can you explain in more detail? I still not sure where I am going wrong. sorry.

19 
SELECT TimeDate as time, 
    AGV as metric, 
cast(BattVoltage AS real) as value

FROM AGV.dbo.AGVbattery

WHERE $__timeFilter(TimeDate) 
AND Program <> 0 
AND Program in ($Program) AND AGV in ($AGV)

ORDER BY TimeDate desc

whereas you are doing

AGV as 'AGV', cast(BattVoltage AS real) as 'Voltage'

This has already been answered not sure what the new issue is?

1 Like