Using plotcharts to compare years

Classic Servoy
1

Hi,

I’m playing a little bit with charts bean. After a certain number of tries, I find the way to have a report showing all monthly revenues, after selecting the year from a dialog box.
I like the result and now I’m asking: is there a simple way to put more than a single year into the chart? It would be fine to have the comparison between the years chosen by the user and the previous year.
Here’s my code (maybe could be useful to someone :wink:

var query_anno = ""
var query_anno = "select distinct yearnr from piani order by yearnr"
var dataset_anniOrd = databaseManager.getDataSetByQuery(controller.getServerName(), query_anno, null, 100000);
var anni_ordini = dataset_anniOrd.getColumnAsArray(1)
var sceltannata = ""
var sceltannata = plugins.dialogs.showSelectDialog("Attenzione","Scegli l'anno",anni_ordini)

for(var c = 1 ; c < 13; c++) //
	{
	elements.plotchart.setLegends(c-1,"")// set legends of chart
	elements.plotchart.setValues(c-1, 0, 0)
	elements.plotchart.setLabels(c-1,"" )
	}
var mesi = new Array
(

"'" +(sceltannata)+'-01-01'+"'"+ ' AND ' + "'" +(sceltannata) +  '-01-31' +"'",
"'" +(sceltannata)+'-02-01'+"'"+ ' AND ' + "'" +(sceltannata) +  '-02-29' +"'",
"'" +(sceltannata)+'-03-01'+"'"+ ' AND ' + "'" +(sceltannata) +  '-03-31' +"'",
"'" +(sceltannata)+'-04-01'+"'"+ ' AND ' + "'" +(sceltannata) +  '-04-30' +"'",
"'" +(sceltannata)+'-05-01'+"'"+ ' AND ' + "'" +(sceltannata) +  '-05-31' +"'",
"'" +(sceltannata)+'-06-01'+"'"+ ' AND ' + "'" +(sceltannata) +  '-06-30' +"'",
"'" +(sceltannata)+'-07-01'+"'"+ ' AND ' + "'" +(sceltannata) +  '-07-31' +"'",
"'" +(sceltannata)+'-08-01'+"'"+ ' AND ' + "'" +(sceltannata) +  '-08-31' +"'",
"'" +(sceltannata)+'-09-01'+"'"+ ' AND ' + "'" +(sceltannata) +  '-09-30' +"'",
"'" +(sceltannata)+'-10-01'+"'"+ ' AND ' + "'" +(sceltannata) +  '-10-31' +"'",
"'" +(sceltannata)+'-11-01'+"'"+ ' AND ' + "'" +(sceltannata) +  '-11-30' +"'",
"'" +(sceltannata)+'-12-01'+"'"+ ' AND ' + "'" +(sceltannata) +  '-12-31' +"'"
)
for(var i = 0 ; i < 13 ; i++)
{
elements.plotchart.setLabels(i,i+1 )
var	query = "SELECT "+
	" SUM(ri.prezuni) as TotMese"+
	" from rigpiani ri"+
	" where ri.data_esecuzione  BETWEEN "+ mesi[i]

var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, 10000);

for( var j = 1 ; j <= dataset.getMaxRowIndex(); j++ )
{
dataset.rowIndex = j;
elements.plotchart.setLegends(j-1,sceltannata)
	if ( dataset[1] != null )
	{
	elements.plotchart.setValues(j-1,i,dataset[1])

	}
	else
	{
	elements.plotchart.setValues(j-1,i,0)
	}
}
}
2

Attached is a sample solution + firebird database

  1. copy the chartdata.gdb to your servoy databases folder
  2. create a database connection in servoy with name “chartdata”
  3. import the solution into servoy

The solution has 3 tables:
-main. The chart is placed on a main form.
(in fact this may be any table)

  • requests.
    For each year you can place a request.
  • line_items.
    (i’ve filled the line items with very simple data to keep the example clear)

table “requests” is linked to table “line items” through key “yearnr”
Check out the solution and all will probably be clear.

If you have any questions, let me know. I’ll be glad to help.

Have fun.

3

maarten:
If you have any questions, let me know. I’ll be glad to help.

Have fun.

Super! :lol:
It seems to be exactly what I needed: thanks a lot, Maarteen :D

4

The download linnk doesn’t work anymore. Does someone has any examples for the plot chart bean?

5

I got it working already.
Is it possible to repaint the whole graph?
Zo when I choose a parameter from a pull down list, I want to repaint the whole graph, but for now this wont work.

6

Did you find out how to repaint or reset the graph? I have the same problem…

Thanks,
Reto