Setting up to use an array

For efficient code, we define a variable and assign to it the text strings that are our SQL statements. Since we need to execute multiple statements, we will use an unbounded Xbasic array.

  1. After the args.Set line, on a new line, enter the following:

dim sqlStmts[0] as C ‘An unbounded Xbasic array

  1. To help step through the array, we need a variable to hold the count. On another new line, enter the following:

dim arrayCount as N ‘Number of array elements

Passing a text string

To pass SQL statements, or any text string, to the server via Xbasic, you can enclose the text in the Xbasic delimiters <<%str% and %str%

For example: sqlStmts[]=<<%str%

  1. On a new line, enter the text just above. Observe the command prompting. At the end of the text, press the Enter key.
  2. Observe that Alpha Anywhere places the closing delimiter two lines further down.
SQL Select statements for this function

This function needs two Select statements to retrieve the data to produce the chart. You will enter the statements directly in the Code Editor window.

Remember, though: you can click the button to use the SQL Query Genie or the SQL Command Window to develop and test SQL statements, and then copy and paste those statements to the Code Editor window.


  1. On the new line after the opening <<%str% delimiter, enter the first of the two SQL statements. It calculates the total revenue for one customer, who is selected using the argument defined earlier:

SELECT Orders.CustomerID, Sum(Order_Details.UnitPrice*Order_Details.Quantity) AS SumOfRevenue

FROM Orders Orders

INNER JOIN [Order Details] Order_Details

ON Orders.OrderID = Order_Details.OrderID WHERE Orders.CustomerID = :localWhatCustomerID

GROUP BY Orders.CustomerID

(To simplify the statements, we are not using the Discount field of the Order Details table.)

  1. Copy the whole array variable definition, including the closing delimiter.
  2. Paste the array variable definition below the first one (and above the end of the function).
  3. Edit the second SQL statement so that it reads like the one in this step. This second statement calculates the total revenue from all other customers.

SELECT 'Others' AS CustomerID, Sum(Order_Details.UnitPrice*Order_Details.Quantity) AS SumOfRevenue

FROM Orders Orders

INNER JOIN [Order Details] Order_Details

ON Orders.OrderID = Order_Details.OrderID WHERE Orders.CustomerID <> :localWhatCustomerID

GROUP BY 'Others'