*****************************************************************
Tutorial 2
*****************************************************************

This tutorial uses three data files:  ENROLL.DBF, STUDENT.DBF,
and CLASSES.DBF to create a multi-group, relational report. 
These files, and their indexes, are found on the CodeReporter
demo disk.  This tutorial is based on the work done in
Tutorial 1 and uses its report: TUT1.REP. 

Tutorial 2 takes up where tutorial 1 left off -- with an
enrollment list.  The report at the end of tutorial 1 displayed
all of the enrollments in sorted order by class code.  But what
if the person reading the report doesn't know what CMPT101 is or
who 423,232 is?  The report created in Tutorial 2 takes this
into account by displaying the descriptive names of the classes
as well as the students' names. 


*****************************************************************
Change the Report Name 	
*****************************************************************

Load the tutorial file used in Tutorial 1 (TUT1.REP) using the 
File | Open menu option and then select
the Files | Save As.  The 'Save Report' dialog is displayed.  In
the 'Name' edit control, enter 'TUT2.REP'.  This creates a new
report file on disk, but also renames the currently loaded
report to this new name. 

*****************************************************************
Defining the relations 	
*****************************************************************

The first task in establishing a relation is to determine what the 
output is to look like. 

Quickly sketch a sample report with a sample of the data. 
The following figure shows this sample output with annotation on which
data file contains which information. As it can be seen,
the three data files needed for this report are
ENROLL.DBF, STUDENT.DBF, and CLASS.DBF.  

+------------------------------------------------------------------+
|                                                                  |
|  * ++ -->  ECON101:  Advanced Micro Economics  <-- *             |
|  && ++ ------> 123,456  John Q. Public  <-- &&    ++ --> A-      |
|                123,456  John Q. Public                   A-      |
|                123,456  John Q. Public                   A-      |
|                123,456  John Q. Public                   A-      |
|                123,456  John Q. Public                   A-      |
|                                                                  |
|            MATH101:  College Algebra                             |
|                123,456  John Q. Public                   A-      |
|                                                                  |
+------------------------------------------------------------------+
    * from CLASSES.DBF,  ++ from ENROLL.DBF, && from STUDENT.DBF


A quick look at the data structures indicates that ENROLL.DBF shares 
the STUDENT_ID field with STUDENT.DBF, and CLASS_CODE with CLASS.DBF.

*****************************************************************
Creating the Relations 	
*****************************************************************

Relations are set up using the Database | Relations menu option.  
An empty 'Relations' dialog box is
displayed. Select the 'Add Slave' button and choose STUDENT.DBF
and STUDENT.MDX in the next two dialog boxes.   Since data files
are related using common information, this also must be
specified.  The Expression Entry dialog box is displayed to
prompt for the field(s) and/or functions used in the master
expression.  Double click on ENROLL->STUDENT_ID and select OK.  
Since the first tag in the STUDENT.MDX index is on the students'
names, and the relation is on the students' ID number, a
different slave tag must be selected.  Use the 'Index Tag' combo
box to select the ID_TAG tag. 

Setting relation type 	

The correspondence between ENROLL.DBF and STUDENT.DBF is a many-to-one.  
This lends itself to an exact match relation -- every record in the 
master data file has exactly one and only one corresponding record 
in the slave data file.  Since the default relation type is exact 
match, no change is required. 

Setting the error action 	

The default, and "safest", error action is to blank out the slave data 
file fields if a corresponding entry cannot be found.  However, since 
the data entered into the ENROLL.DBF data file should have been validated
at the time of entry, every entry should have a corresponding
record in the STUDENT.DBF.   Set the 'Error Action' combo box to
'Stop With Error'.  If an error occurs, the report aborts and an
error message is displayed.   Once the error action is set, the
first master-slave relation is properly established.   Click OK.

Add a new slave to the ENROLL.DBF data file using  CLASSES.DBF
and CLASSES.MDX.  Use the ENROLL->CLASS_CODE as the relation
expression.  Since ENROLL.MDX has only one tag, CODE_TAG, it is
automatically selected.  In addition, set the 'Error Action'
combo box to 'Stop With Error'.  Once this relation is completely
set up, select the OK button.   From this point on, the
composite data file includes ENROLL.DBF, STUDENT.DBF, and
CLASSES.DBF, according to the relations just set up. 


*****************************************************************
Adding the groups 	
*****************************************************************

As it can be seen in the rough sketch of the report (above), there are 
two distinct groups: one that
outputs the name of the classes, and the other that outputs the
students. Since the TUT1.REP report already has a main 'Body'
group that outputs some of the students' information, only one
group must be created. 

