Development Team Blog

What do you need to do when you want to enrich your web application with some charts? First of all it is important to find data that let itself nicely present in a chart but you probably have that else you won't be interested in reading this blog. Then you need to decide what kind of chart you would like to use. Do you want a pie, a bar or a line graph? Dealt with this it is time to find a good API, a good tool that you can use. There are many solutions available on the web. For this blog I used the Google Charts API. Take a look at the chart gallery they offer.

For this blog I took the customer table of the AJAX Order Entry example that is provided with Visual DataFlex. In the table there is a credit limit and a purchases value per customer and we can use these values to produce the following Top N customers chart.



The Base
First step in this process is to create a page in which we add the code to display the chart. Take a look at one of the existing pages and see that it consist of some HTML code and some include statements to bring in page top and bottom. So let us start with that.

Code:

<!-- #include FILE="pagetop.inc.asp" -->


<title>Order Entry - Top N Customers</title>

<!-- #include FILE="head.inc.asp" -->



<!-- #include FILE="bodytop.inc.asp" -->
<!-- #INCLUDE FILE="bodybottom.inc.asp" -->


<!-- #INCLUDE FILE="pagebottom.inc.asp" -->

Save the above as TopNCustomers.Asp and run it in your browser. You will get the following result:



The charts we add need to be placed on the page and for this we use/add two placeholders in the page. The two placeholders are elements each with their own ID. Change the source above to:

Code:

<!-- #include FILE="pagetop.inc.asp" -->


<title>Order Entry - Top N Customers</title>

<!-- #include FILE="head.inc.asp" -->



<!-- #include FILE="bodytop.inc.asp" -->




<!-- #INCLUDE FILE="bodybottom.inc.asp" -->


<!-- #INCLUDE FILE="pagebottom.inc.asp" -->

The two bold printed lines were added. This is the pure HTML part of the whole job. We need to add Visual DataFlex code to provide data and JavaScript code to convert the data to a chart.

The Visual DataFlex Part
For the Visual DataFlex source code either create a new webservice object to the project or use an existing. For this example I used the existing webservice.wo. Open the file in the Visual DataFlex Studio and add the following function between the object...end_object code.

Code:

{ Published = True }
{ Description = "Delivers an array with the top N customers based on balance data. When N is passed as 0, all customers are returned." }
Function TopNCustomers String sSessionKey Integer iTopN Returns tAJAXTopNCustomers
    tAJAXTopNCustomers TopNCustomersInfo
    Handle hoSessionManager hoErrorHandler
    Integer iError
    Boolean bResult
    
    Get phoSessionManager to hoSessionManager
    Get phoErrorHandler to hoErrorHandler
        
    //  Record errors
    Send StartRecording of hoErrorHandler
    
    //  Validate session
    Get ValidateSession of hoSessionManager sSessionKey to iError
    If (iError = 0) Begin            
        //  Validate access rights like it is an RMI call
        Get HasRights of hoSessionManager sSessionKey "rmi" "oCustomer" "TopNCustomersWS" to bResult
        If (bResult) Begin
            Get TopNCustomersWS of oCustomer iTopN to TopNCustomersInfo.TopNCustomers
        End
        Else Begin
            Error DFERR_OPERATOR "Action not allowed"
            Move DFERR_OPERATOR to TopNCustomersInfo.tResponse.sReturnValue
        End            
    End
    Else Begin
        Move iError to TopNCustomersInfo.tResponse.sReturnValue
    End
    
    //  Fetch errors
    Get FinishRecording of hoErrorHandler to TopNCustomersInfo.tResponse.aErrors
    
    Function_Return TopNCustomersInfo
End_Function

As you can see the code is already quite advanced because it keeps track of errors, performs a check if you are allowed to query the data etc.
The function call returns an struct described as:

Code:

Struct tAJAXTopNCustomers
    tTopNCustomer[] TopNCustomers
    TAjaxRMIResponse tResponse    
