PRODUCT   :  R:BASE                  VERSION      :  3.1B or Higher
   Advanced Reporting Technique
   R:BASE 3.1 or higher & Personal R:BASE 1.0 or higher
   You can use Structured Query Language (SQL) in a simple report lookup 
   expression to look up sums, minimums, maximums, averages, and counts 
   without programming. You can even look up these statistics in tables 
   other than the report's base table.
   Use this powerful feature to accomplish tasks like these:
   <>  Print a sum, a minimum, a maximum, an average, or a count in a report 
       header or break header
   <>  List a group total and the percent of grand total that the group total 
       represents on each detail line of a report
   <>  Print a report based on one primary table like CUSTOMER where each 
       customer identification number (custid) is unique and in the process 
       look up totals, averages, minimums, maximums, and counts using many 
       rows in other transaction tables like transmaster, where several 
       rows might contain the same custid number.
   SQL & Reports
   This is possible because of the way R:BASE processes a report. As 
   documented in R:BASE Reports Q & A in the November/December 1991 
   R:BASE EXCHANGE, R:BASE uses the report definition stored in the SYSREP 
   table to build an SQL query when you print a report. Because it builds 
   a SQL query, you can use the SQL SELECT functions in report lookup 
   expressions. Basically, you can look up an expression or a SELECT 
   function result because those are legal SQL operations.
   By using this technique, you can show totals, percentages, averages, 
   minimums, maximums, and counts anywhere in a report even in the header or 
   on every detail line. Although an R:BASE report goes row by row through 
   the driving table and has no knowledge of groups of rows, SQL in a lookup 
   expression can compute statistics that are based on groups of rows.
   This technique does have some limitations:
   <>  The lookup expression or SQL function (the item you're looking up) 
       must fit within a maximum length of 18 characters.
   <>  The lookup must contain a WHERE clause.
   <>  You cannot look up values in a view, only in a table.
   <>  There's a limit to the number of lookup expressions you can have in 
       a report (usually 30 to 40) before you run out of memory handles. 
       For more information on this limit, see the last question and answer 
       in R:BASE Reports Q & A in the November/December 1991 R:BASE EXCHANGE. 
       If you have many other lookup expressions, you might not be able to 
       take advantage of this technique.
   You can use any legal SQL function or expression in a report lookup as 
   long as the entire item including the required opening and closing 
   parentheses, the internal parentheses, and the function name (SUM, MIN, 
   MAX, AVG, or COUNT) is 18 or fewer characters in length. If your column 
   names are longer than eight characters, abbreviate them in the SQL 
   functions by using the first eight characters.
   Looking Up Expressions
   If you're using an expression instead of an SQL function, conserve on 
   space by omitting all spaces. Looking up an expression is handy when, 
   for example, you want the complete name when the first name is in FNAME 
   and the last name is in LNAME. Use the following lookup expression to 
   look up the complete name in one pass:
   vname (fname&lname) IN customer WHERE custid = custid
   It works because (FNAME+LNAME) is only 13 characters. If both the column 
   names had eight characters, the lookup item would be over the 18-character 
   limit, thus the expression wouldn't work.
   You can use the same technique to grab the state and ZIP code in one pass:
   vsz = (state&zipcode) IN customer WHERE custid = custid
   Pound-position Abbreviations
   If you have long column names, you can still use this technique by using 
   the  pound-position shorthand for columns. But it can lead to problems 
   later on if you change the order of the columns in the table. The pound-
   position shorthand is the pound sign (#) in combination with the column's 
   position number in the table list. For example, custid is the second 
   column in the transmaster list, so you could use #2 in place of custid.
   Use the LIST command to list the customer table (in Personal R:BASE, 
   press [F3] and choose customer under Columns to view the list of columns 
   in customer). You'll see that city is the fourth column, state is the 
   fifth, and zipcode is the sixth. Therefore, you could look up all three 
   columns at once by using this lookup expression:
   vcsz = (#4+',') IN customer WHERE custid = custid
   By using the abbreviation, you can look up a lot of columns at once, but 
   the pound-position abbreviations will cause you headaches later on if you 
   change the physical order of the columns in the table. You could change 
   the order by using the PROJECT command, for example, or by removing a 
   column and adding it back in.
   Only in Reports
   R:BASE doesn't process form lookups by using SQL queries, so you can use 
   this technique only in reports lookups and not in forms lookups.
   Putting the Balance in a Header
   Below are several examples that demonstrate how easy use SQL lookups 
   can be.
   Look at Printing a Bill in the November/December 1991 R:BASE EXCHANGE. 
   The third and fourth expressions use the SUM function to add up all the 
   charges and payments so that the customer's balance can be computed and 
   printed at the top of the bill. Here are those expressions:
   vchgsum = (SUM(amount)) IN charges WHERE custid = custid 
   vpaysum = (SUM(amount)) IN payments WHERE custid = custid
   The fifth expression computes the balance:
   vbal = (.vchgsum .vpaysum)
   The report is based on a view that combines three tables (customer, 
   payments, and charges). The sums and the balance are computed for every 
   row in the view, so you can print any of these amounts at any time in a 
   header, in the detail lines, or in a footer. You don't have to wait for 
   R:BASE to go through all the rows.
   Summary Statistics in Customer Report
   Here's another example. This is a modification of the customer report 
   in the concomp sample database included with R:BASE 3.1. The report is 
   based on the customer table, which lies on the one- side of two one-to-
   many relationships with other tables. The many-side tables directly 
   related to customer are contact and transmaster. Add the following 
   lookup expressions to look up how many times a customer has been 
   contacted, the customer's total order amount, largest order, smallest 
   order, the average of all the customer's orders, and the percentage of 
   total revenue that customer's total represents:
   vtalked = (COUNT(custid)) IN contact WHERE custid = custid 
   vtotord = (SUM(netamount)) IN transmaster WHERE custid = custid 
   vmaxord = (MAX(netamount)) IN transmaster WHERE custid = custid 
   vminord = (MIN(netamount)) IN transmaster WHERE custid = custid 
   vavgord = (AVG(netamount)) IN transmaster WHERE custid = custid 
   vgrand = (SUM(netamount)) IN transmaster WHERE custid IS NOT NULL 
   vpercent = ((.vtotord / .vgrand) * 100)
   These variables are computed for every row in customer, which is the 
   driving table for the report. Because customer is the one-side table, 
   no breakpoints are necessary (each custid appears in only one row). You 
   can locate the summary variables in the detail section and they'll print 
   for each customer. For example, you might want to add this these extra 
   detail lines to the detail section of the customer report:
   Times Contacted: S    vtalked           E 
   Total Orders: S     vtotord           E
   Percent of All Orders: S  vpercent  E percent 
   Largest Single Order: S     vmaxord           E 
   Smallest Single Order: S     vminord           E 
   Average Order: S    vavgord           E
   You can use this technique any time you want to base a report on a 
   one-side (master or header) table and print summary statistics on 
   amounts stored in the many-side (transaction) tables.
   Adding a Picture Format
   You'll probably want to put a label such as Percent of All Orders: 
   in front of summary items, as in the example above. When you use a 
   label, you might want to left justify the item by adding a picture 
   format to the field. Remember, when you add [<] to the picture format 
   to left justify a numeric field like CURRENCY, you must also include 
   characters modifiers. If you don't, the amounts won't print; you'll 
   get asterisks (*). Here's an example of a picture format for a CURRENCY 
   variable that you want to left justify and that will never be larger 
   than 99 million dollars:
   You might get customers listed in the report who haven't ordered 
   anything yet, so add the following as the NULL picture format:
   No Orders Yet
   Now the report will automatically print No Orders Yet next to the 
   appropriate label.
   Other Opportunities
   Use this technique in any report in which you want to look up and print 
   summary information (sums, averages, minimums, maximums, and counts). 
   You can even use it to look up and summarize values in the driving table.