DOCUMENT #691
=======================================================================
CROSSTAB STYLE REPORTS
=======================================================================
PRODUCT: R:BASE VERSION : 3.1B or Higher
=======================================================================
CATALOG: Programming In R:BASE AREA : Reports
=======================================================================
Many times you'll want to print a report that presents your data in the
same way as the CROSSTAB command, a tabular format with data values
across the top, data values down the side, a sum or a count in the cell
and both row and column totals.
The July/August 1990 Exchange article "Save CROSSTAB Results in
R:BASE 3.0 Work Tables" tells you how to save the results of your
crosstab command into a table and then use that table to base a report
on. This program allows you complete variability in the number of data
values you'll have across the top, and with no real limit on the width
of the output.
In a report, you need to know how many data values you'll have across
the top of your table, and what the actual data values are. If you know
this, continue reading, you'll see how to set up the variables to
compute the cell values, and to calculate row and column totals. If you
don't know how many or what the values will be across the top, or if
they change each time you print the report, stop reading, call the
automated FAX server at (206) 649-2789 and download document #269. This
is the "Save CROSSTAB Results in R:BASE 3.0 Work Tables" article.
The column printed on the side of the table can have many different
values and can change each time you print the report. You don't have to
know what these values are or how many different ones you have.
Choosing the top and side columns
=================================
Pick the two columns you want to crosstab. Usually, the one with the
fewest unique values is the top column. The number of unique data values
determines how many columns you'll need across the report. Generally
you'll have one column for each separate data value for the top column.
One variable needs to be defined for each cell value, one for each
column total, and one for the row total. If there are 12 columns across
the report, that's a minimum of 25 variables. A report has a limit of 99
expressions so it is possible to run out of variables. Also, a report
can only be defined 255 characters wide. If there are 12 columns and
each is located 10 characters wide, this already totals 120 characters
without allowing for row totals and the side column data. If you'll
need more than 99 variables or 255 characters wide, call the automated
FAX server at (206) 649-2789 and download document #269. This is the
"Save CROSSTAB Results in R:BASE 3.1 Work Tables" article. The column
that you choose for the side column is your break column. The variables
accumulate the cell values for each combination of side column value and t
op column value. The result is printed when the side column value changes.
The cell computation
====================
The cell can contain a count, sum, average, minimum, maximum or other
calculation. See the article "Basic Statistics: Count, Average, Sum,
Minimum, Maximum" in this Exchange for more information on calculating
the cell values. The cell computations must be conditional on the top
column values. The variable that is to be printed will use the IFEQ
logical function. Because the side column is the break column, the cell
computation automatically takes that value into account.
Remember that report expressions are evaluated once for every row in the
driving table of the report. Thus it may be slower to use the lookup
expressions than to do calculations on data values from the row. It
depends on how many rows the lookup needs to compute on.
Below are sample expressions to calculate the count, sum, average,
minimum and maximum. These expressions are duplicated for each top
column value. The top column data value (datacol) is "A" and the side
column is id.
COUNT
-----
vtotal = (IFEQ(datacol,'A',(.vtotal+1),.vtotal))
or
vtotal = COUNT(datacol) FROM tbl WHERE datacol='A' and id=id
SUM
---
vsum = (IFEQ(datacol,'A',(.vsum+amount),.vsum))
or
vsum = SUM(amount) FROM tbl WHERE datacol='A' and id=id
AVERAGE
-------
Note that you need 3 expressions to compute the average for each cell,
not 1. This increases the number of required expressions.
vavgsum = (IFEQ(datacol,'A',(.vavgsum+amount),.vavgsum))
vcnt = (IFEQ(datacol,'A',(.vcnt+1),.vcnt))
vavgprt = (.vavgsum/.vcnt)
or
vavgprt = AVG(datacol) FROM tbl WHERE datacol='A' and id=id
MINIMUM
-------
Note that if the top column value is TEXT you first need to convert it
to INTEGER using the ICHAR function. Functions using text cannot be
nested.
vichrdatacol = (ICHAR(datacol))
vcount = (.vcount+1)
vmin = (IFEQ(.vichrdatacol,'65',(IFEQ(.vcount,1,amount, +
(IFLT(amount,.vmin,amount,.vmin)) )) ))
or
vmin=MIN(amount) FROM tbl WHERE datacol='A' and id=id
MAXIMUM
-------
Note that if the top column value is TEXT you first need to convert it
to INTEGER using the ICHAR function. Functions using text cannot be
nested.
vichrdatacol = (ICHAR(datacol))
vcount = (.vcount+1)
vmax = (IFEQ(.vichrdatacol,'65',(IFEQ(.vcount,1,amount, +
(IFGT(amount,.vmax,amount,.vmax)) )) ))
or
vmax=MIN(amount) FROM tbl WHERE datacol='A' and id=id
Creating the Report
===================
A report designed from the CONCOMP sample database showing the amount
sold per model by transaction date would be as follows. The report is
based on the view Prodview.
1. Decide which columns you'll use for across the top and down the side
of your table. The column that is down the side becomes your break column.
The column across the top determines the comparison values that you will
use in your expressions. Model is the top column, Transdate becomes the
side column.
2. Decide the column that will generate your cell values. What function
will you perform on that column (sum, count, avg, min, max, other)? For
this example, we want the SUM of the column Extprice.
3. Write out the initial expression(s) to compute your cell values.
These expressions repeat for each of the separate data values of your
top column. Write down the data values from the top column to use in the
expressions. Each cell needs a variable with a unique name. These
expressions are all reset at the breakpoint (the side column).
There are 8 different product models so 8 expressions are needed. Notice
that the third argument of the IFEQ is accumulating the total amount
sold for a specific model.
Cell expressions
----------------
vsalestot1 CURRENCY= (IFEQ(model,'cx3000',(.vsalestot1+extprice),+
.vsalestot1))
vsalestot2 CURRENCY= (IFEQ(model,'cx3010',(.vsalestot2+extprice),+
.vsalestot2)) ....
vsalestot8 CURRENCY= (IFEQ(model,'px3040',(.vsalestot8+extprice),+
.vsalestot8))
4. Write out the total expressions. There is one for each column (the
same expression as the cell expression for the column, but it isn't reset
at the break) and one expression for the row totals. The total expression
for the row will simply add together the cell expressions.
Column total expressions
------------------------
vcoltot1 CURRENCY= (IFEQ(model,'cx3000',(.vcoltot1+extprice),.vcoltot1))
vcoltot2 CURRENCY= (IFEQ(model,'cx3010',(.vcoltot2+extprice),.vcoltot2))
.....
vcoltot8 CURRENCY= (IFEQ(model,'px3040',(.vcoltot8+extprice),.vcoltot8))
Row total expression
--------------------
vrowtot CURRENCY= (.vsalestot1+.vsalestot2+.vsalestot3+.vsalestot4++
.vsalestot5+.vsalestot6+.vsalestot7+.vsalestot8)
Grand total expression
----------------------
vgrandtot CURRENCY= (.vcoltot1+.vcoltot2+.vcoltot3+.vcoltot4+.vcoltot5++
.vcoltot6+.vcoltot7+.vcoltot8)
5. Set up the breakpoint. Choose Create breakpoints and enter the side
column name, transdate, as break1. Say Yes to reset variables and choose
the cell expression variables (vsalestot1,vsalestot2,.... vsalestot8).
6. Locate the fields and text. Place the data values for the top column
on the report as text values on page header lines (PH). Locate the side
column and the cell variables on a break footer line (F1). Locate the
total variables on a page footer line (PF) or a report footer line (RF).
Lines can be added to separate the headings as in the CROSSTAB command.
Transdate, break column
| actual data values from the Model column
PH | /
PH | / MODEL row total
PH Trans | / | |
PH Date | CX3000 CX3010 CX3020 ...MX3030 (Total) |
PH --------| ------- ------- ------- ------- -------- |
F1 S E| S E S E S E ...S E S E |
PF | ------- ------- ------- ------ -------- |
PF | S E S E S E ...S E S E |
PF | | | |
| | |
| | | |
|
\ |
grand total |
column total variables
7. Print the report:
MODEL
Trans |
Date | CX3000 CX3010 CX3020 MB3030 MX3020 MX3030 (Total)
--------| ------- ------- ------- -------- -------- ------- --------
01/03/89| $27,000 $0 $0 $0 $0 $0 $27,000
01/09/89| $9,500 $0 $0 $0 $0 $0 $9,500
01/10/89| $0 $0 $22,800 $0 $24,000 $0 $46,800
01/12/89| $77,500 $0 $77,500 $21,000 $0 $0 $176,000
02/23/89| $0 $47,250 $9,875 $64,250 $0 $0 $121,375
02/24/89| $0 $0 $0 $73,500 $0 $0 $73,500
02/27/89| $0 $0 $0 $0 $87,500 $0 $87,500
02/28/89| $0 $0 $0 $0 $0 $22,500 $22,500
03/03/89| $0 $0 $0 $52,500 $0 $0 $52,500
03/14/89| $77,500 $37,800 $0 $0 $0 $0 $115,300
03/15/89| $18,000 $0 $0 $52,500 $0 $0 $70,500
03/16/89| $77,500 $10,500 $0 $0 $0 $0 $88,000
03/19/89| $18,000 $0 $0 $0 $0 $0 $18,000
03/20/89| $9,500 $0 $0 $21,000 $0 $0 $30,500
|-------- ------- -------- -------- -------- ------- --------
|$314,500 $95,550 $110,175 $126,000 $111,500 $22,500 $780,225