795.TXT
=====================================================================
CrossTab-Style Reports
=====================================================================
PRODCUT: R:BASE VERSION: 4.5++ or Higher
=====================================================================
CATALOG: Forms, Reports & Labels AREA : Reports
=====================================================================
The CROSSTAB command is useful for computing quick statistics on data.
It displays in a tabular format the relationship between two columns
of data. In addition, a sum, average, or other calculation can be
displayed in the cell where two data values intersect. The cell
calculation is the value that appears at the row and column
intersections and is the value used for row and column totals. To
just display CrossTab results to the screen, select the Crosstab
option from the Calculate menu in Browse/edit. Often, however, you'll
want to print the CrossTab results as a report. There are many
different ways to display CrossTab results to a file or printer.
Store the results in a table and print a report from the table
Use the R:BASE Report Writer to create a CrossTab-style report
Use Crystal Reports for R:BASE 3.0 to create a CrossTab report
Output the results of a CROSSTAB command to a file or the printer
Create a PAGEMODE CrossTab-style report
transdate | CX3000 CX3010 CX3020 (Total)
--------- | ------------ ------------ ------------ ---------
10/02/94 | $32,400.00 $0.00 $0.00
$32,400.00
10/08/94 | $9,500.00 $0.00 $0.00
$9,500.00
10/09/94 | $0.00 $0.00 $1,900.00
$1,900.00
10/11/94 | $77,500.00 $0.00 $77,500.00
$155,000.00
10/22/94 | $0.00 $47,250.00 $9,875.00
$57,125.00
11/13/94 | $77,500.00 $37,800.00 $0.00
$115,300.00
11/14/94 | $18,000.00 $0.00 $0.00
$18,000.00
11/15/94 | $77,500.00 $10,500.00 $0.00
$88,000.00
11/18/94 | $18,000.00 $0.00 $0.00
$18,000.00
11/19/94 | $9,500.00 $0.00 $0.00
$9,500.00
11/26/94 | $3,060.00 $0.00 $0.00
$3,060.00
11/27/94 | $4,590.00 $0.00 $0.00
$4,590.00
--------- | ------------ ------------ ------------ ---------
---
| $327,550.00 $95,550.00 $89,275.00
$512,375.00
Store the results in a table
The July/August 1990 Exchange contained an article, "Save CROSSTAB
results in R:BASE work tables" (document #269 on the FAX server),
with program code describing how to save the results of a CROSSTAB
command into a table. Once the data was stored in a table, the data
could be viewed in Browse/edit or in a report created with the
R:BASE Report Writer. This method provides a great deal of
flexibility in viewing the results, but requires writing and
maintaining a program.
Use the R:BASE Report Writer
The article "CrossTab Style Reports" (document #691 on our FAX server)
in the March/April 1993 Exchange describes the report expressions
necessary to create a CrossTab-style report using the R:BASE Report
Writer with no program code needed. This method is limited by the
number of report expressions and the width of the report writer
layout screen (255 columns). An advantage to this method is that the
report is easily integrated into an application and no program code
needs to be written.
Crystal Reports 3.0 for R:BASE
Crystal Reports 3.0 for R:BASE has a new CrossTab report feature. A
CrossTab report is simple to set up in Crystal Reports. First, select
the field(s) that you want to appear as column headings and place them
in the Columns box. Next, select the field(s) that you want to appear
as row headings and place them in the Rows box. Finally, select the
field that you want summarized and place it in the Summarized Field
box.
You can have multiple row or column headings but only one summarized
field, which is required. When you have selected more than one heading
for a row or column, the heading at the top of the list becomes the
outer most heading, the next heading on the list falls just inside
the first heading, and so on.
When you have multiple row or column headings, Crystal Reports
summarizes the data in the order in which the headings appear. For
example, if the top field in the Row box is State and the next field
is City, the report will summarize by state and then within each
state, by city.
A formula can be used as a row or column heading or as a summarized
field. Data can be grouped just as you do in any other Crystal
report. Select a row or a column heading as the group by field and
Crystal breaks the report into a series of mini CrossTab reports,
each one showing a single group.
Refer to the Crystal Reports User's Manual and on-line help for
complete information on creating and using CrossTab reports in
Crystal Reports 3.0. A disadvantage of using Crystal Reports 3.0 to
create a CrossTab report is that the report is difficult to
incorporate into an R:BASE application.
Output the CROSSTAB command
To print a CrossTab report quickly and easily, send the results of
a CROSSTAB command to a file or the printer directly from R:BASE.
This method is easy to integrate into an application, but you have
little control over the width, page breaks or other formatting
options. The cell calculation is limited to those available in the
CROSSTAB command. Just a few R:BASE commands are needed, for example:
SET WIDTH 300
SET LINES 0
OUTPUT PRINTER
CROSSTAB model BY transdate FROM prodview
OUTPUT SCREEN
A PAGEMODE report
With the addition of PAGEMODE in R:BASE 4.5++, another option opens
up for creating CrossTab-style reports. You need to write program
code, but there are no limitations to the width or length of the
output, and you can add your own calculations for cell values. You
can have multiple cell calculations. The top and side columns are
completely dynamic. You have a number of formatting options, fewer
expressions are needed, and there are no limits on variables or
expressions. Cursors, not sorts or expressions, do the work of
separating the data into groups. In addition, the report is easily
integrated into an application.
The following example is based on a view. Tables can't be used
because, in this example, the top column and side column come from
different tables. To calculate a sum for the intersection of the data
values, you need to be able to use both columns in a WHERE clause,
thus both columns need to be in the same table, or as in this case,
view. The view selects just the columns that will be used in the
CrossTab. You define a cursor on the view for the column values that
appear across the top of the CrossTab, and a separate cursor for the
column values that go down the side. A normal nested cursor
relationship will not produce the correct results. Each possible top
value must be tested with each side value. If the cursors are
related, only the top column values that match are selected. The data
is not easily displayed_you can't just write the cell calculation in
the next column location, the calculation needs to match up to the
top value displayed for the column. This PAGEMODE report structure
lets you write the data in correct positions by simply incrementing
row and column display variables. No complex calculations are
needed to figure out where to display the data.
The view definition using tables from the sample Concomp database:
CREATE VIEW xcross AS +
SELECT model, transdate, invoicetotal +
FROM transmaster, transdetail +
WHERE transmaster.transid = transdetail.transid
*(CROSS.RMD - CrossTab report using PAGEMODE)
SET MESSAGES OFF
SET LINES 40
SET WIDTH 200
SET VAR vmodel TEXT, vtransid INTEGER, vdate DATE, +
vcol INTEGER = 12, vrow INTEGER = 5
DECLARE c1 CURSOR FOR SELECT DISTINCT transdate FROM xcross
DECLARE c2 CURSOR FOR SELECT DISTINCT model FROM xcross
OUTPUT cross.out
SET PAGEMODE ON
OPEN c2
FETCH c2 INTO vmodel i1
WHILE SQLCODE <> 100 THEN
WRITE .vmodel AT 3, .vcol
WRITE '------------' AT 4, .vcol
SET VAR vcol = (.vcol + 15)
FETCH c2 INTO vmodel i1
ENDWH
CLOSE c2
WRITE 'Totals' AT 3, .vcol
WRITE '------------' AT 4, .vcol
SET VAR vcol = 12
OPEN c1
FETCH c1 INTO vdate i2
WHILE SQLCODE <> 100 THEN
WRITE .vdate, '|' AT .vrow, 1
OPEN c2 RESET
FETCH c2 INTO vmodel i1
WHILE SQLCODE <> 100 THEN
SELECT SUM(invoicetotal) INTO vtotal i3 FROM xcross +
WHERE model = .vmodel AND transdate = .vdate
SHOW VAR vtotal=12 AT .vrow, .vcol
SET VAR vcol = (.vcol + 15)
FETCH c2 INTO vmodel i1
ENDWH
SELECT SUM(invoicetotal) INTO vrowtotal i4 +
FROM xcross WHERE transdate = .vdate
SHO VAR vrowtotal=12 AT .vrow, .vcol
SET VAR vrow = (.vrow + 1)
SET VAR vcol = 12
FETCH c1 INTO vdate i2
ENDWH
SET VAR vrow = (.vrow + 1)
SET VAR vrow2 = (.vrow + 1)
SET VAR vcol = 12
CLOSE c2
OPEN c2
FETCH c2 INTO vmodel i2
WHILE SQLCODE <> 100 THEN
WRITE '-------------' AT .vrow,.vcol
SELECT SUM(invoicetotal) INTO vcoltotal i4 FROM xcross
WHERE +
model = .vmodel
SHOW VAR vcoltotal=12 AT .vrow2, .vcol
SET VAR vcol = (.vcol + 15)
FETCH c2 INTO vmodel i2
ENDWH
SELECT SUM(invoicetotal) INTO vgrandtot i4 FROM xcross
WRITE '-------------' AT .vrow, .vcol
SHOW VAR vgrandtot=12 AT .vrow2, .vcol
OUTPUT SCREEN
SET PAGEMODE OFF
SET LINES 20
SET WIDTH 79
DROP CURSOR c1
DROP CURSOR c2
SET MESSAGES ON
Initialize variables.
In most PAGEMODE reports you are doing breakpoints, in which case the
cursors are related, the second cursor is designed to retrieve
matching rows based on the current value of the first cursor. Here,
the cursors are not related. One is defined to retrieve the top
column values, the other to retrieve side column values.
Write all the data values and heading information across the top of
the report. The same column increment is used later when the cell
values are written. Uses the cursor defined to retrieve the top
column values only.
Close the top column cursor. It is reopened and used again later.
Write the heading for the row totals column. The vcol variable
contains the correct column location.
Begin the data retrieval for the side column and the cell
calculations.
Write the data value for the side column of the report.
Calculate the cell value for this side value combined with each top
value. If there is no data intersection, a zero is returned to the
vtotal variable. Cycle through all the possible top column values.
Values fetched from both cursors are used in the WHERE clause.
Calculate and write the row total.
Reset variables for the next side column value.
Get the next side column value and cycle through all the top values
and compute the corresponding cell values.
Reset variables to write the column totals.
Re-open the top cursor and calculate the column totals.
Do the grand total.
Close the report and reset the environment