Adding the 'Class' group 	

To create a new group, use the Groups | New Group menu option.  
CodeReporter prompts for a descriptive
name for the new group.  Enter 'Class'.  Once this name is
entered, the 'Group Modify' dialog box is invoked.   The 'Class'
group position should be above the 'Body' group (since it is
outputted above the 'Body' group).  Change the Position setting
from 2 to 1.   The group expression for the new group is
initially left blank.  This indicates that the group is reset
for each new record.  The sketch report (above), shows that
the title of the class is only displayed each time it changes. 
This means that there must be a group expression on the classes'
IDs.  Enter ENROLL->CLASS_CODE into the 'Expression' edit
control manually, or by using the 'Easy Expression' button. 
This causes the group to be reset each time the enrollment for a
class ends and enrollment for the next class begins. Do not press OK.


*****************************************************************
Repeat Header 	
*****************************************************************

Check the Repeat Header box in the 'Class' group definition.  
Since this group could easily span a page break, setting this option 
will cause the 'Class' header to be reprinted at the top of the page 
-- resulting in an easier to understand report. Click OK.
	

Adding the output objects 	

Since the Tutorial2 report uses the actual class descriptions and 
students' names as identifiers, the field name labels should be
removed.  Individually select the STUDENT_ID,
CLASS_CODE, and MARK label objects in the page header and choose
the Object | Delete menu option (or press the 'Delete' key).  In
addition, delete the ENROLL->CLASS_CODE expression object from
the 'Body' group.  According to the sample output, 
the class information is displayed in its own group. 

Add the 'Class' header

Select the 'Class' group and create a new expression object at 
0,0 using the Insert key.  When the
Create Object dialog appears, enter  

    ENROLL->CLASS_CODE+':'+CLASSES->TITLE 

in the Object Text edit control and select the
Expression radio button.   This creates an expression object
that contains both the short-hand class code and the class's
descriptive title. Character fields and/or character strings may
be joined together using a plus (+) to create a single object. 

The class code and the it's title could have been added to the
report as separate objects.  However, in this case,
concatenating them together works well. 

Add the students' names 	

Position the remaining two 'Body' objects to approximately the 
following coordinates by dragging them or using the 
Modify Object dialog box: 
          ENROLL->STUDENT_ID -- Left 93, Top 0 
          ENROLL->MARK       -- Left 415, Top 0  

Create an expression object using the Objects | New menu option while the
'Body' group is selected. 
          TRIM(STUDENT->FIRST_NAME)+' '+STUDENT->LAST_NAME 

STUDENTS.DBF stores the students' names in
first name and last name fields. The simplest way to combine the
name fields would be to simply use a plus sign between them
(STUDENT->FIRST_NAME+STUDENT->LAST_NAME).  However, this causes
some formatting problems.  In almost all cases a student's name
is not going to take up the entire first name or last name
field.  This results in a lot of extra trailing spaces.  Simply
adding the fields together does nothing to eliminate these
spaces and a name would be output like: 

John Q.               Public 

In this output object, the TRIM() function should be used
to remove the trailing spaces from the FIRST_NAME field.  The
addition of the blank space between the names is necessary to
stop the first character of the last name from being  right next
to the last character of the first name. 

*****************************************************************
Display to the screen 	
*****************************************************************

Display the partially completed report using the File | Display menu option. 

*****************************************************************
Sorting 	
*****************************************************************

This report uses the sort ordering found in the top
master tag as set up in Tutorial 1.   The report is sorted
according to the CLASS_CODE tag.  The students within the
classes should also be alphabeticalized so that they can be
located quickly.  The top master tag is limited to fields within
the top master data file, but the last names are in the
STUDENT.DBF data file.  In order to sort by CLASS_CODE and the
students' last name, a sort expression must be used. 


Sort expression 	

By selecting the Database | Sort menu option,
an expression -- containing any of the fields in the extended
data file -- can be entered and used to sort the composite data
file. Select the Database | Sort menu and enter the following
expression: 
               ENROLL->CLASS_CODE+STUDENT->LAST_NAME 

The report will now be sorted primarily according to the class code and
secondarily according to the student's last name. 

Using a top master tag with a sort expression 	

The top master tag and the sort expression cannot each handle part of the
sorting tasks.   The top master tag and a sort expression should
not be used together in a report.  If both are specified, only
the sort expression is used. 

When a sort and a query are used together in a report,
CodeBase's Bit Optimization Technology can produce a report
faster than a top master tag. 

Select the Database | Top Master Tag menu option and select
the 'Close Index' button to remove the top master tag from this
report. 