End_Struct

With the first member we can transfer the real customer data, with the second member we can keep track of errors and other information messages. The first member is an array of a struct described as:

Code:

Struct tTopNCustomer
    String sCustomerName
    Currency cuPurchases
    Currency cuBalance
    Currency cuCreditLimit
End_Struct

For each customer we will transfer the name, the purchase, the balance and credit limit as currency values.

As you can see in the code the TopNCustomers function in our webservice object calls a function named TopNCustomersWS defined in the customer cAjaxWebBusinessProcess process object. This way the webservice object only holds the outer function call and the cAjaxWebBusinessProcess can provide the data. A method inside a cAjaxWebBusinessProcess cannot be called from the outside world if it is not published and we deal with the rights in the webservice object.

The code for TopNCustomersWS is as follow:

Code:

Function TopNCustomersWS Integer iTopN Returns tTopNCustomer[]
    tTopNCustomer[] TopNCustomerInfo
    Integer iField iElement
    
    If (iTopN = 0) Begin
        Get_Attribute DF_FILE_RECORDS_USED of CUSTOMER.File_Number to iTopN
    End
    
    Send Clear of oCustomer_DD
    Get_FieldNumber CUSTOMER.BALANCE to iField
    Fill_Field CUSTOMER.File_Number iField With DF_HIGH
    Send Request_Read of oCustomer_DD LT CUSTOMER.File_Number 3
    Repeat
        Move (Trim (CUSTOMER.NAME)) to TopNCustomerInfo[iElement].sCustomerName
        Move CUSTOMER.PURCHASES to TopNCustomerInfo[iElement].cuPurchases
        Move CUSTOMER.BALANCE to TopNCustomerInfo[iElement].cuBalance
        Move CUSTOMER.CREDIT_LIMIT to TopNCustomerInfo[iElement].cuCreditLimit
        Increment iElement
        Send Locate_Next of oCustomer_DD
    Until (iElement >= iTopN or (not (Found)))
    
    Function_Return TopNCustomerInfo
End_Function

If the function is called with a zero for the top N amount we return the values for all customers. With a large table that is not a good idea. The code starts by seeding the highest possible value in the buffer for customer and then finding by index 3 in a reverse order. This way the best performing customers will show up first. Now we have done the DataFlex side don't forget to compile the application.

The JavaScript Part
We return to the ASP file contents. We need to include some script code so that the data can be retrieved and converted in a chart. First we add code to include Google Javascript for the charts to the section with:

Code:

<script type="text/javascript" src="http://www.google.com/jsapi"></script>

This does not do anything on its own. We need to another piece of script code that starts the whole chart work. Add the following too to the section:

Code:

<script type="text/javascript">
    google.load("visualization", "1", {packages:["corechart"]});
    google.setOnLoadCallback(drawChart);
</script>

If you save your ASP file and would run it you can see that the browser goes out to the web to include Google's code but in the end does do nothing. For the chart we need to add our code to the event named drawChart. This code is:

Code:

function drawChart() {
    var oElement, oCall;
    
    oElement = document.getElementById("purchases_visualisation");
    if (oElement) {
        oElement.innerHTML = "";
    }

    oCall = new vdf.ajax.JSONCall("TopNCustomers");
    oCall.addParam('sSessionKey',vdf.sys.cookie.get("vdfSessionKey"));
    oCall.addParam('iTopN',5);
    oCall.onFinished.addListener(onDataLoaded);
    oCall.send(true);        
}

and should be added before the 2nd tag. The first step in the function is to place a waiting animated gif to the page to tell the user that the page is being constructed on a slow connection. Find the animated gif as one of the attachments on this blog. On your local machine you probably do not see the loader at all, the loading and processing is too fast. In the 2nd part of the function we create a Visual DataFlex AJAX Library JSONCall object to get the data into our page. We ask the webservice to return the data for 5 customers. If you want a different number of customers simply change the 2nd parameter amount from 5 to more or less. When the function call is finished we want to process the data in a routine called onDataLoaded.

