Chart from db query

[BoboParticipant]

I have a data adapter from a db query similar to “select date, product, sum(amount) / count(1) from table group by date, product”.
To view many series as there are products, i have to pivot my data, statically, as “select date, product, sum(case when product = ‘PA’ then amount else 0 end) AMOUNT_PA, sum(case when product = ‘PB’ then amount else 0 end) AMOUNT_PB… and so on” and then declare many series as amount fields (the same number of products).
This procedure is static, if the products increased i have to modify statement and series.
There is a way to make it dynamic? I wuld use the first statement in my data adapter.

Sorry for my english and thanks to everyone for your support

[HristoParticipant]

Hello Bobo,

Could you provide us with one simplified example that demonstrates your case?
Please, provide us with more details.

Best Regards,
Hristo Hristov

jQWidgets team
https://www.jqwidgets.com

[BoboParticipant]

Hallo Hristo,

these are chart settings:

settings2 = {
title: “Andamento Obiettivi nel periodo selezionato”,
description: “”,
source: adapter1,
enableAnimations: true,
showLegend: true,
padding: { left: 10, top: 5, right: 10, bottom: 5 },
titlePadding: { left: 50, top: 0, right: 0, bottom: 10 },
xAxis:
{
dataField: ‘DT_EVENTO’,
formatFunction: function (value) {
var mm = value.getMonth() + 1, // getMonth() is zero-based
dd = value.getDate();
return [
(dd>9 ? ” : ‘0’) + dd,
(mm>9 ? ” : ‘0’) + mm,
value.getFullYear()
].join(‘/’);
},
type: ‘date’,
baseUnit: ‘month’,
valuesOnTicks: true,
minValue: dtIni,
maxValue: dtFin,
tickMarks: {
visible: true,
interval: 1,
color: ‘#BCBCBC’
},
unitInterval: 1,
gridLines: {
visible: true,
interval: 3,
color: ‘#BCBCBC’
},
labels: {
angle: -45,
rotationPoint: ‘topright’,
offset: { x: 0, y: -25 }
}
},
valueAxis:
{
visible: true,
title: { text: ‘Percentuale’ },
tickMarks: { color: ‘#BCBCBC’ },
maxValue: 100,
minValue: 0,
unitInterval: 10
},
colorScheme: ‘scheme04’,
seriesGroups:
[
{
type: ‘line’,
series: [
{ dataField: ‘P_ESE_LUD’, displayText: ‘ESRCIZI LUDICI’ },
{ dataField: ‘P_LAV_FIS’, displayText: ‘LAVORO FISICO’ },
{ dataField: ‘P_MOV_DIF’, displayText: ‘MOVIMENTO DIFENSIFO’ },
{ dataField: ‘P_MOV_OFFF’, displayText: ‘MOVIMENTO OFFENSIVO’ },
{ dataField: ‘P_PRI_FAS’, displayText: ‘PRIMA FASE’ },
{ dataField: ‘P_REP’, displayText: ‘REPARTO’ },
{ dataField: ‘P_SKI’, displayText: ‘SKILLS’ }
]
}
]
}

and this is the query that generate the adapter (query calculates cumulative values, this is the reason for the variables)

select DT_EVENTO, MINUTI_ALLTO,
MOV_DIF, @cumMOV_DIF := @cumMOV_DIF + MOV_DIF MOV_DIF_CUM, @cumMOV_DIF / minuti_allto * 100 P_MOV_DIF,
LAV_FIS, @cumLAV_FIS := @cumLAV_FIS + LAV_FIS LAV_FIS_CUM, @cumLAV_FIS / minuti_allto * 100 P_LAV_FIS,
ESE_LUD, @cumESE_LUD := @cumESE_LUD + ESE_LUD ESE_LUD_CUM, @cumESE_LUD / minuti_allto * 100 P_ESE_LUD,
MOV_OFF, @cumMOV_OFF := @cumMOV_OFF + MOV_OFF MOV_OFF_CUM, @cumMOV_OFF / minuti_allto * 100 P_MOV_OFF,
PRI_FAS, @cumPRI_FAS := @cumPRI_FAS + PRI_FAS PRI_FAS_CUM, @cumPRI_FAS / minuti_allto * 100 P_PRI_FAS,
REP, @cumREP := @cumREP + REP REP_CUM, @cumREP / minuti_allto * 100 P_REP,
SKI, @cumSKI := @cumSKI + SKI SKI_CUM, @cumSKI / minuti_allto * 100 P_SKI
from
(
select a.dt_evento, MINUTI_ALLTO,
sum(case when at.DS_OBIETTIVO = ‘MOVIMENTO DIFENSIVO’ then ifnull(e.QT_MINUTI_EFFETTIVI,0) else 0 end) MOV_DIF,
sum(case when at.DS_OBIETTIVO = ‘LAVORO FISICO’ then ifnull(e.QT_MINUTI_EFFETTIVI,0) else 0 end) LAV_FIS ,
sum(case when at.DS_OBIETTIVO = ‘ESERCIZI LUDICI’ then ifnull(e.QT_MINUTI_EFFETTIVI,0) else 0 end) ESE_LUD,
sum(case when at.DS_OBIETTIVO = ‘MOVIMENTO OFFENSIVO’ then ifnull(e.QT_MINUTI_EFFETTIVI,0) else 0 end) MOV_OFF,
sum(case when at.DS_OBIETTIVO = ‘PRIMA FASE’ then ifnull(e.QT_MINUTI_EFFETTIVI,0) else 0 end) PRI_FAS,
sum(case when at.DS_OBIETTIVO = ‘REPARTO’ then ifnull(e.QT_MINUTI_EFFETTIVI,0) else 0 end) REP,
sum(case when at.DS_OBIETTIVO = ‘SKILLS’ then ifnull(e.QT_MINUTI_EFFETTIVI,0) else 0 end) SKI
from allto_albero_tecnico at
join allto_allenamento a on a.cd_categ = ‘$cat’
left join allto_esercizi e on e.DT_EVENTO = a.DT_EVENTO and e.CD_CATEG = a.CD_CATEG and e.ID_OGGETTO_ALLTO = at.ID_OGGETTO_ALLTO
join (
select DT_EVENTO, @min_tot := @min_tot + sum(ifnull(QT_MINUTI_EFFETTIVI,0)) MINUTI_ALLTO
from allto_esercizi
where ifnull(cd_categ,’$cat’) = ‘$cat’ and
ifnull(dt_evento,str_to_date(‘$dtIni’,’%d/%m/%Y’)) between str_to_date(‘$dtIni’,’%d/%m/%Y’) and STR_TO_DATE(‘$dtFin’,’%d/%m/%Y’)
group by DT_EVENTO
) min_tot on a.DT_EVENTO = min_tot.dt_evento
where ifnull(a.cd_categ,’$cat’) = ‘$cat’ and
ifnull(a.dt_evento,str_to_date(‘$dtIni’,’%d/%m/%Y’)) between str_to_date(‘$dtIni’,’%d/%m/%Y’) and STR_TO_DATE(‘$dtFin’,’%d/%m/%Y’)
group by a.dt_evento, MINUTI_ALLTO
) A
order by 1,2

Let me know if you need any more details

[adminKeymaster]

Hi Bobo,

In order to perform dynamic chart updates, please refer to: https://www.jqwidgets.com/jquery-widgets-demo/demos/jqxchart/jqxchart_1second_updates.htm?light. The example shows how to implement the required functionality and update the Chart data. In the demo, each second the Chart displays a new data set. That chart demo is also available for Angular, React and Vue.

Best regards,
Peter Stoev

jQWidgets Team
https://www.jqwidgets.com/

[Author]

Posts