Org Chart [examples]

Dynamic node backcolor

The optional Dynamic node backcolor Attribute specifies the delegate to return the background color for the node for the current record being processed.

The ‘Lianja Org Chart Demo’ App specifies a delegate:

oc_getBackColor

The delegate is contained in the Custom delegate library as described below.

proc oc_getBackColor()
	if lower(title) = "sales representative"
		return "lightgreen"
	elseif lower(title) = "vice president, sales"
		return "lightpink"
	elseif lower(title) = "sales manager"
		return "lightblue"
	elseif lower(title) = "inside sales coordinator"
		return "beige"
	else
		return ""
	endif
endproc

https://www.lianja.com/doc/index.php/Org_Chart_Options

---

 

Charts [examples]

Agregate data from multiple records. Data for a Chart section consists of a comma-separated string. SQLEVAL() returns string from a singleton SQL select. It also creates an array called _sqlvalues when SQL select returns multiple rows. ASTRING() returns comma-separated string from an array.

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

https://www.lianja.com/doc/index.php/Data_Visualization_with_Charts

---

Generate a background report PDF when a bar is clicked.

////////////////////////////////////////////////////////////////
// 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

https://www.lianja.com/doc/index.php/Data_Visualization_with_Charts

---

Data groups are separated with the | character.

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

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”)}

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”)}

When Value attribute is of this length, it is easier to write a script in the Library and is called.

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

// 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

https://www.lianja.com/doc/index.php/Data_Visualization_with_Charts

---

 

 

 

Chart

Q:

I was trying the chart options, and I saw there are RGrpah.com charts, and I tried to modify some of them (in example for negative values in “line” chart) and then I was trying to add a new one, following the script structure of RSP files.

This is an example I wrote and saved as graph_linefilledblue.rsp in library folder:

(due to interference with blog syntax, underscore was inserted into code, i.e. h_tml for html, h_ead for head, s_cript for script, b_ody for body, s_tyle for style, d_iv for div…)

<%@ Language=Recital %>
<h_tml>
<h_ead>
<s_cript>
<%
set macros off
%>

