What needs to happen, and why


In the drawChart function, locate the variable named data. Look at the structure of the sample data in the original code; it has “categories” and “values”:

Notice the syntax of brackets, quotation marks, and commas. We will use this to define a template in our getDataForChart function for the SQL result set. Our getDataForChart function will then use this template to format the SQL data so that it can be used by the drawChart function.

The template defines the delimiters of the data values, so the variable assignment would look something like this:

chartDataTemplate = "[‘text‘, number],"

(The last segment of data should not have a comma at the end; we will need to trim this.)

We will use the Alpha Anywhere function a5_mergeDataIntoTemplate() to format the data.

Perform a Web search for the function a5_mergeDataIntoTemplate().

See how the function expects a field name to be formatted when it is an input parameter:

{ds.data("fieldname")}, or in our case, {ds.data("CustomerID")}

This means our template must include " marks. To do this, place a \ before each one as an escape character, like this: {ds.data(\"CustomerID\")}

In the template, this becomes:

chartDataTemplate = "[‘{ds.data(\"CustomerID\")},

The data between the { } is evaluated as Xbasic.

Because the parameters are eventually being passed to JavaScript, it is a recommended practice to “escape” the entire parameter, like this:

chartDataTemplate = "['{js_escape(ds.data(\"CustomerID\"))}', js_escape is an Xbasic function that prepares a data string to be JavaScript-friendly.


In the getDataForChart function, make a new line after the dim results line, and enter the following 2 lines:

dim chartDataTemplate as C ‘Template for JS data for chart

chartDataTemplate = "['{js_escape(ds.data(\"CustomerID\"))}',

{js_escape(ds.data(\"SumOfRevenue\"))}],"


Merging data into the template

We need a variable to hold the merged data.

Below the chartDataTemplate variable assignment you just wrote, open a new line and enter the following two lines:

dim chartData as C

chartData = ""

The merging takes place after retrieving and releasing the result set. After the

FreeResult function, open a new line and enter the following (on one line):

chartData = chartData + a5_mergeDataIntoTemplate(chartDataTemplate,results)

Our template adds a comma at the end of each data “segment.” There should not be a comma at the end of the entire data string, so remove it using a trimming function:

After the call to close the connection, open a new line and enter the following text:

chartData = rtrim(chartData,",")

You could also use the following syntax:

chartData = strip_trailing_char(chartData,",")

Adding virtual column headers

The template formats the sales data in to a two-column table: the CustomerID of the companies whose names begin with a certain letter, and the total sales of each company. To identify those virtual columns, we supply the header text and format it to fit the template. The header text will also become mouse-over labels for slices of the pie chart.

  1. Above the “rtrim” line you just entered, open a new line and enter the following two lines:

dim headerText as C

headerText = "['Company', 'Sales'],"

  1. Then, after retrieving the results sets and trimming the trailing comma, we place the header before the rest of the data. After the “rtrim” line, open a new line and enter the following:

chartData = "[" + headerText + crlf() + chartData + "]"

Notice that the entire data string, including the header, must be enclosed in [ ]. After the call to close the database connection, you should have four lines of code:



Returning the data string back to the calling JavaScript function

The variable chartData now contains a JSON string (JavaScript Object Notation; a data interchange format). The last thing we need to do in this Xbasic function is to return this string back to the UX Component, and then call the JavaScript function drawChart.


  1. Before the END FUNCTION line, make a new line and enter the following text:

dim sendThisToChart as C ‘Return this to the calling function

(Good programming practice would place this with the other variable declarations, but we will do it here for the sake of training flow.)

  1. After this, make another new line and enter the following text:

sendThisToChart = "{dialog.object}._data=" + chartData + ";drawChart();"

This means:

  • "{dialog.object}._data=" – declare a variable _data within the UX Component, and assign to it…
  • + chartData + – the string in the variable chartData.
  • ";drawChart();" – end that first JavaScript command, then call the Google Chart JavaScript function.
  1. To execute these commands and return to the calling function, make one more new line below the one you just make, and enter the following text:

getDataForChart = sendThisToChart

  1. Save the function code.
Making the code easier to read
  1. Select all the code in the function.
  2. Right-click in the open space.
  3. From the menu that appears, choose Format code.