How to Add a Chart to Your Visual DataFlex Web Application?
by
, 19-Feb-2011 at 05:57 AM (41248 Views)
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.
Save the above as TopNCustomers.Asp and run it in your browser. You will get the following result: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 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:
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.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 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.
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.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
The function call returns an struct described as:
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 tAJAXTopNCustomers tTopNCustomer[] TopNCustomers TAjaxRMIResponse tResponse End_Struct
For each customer we will transfer the name, the purchase, the balance and credit limit as currency values.Code:Struct tTopNCustomer String sCustomerName Currency cuPurchases Currency cuBalance Currency cuCreditLimit End_Struct
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:
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.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
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:
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" src="http://www.google.com/jsapi"></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:<script type="text/javascript"> google.load("visualization", "1", {packages:["corechart"]}); google.setOnLoadCallback(drawChart); </script>
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.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); }
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.
Google wants you to pass the data for the chart via a DataTable. An object of this DataTable is defined viaCode: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'}); } }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:var purchasesData = new google.visualization.DataTable();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.addColumn('string', 'Customer'); purchasesData.addColumn('number', 'Purchases');The data from our array of the TopNCustomer struct is retrieved in a loop and transfered to the DataTable object.Code:purchasesData.addRows(oResult.TopNCustomers.length);
Finally the JavaScript code creates the pie chart object inside our element with: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); }
Now run your example and see the result is:Code:var chart = new google.visualization.PieChart(oElement); chart.draw(purchasesData, {width: 1000, height: 400, title: 'Top 5 Customers on Purchases'});
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:
The second chart has 3 data columns which make it possible to compare the purchases against the creditlimit.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'}}); }
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.