The code for onDataLoaded is a bit more complex than the code so far but still good understandable (I think). While the end result shows two graphs let us start with one.

Code:

function onDataLoaded(oEvent) {
    var i, oResult = oEvent.oSource.getResponseValue();
    var purchasesData = new google.visualization.DataTable();
    
    oElement = document.getElementById("purchases_visualisation");
    if (oElement) {    
        purchasesData.addColumn('string', 'Customer');
        purchasesData.addColumn('number', 'Purchases');
        
        purchasesData.addRows(oResult.TopNCustomers.length);
        
        for (i = 0; i < oResult.TopNCustomers.length; i++) {
            purchasesData.setValue(i, 0, oResult.TopNCustomers[i].sCustomerName);
            purchasesData.setValue(i, 1, oResult.TopNCustomers[i].cuPurchases);
        }

        var chart = new google.visualization.PieChart(oElement);
        chart.draw(purchasesData, {width: 1000, height: 400, title: 'Top 5 Customers on Purchases'});
    }
}

Google wants you to pass the data for the chart via a DataTable. An object of this DataTable is defined via

Code:

var purchasesData = new google.visualization.DataTable();

Depending on the type of chart there are more or less columns with data to provide. In the pie chart we use two columns, the name and the purchases. This column adding is done with the addColumn method.

Code:

purchasesData.addColumn('string', 'Customer');
purchasesData.addColumn('number', 'Purchases');

At the time the onDataLoaded is executed we know how many values (rows) were returned and we dimension the data table to the same amount via the addRows function call.

Code:

purchasesData.addRows(oResult.TopNCustomers.length);

The data from our array of the TopNCustomer struct is retrieved in a loop and transfered to the DataTable object.

Code:

for (i = 0; i < oResult.TopNCustomers.length; i++) {
    purchasesData.setValue(i, 0, oResult.TopNCustomers[i].sCustomerName);
    purchasesData.setValue(i, 1, oResult.TopNCustomers[i].cuPurchases);
}

Finally the JavaScript code creates the pie chart object inside our element with:

Code:

var chart = new google.visualization.PieChart(oElement);
chart.draw(purchasesData, {width: 1000, height: 400, title: 'Top 5 Customers on Purchases'});

Now run your example and see the result is:



Adding the second chart is easy now because it simply requires a second DataTable object, other information to be added to the array and call a different type of chart. So add:

Code:

oElement = document.getElementById("creditlimit_visualisation");
if (oElement) {    
    creditlimitData.addColumn('string', 'Customer');
    creditlimitData.addColumn('number', 'CreditLimit');
    creditlimitData.addColumn('number', 'Purchases');
    
    creditlimitData.addRows(oResult.TopNCustomers.length);
    
    for (i = 0; i < oResult.TopNCustomers.length; i++) {
        creditlimitData.setValue(i, 0, oResult.TopNCustomers[i].sCustomerName);
        creditlimitData.setValue(i, 1, oResult.TopNCustomers[i].cuCreditLimit);
        creditlimitData.setValue(i, 2, oResult.TopNCustomers[i].cuPurchases);
    }

    var chart = new google.visualization.ColumnChart(oElement);
    chart.draw(creditlimitData, {width: 1000, height: 400, title: 'Credit Limit vs Purchases', hAxis: {title: 'Customers', titleColor:'blue'}});
}

The second chart has 3 data columns which make it possible to compare the purchases against the creditlimit.

I hope the above blog helps you in adding more attracting pages to your Visual DataFlex AJAX web application. Do not forget; there are many more providers of charts and the fact that we used Google for this blog does not mean that Data Access suggests or advises to use Google Charts. We also can take no responsibility if Google over time changes the API or removes the product from the product offering.