Mirage Client Controls Spotlight

Recital Developer Center / Technical Articles /Mirage Client Controls Spotlight - Graph Controls: Bar Charts

Mirage Client Controls Spotlight - Graph Controls: Bar Charts

Related Links
Recital Home Recital Product Family Recital Terminal Developer Recital Visual Developer Recital Database Server Recital Web Developer

	Email this article
	Print this article

The first in a series of articles examining in depth a particular Mirage .NET control. This month the spotlight is on the Graph Controls. Part 1, Bar Charts.

Yvonne Milne
July, 2006. Updated March, 2007

Overview

Data visualization using graphs can be a particularly effective way to transform raw data into useful information. The Recital Mirage .NET Client offers three graph controls:

Part 1 examines Bar Charts.

Bar Charts - Simple

The Bar Chart is based on the @<row>,<col> TO <row>,<col> box drawing command with graph control specific properties. The following properties are required as a minimum:

Property	Example	Description
control	control=graphcontrol	This defines the box as a graph control and must be specified.
id	id=chart1	A unique id for the control.
type	type=bar	The type of graph control: bar for a bar chart, pie for a pie chart and line for a line chart.
data	data=10,20,30,40,50,60	A comma-separated list of the data to graph.

Click image to display full size

Fig 1: Simple Bar Chart.

@02,05 to 13,40 properties "control=graphcontrol;;
	id=chart1;;
	type=bar;;
	data=10,20,30,40,50,60"
read

If not configured, the vertical axis defaults to six ticks with an increment of 20, ranging from 0 to 100: 0,20,40,60,80,100. To configure the vertical axis, use the following properties:

Property	Example	Description
minValue	minValue=0	The minimum data value.
maxValue	maxValue=600	The maximum data value.
yTicks	yTicks=7	The number of tick marks.
yTickinc	yTickinc=100	The tick mark interval.
yValueLabels	yValueLabels=0,100,200,300,400,500,600	The tick mark text labels.

Tip
The minValue is optional, but when used must match the first yValueLabels tick.

Click image to display full size

Fig 2: Simple Bar Chart: vertical axis configured.

@02,05 to 13,40 properties "control=graphcontrol;;
	id=chart1;;
	type=bar;;
	data=103,220,300,450,150,560;;
	minvalue=0;;
	maxvalue=600;;
	yTicks=7;;
	yTickinc=100;;
	yValueLabels=0,100,200,300,400,500,600"
read

The data itself is labeled, using the xValueLabels property:

Property	Example	Description
xValueLabels	xValueLabels=One,Two,Three,Four,Five,Six	The data labels.

Click image to display full size

Fig 3: Simple Bar Chart: vertical axis configured, data labeled.

@02,05 to 13,40 properties "control=graphcontrol;;
	id=chart1;;
	type=bar;;
	data=103,220,300,450,150,560;;
	minvalue=0;;
	maxvalue=600;;
	yTicks=7;;
	yTickinc=100;;
	yValueLabels=0,100,200,300,400,500,600;;
	xValueLabels=One,Two,Three,Four,Five,Six"
read

Bar Charts - Data Based

The simple bar chart example above has the data and labels specified as constant values, but in most cases these will be strings built up from the source data tables. This example uses the shippers and orders tables from the southwind database that is included in Recital product distributions.

Click image to display full size

Fig 4: Data Based Bar Chart.

The SQL query pulls obtains the number of orders from the orders table that correspond to each shipping company. The shippers table is also referenced so that the shipping company name is returned rather than just the shipping company code number stored in the orders table. For ease of manipulation, the query is saved into a temporary table.

// Define variables to be used including a temporary table
c_values = "" 
c_names  = ""
c_table  = "_" + tmpnam()

// Open the database and extract the data into the temporary table
exec sql
open database southwind;

exec sql
select count(orderid) orders, shippers.companyname shipper
	from orders, shippers
	where orders.shipvia = shippers.shipperid
	group by shipvia
	save as &c_table;

Traverse the temporary table using the 4GL SCAN command to build up the comma-separated strings: c_values with the number of orders, c_names with the shipping company names.

// Scan the table to build up the strings
use &c_table
scan
	c_values = c_values + ltrim(str(orders))+","
	c_names  = c_names + trim(shipper)+","
endscan

// Add an extra 0
c_values = c_values + "0"

The c_values variable can then be used as the bar chart's data property and the c_names variable as the xValueLabels property.

Tip
Adding an extra 0 to the end of the data property often gives a cleaner result to wide bar charts.

