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.

Name:  Lianja charts.jpg Views: 76 Size:  52.3 KB

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.


Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.