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. 
     vtotal = (IFEQ(datacol,'A',(.vtotal+1),.vtotal))
     vtotal = COUNT(datacol) FROM tbl WHERE datacol='A' and id=id
     vsum = (IFEQ(datacol,'A',(.vsum+amount),.vsum))
     vsum = SUM(amount) FROM tbl WHERE datacol='A' and id=id
     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)
     vavgprt = AVG(datacol) FROM tbl WHERE datacol='A' and id=id
     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 
     vichrdatacol = (ICHAR(datacol))
     vcount = (.vcount+1)
     vmin = (IFEQ(.vichrdatacol,'65',(IFEQ(.vcount,1,amount, +
            (IFLT(amount,.vmin,amount,.vmin)) )) ))
     vmin=MIN(amount) FROM tbl WHERE datacol='A' and id=id
     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 
     vichrdatacol = (ICHAR(datacol))
     vcount = (.vcount+1)
     vmax = (IFEQ(.vichrdatacol,'65',(IFEQ(.vcount,1,amount, +
            (IFGT(amount,.vmax,amount,.vmax)) )) ))
     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),+
       vsalestot2 CURRENCY= (IFEQ(model,'cx3010',(.vsalestot2+extprice),+
                            .vsalestot2))  ....
       vsalestot8 CURRENCY= (IFEQ(model,'px3040',(.vsalestot8+extprice),+
     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++
     Grand total expression
     vgrandtot  CURRENCY= (.vcoltot1+.vcoltot2+.vcoltot3+.vcoltot4+.vcoltot5++
     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:
      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