Define the graph, macro-substituting the c_values and c_names variables (using the 4GL & macro operator).

// Define the graph
@02,05 to 13,75 properties "control=graphcontrol;;
	id=chart1;;
	type=bar;;
	data=&c_values;;
	minvalue=0;;
	maxvalue=600;;
	yTicks=7;;
	ytickinc=100;;
	yValueLabels=0,100,200,300,400,500,600;;
	xValueLabels=&c_names"
read

Complete source:

// Define variables to be used including a temporary table
c_values = "" 
c_names  = ""
c_table  = "_" + tmpnam()

// Open the database and extract the data into the temporary table
exec sql
open database southwind;

exec sql
select count(orderid) orders, shippers.companyname shipper
	from orders, shippers
	where orders.shipvia = shippers.shipperid
	group by shipvia
	save as &c_table;

// Scan the table to build up the strings
use &c_table
scan
	c_values = c_values + ltrim(str(orders))+","
	c_names  = c_names + trim(shipper)+","
endscan

// Add an extra 0
c_values = c_values + "0"

// Define the graph
@02,05 to 13,75 properties "control=graphcontrol;;
	id=chart1;;
	type=bar;;
	data=&c_values;;
	minvalue=0;;
	maxvalue=600;;
	yTicks=7;;
	ytickinc=100;;
	yValueLabels=0,100,200,300,400,500,600;;
	xValueLabels=&c_names"
read

close databases

Drilling-Down

The bar chart graph control has an onDoubleClick event handler that can be used to 'drill down' into individual bars when the bar is double-clicked. Three parameters are passed to the procedure associated with the onDoubleClick event handler:

Parameter	Data Type	Description
DataSet	Numeric	Bar position number, starting from 0.
DataLabel	Character	Data label, corresponds to 'xValueLabels' text for the bar.
DataValue	Numeric	Data Value, corresponds to 'data' for the bar.

The procedure is defined as follows:

@02,05 to 13,75 properties "control=graphcontrol;;
	id=chart1;;
	type=bar;;
	data=&c_values;;
	minvalue=0;;
	maxvalue=600;;
	yTicks=7;;
	ytickinc=100;;
	yValueLabels=0,100,200,300,400,500,600;;
	xValueLabels=&c_names;;
	onDoubleClick=bar_drilldown"

At its simplest, the event procedure could display the bar's exact data value:

Click image to display full size

Fig 5: Simple Drill Down Event.

procedure bar_drilldown   
  parameter DataSet,DataLabel,DataValue
  dialog box ltrim(str(datavalue)) + " Orders have been shipped by " + DataLabel
return

c_values = "" 
c_names  = ""
c_table  = "_" + tmpnam()

exec sql
open database southwind;

exec sql
select count(orderid) orders, shippers.companyname shipper
	from orders, shippers
	where orders.shipvia = shippers.shipperid
	group by shipvia
	save as &c_table;

use &c_table
scan
	c_values = c_values + ltrim(str(orders))+","
	c_names  = c_names + trim(shipper)+","
endscan

c_values = c_values + "0"

@02,05 to 13,75 properties "control=graphcontrol;;
	id=chart1;;
	type=bar;;
	data=&c_values;;
	minvalue=0;;
	maxvalue=600;;
	yTicks=7;;
	ytickinc=100;;
	yValueLabels=0,100,200,300,400,500,600;;
	xValueLabels=&c_names;;
	onDoubleClick=bar_drilldown"
read

close databases

The drill down event procedure can also be used to change the bar chart properties. The mirage_demo application which is included in the Recital Visual Developer distribution gives an example of this, demonstrating the use of the SHOW OBJECT command:

// Extract from demo_graphs.prg
procedure bar_drilldown
parameter DataSet,DataLabel,DataValue  
	if lower(DataLabel) = "jan"
		show object "graphcontrol" 
		properties "command=properties;;
		id=barchart;numDataSets=4;data=5,10,3,7;;
		ytickinc=2;maxvalue=10;;
		xValueLabels=Week 1,Week 2,Week 3,Week 4;;
		ytitle=Week;xtitle=Amount ('000 $);;
		onDoubleClick=bar_drilldown;;
		shadowstyle=raised;wallpaper=+gradient_blue.gif"
	elseif lower(DataLabel) = "2000"
		show object "graphcontrol" 
		properties "command=properties;;
		id=barchart;text=Bar Chart;;
		shadow=true;numDataSets=6;;
		maxvalue=100;ytickinc=20;;
		data=20,30,50,70,28,59;;
		xValueLabels=Jan,Feb,Mar,Apr,May,Jun;ytitle=Month;;
		xtitle=Amount ('000 $);;
		onDoubleClick=bar_drilldown;;
		shadowstyle=raised;wallpaper=+gradient_blue.gif"
    endif
return

Or here is an example where a second bar chart is displayed based on a second SQL query, breaking down the orders by shipping company by the id number of the employee who took the order. This second bar chart also has an onDoubleClick event, showing the exact number of orders by employee name.

Click image to display full size

Fig 6: Drill Down Events.

Procedure bar2_drilldown
parameter DataSet,DataLabel,DataValue
  exec sql
  select trim(firstname)+" "+lastname
  	from employees
	where employeeid = val(Datalabel)
  	into c_empname;
  dialog box ltrim(str(datavalue)) + " Orders by " + c_empname
return

procedure bar_drilldown   
  parameter DataSet,DataLabel,DataValue
  dialog box ltrim(str(datavalue)) + " Orders have been shipped by " + DataLabel
  
  c_values2 = "" 
  c_names2  = ""
  c_table2  = "_" + tmpnam()
  
  exec sql
  select count(*) orders, employeeid employee
  	from orders inner join shippers
  	on orders.shipvia = shippers.shipperid
  	group by orders.employeeid
  	where shippers.companyname=DataLabel
  	save as &c_table2;
  
  use &c_table2
  scan
  	c_values2 = c_values2 + ltrim(str(orders))+","
  	c_names2 = c_names2 + ltrim(str(employee))+","
  endscan
  
  c_values2 = c_values2 + "0"
  
  @14,05 to 23,75 properties "control=graphcontrol;;
  	id=chart2;;
  	type=bar;;
  	data=&c_values2;;
  	minvalue=0;;
  	maxvalue=100;;
  	yTicks=6;;
  	ytickinc=10;;
  	yValueLabels=0,20,40,60,80,100;;
  	xValueLabels=&c_names2;;
  	onDoubleClick=bar2_drilldown"                  
return

c_values = "" 
c_names  = ""
c_table  = "_" + tmpnam()

exec sql
open database southwind;

exec sql
select count(orderid) orders, shippers.companyname shipper
	from orders, shippers
	where orders.shipvia = shippers.shipperid
	group by shipvia
	save as &c_table;

use &c_table
scan
	c_values = c_values + ltrim(str(orders))+","
	c_names  = c_names + trim(shipper)+","
endscan

c_values = c_values + "0"

@02,05 to 13,75 properties "control=graphcontrol;;
	id=chart1;;
	type=bar;;
	data=&c_values;;
	minvalue=0;;
	maxvalue=600;;
	yTicks=7;;
	ytickinc=100;;
	yValueLabels=0,100,200,300,400,500,600;;
	xValueLabels=&c_names;;
	onDoubleClick=bar_drilldown"
read

close databases

Pretty Please

There is a whole range of further properties that can be used to configure the display of the Bar Chart:

Property	Example	Description
xtitle	xtitle=No of Orders	The vertical axis title.
ytitle	ytitle=Shipping Company	The horizontal axis title.
pixelyTitle	pixelyTitle=20	The row position in pixels of the horizontal axis title.
pixelxTitle	pixelxTitle=500	The column position in pixels of the horizontal axis title.
pixelWidthTitle	pixelWidthTitle=200	The width of the horizontal axis title.
borderColor	borderColor=red	The foreground color of the border.
borderWidth	borderWidth=3	The width of the border line.
shadowStyle	shadowStyle=raised	The shadowing effect style: raised, recessed, etchedIn, etchedOut. The shadowing effect is only available if borderWidth is 0 or not specified.
shadowWidth	shadowWidth=3	The width in pixels of the shadowing effect.
wallpaper	wallpaper=+gradient_blue.gif	The background, 'wallpaper' image. The image file name can be preceded by a '-' to tile the image to fit or by a '+' to stretch the image to fit.
titleBackColor	titleBackColor=blue	The background color of the horizontal and vertical axes titles.
titleForeColor	titleForeColor=white	The foreground color of the horizontal and vertical axes titles.
labelForeColor	labelForeColor=navy	The foreground color of the data labels (xValueLabels) and tick labels (yValueLabels).
colorData	colorData=red,yellow,green	The bar colors. The default colors are: blue, red, green, yellow, aqua, pink, fuchsia, gray, orange, navy, white, coral, beige, paleturquoise, darkgray. These are repeated as required
axisForeColor	axisForeColor=navy	The color of the vertical and horizontal axes.
horiLineColor	horiLineColor=gray	The color of the horizontal gridlines.
vertLineColor	vertLineColor=silver	The color of the vertical gridlines.

Click image to display full size

Fig 7: Bar Chart with additional display properties set.

@02,05 to 13,75 properties "control=graphcontrol;;
	id=chart1;;
	type=bar;;
	data=&c_values;;
	minvalue=0;;
	maxvalue=600;;
	yTicks=7;;
	ytickinc=100;;
	yValueLabels=0,100,200,300,400,500,600;;
	xValueLabels=&c_names;;
	xtitle=No of Orders;;
	ytitle=Shipping Company;;
	shadowStyle=raised;;
	shadowWidth=3;;
	wallpaper=+gradient_blue.gif;;
	titleBackColor=blue;;
	titleForeColor=white;;
	labelForeColor=navy;;
	colorData=red,yellow,green;;
	axisForeColor=navy;;
	horiLineColor=gray;;
	vertLineColor=silver"
read

Click image to display full size

Fig 8: Bar Chart with repositioned horizontal title.

@02,05 to 13,75 properties "control=graphcontrol;;
	id=chart1;;
	type=bar;;
	data=&c_values;;
	minvalue=0;;
	maxvalue=600;;
	yTicks=7;;
	ytickinc=100;;
	yValueLabels=0,100,200,300,400,500,600;;
	xValueLabels=&c_names;;
	xtitle=No of Orders;;
	ytitle=Shipping Company;;
	pixelyTitle=20;;	
	pixelxTitle=524;;
	pixelWidthTitle=158;;
	wallpaper=+gradient_blue.gif;;
	titleBackColor=blue;;
	titleForeColor=white;;
	labelForeColor=navy"
read

Setting multiple properties can lead to a "String is too long" error. In which case the properties should be split between the original control definition and one or more subsequent SHOW OBJECT...properties "command=properties" commands:

@02,05 to 13,75 properties "control=graphcontrol;;
	id=chart1;;
	type=bar;;
	data=&c_values;;
	minvalue=0;;
	maxvalue=600;;
	yTicks=7;;
	ytickinc=100;;
	yValueLabels=0,100,200,300,400,500,600;;
	xValueLabels=&c_names;;
	xtitle=No of Orders;;
	ytitle=Shipping Company;;
	pixelyTitle=20;;	
	pixelxTitle=524;;
	pixelWidthTitle=158;;
	shadowStyle=raised;;
	shadowWidth=3;;
	wallpaper=+gradient_blue.gif"
show object "graphcontrol" properties "command=properties;id=chart1;;
	titleBackColor=blue;;
	titleForeColor=white;;
	labelForeColor=navy;;
	colorData=red,yellow,green;;
	axisForeColor=navy;;
	horiLineColor=gray;;
	vertLineColor=silver"
read

Appendix 1: Color Name Reference

The following color names can be used by the Mirage .NET client.

Colors
aliceblue antiquewhite aqua aquamarine azure beige bisque black blanchedalmond blue blueviolet brown burlywood cadetblue chartreuse chocolate coral cornflowerblue cornsilk crimson cyan darkblue darkcyan darkgoldenrod darkgray darkgreen darkkhaki darkmagenta darkolivegreen darkorange darkorchid darkred darksalmon darkseagreen darkslateblue	darkslategray darkturquoise darkviolet deeppink deepskyblue dimgray dodgerblue firebrick floralwhite forestgreen fuchsia gainsboro ghostwhite gold goldenrod gray green greenyellow honeydew hotpink indianred indigo ivory khaki lavendar lavendarblush lawngreen lemonchiffon lightblue lightcoral lightcyan lightgray lightgreen lightpink lightsalmon	lightseagreen lightskyblue lightslategray lightsteelblue lightyellow lime limegreen linen magenta maroon mediumaquamarine mediumblue mediumorchid mediumpurple mediumseagreen mediumslateblue mediumspringgreen mediumturquoise mediumvioletred midnightblue mintcream mistyrose moccasin navajowhite navy oldlace olive olivedrab orange orangered orchid palegoldenrod palegreen paleturquoise palevioletred	papayawhip peachpuff peru pink plum powderblue purple red rosybrown royalblue saddlebrown salmon sandybrown seagreen seashell sienna silver skyblue slateblue slategray snow springgreen steelblue tan teal thistle tomato turquoise violet wheat white whitesmoke yellow yellowgreen