Data Visualization with Charts

From Lianjapedia
Jump to: navigation, search

See Also

Chart Options, Charts Section Attributes, Chart UI Framework Class

Using Chart Sections

This article explains how to use Chart Sections for data visualization. The target audience is beginner to advanced developers who have read through and understood the Getting Started with Lianja tutorial. Advanced developers may be particularly interested in the Ideas for Further Development at the end of the article.

Lianja Apps are made up of Pages. Pages are made up of Sections.

Previously we have seen how to build Pages and add Form, Grid and ImageStrip Sections and quickly populate them using drag and drop. The Lianja App Builder also provides a range of other easy to use Section types for a rich and functional user interface. All Sections can be added from the Form Tools Section toolbutton. Chart Sections provide a quick and easy way to clearly and graphically communicate information about your data from the simplest pie or bar chart through to more sophisticated and complex graphs. Chart Sections can be based on live data that is updated and refreshed as you navigate and/or serve as the starting point for drilling down into more detailed information.

Chart example


Creating a Chart Section

So, let's get building:

  • Create a new App or open an existing one
  • Open a database
  • Add a Chart Section to a Page in your App

When you add a Chart Section it has default values already set, so you will see a sample chart displayed.

Add Chart section


In this first example, I'm going to get the Chart Section data from another Section on the Page, so click products in the Tables Explorer in the Sidebar and drag it onto an empty area of the Page to create a Form Section for the products table.

Add products Form section


If you want to rearrange the Sections, click the Form Section Header and drag it on to the Chart Section Header and the Sections will swap position.

Swap sections


Now, double-click on the Chart Section Header (or click on the cog icon) to open the Section Attributes and scroll down to the Chart options.

Attributes


Many of the options deal with the Chart's appearance and descriptions are given for these in the summary table below.

The essence of the Chart is, of course, the data, so type the following in the Data attribute:

Attribute Value
Data {products.unitsinstock},{products.reorderlevel}

As you fill in the Attribute, the chart will go blank as it tries to display live output, but once the entry is complete it will redisplay based on the updated value. If it's still blank once you've finished, check for typos!

The curly braces above {}, cause the values in the braces to be evaluated, so your Chart should now have two bars based on the unitsinstock and reorderlevel fields in the products table. Use the Labels Attribute to indicate this:

Attribute Value
Labels Units in Stock,Reorder Level

Then give the Chart a suitable Title, e.g.:

Attribute Value
Title Current Stock and Reorder Level

Click the Done button in the Section Attributes dialog and your Page should look something like this:

Bar chart


The Labels above Attribute is checked by default and causes the value count to be displayed on Bar Chart bars as you can see here.

For desktop Apps, to refresh the Chart Section as the Product records are navigated, we need a relation between the two Sections. This step is not required for web Apps. Click the Relationship Builder + next to the Products Section Header then click in the Chart Section Header to create the relationship. Use the Page ActionBar to move through the records of the products table and you will see the Chart updated to reflect the changing data.

Relation


At this stage, you might want to try changing the Graph type Attribute to see the different options. For example, a Line graph:

Line graph


Or a Pie chart (uncheck the Gradient Attribute for this one):

Pie chart


Chart Section Attributes and Descriptions Summary

Attribute Description
Graph type The type of chart. This is set when the Chart Section is added to the Page, but can be changed
URL The URL based on the attributes to generate the chart. Read only
Width The width of the chart in pixels
Height The height of the chart in pixels
Left gutter The left gutter/margin in pixels
Right gutter The right gutter/margin in pixels
Top gutter The top gutter/margin in pixels
Bottom gutter The bottom gutter/margin in pixels
Background color The background color of the Section
Shadow A toggle for enabling 3D shadow effects
Effects A toggle for enabling special effects
Gradient A toggle to enabling gradient colors on for the segments
Gradient start The gradient start color when Gradient is checked
Gradient end The gradient end color when Gradient is checked
Title The chart title
Title foreground color The foreground color for the Title
Title background color The background color for the Title
Data character
Labels The x axis labels
Label foreground color The foreground color for the Labels
Labels above A toggle to display data values on bar charts
Legend labels The chart legend labels for charts with grouped data
Tooltips A comma-separated list of tooltips to be passed to the delegate
Colors A comma-separated list of html-style colors, one for each segment
Delegate The name of delegate function to be called when a segment is clicked. The current Tooltip is passed to the delegate.