*****************************************************************
Totals 	
*****************************************************************

The last task in this tutorial is to create and output
two totals: one that counts the number of students in a class,
and a second that counts the number of enrollments listed in the
report. In essence, both totals are mostly the same.  The class
total resets for each new class, while the report wide total
does not. 

Creating the total variables 	

A total output object may only output a previously defined total variable.  
This variable is created using the Report | Totals menu option.  Once the
'Totals' dialog box is displayed, select the 'Create' button and
enter REPORT_TOT for the total variable's descriptive name.  
The Expression Entry dialog box is displayed to facilitate the
entry of the total's expression.  Since the REPORT_TOT variable
is a count type total, the expression is ignored.  Enter '1' for the
expression.  CodeReporter then fills in default information for
the total variable.

Change the 'Type' list box so that 'Count' is highlighted. 
When the 'Reset Group' list box is set to 'NONE' the total
variable is never reset -- its value is updated throughout the
entire report.  Since this is what is desired for the REPORT_TOT
object, no change is necessary. 

Select the 'Create' button again to create the CLASS_TOT total variable.
Again, since CLASS_TOT is a count type variable, the expression is ignored.  
Enter 1 for the expression.
When the 'Totals' dialog is displayed, highlight CLASS_TOT in the 'Name'
list box and change the 'Reset Group' setting to 'Class' and the
'Type' to 'Count'.  The total variables are now created.  Select
'OK' to exit back to the report design screen. 

Adding the REPORT_TOT total object

Select the summary and create a new output object.  When the 
'Create Object' dialog appears, choose
the 'Total Lookup' button.  The resulting dialog box contains
both totals created, REPORT_TOT, and CLASS_TOT.  Double click on
REPORT_TOT.  The dialog box closes, REPORT_TOT is displayed in
the 'Object Text' edit control and the 'Total' radio button is
selected.  This object is complete, so select OK. Now create a
label object -- Total Enrollment: -- and place it in the summary
with the REPORT_TOT object. 

*****************************************************************
Where to place CLASS_TOT 	
*****************************************************************

The proper place for the CLASS_TOT output object is in the 'Class' footer.
However, since all group footers have a default size of zero when initially
created, the 'Class' footer must be re-sized before the output
object can be entered. 

Select the Groups | Size Groups menu option.  A
dialog box, listing all of the groups created for the report, is
displayed.  Highlight the 'Class' group and change the 'Footer
Height' edit control to 24 points and select OK. 

Creating the CLASS_TOT object as an expression

Create an object in the newly sized 'Class' footer.  
Instead of simply selecting CLASS_TOT from the Total Lookup dialog, 
enter 'CLASS_TOT' in the Object Text edit control, select the Expression 
radio control and choose OK.   CodeReporter complains because it thinks
CLASS_TOT is a field name, and it cannot find it.   

Change the 'CLASS_TOT' to 'CLASS_TOT()' and select OK again.  This
time CodeReporter accepts the total variable.  When a set of
parentheses is appended to a name in an expression, CodeReporter
automatically looks for a function, total or calculation of that
name.  Since CLASS_TOT is a defined total, it is found and
accepted.   The benefits of having the total variable in an
expression is not seen in this example.  However, if a total is
to be altered in anyway, or used in conjunction with a dBASE
function, another total, or a calculation, it must be done in an
expression. 

A plain label, such as 'Total in this class', can be added to
identify the total, however this provides an opportunity to use
an expression to combine in an output object a character string
and a data file field. Create a new object in the 'Class' footer
and select the Easy Expression button in the Create Object
dialog box.  When the Expression Entry dialog box appears, enter
the following expression manually typing in the character
string, and using the 'Fields' list box to include the
CLASS_CODE field. 

             'Total in '+ENROLL->CLASS_CODE+':' 

Once this expression is entered, return to the report design screen and
display the completed report.

*****************************************************************
Overview 	
*****************************************************************

This tutorial has used the basic skills listed below
to create a multi-group, relational report. 

         Using an existing report for the basis of a new one by using
           the Save As menu option to change the name of the current report. 
         Designing and creating a relation with one top master and two 
           slave data files. 
         Adding an additional group to the report, changing its position 
           in the report, and modifying the size of the group footer. 
         Creating expression output objects that combine two character 
           fields and combine character strings and fields using the 
           Concatenate I operator (+). 
         Using TRIM() to remove spaces between concatenated character fields.
         Using a sort expression to sort on a field in the master data file 
           and a field in a related slave data file. 
         Creating and placing a subtotal and a running total. 
         Using a total variable in an expression. 

