======================================================================
USE SQL IN REPORT LOOKUPS TO PUT SUMS & OTHER STATS IN THE HEADER
======================================================================
PRODUCT : R:BASE VERSION : 3.1B or Higher
CATEGORY : REPORTS SUBCATEGORY : SQL, EXPRESSIONS
======================================================================
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.
Restrictions
============
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:
[<]$99,999,999.00
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.