Mirage Client Controls Spotlight

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

Mirage Client Controls Spotlight - Graph Controls: Pie 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 2, Pie Charts.

Yvonne Milne
July, 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 2 examines Pie Charts.

Pie Charts - Simple

The Pie 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=pie	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,70	A comma-separated list of the data to graph.

Click image to display full size

Fig 1: Simple Pie Chart.

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

To configure the legend, use the following properties:

Property	Example	Description
xValueLabels	xValueLabels=Mon,Tue,Wed,Thu,Fri,Sat,Sun	The legend data labels.
pixelyLegend	pixelyLegend=5	The row position of the legend.
pixelxLegend	pixelxLegend=275	The column position of the legend.
pixelWidthLegend	pixelWidthLegend=80	The width of the legend.
pixelHeightLegend	pixelHeightLegend=114	The height of the legend.

Click image to display full size

Fig 2: Simple Pie Chart: legend configured.

@02,05 to 13,40 properties "control=graphcontrol;;
	id=chart1;;
	type=pie;;
	data=10,20,30,40,50,60,70;;
	xValueLabels=Mon,Tue,Wed,Thu,Fri,Sat,Sun;;
	pixelyLegend=5;;
	pixelxLegend=275;;
	pixelWidthLegend=80;;
	pixelHeightLegend=114"
read

The percentages are computed automatically and added into the legend.

Pie Charts - Data Based

The simple pie 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 employees and orders tables from the southwind database that is included in Recital product distributions.

Click image to display full size

Fig 3: Data Based Pie Chart.

The SQL query pulls obtains the number of orders from the orders table that correspond to each employee. The employees table is also referenced so that the employee's first and last names are returned rather than just the employee 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,
	employees.firstname forename,
	employees.lastname surname
	from orders, employees
	where orders.employeeid = employees.employeeid
	group by employeeid
	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(forename)+" "+trim(surname)+","
endscan

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

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

// Define the graph
@02,05 to 13,48 properties "control=graphcontrol;;
	id=chart1;;
	type=pie;;
	data=&c_values;;
	xValueLabels=&c_names;;
	pixelxLegend=275;;
	pixelyLegend=5;;
	pixelWidthLegend=160;;
	pixelHeightLegend=150"
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,
	employees.firstname forename,
	employees.lastname surname
	from orders, employees
	where orders.employeeid = employees.employeeid
	group by employee
	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(forename)+" "+trim(surname)+","
endscan

// Define the graph
@02,05 to 13,48 properties "control=graphcontrol;;
	id=chart1;;
	type=pie;;
	data=&c_values;;
	xValueLabels=&c_names;;
	pixelxLegend=275;;
	pixelyLegend=5;;
	pixelWidthLegend=160;;
	pixelHeightLegend=15"
read

close databases

Drilling-Down

The pie chart graph control has an onDoubleClick event handler that can be used to 'drill down' into individual pie segments when the legend pie label is double-clicked. The label is passed as a parameters to the procedure associated with the onDoubleClick event handler:

Parameter	Data Type	Description
DataLabel	Character	Data label, corresponds to 'xValueLabels' text for the pie segment.

The procedure is defined as follows:

@02,05 to 13,48 properties "control=graphcontrol;;
	id=chart1;;
	type=pie;;
	data=&c_values;;
	xValueLabels=&c_names;;
	pixelxLegend=275;;
	pixelyLegend=5;;
	pixelWidthLegend=160;;
	pixelHeightLegend=150;;
	onDoubleClick=pie_drilldown"

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

Click image to display full size

Fig 4: Simple Drill Down Event.

procedure pie_drilldown   
  parameter DataLabel
  exec sql
  select orders from &c_table
  	where trim(forename)+ " " + trim(surname) = DataLabel
  	into n_orders;
  dialog box DataLabel + " has " + ltrim(etos(n_orders))+ " orders."
return

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

exec sql
open database southwind;

exec sql
select count(orderid) orders,
	employees.firstname forename,
	employees.lastname surname
	from orders, employees
	where orders.employeeid = employees.employeeid
	group by employee
	save as &c_table;

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

@02,05 to 13,48 properties "control=graphcontrol;;
	id=chart1;;
	type=pie;;
	data=&c_values;;
	xValueLabels=&c_names;;
	pixelxLegend=275;;
	pixelyLegend=5;;
	pixelWidthLegend=160;;
	pixelHeightLegend=15;;
	onDoubleClick=pie_drilldown"
read

close databases

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

Part 1 of this article, covering Bar Charts, gives as example where drilling into an orders by Shipping Company bar chart displays a second bar chart based on a new 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.

Here we drill down into our Pie Chart to show all the orders by a particular employee in grid format in a listbox control:

Click image to display full size

Fig 5: Drill Down Event.

Procedure pie_drilldown   
  parameter DataLabel
  exec sql
  select orders.orderid, orders.customerid,
  	orders.orderdate, orders.requireddate
  	from orders inner join employees
  	on orders.employeeid = employees.employeeid
  	where trim(employees.firstname)+ " " + trim(employees.lastname) = Datalabel
  	into array temp;

  store 0 to mlistbox
  @15,05 get mlistbox;
  from temp;
  function "&T";
  size 8,43;
  properties "columnTitles=Order,Customer,Ordered,Required;;
  	columnWidths=C10,C5,C8,C8" 
  show object mlistbox properties "command=show"
return

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

exec sql
open database southwind;

exec sql
select count(orderid) orders,
	employees.firstname forename,
	employees.lastname surname
	from orders, employees
	where orders.employeeid = employees.employeeid
	group by employee
	save as &c_table;

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

@02,05 to 13,48 properties "control=graphcontrol;;
	id=chart1;;
	type=pie;;
	data=&c_values;;
	xValueLabels=&c_names;;
	pixelxLegend=275;;
	pixelyLegend=5;;
	pixelWidthLegend=160;;
	pixelHeightLegend=15;;
	onDoubleClick=pie_drilldown"
read

close databases

Display Properties

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

Property	Example	Description
borderColor	borderColor=red	The foreground color of the border.
borderWidth	borderWidth=3	The width of the border line.
shadowStyle	shadowStyle=etchedOut	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.
labelForeColor	labelForeColor=blue	The foreground color of the legend labels (xValueLabels).
colorData	colorData=red,yellow,green	The pie segment 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

Click image to display full size

Fig 6: Pie Chart with additional display properties set.

@02,05 to 13,48 properties "control=graphcontrol;;
	id=chart1;;
	type=pie;;
	data=&c_values;;
	xValueLabels=&c_names;;
	pixelxLegend=275;;
	pixelyLegend=5;;
	pixelWidthLegend=160;;
	pixelHeightLegend=150;;
	onDoubleClick=pie_drilldown;;
	borderColor=navy;;
	borderWidth=3;;
	wallpaper=+gradient_blue.gif;;
	labelForeColor=blue;;
	colorData=red,yellow,pink,green,orange,purple,blue,black,white"
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,48 properties "control=graphcontrol;;
	id=chart1;;
	type=pie;;
	data=&c_values;;
	xValueLabels=&c_names;;
	pixelxLegend=275;;
	pixelyLegend=5;;
	pixelWidthLegend=160;;
	pixelHeightLegend=150;;
	onDoubleClick=pie_drilldown;;
	borderColor=navy;;
	borderWidth=3;;
	wallpaper=+gradient_blue.gif;;
	labelForeColor=blue"
show object "graphcontrol" properties "command=properties;id=chart1;;
	colorData=darkred,yellow,lightpink,darkolivegreen,orange,purple,blue,black,white"
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