Reading SQL Query variables for Grafana E-charts

Grafana
,
1

I started the SQL Query in Grafana declaring some variables:

DECLARE @ MOTIVO1 VARCHAR (15) = ‘6712’;
DECLARE @ MOTIVO2 VARCHAR (15) = ‘2618’;
DECLARE @ MOTIVO3 VARCHAR (15) = ‘2923’;
DECLARE @ MOTIVO4 VARCHAR (15) = ‘2941’;

After that I SUM values aggregated by these categories and take a percentage of the total…
I would like to call this @ MOTIVO1, 2 , 3 , 4 … and other variables to the graph legend.

legend: {
data: [‘Forest’, ‘Steppe’, ‘Desert’, ‘Wetland’]

Is it possible?

2

Welcome @fplaner

Please share the whole query

3

Hello,

Here is the query variables, when I map the @ MOTIVO1 I want the value 6712 as graph category:

DECLARE @ MACHINE VARCHAR (15) = ‘MACHINE_07’;
DECLARE @ PRODLINE VARCHAR (15) = ‘PROD_LINE_01’;
DECLARE @ MOTIVO1 VARCHAR (15) = ‘6712’;
DECLARE @ MOTIVO2 VARCHAR (15) = ‘2618’;
DECLARE @ MOTIVO3 VARCHAR (15) = ‘2923’;
DECLARE @ MOTIVO4 VARCHAR (15) = ‘2941’;

Here ir the Graph Set-up:

let EIXO_X = ;
let Y1 = ;
let Y2 = ;
let Y3 = ;
let Y4 = ;

data.series.map((s) => {
EIXO_X = s.fields.find((f) => f.name === ‘HORA’).values;
Y1 = s.fields.find((f) => f.name === ‘MOTIVO1’).values;
Y2 = s.fields.find((f) => f.name === ‘MOTIVO2’).values;
Y3 = s.fields.find((f) => f.name === ‘MOTIVO3’).values;
Y4 = s.fields.find((f) => f.name === ‘MOTIVO4’).values;
});

var app = {};
const posList = [
‘left’,
‘right’,
‘top’,
‘bottom’,
‘inside’,
‘insideTop’,
‘insideLeft’,
‘insideRight’,
‘insideBottom’,
‘insideTopLeft’,
‘insideTopRight’,
‘insideBottomLeft’,
‘insideBottomRight’
];
app.configParameters = {
rotate: {
min: -90,
max: 90
},
align: {
options: {
left: ‘left’,
center: ‘center’,
right: ‘right’
}
},
verticalAlign: {
options: {
top: ‘top’,
middle: ‘middle’,
bottom: ‘bottom’
}
},
position: {
options: posList.reduce(function (map, pos) {
map[pos] = pos;
return map;
}, {})
},
distance: {
min: 0,
max: 100
}
};
app.config = {
rotate: 90,
align: ‘left’,
verticalAlign: ‘middle’,
position: ‘insideBottom’,
distance: 15,
onChange: function () {
const labelOption = {
rotate: app.config.rotate,
align: app.config.align,
verticalAlign: app.config.verticalAlign,
position: app.config.position,
distance: app.config.distance
};
myChart.setOption({
series: [
{
label: labelOption
},
{
label: labelOption
},
{
label: labelOption
},
{
label: labelOption
}
]
});
}
};
const labelOption = {
show: true,
position: app.config.position,
distance: app.config.distance,
align: app.config.align,
verticalAlign: app.config.verticalAlign,
rotate: app.config.rotate,

fontSize: 8,
rich: {
name: {}
}
};
return {
tooltip: {
trigger: ‘axis’,
axisPointer: {
type: ‘shadow’
}
},
legend: {
data: [replaceVariables(‘@ MOTIVO1’), ‘@ MOTIVO2’, ‘@ MOTIVO3’, ‘@ MOTIVO4’]
},
toolbox: {
show: true,
orient: ‘vertical’,
left: ‘right’,
top: ‘center’,
feature: {
mark: { show: true },
dataView: { show: true, readOnly: false },
magicType: { show: true, type: [‘line’, ‘bar’, ‘stack’] },
restore: { show: true },
saveAsImage: { show: true }
}
},
xAxis: [
{
type: ‘category’,
axisTick: { show: false },
data: EIXO_X
}
],
yAxis: [
{
type: ‘value’
}
],
series: [
{
name: replaceVariables(“@ MOTIVO1”),
type: ‘bar’,
barGap: 0,
label: labelOption,
emphasis: {
focus: ‘series’
},
data: Y1
},
{
name: ‘@ MOTIVO2’,
type: ‘bar’,
label: labelOption,
emphasis: {
focus: ‘series’
},
data: Y2
},
{
name: ‘@ MOTIVO3’,
type: ‘bar’,
label: labelOption,
emphasis: {
focus: ‘series’
},
data: Y3
},
{
name: ‘@ MOTIVO4’,
type: ‘bar’,
label: labelOption,
emphasis: {
focus: ‘series’
},
data: Y4
}
]
};

image
image1892×611 89.7 KB

4

Hey @fplaner, welcome to the community.

Sorry to say but your question isn’t really a Grafana question. You should first work on your sql skills and try to make it shows data as a simple table. When you get it to work, then you move to plug-in configuration like E-Charts.

Just to give you some pointers: try to drop the “DECLARE” statement, this is what makes sql server queries to sucks. Your query seems more like a classic “count, where, group by” case, so try to work with the logic bellow:

SELECT my_motivo_column as "metric",
    count(my_motivo_column) as "value",
    my_time_column as "time"
FROM my_table
WHERE my_motivo_column IN ('6712' , '2618', '2923', '2941')
GROUP BY my_motivo_column

I wish you good studies.

1 Like