using Google Charts and arrayData

[Dino]

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 (11.29 KiB) Viewed 10889 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 (50.44 KiB) Viewed 10889 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 (79.3 KiB) Viewed 10889 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 (172.78 KiB) Viewed 10889 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 (98.66 KiB) Viewed 10889 times

and this is the final result:

axesgraph03.png (86.19 KiB) Viewed 10889 times

[Dino]

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(",",""))