<%@ include=”graphs/RGraph.common.core.js” %>
<%@ include=”graphs/RGraph.line.js” %>
</s_cript>
</h_ead>
<%
set macros on
//–
// graph_line.rsp?parameter=value&parameter=value…
//
// parameters
//–
private m_width = getParameter(“width”, “760”)
private m_height = getParameter(“height”, “350”)
private m_id = getParameter(“id”, “graphCanvas”)
private m_data = getParameter(“data”, “10,4,17,50,25,19,20,25,30,29,30,29|;
4,17,50,25,19,20,25,30,29,30,29,45|;
50,25,19,20,25,30,29,30,29,10,4,17”)
private m_labels = getParameter(“labels”, “”)
private m_keylabels = getParameter(“keylabels”, “2009,2010,2011”)
private m_guttertop = getParameter(“guttertop”, “25”)
private m_gutterbottom = getParameter(“gutterbottom”, “25”)
private m_gutterleft = getParameter(“gutterleft”, “50”)
private m_gutterright = getParameter(“gutterright”, “60”)
private m_shadow = getParameter(“shadow”, “true”)
private m_title = getParameter(“title”, “Untitled Line Chart”)
private m_titleforecolor = getParameter(“titleforecolor”, “gray”)
private m_titlebackcolor = getParameter(“titlebackcolor”, “”)
private m_labelsabove = getParameter(“labelsabove”, “true”)
private m_margin = getParameter(“margin”, “5”)
private m_colors = getParameter(“colors”, “lightblue,lightgreen,red,yellow”)
private m_labelforecolor = getParameter(“labelforecolor”, “gray”)
private m_backcolor = getParameter(“backcolor”, “#f7f7f7”)
private m_effects = getParameter(“effects”, “true”)
private m_targetui = getParameter(“targetui”, “web”)

if len(m_labels)=0 and m_targetui = “phone”
m_labels = “J,F,M,A,M,J,J,A,S,O,N,D”
elseif len(m_labels)=0
m_labels = “Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec”
endif

// emit javascript code
text raw
<b_ody style=”background:&m_backcolor;overflow:hidden;”>
<s_tyle>
.center-div
{
position: relative;
margin: auto;
top: 0;
right: 0;
bottom: 0;
left: 0;
width: &m_width.px;
height: &m_height.px;
}
</s_tyle>
<d_iv class=”center-div”>
<c_anvas id=”&m_id” width=”&m_width.px” height=”&m_height.px” style=”background:&m_backcolor;overflow:hidden;”>[No canvas support]</canvas>
</d_iv>
<s_cript>
window.onload = function ()
{
var line = new RGraph.Line({
id: ‘cvs’,
data: [
1,3,4,2,5,6,4,5,3,7,8,9,8,9,10,13,15,12,16,17,15,1 8,15,15,19,16,19,
21,22,20,23,21,25,24,23,26,25,27,28,27,26,28,29,28 ,28,30,32,31,32,33,34,
35,34,36,38,35,36,37,39,40,42,43,46,45,44,46,48,47 ,49,50,52,51,49,56,55,
61,62,65,66,68,69,75,76,78,80,84,86,89,91,95,92,99 ,105,108,106,110,111,
119,125,128,134,139,143,148,150
],
options: {
labels: [‘Aug 13, 2012′,’Sep 9 2013′,’Oct 6 2014’],
gutterLeft: 75,
gutterRight: 55,
filled: true,
fillstyle: [‘#C2D1F0’],
colors: [‘#3366CB’],
shadow: false,
tickmarks: null,
numxticks: 0,
backgroundGridVlines: false,
backgroundGridBorder: false,
noxaxis: true,
textSize: 16,
textAccessible: true
}
}).draw();
};
</s_cript>
endtext
%>
</b_ody>
</h_tml>

I know it’s not finish, specially for vars, I was thinking first to open it and see if it’s working (as the script code is copied from https://www.rgraph.net/demos/svg-line-filled-blue.html) I’m not able to find some “index” or similar for add it to the main program. A: Webview sections and webview gadgets can be based on an rsp file. Have a look at the lianjachartsdemo App (Lianja Charts Demo). That was created before Chart Sections were introduced and its sections are Webview sections with URLs set to the chart rsp scripts.

---

Q: is there a way to write a query to split values from a column into multiple columns and adding the total? I have the following table:

Code:

Table: cust_transactions
pur_date      cus_name pur_type  pur_total
01/01/2016    AMW      I         15.00
01/03/2016    FTY      i         40.00
01/05/2016    TEC      X         30.00
01/06/2016    AMW      X         20.00
01/07/2016    AMW      i         90.00
01/10/2016    FTY      I         100.00
01/13/2016    TEC      I         355.00
01/18/2016    AMW      X         29.99
01/20/2016    FTY      X         23.00
01/20/2016    FTY      I         50.00
01/22/2016    FTY      i         187.00
01/30/2016    TEC      i         325.00

I would like to get the data filtered and have the following output;

Code:

cus_name pur_type_i   pur_type_I  pur_type_X
AMW      90.00	      15.00 	  49.99						
TEC      325.00       355.00      30.00
FTY      227.00       150.00      23.00

the above output is splitting the values “i”, “I” and “X” from 1 column into different columns and at the same time is adding its total.

A:

I can not see how to get it in one pass.
Trying with Lianja’s Southwind PRODUCTS table (supplierid<3 to narrow the result, more similar to your example data)
Equivalents:
supplierid=cus_name,
categoryid=pur_type,
unitsinstock=pur_total

Code:

select supplierid,categoryid,unitsinstock from products where supplierid<3

got

Code:

SUPPLIERID CATEGORYID         UNITSINSTOCK


          1          1             22.00000
          1          1             63.00000
          1          2             89.00000
          2          2             53.00000
          2          2              0.00000
          2          2             76.00000
          2          2            104.00000
          2          3              0.00000

Then

Code:

select supplierid,categoryid,icase(categoryid=1,sum(unitsinstock),sum(0)),icase(categoryid=2,sum(unitsinstock),sum(0)),icase(categoryid=3,sum(unitsinstock),sum(0)) from products group by categoryid,supplierid having supplierid<3 into cursor FIRST_CURSOR

got

Code:

 SUPPLIERID CATEGORYID                  EXPR0001                  EXPR0002                  EXPR0003


          1          1                        85                         0                         0
          1          2                         0                        89                         0
          2          2                         0                       233                         0
          2          3                         0                         0                         0

I understand that you want result:

Code:

SUPPLIERID    EXPR0001                  EXPR0002                  EXPR0003

          1          85                       89                         0
          2          0                       233                         0

This is a step away.

Code:

select supplierid, sum(expr0001), sum(expr0002), sum(expr0003) from FIRST_CURSOR group by supplierid

Put all together in Lianja’s Console or in script:

Code:

open database southwind
select supplierid,categoryid,icase(categoryid=1,sum(unitsinstock),sum(0)),icase(categoryid=2,sum(unitsinstock),sum(0)),icase(categoryid=3,sum(unitsinstock),sum(0)) from products group by categoryid,supplierid having supplierid<3 into cursor FIRST_CURSOR
select supplierid, sum(expr0001), sum(expr0002), sum(expr0003) from FIRST_CURSOR group by supplierid

Code:

 SUPPLIERID                  EXPR0001                  EXPR0002                  EXPR0003


          1                        85                        89                         0
          2                         0                       233                         0

In MS SQL I believe it can be done by

Code:

select 
case 
when categoryid=1 then sum(unitsinstock)
else 0.00
end as expr0001,

case 
when categoryid=2 then sum(unitsinstock)
else 0.00
end as expr0002,

case 
when categoryid=3 then sum(unitsinstock)
else 0.00
end as expr0003,

categoryid,
supplierid  
from products group by categoryid, supplierid

A2:

what you are trying to do is call a crosstab.

Here’s a great article on using crosstabs with VFP: http://www.tomorrowssolutionsllc.com…Cross-tabs.PDF This is useful for getting an understanding of crosstabs, and also the use of the parameters

We use FastxTab (as it is, indeed, faster). Here’s an article that demonstrates fastxtab and extends it to use long fieldnames: http://sandstorm36.blogspot.com/2011…cross-tab.html

The download for fastxtab is: https://www.universalthread.com/Show…m.aspx?ID=9944

---

Q:

Trying to get a line graph going. Everything works EXCEPT X-axis labels in line charts. X-axis insists on displaying months rather than my labels. If I flip line to bar chart x-axis labels show up fine. Am I missing something here? My setup is below.

A:

There’s a bug in the library file graph_line.rsp that was introduced in 3.1. I have fixed this in the next 3.2 release.

The good news is you have all the source code for these charts so you can study these .rsp files and edit the file to fix it quite easily.

– Select the library workspace
– Double click the file graph_line.rsp
– Change line 48 to be:

elseif len(m_labels)=0

---

Lianja chart gadgets provide charting functionality already and they are fully integrated in with Lianja. So building dashboards can be done now.

As development of 4.1 has progressed I have considered adding a “chart” class to the framework classes so that charts can be easily embedded into grid cells and formgrid cells.

---

Chart

Q:

in a web app how do I create a chart with dynamic data from a vt_table?

A:

You can use {…} macros in many places where you would normally specify a comma separated list of values. This {…} macro can contain a custom procedure that is called to substitute the value returned from it. Typically in the case of charts it should return a comma separated list.

e.g.

{getSomeData(args,...)}

These {…} macros can be also specified as part of page headers, section headers, section footers etc.

Macros are not commands. They are used to substitute expressions into an attribute.

For example.

Let’s say you have a section header caption like this.

Customer is {customers.name}

Then as the section header is updated as you navigate between records the caption will display the current customer name.

These {…} macros can be used in the attributes for UI elements so that the value is dynamic and context sensitive to the current data you are viewing.

---

.rsp page that generates the Gantt chart probably needs to have the following in the

<meta charset=”UTF–8“>

If you rtrim() the names and they will be right justified next to the gantt chart.

---

Q:

I’m doing some tests with charts.

If you recall a graph with the command:

Code:

       m_data = "1,2,3,3,4,5|1,2,3"
        m_keylabels = "a,b"
        m_labels = "1,2,3,4,5"
        
        m_url = "lib:/graph_line.rsp?data=" + m_data + "&keylabels=" + m_keylabels + "&labels=" + m_labels + "&title=" + m_title            
        
        lianja.get("Scout.SectionChart").url = m_url

I get this:

It is not possible to do so that the second line, the green one, occupies only the first 3 points of the graph, without stretching up to 5?

A:

No, the line chart you refer to gas a max and min value and the graph is rendered based on that.

Maybe you should be using a bar chart or a Gantt chart.

A2:

I try with a bar chart.

I noticed that if I send a “null” value, the graph is correct …

but you can not decide on the vertical scale is always 100 …

---

Q:

I’m having problems to show the progression of the tasks as I’m unable to build an array that passes the correct type of information to the chart, as I need to pass it in the following format: [int,int,int,str]. So, I’m able to build an array that passes this as an string but it doesn’t work. Any idea? This is the code I’m using for the function. I’ve build also an script (yeardays) that convert a date to a number of days, so the user inserts a date but the script passes an int to the array. etd is the initial date, eta is the due date, accomplishment is the percentage of accomplishment and name is the name of the task

select id from tareas where project=p_project into array numtareas
 select etd from tareas where project=p_project into array etds
 select eta from tareas where project=p_project into array etas

 select accomplishment where project=p_project from tareas into array accomplishments
 select name from tareas where project=p_project into array names
 declare tab[alen(numtareas,1),4]
 declare gantt[alen(numtareas,1)]
 for o=1 to alen(tab,1)
 tab[o,1]=yeardays(etds[o])
 tab[o,2]=yeardays(etas[o])-yeardays(etds[o])
 tab[o,3]=accomplishments[o]
 tab[o,4]=names[o]
 gantt[o]=concat("[",str(tab[o,1]),str(tab[o,2]),str(tab[o,3]),"'",tab[o,4],"'","]")
 endforreturn implode(',',gantt)

A:

If you need format [int,int,int,str], then I do not see that you put sign “,” in your concat. Try in console this CONCAT expression first

Code:

gantt[o]=concat("[",str(tab[o,1]),str(tab[o,2]),str(tab[o,3]),"'",tab[o,4],"'","]")

When I simulate with this code:

Code:

declare tabx[1,4]
declare gantt[1,1]
tabx[1,1]=11
tabx[1,2]=12
tabx[1,3]=13
tabx[1,4]='MyName'
gantt[1,1]=concat("[",str(tabx[1,1],2,0),str(tabx[1,2],2,0),str(tabx[1,3],2,0),"'",tabx[1,4],"'","]")
?gantt[1,1]

… I get this;

Code:

[111213'MyName']

Correct CONCAT:

Code:

gantt[1,1]=concat("[",str(tabx[1,1],2,0),",",str(tabx[1,2],2,0),",",str(tabx[1,3],2,0),",'",tabx[1,4],"'","]")

and result is:

Code:

[11,12,13,'MyName']

---

Q:

The problem now are the Gantt labels.

A:

Gantt is another problem with accents.

I applied Berry’s soulution (I edited graph_gantt.rsp in your app)…:

…and it works, showing your accent letters:

 

A:

tab[o,4]=padr(names[o],30,’ ‘)

If you rtrim() the names and they will be right justified next to the gantt chart.

Chart

The OrgChart section is data bound and when clicking or double clicking a node you can switch pages or relate information in other sections as I have done in this simple example shown below.
There are so many things that you can do with this and as it is a WebViewWidget you can refresh it with different contents when clicking or double clicking to drill down through the hierarchy.
This simple example is all coming from the southwind!employees table. No coding required.

And here it is running in Chrome against the Lianja Cloud Server.

Org Chart section with its own attributes, click and dblClick delegates, and color coded nodes based on the data being processed to display it.

---

You can develop complete data visualization components and embed these into your WebViews in Lianja. These work across Desktop, Web and Mobile.When embedded into a WebView generated by a Lianja/VFP (.rsp page) or JavaScript (.jssp page) they can be dynamically changed as you navigate through your data in form or grid sections. This provides powerful data visualization functionality for data driven management dashboards with little to no coding required.

Tip: You can use the built-in Lianja Odata_read(), print_json(), and the json_encode() functions in your .rsp or .jssp pages to generate the JSON for the charts.
More about writing Lianja Server pages can be found here.

---