Hovering the mouse over the URL attribute value will show the generated URL with all the option settings.

Data - Preparing Aggregate Data

In the example Chart above, the visualization was based on data from an individual record and was refreshed as the records were navigated. However, we often need to plot aggregated data from multiple records and drill down into the details from these data collections. As mentioned, the Data for a Chart Section should consist of a comma-separated string. In this next example, we're going use SQL Select statements in conjunction with the SQLEVAL() function to query the data tables, then format the results.

commaSeparatedString = SQLEVAL(SQLSelectStatement)

The SQLEVAL() function returns a comma-separated string of values from a singleton SQL Select statement. It also creates an array called _sqlvalues when the SQL Select statement returns multiple rows and that's what we are going to use here. We'll also need the ASTRING() (or IMPLODE()) function as it returns a comma-separated string from an array (in other situations you can also change the separator character).

commaSeparatedString = ASTRING(Array)

So in a few lines (feel free to add in error checking) I can return the results of a multi-row SQL Select statement as a comma-separated string:

//strselect.prg - created in the Library for use in any App
parameter p_sqlselect
sqleval(p_sqlselect)
return astring(_sqlvalues)
Function


As before, create or open an App, open the southwind database and add a new Vertical Bar Chart Section. Then open up the Section Attributes and fill in the Data and Labels Attributes and any chosen appearance Attributes:

For desktop Apps:

Attribute Value
Data {strselect('select count(orderid) from orders group by employeeid order by employeeid')}
Labels {strselect('select distinct ltrim(etos(employeeid)) from orders order by employeeid')}

For web Apps, include the database reference in the SQL SELECT statements:

Attribute Value
Data {strselect('select count(orderid) from southwind.orders group by employeeid order by employeeid')}
Labels {strselect('select distinct ltrim(etos(employeeid)) from southwind.orders order by employeeid')}

Note: use the database.table syntax here, not database!table.

Which gives us the following Chart totaling orders in the orders table for each Employee and labeling the bars with the Employee ID number:

Aggregate Data


Let's add another Section to this Page too: select employees in the Table Explorer in the Sidebar and drag it onto the Page to create a Form Section. Double-click on the Employee ID caption and check the Search field Attribute to True. Add in the Photo and Notes if you like by dragging from the Columns Explorer into the Section to automatically create the Gadgets.

Then, use the Relationship Builder to create a relation between the Chart Section and the Employee Form Section.

In desktop Apps, certain Chart types - Vertical Bar Chart being one of them - have Tooltips Click events enabled. When a bar is clicked and Tooltips are set, the current Tooltip value is passed as a search string to any related form.

So, set the Chart Section Tooltips Attribute to be the Employee IDs (just like the Labels) and click Done to save:

Attribute Value
Tooltips {strselect('select distinct ltrim(etos(employeeid)) from orders order by employeeid')}

Now, if you click on one of the bars in the Chart, the employee details for that Employeeid will be displayed below:

Click


Passing the Tooltip as the search string to a related Section is the default behavior. There is also the option to define a Delegate script. The Tooltip is passed to the Delegate script and can be handled as required. In the Chart Section Attributes, click the [...] next to the Delegate and it will generate a script name and take you into the Editor in the Apps Workspace. If you want to choose the name of your script, type it in:

Attribute Value
Delegate page1_section1_click

For example, generate a background report when a bar is clicked:

Click Delegate


////////////////////////////////////////////////////////////////
// Event delegate for 'click' event
proc page1_section1_click(arg)
 
    // Position on the correct Employeeid using SEEK()
    if seek(val(arg),employees)
        // Refresh the Employees Section
        Lianja.getelementbyid("Page1.section2").refresh()
        // Generate and display a PDF report on the employee's orders
        // using a hidden WebView Section and the Library report.rsp
        myreport = createObject("webview") 
        myreport.url = "lib:/report.rsp?database=southwind&table=orders";
            +"&fields=*&filter=employeeid={employees.employeeid}";
            +"&heading=Orders for Employee {employees.employeeid}"
        myreport.refresh()
        cTempfile = sys(3) + ".pdf"
        myreport.print('', 'Portrait', 'PDF', cTempfile)
        showdocument("file:///"+cTempfile)
    endif
endproc

Data - Groups

As we saw in the Attributes Summary table above, Data groups are separated with the | character. For example, this will create 3 data groups:

Attribute Value
Data 1,2,3|4,5,6|7,8,9

As will this:

Attribute Value
Data {sqleval("select sum(unitsinstock), sum(unitsonorder) from products where categoryid = 1")} | {sqleval("select sum(unitsinstock), sum(unitsonorder) from products where categoryid = 2")} | {sqleval("select sum(unitsinstock), sum(unitsonorder) from products where categoryid = 3")}

Remember to include the 'database.' reference for web Apps:

Attribute Value
Data {sqleval("select sum(unitsinstock), sum(unitsonorder) from southwind.products where categoryid = 1")} | {sqleval("select sum(unitsinstock), sum(unitsonorder) from southwind.products where categoryid = 2")} | {sqleval("select sum(unitsinstock), sum(unitsonorder) from southwind.products where categoryid = 3")}

Make sure some of your products have got unitsonorder > 0 before you try this one or the Order bars will all be 0.

Data Groups


Once you are dealing with Attributes of this length, it is usually easier and more manageable to write a script to return the Attribute value and just specify a call to the script in the Attribute itself. Your script can be stored in the Library (as with the strselect.prg script above) if it will be used by more than one App, or stored in the App if it is App-specific. To store in the App, select the Apps Workspace (with the App open), click the + button at the bottom of the Files explorer to Create a new file and specify the filename with file extension.

This is the approach used for the example Grouped Vertical Bar Chart Example shown at the top of this article. A single script stored in the App handles the key Data, Labels and Tooltips Attributes. As you can see from the code below, you can use SQL or NoSQL or a combination of both.

// page1_vbar1.prg
// Script for Section vbar1 on Page page1
parameters p_prop
private m_return = ""
if pcount() != 1
	return m_return
endif
do case
case lower(p_prop) = "labels"
	select distinct ltrim(etos(employeeid)) from southwind.employees;
	order by employeeid into array m_labelarray
	m_return = astring(m_labelarray)
case lower(p_prop) = "tooltips"
	select distinct ltrim(etos(employeeid)) from southwind.employees;
	order by employeeid into array m_toolarray
	for i = 1 to alen(m_toolarray)
		m_return = m_return + m_toolarray(i) + ",";
		+ m_toolarray(i) + ",";
		+ m_toolarray(i) + ","
	next
	m_return = left(m_return,len(m_return)-1)
case lower(p_prop) = "data"
	if not isserver()
		// save data context if running on desktop
		save datasession
	else
		open database southwind
	endif
	use orders order employeeid in 0
	use employees order employeeid in 0
	scan
		select orders
		m_count2009 = cntvalues(year(orderdate)=2009, employees.employeeid)
		m_count2010 = cntvalues(year(orderdate)=2010, employees.employeeid)
		m_count2011 = cntvalues(year(orderdate)=2011, employees.employeeid)
		m_return = m_return + ltrim(str(m_count2009)) + "," ;
		+ ltrim(str(m_count2010)) + "," + ltrim(str(m_count2011))+"|"
		select employees
	endscan
	if not isserver()
		// restore data context
		restore datasession
	endif
	return left(m_return,len(m_return)-1)
endcase
return m_return

Then in the Chart Section Attributes dialog, just specify the calls to the script, passing the relevant parameter:

Attribute Value
Data {page1_vbar1("data")}
Labels {page1_vbar1("labels")}
Tooltips {page1_vbar1("tooltips")}

Note that you provide a Tooltip for each bar in the group, not just each group.

To complete the example Page, just do the following:

  • Add a Form Section for employees
  • Check the Employee Id Search field to True
  • Create a relation between the Chart Section and the Employee Form Section
  • Add a Grid Section for orders (shift-drag and drop from the Tables Explorer onto the Page)
  • Create a relation between the Employee Form Section and the Orders Grid Section

Now, you will be able to click on a bar in the Chart and refresh the related Employee Form and Orders Grid all via the default behavior of the Tooltips.

Ideas for Further Development

Chart Sections are based on the script files graph_ChartType.rsp (e.g. graph_vbar.rsp for a Vertical Bar Chart. You can see the code for the rsp scripts in the Library Workspace if you are interested in looking under the hood and could also them as templates for your own rsp pages in conjunction with a WebView Section if you want to customize the output. To see how to call an rsp page in a WebView Section (URL attribute), take a look at the charts in the Lianjachartsdemo App or the reports in the Lianjademo App.

Library scripts