LIST as a data source for JAVASCRIPT CHARTS (JS Charts)

[parkjammer]

I have a complex Oracle query that only works inside a LIST; it won't function
inside of a data-series definition.

Therefore I planned to:

• Create a LIST (which works fine)
• Populate a DATA SERIES from the LIST (which doesn't work)
• Populate a JAVASCRIPT CHART (JS Chart) from the DATA SERIES

I haven't been able to get this to work.

If you have successfully employed something similar I'd love to review your method
and take a hint or two.


As a workaround, I have been using a variant of the process:

• Create a LIST
• Create a JAVASCRIPT CHART (JS Chart)
• Populate the chart with a JAVASCRIPT FUNCTION that pulls data from the LIST
• But... there are challenges with picking picking up the LIST data as an object so I've had to pick it up as a string and then attempt to convert it using JSON.PARSE().

Thanks for taking a look.

[Sarah Mitchell]

Re: LIST as a data source for JAVASCRIPT CHARTS (JS Charts)

I have a complex Oracle query that only works inside a LIST; it won't function
inside of a data-series definition.

Therefore I planned to:

• Create a LIST (which works fine)
• Populate a DATA SERIES from the LIST (which doesn't work)
• Populate a JAVASCRIPT CHART (JS Chart) from the DATA SERIES

I haven't been able to get this to work.

If you have successfully employed something similar I'd love to review your method
and take a hint or two.


As a workaround, I have been using variant of the process:

• Create a LIST
• Create a JAVASCRIPT CHART (JS Chart)
• Populate the chart with a JAVASCRIPT FUNCTION that pulls data from the LIST
• But... there are challenges with picking picking up the LIST data as an object so I've had to pick it up as a string and then attempt to convert it using JSON.PARSE().

Thanks for taking a look.

Hello Allan,

The Data Series is a server-side feature, populated on the server and sent to the client. To use a Data Series in a Javascript Chart, the Data Series must be published client side. This is done by checking a box at the bottom of the Data Series Dialog.

What is it about your query that it cannot be used to define a Data Series and needs to be put in a List control? If the query requires a custom query or a stored procedure, the Data Series can be defined using a "custom" definition using Xbasic. A custom SQL query can also be specified for the SQL data source definition for a Data Series.

The second procedure you have outlined is the recommended method for populating a Javascript Chart using data in a List control. You mention issues using the list's data object that requires using JSON.parse(). Could you be more specific?

Thanks

[parkjammer]

Re: LIST as a data source for JAVASCRIPT CHARTS (JS Charts)

.
.

WARNING:	The problem arises from lengthy SQL statements and interactions between LISTS, DATA SERIES, and JAVASCRIPT CHARTS. It is therefore quite long. Doing my best to make it clear.
Video	A screencast video walking through from the original problem to an eventual solution (however over-wrought) can be seen here. It's 15 minutes long and you can see the successful result if you go to the 13:38 mark. SCREENCAST: A5_Issue_-_complex_Oracle_SQL_in_LIST

Sara, thanks for your response.

There are three related issues. They are difficult to reproduce unless you have a
fairly involved (though not unusual) SQL statement on an Oracle database.

The issues are:

1. It can be necessary to "wrap" a complex Oracle SQL statement in a more simple form in order it to run within the LIST
2. Using NATIVE SQL in a DATA SERIES definition... apparently you can't do this; neither has populating the data-series from a list worked.
3. When using the javascript-function technique to populate a JS Chart the resulting data-object isn't feeding the chart properly

Issue #1: complex Oracle SQL statement

Generic SQL statement (works fine usually but will fail for complex Oracle statements) [COLOR="#0033cc"] [B]SELECT[/B] field_1 , field_2 , field_3 [B]FROM[/B] Table_A , Table_B , Table_C [B]WHERE[/B] Table_A.id_field = Table_B.id_field and Table_B.id_field = Table_C.id_field [/COLOR]

WRAPPED (Make the original query into a sub-query, wrap it in a simple SELECT, then LIST and field visibility/selection will work) [COLOR="#FF0000"] SELECT TabSUB.field_1 , TabSUB.field_2 , TabSUB.field_3 FROM ( [COLOR="#0033cc"] [B]SELECT[/B] field_1 , field_2 , field_3 [B]FROM[/B] Table_A , Table_B , Table_C [B]WHERE[/B] Table_A.id_field = Table_B.id_field and Table_B.id_field = Table_C.id_field [/COLOR] ) [B]TabSUB[/B] ORDER BY 1, 2, 3 [/COLOR]

ERROR: Desired Query (note) SELECT sales_date as SALESDATE , round(sum(TabSD.actual_quantity), 2) as HISTORY , round(sum(decode(TabSD.enter_fore,NULL,(nvl(TabSD.col_for_over,(((nvl(TabSD.manual_stat,nvl(TabSD.sim_val_1,TabSD.Fore_4)) * (1.00 - nvl(decode(TabSD.trg_cannizn_perc,1,TabSD.trg_cannizn_perc,0),0)) * (1.00 - nvl(TabSD.src_cannizn_perc,0))*(1.00+nvl(TabSD.manual_fact,0))+nvl(TabSD.fixed_prom,0))*(1-nvl(TabSD.rule_coll,0)))+(nvl(TabSD.int_cost,0)*nvl(TabSD.rule_coll,0))))),TabSD.ff)*1), 2) as FORECAST , min(add_months(TabSP.Max_Sales_Date,-12)) as DATERANGE_START , max(add_months(TabSP.Max_Sales_Date, 12)) as DATERANGE_END FROM SALES_DATA TabSD , ITEMS TabI , T_EP_ITEM TabTEI , LOCATION TabL , T_EP_SITE TabTES , MDP_MATRIX TabMDP , T_EP_ORGANIZATION TabTEO , (select to_date(substr(pval,1,10), 'MM-DD-YYYY') as Max_Sales_Date from sys_params where upper(pname) like upper('MAX_SALES_DATE')) TabSP WHERE TabSD.item_id = TabI.item_id AND TabI.t_ep_item_ep_id = TabTEI.t_ep_item_ep_id AND TabL.t_ep_site_ep_id = TabTES.t_ep_site_ep_id AND TabSD.location_id = TabL.location_id AND TabSD.location_ID = TabMDP.location_id AND TabSD.item_ID = TabMDP.item_id AND TabMDP.t_ep_organization_ep_id = TabTEO.t_ep_organization_ep_id AND ( upper(TabTEI.ITEM) like upper('101STMS0285 400A') ) AND TabSD.sales_date >= (add_months(TabSP.Max_Sales_Date,-12)) AND TabSD.sales_date < (add_months(TabSP.Max_Sales_Date, 12)) AND ( round(TabSD.actual_quantity,2) > 0 OR round((decode(TabSD.enter_fore,NULL,(nvl(TabSD.col_for_over,(((nvl(TabSD.manual_stat,nvl(TabSD.sim_val_1,TabSD.Fore_4)) * (1.00 - nvl(decode(TabSD.trg_cannizn_perc,1,TabSD.trg_cannizn_perc,0),0)) * (1.00 - nvl(TabSD.src_cannizn_perc,0))*(1.00+nvl(TabSD.manual_fact,0))+nvl(TabSD.fixed_prom,0))*(1-nvl(TabSD.rule_coll,0)))+(nvl(TabSD.int_cost,0)*nvl(TabSD.rule_coll,0))))),TabSD.ff)*1),2) > 0 ) GROUP BY sales_date

ADJUSTED: seems to fix the problem... but why? (note) [COLOR="#FF0000"] SELECT TabSUB.SALESDATE , TabCLDR.DATET , nvl(TabSUB.HISTORY,0) as HISTORY , nvl(TabSUB.FORECAST,0) as FORECAST , TabSUB.DATERANGE_START , TabSUB.DATERANGE_END FROM ( [COLOR="#0033cc"] SELECT sales_date as SALESDATE , round(sum(TabSD.actual_quantity), 2) as HISTORY , round(sum(decode(TabSD.enter_fore,NULL,(nvl(TabSD.col_for_over,(((nvl(TabSD.manual_stat,nvl(TabSD.sim_val_1,TabSD.Fore_4)) * (1.00 - nvl(decode(TabSD.trg_cannizn_perc,1,TabSD.trg_cannizn_perc,0),0)) * (1.00 - nvl(TabSD.src_cannizn_perc,0))*(1.00+nvl(TabSD.manual_fact,0))+nvl(TabSD.fixed_prom,0))*(1-nvl(TabSD.rule_coll,0)))+(nvl(TabSD.int_cost,0)*nvl(TabSD.rule_coll,0))))),TabSD.ff)*1), 2) as FORECAST , min(add_months(TabSP.Max_Sales_Date,-12)) as DATERANGE_START , max(add_months(TabSP.Max_Sales_Date, 12)) as DATERANGE_END FROM SALES_DATA TabSD , ITEMS TabI , T_EP_ITEM TabTEI , LOCATION TabL , T_EP_SITE TabTES , MDP_MATRIX TabMDP , T_EP_ORGANIZATION TabTEO , (select to_date(substr(pval,1,10), 'MM-DD-YYYY') as Max_Sales_Date from sys_params where upper(pname) like upper('MAX_SALES_DATE')) TabSP WHERE TabSD.item_id = TabI.item_id AND TabI.t_ep_item_ep_id = TabTEI.t_ep_item_ep_id AND TabL.t_ep_site_ep_id = TabTES.t_ep_site_ep_id AND TabSD.location_id = TabL.location_id AND TabSD.location_ID = TabMDP.location_id AND TabSD.item_ID = TabMDP.item_id AND TabMDP.t_ep_organization_ep_id = TabTEO.t_ep_organization_ep_id AND ( upper(TabTEI.ITEM) like upper('101STMS0285 400A') ) AND TabSD.sales_date >= (add_months(TabSP.Max_Sales_Date,-12)) AND TabSD.sales_date < (add_months(TabSP.Max_Sales_Date, 12)) AND ( round(TabSD.actual_quantity,2) > 0 OR round((decode(TabSD.enter_fore,NULL,(nvl(TabSD.col_for_over,(((nvl(TabSD.manual_stat,nvl(TabSD.sim_val_1,TabSD.Fore_4)) * (1.00 - nvl(decode(TabSD.trg_cannizn_perc,1,TabSD.trg_cannizn_perc,0),0)) * (1.00 - nvl(TabSD.src_cannizn_perc,0))*(1.00+nvl(TabSD.manual_fact,0))+nvl(TabSD.fixed_prom,0))*(1-nvl(TabSD.rule_coll,0)))+(nvl(TabSD.int_cost,0)*nvl(TabSD.rule_coll,0))))),TabSD.ff)*1),2) > 0 ) GROUP BY sales_date ) TabSUB [/COLOR] RIGHT OUTER JOIN INPUTS TabCLDR ON TabSUB.SALESDATE = TabCLDR.DATET WHERE TabSUB.SALESDATE = TABCLDR.DATET ORDER BY TabCLDR.DateT [/COLOR]

Issue #2: data-series populated via NATIVE SQL

• No detail here... just doesn't seem to be possible with NATIVE SQL
• Have attempted to populate from a proper LIST but this hasn't worked either

Issue #3: populating a JS Chart from a LIST

• Used examples from the available JS Chart videos
• Augmented with examples from the forum and general net searches
• However... the resulting object variable does not feed the scatter chart properly
• Had to loop through the object and compose it as a string
• Then covert the string back to an object using JSON.PARSE()
• Finally got it to work... but clearly there must be an easier method.
  

Javascript that doesn't work (Clearly some kind of array/object definition issue) function js_ListData_History_2() { // Pick up the control ID (a LIST) var lObj = {dialog.object}.getControl('N1L1P2_LST1'); // Get the data from the LIST var _d = lObj._data // Define an empty array var _a = []; // Pull the content of a list and put it in an array for(var i = 0; i < _d.length; i++) { // Compose date into YYYY/MM/DD format _a.push('{"x":' + '"' + _d[i].SALESDATE.substring(6,10) + '-' + _d[i].SALESDATE.substring(0,2) + '-' + _d[i].SALESDATE.substring(3,5) + "T00:00:00.000Z" + '"' + ', "y":' + _d[i].HISTORY +'}'); }; // Test line document.getElementById('N1L3P2_DIV1').innerHTML = _a; return _a; }

Javascript that DOES WORK (Granted, it's a fairly convoluted way to get it to work) function js_ListData_History_4() { // Pick up the control ID (a LIST) var lObj = {dialog.object}.getControl('N1L1P2_LST1'); // Get the data from the LIST var _d = lObj._data // Define an empty array var _a = []; // Define an object to hold dynamically gathered data from LIST var vl_data_dynamic; // Define a string for JSON content assembly var vl_json_content = ""; // Pull the content of a list and put in a JSON compliant string for later conversion to an object // for(var i = 0; i < 1; i++) { for(var i = 0; i < _d.length; i++) { // Compose date into YYYY/MM/DD format vl_json_content = vl_json_content + '{"x":' + '"' + _d[i].SALESDATE.substring(6,10) + '-' + _d[i].SALESDATE.substring(0,2) + '-' + _d[i].SALESDATE.substring(3,5) + '"' + ', "y":' + _d[i].HISTORY +'}'; // Sample JSON PARSE date format: 2016-04-26T18:09:16Z if (i < ( _d.length) - 1 ) { vl_json_content = vl_json_content + ", "; }; }; // Add bounding single-quotes vl_json_content = '[' + vl_json_content + ']'; // Test alert //alert(vl_json_content); // The array does not work on its own; it appears to need conversion to an object vl_data_dynamic = JSON.parse(vl_json_content); // Test line document.getElementById('N1L3P2_DIV1').innerHTML = vl_data_dynamic; return vl_data_dynamic; }

[peteconway]

Re: LIST as a data source for JAVASCRIPT CHARTS (JS Charts)

Well Sarah, thats as specific as it comes. Allan, these are issues that have driven me crazy over the years, I just never had the skill to do what you just did, and Sarah, if Allan is not on the consultative review team of Alpha (if it even exists and if it doesn't, it should) and if he's not it, he should be.

Allan, I can't thank you enough for this presentation but you will get a request to submit a test case to fix it...

Great work, thanks man.

Pete

[parkjammer]

Re: LIST as a data source for JAVASCRIPT CHARTS (JS Charts)

Thanks Pete.

I have had 50+ of these types of issues since I started working with Alpha. I remain
impressed with many of Alpha's capabilities and its promise... but often have wondered
if I'm missing an "Alpha gene" somewhere that makes this stuff smooth and easy.

Perhaps I'm not entirely nuts...

:-)

[peteconway]

Re: LIST as a data source for JAVASCRIPT CHARTS (JS Charts)

Same, Allen's work is a relief for me as well. Hope it gets a result.

[Michael1954]

Re: LIST as a data source for JAVASCRIPT CHARTS (JS Charts)

When I hit this type of problem in Alpha I tend to try something else. While I don't fully understand the problem I was wondering if you could build a Google chart server-side. Selwyn has an excellent video. UX_V12_86 the first of 3 is http://www.ajaxvideotutorials.com/V1...tingData_1.swf

[peteconway]

Re: LIST as a data source for JAVASCRIPT CHARTS (JS Charts)

Well thats how I ended up doing via the List Control.

[parkjammer]

Re: LIST as a data source for JAVASCRIPT CHARTS (JS Charts)

Michael,

It's a fair question.

I've built fairly complex UX components in the past involving lists, charts, data-series, maps, etc.

At the time I used a server-side set of SQL and PL/SQL constructs to "pre-aggregate" the data. That is, the complex Oracle SQL was being executed by an external process and a single table was the result.

A LIST or a DATA-SERIES has no problem picking up data from the resulting single table. It's a simple SQL statement.

The problem is that the data is refreshed only once or twice a day from the complex query into the single resultant table. That isn't good enough for my present needs.

As a result, a more complex Oracle SQL query needs to be executed from the LIST or from a DATA-SERIES in order to feed a chart (server-side or client-side).

This leads to three problems:

1. LISTs have challenges with complex Oracle SQL queries (see "sub-query" reference in prior note)
2. DATA-SERIES do not seem to be able to handle complex Oracle SQL queries at all
3. When a LIST can be made to perform the query, there are issues in feeding the data to a JS CHART or to a DATA-SERIES

So I made it work using a workaround that took me many hours to figure out.

It seems likely there is a proper way to do it that is more straight forward and that's what I'm looking for now.

Cheers!

[parkjammer]

Re: LIST as a data source for JAVASCRIPT CHARTS (JS Charts)

Michael,

Thanks for the video reference. I've searched for and watched many videos; I didn't watch this one before (I watched the others about using Javascript Charts, using javascript to populate them with SQL data, using data-series drawn from SQL-data, etc). As a note, the link you included launches a downloaded version of the file that I can't run (no flash components any longer). The YouTube reference is:

Alpha Anywhere Google chart getting data Part 1, 2 and 3
|
|
|-- https://www.youtube.com/watch?v=3Rdf1SEKg_Q
|
|-- Uses server-side XBASIC to query a database with SQL
|-- Delivers the result as javascript and JSON to the client-side UX

The one challenge with this method is that it's one-way; the data goes from SQL to the chart.

If I want two-way interaction (view, edit, save) then I need a LIST to view/edit/save data and a CHART that draws from the LIST.

Could I write this all as custom lists, forms, javascript, and xbasic? Of course. But that entirely defeats the purpose fo a RAD tool that is intended to be "low code".

On the whole I find almost everything I've ever tried to do with Alpha has required getting 80% there with some pre-built module and then building the remaining 20% with raw code. The problem is that the latter 20% of code took 80% of the time.

Onwards...

When I hit this type of problem in Alpha I tend to try something else. While I don't fully understand the problem I was wondering if you could build a Google chart server-side. Selwyn has an excellent video. UX_V12_86 the first of 3 is http://www.ajaxvideotutorials.com/V1...tingData_1.swf

[Michael1954]

Re: LIST as a data source for JAVASCRIPT CHARTS (JS Charts)

If I want two-way interaction (view, edit, save) then I need a LIST to view/edit/save data and a CHART that draws from the LIST.

The method outlined requires a visit to the server. So after saving you just redraw the list.

I have an offline list that I edit, I can loop through the list very easily and use it as a data source. Our lists are virtualised and about 7,500 lines. It is acceptable from a speed point of view but it is not "quick". My point is you can use the list as a data source and build arrays. These arrays can be displayed as graphs with Rgraph.

[parkjammer]

Re: LIST as a data source for JAVASCRIPT CHARTS (JS Charts)

Michael,

I'm aware that populating, refreshing, or saving contents from a LIST requires a call to the server.

The video referenced in the prior thread is certainly a good and helpful video for pulling SQL data
on the server side and delivering it to a data-series or chart. It does not provide a method for
making changes and saving them back.

Using a LIST isn't the only method for two-way (view & edit) of data but it certainly exists for that
purpose and ideally should be plug-n-play.

As noted prior, my challenges have been:

• Getting complex Oracle SQL queries to work inside of a LIST (there appears to a flaw in the Alpha interface)

• Getting the data from a LIST smoothly to a JS Chart (Rgraph), specifically a date-centric scatter diagram.
  • Assigning the LIST content to an OBJECT using the alpha methods does not produce a result that can easily be fed to the javascript chart (or so it seems)
  • so I had to convert it to a string and map it back to an OBJECT (not an ARRAY OBJECT)
  • It took me a long while to sort this out.

So do I have a solution? Well, it works.

It's just that I figure there should have been a better, simpler, and more obvious way to achieve this end.

If you are taking data directly from a LIST (specifically a DATE-centric data set) and populating a JS CHART then I'd be curious to see the javascript function you are using to do that.

Thanks for taking a moment to look!

The method outlined requires a visit to the server. So after saving you just redraw the list.

I have an offline list that I edit, I can loop through the list very easily and use it as a data source. Our lists are virtualised and about 7,500 lines. It is acceptable from a speed point of view but it is not "quick". My point is you can use the list as a data source and build arrays. These arrays can be displayed as graphs with Rgraph.

[GarthMc]

Re: LIST as a data source for JAVASCRIPT CHARTS (JS Charts)

Hi,

I too have had issues with using native queries when attempting to use Oracle, (we use lots of package based functions within sql here). I find creating the query as a view on the database, then referring to that gets around those problems.

[parkjammer]

Re: LIST as a data source for JAVASCRIPT CHARTS (JS Charts)

Thanks for the note.

I've resolved to use interim tables (SQL to interim-table, allow quick view/edit, then use procedures and MERGE statements to save the data back... it is aggregated data and required proportioning).