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