using Google Charts and arrayData

Use this Forum to post tips and techniques for using aXes. Please explain in detail. This Forum is managed by the aXes user community. LANSA cannot guarantee the accuracy of any information posted to this Forum.

Moderator: jeanmichel

Post Reply
User avatar
Dino
Posts: 46
Joined: 19 May 2017, 08:29

using Google Charts and arrayData

Post by Dino » 03 Jun 2022, 02:59

There are different ways to populate graphics, I like to have all the control using arrayData.

Let's say you have an screen like this one:
axesgraph00.png
axesgraph00.png (11.29 KiB) Viewed 83 times
and you want to show a Pie Chart split between the balance and the available credit limit, and a column chart with the sales per month...

as you can see in the screen, you can calculate the available credit limit by a simple difference,
but not all the sales per the month are displayed because the subfile didn't fit in the screen.

If you know what table, physical file is that one, then a simple solution is to create a "Dynamic Table",
which is just a sql query over that table and you can use a variable for example,
in this case the customer Id, to get the information you want. You define this "dynamic table"
at project level in aXes and be sure to restart your development session after that to see the changes:
axesgraph01.png
axesgraph01.png (50.44 KiB) Viewed 83 times
the code for this dynamic table, or sql query over the table LTCCUSTSL in the library G14RHOLIB is complete here, notice the use of a variable i called SQLVariableCustomer and the control of which field I return as value, and which one as the text to display:

Code: Select all

-- ====================================================================================
-- List of sales per month that belong to a customer specified by :SQLVariableCustomer
-- ====================================================================================

    DefineObjectInstance { 

      className          = "DynamicTable", 
      name               = "CustomerSales",   
      source             = "sql", 
      selectSQLcommand   = "LZPRMNTH, month from G14RHOLIB.LTCCUSTSL where LTCCUSTID = ':SQLVariableCustomer' ", 
      resultColumnNames  = { "value", "text" },

    }; 
With that in mind, I can go to aXes, identify the screen and the fields I want to access from the screen, like customerID, creditLimit and currentBalance (be very careful with lowercase and uppercase usage):
axesgraph02.png
axesgraph02.png (79.3 KiB) Viewed 83 times
once you have done that, you can bring a couple of google chart extensions to the screen and add the next properties for the first graph:

chartType : Pie Chart
dataSourceType : Fixed Array (Google Array)
arrayData :

Code: Select all

var available = 
    parseFloat(FIELDS("creditLimit").getValue())
    - parseFloat(FIELDS("currentBalance").getValue());
ENV.returnValue = [["Range","Amount",{ role: 'style'}]];  
ENV.returnValue.push(
    ["Available",
    parseFloat(available),
    "color:#202080"]) ;
ENV.returnValue.push(
    ["Balance",
    parseFloat(FIELDS("currentBalance").getValue()),
    "color:#802020"]) ;
notice the code is basically filling an array, retrieving the value of the fields from the screen, and because they are considered string, i am converting those to numbers, using the parseFloat() function before calculating and before adding to the array. The style is just an RGB color in HEX.
axesgraph04.png
axesgraph04.png (172.78 KiB) Viewed 83 times
and for the second graph, with the column chart and the variable:

chartType : Column Chart
dataSourceType : Fixed Array (Google Array)
arrayData:

Code: Select all

customer = FIELDS("customerID").getValue();
TABLEMANAGER.loadDynamicTable("CustomerSales",
    USERENV.dynamicTablesFile,
    {SQLVariableCustomer : customer}, false );
result = TABLEMANAGER.getTable("CustomerSales");
ENV.returnValue = [["Month","Amount",{ role: 'style'}]];  
  for (var i = 0; i < result.childCount(); i++)   {
	var oRow = result.child(i);
    ENV.returnValue.push([oRow.text,oRow.value,"color:#208020"]) ;
  }
in the second code to populate an array as well, we access the DynamicTable we defined before, pass the variable with the customer id, and for each record we retrieve, we add to the array.
axesgraph05.png
axesgraph05.png (98.66 KiB) Viewed 83 times
and this is the final result:
axesgraph03.png
axesgraph03.png (86.19 KiB) Viewed 83 times

User avatar
Dino
Posts: 46
Joined: 19 May 2017, 08:29

Re: using Google Charts and arrayData

Post by Dino » 08 Jun 2022, 06:11

By the way, as aXes takes the input fields in the screen as strings, sometimes in the conversion to number, those "," in the number won't be recognized correctly, so this:

Code: Select all

parseFloat(FIELDS("currentBalance").getValue())
could easily work better like this when you have edition codes/words:

Code: Select all

parseFloat(FIELDS("currentBalance").getValue().replace(",",""))

Post Reply