Media Share 9/MEDIASHARE_09.ISO/dbase/a4_faq.zip/REPORTS.TXT

Text File | 1993-01-13 | 25KB | 501 lines

            Common Questions about Alpha FOUR Reports

This document covers some of the more commonly asked questions
about reports in Alpha FOUR. For specific problems with printing
a report (such as page alignment) see the document on printing.

                        Printing/Display

Q    I don't understand the different sections included in a
report: when they print and where to put my data. For example,
what's the difference between the Report Header section and the
Page Header section?

A    The different sections in a Report can seem confusing at
first. Let's try to give some perspective to this by giving a
short description of what each of the sections do.

Report    Prints at the top of the report, replaces the
Header    page header on the first page only. This section
          usually includes things like the report title,
          the date etc...
Page      Prints at the top of each page. Usually includes
Header    things like page numbers. Note: The page header
          will be replaced with the Report header on the
          first page.
Titles    Appears at the top of every page, after any
          report header or page header. You can optionally
          specify that Titles should be printed only on
          page 1, or that Titles should be reprinted after
          Group Headers.
Group n   If you are using sub grouping you may want
Header    certain text or fields printing only once at the
          top of each group break. For example, if you're
          grouping on Company you may want the Company name
          and address to appear only once at the top of
          that group. For clarity you can have the
Detail    Prints once for each record in the database. This
          is the meat of most reports. This is where you
          would place fields from your database to print
          for every record.
Group n   Like the group header, this section prints at a
Footer    group break but below all the records contained
          in that group. Summary values like a group total
          would be appropriate to place here.
Page      Prints at the bottom of each page. Used primarily
Footer    to align data specifically at the bottom of the
          page.
Summary   This prints at the very end of the report. Most
          often this section is used to print grand totals
          and other summary values or anything you want
          printed at the end of the report. You can also
          specify that the summary section print on a
          separate page by itself (on the Report Parameters
          screen).

Most of the sections can be customized so that they replace other
sections (the group header can replace the page header for
instance) or print only on certain pages. For example you can opt
to print the Titles section only on page 1 by saying yes to this
question on the Report Parameters Screen (although you can
accomplish virtually the same thing by placing your headings in
the report header section and omitting the titles section
completely). See the Report Parameters screen or the Grouping
Parameters screen for some options.

Sections can also be added and deleted according to your needs.
In fact a report with only a group footer and summary section may
be desirable if you only need totals, averages and other summary
values printing out and don't need the detail on each record.
Don't worry about deleting a section if you don't require it, you
can always get it back, you'll just have to add the fields to
that section again. If you delete the detail section, the records
still pass through the report they just don't print individually.
Your totals and other summary values in the footers and summary
section are still valid.


Q    In my Alpha FOUR reports there are times when I would like
to suppress an entire line from printing but only if a certain
condition is met. Is there a way I can do this?

A    In Alpha FOUR and Alpha/three, you can suppress the printing
of any line in a report (empty or filled) by following the
example outlined below.

First create a calculated field in the report using an expression
such as:
     IF(FIELD="XXX"," ",CHR(127))
Insert this calculated field in the report anywhere on the line
to be blanked (this field will be invisible). Next go into the
format options screen (Alt-O) and add a dash, "-" (the delete-
when-blank format code) in the FMT column for that calculated
field. Finally make sure to set "Suppress blank lines?" to YES on
the Report Parameters screen (F7, report Parameters).

When a field formatted with a dash is blank, Alpha FOUR will
usually suppress the entire line the field is on from printing.
The expression causes Alpha FOUR to choose between a blank space
(" ") which would allow the line to be suppressed, and CHR(127)
which represents a character that cannot be suppressed by the
dash.

If the condition in the expression is met, FIELD="XXX", the
calculated field will return a blank space. In this case the "-"
format option ("delete-when-blank") takes over and instructs
Alpha FOUR to suppress the entire line from printing on the
report. If the condition is not met (FIELD does not equal "XXX")
the calculated field returns a value of CHR(127) (the non-
suppressible line character  ). This character ( ) is inserted on
that line in the report which forces Alpha FOUR to disregard the
dash formatting option and print the line.

ASCII character 127 () is Alpha FOUR's symbol for a non-
suppressible line. It is the same character used when you press
Ctrl-Bksp on a line in a Report to force a blank line. It is used
by the program internally and won't print.

Example: Suppose a report uses one level of grouping, with a
group footer that prints the total number of records in the
current group. If a group contains only one record, printing the
footer would be redundant, so you want to suppress the entire
footer. However, when the group contains two or more records, you
want to print the footer.
1) From the Report Layout screen, press Alt-C to access the
   Calculated Fields definition screen.
2) Enter a name for the new calculated field, such as SUPPRESS.
3) For the field's expression, enter:
        IF(SYSTEM->CNTR_GRP1=1," ",CHR(127))
4) Press F10 (Continue) to store the new calculated field.
5) Place this CALC->SUPPRESS field in the group footer anywhere
   on the line that you want suppressed. In this case place it
   on every line in the footer because you want the whole footer
   suppressed.
6) Press Alt-O to access the Formatting Options screen.
7) Enter a dash (-) in the "Fmt" column for every occurrence of
   the CALC->SUPPRESS field. This format code forces Alpha FOUR
   to delete the entire line when the CALC->SUPPRESS field
   contains a blank space.
8) Press F10 (Continue) to save the new formatting options.

When the Report prints, here's what happens:
1) When Alpha FOUR gets to the group footer section, it
   evaluates the expression:
        IF(SYSTEM->CNTR_GRP1=1," ",CHR(127))
2) Alpha FOUR checks the current value of SYSTEM->CNTR_GRP1. If
   SYSTEM->CNTR_GRP1 equals 1 (indicating there was only one
   record in group 1) a blank space is inserted into the line.
   If SYSTEM->CNTR_GRP1 equals anything but 1, CHR(127) ( ) is
   inserted onto the line.
3) If the line contains a blank space, the "-" format takes
   effect, and deletes the entire line from the report. However,
   if the line contains  , the line cannot be suppressed, so
   Alpha FOUR prints the entire line.

NOTE: Although this technique causes a line to be suppressed from
printing, the data on that line is still technically part of the
report and is included in any appropriate summary values. It is
only the printing that has been suppressed. This technique also
works in Forms and Mail Labels.


Q    Is there a way I can get a blank line after every so many
records in a report?

A    This expression will create a calculated field that will
print as a blank line but only every so many records. For example
suppose you want a blank line after every 5 records.

Define a calculated field in the report with the following
expression: INT(SYSTEM->CNTR_GRAND/n)
where n is the number of records you want printed before a blank
line.

Create one grouping level. The field on which the group is based
will be the calculated field above. Next, place your cursor on a
blank line at the bottom of your group 1 footer.  Press the CTRL
and BKSP keys simultaneously.  This will add a small house-shaped
character () which will force a blank line at that point in
conjunction with the group level above.

The calculated field creates a dummy numbering system that forces
the group 1 footer to print every so many records. The  symbol
forces a blank line to print whenever the group 1 footer prints.


Q    What are my options for printing multiple copies of a
report?

A    Aside from the obvious - printing the report twice (you can
put the keystrokes to do this into a script), you also have a few
other options:

On a Laserjet: To get multiple copies of reports on an HP
Laserjet (and many other laser printers) modify the printer
configuration. The following changes to the printer configuration
cause Alpha FOUR to print n number of copies of each page. After
the report is done printing, you can collate the pages into
multiple copies of the report.

To get into the printer configuration; from the main menu, choose
Other, Configuration, Printer configuration, Create/edit. Place
the following in the setup codes into the printer configuration:
Setup codes: 27 38 108 nn 88  ("nn" should be replaced by the
decimal value representing the number of copies - "50" for 2
copies "51" for 3 etc). For example, for 2 copies use the codes:
27 38 108 50 88
Close out codes: 27 69 (this resets the printer back to one
copy).

The main drawback to this method is that multiple copies of each
page will print out one after the other rather than one report
then the second and so on. You then have to go through and
separate the pages.

On other printers: An alternative method for printing multiple
copies of a report is rather involved but this will work if there
are no other options. Here you'll be creating a set with a child
database that has one, two, 10 or more records that each match
every record in your database (now the parent database of the
set). When the report prints it will print multiple copies of
each record because the set now contains multiple "virtual"
records for each of your records. The number of copies printed is
determined by how many records you create in the child database.

Reconfigure your current database and add a new field called DUP.
This field will be used to link the two databases into a set. The
field should be Character type with a length of one. When this is
completed, globally update this field so that every record
contains the letter "A" in this field. Next, create a new
database and call it COPIES. The only field in this database
should be called TEMP and it should be Character in type and have
a length of one. Then enter as many records as you want copies
into this new database and the TEMP field should contain the
letter "A" for each record. For example to get two copies of the
report enter two records into COPIES. After you have entered the
records build an index on the field TEMP.

Next build a set from these two databases. Link your database as
the parent with link mode set to "All", and use the newly created
TEMP index as the linking index. You may then borrow the report
from the parent database and print it out. Alpha FOUR will create
a virtual copy of each record in parent database for each match
it finds in the child thus creating the desired copies of the
report.

Note: This method will only work if you have each record starting
on a separate page. If more than one record appears on a page you
will get duplicate copies of each record rather than duplicate
copies of each page.


Q    How can I start the page numbering at other than 1?

A    Here is a calculated expression you can use to start
numbering pages beginning with a number other than 1:
     nn+IF(SYSTEM->CNTR_GRAND<=1,1,SYSTEM->PAGENUMBER)
"nn" should be one less than the number you actually want to
begin with. For example if you want to begin with the number 2,
replace "nn" with the number 1: 1+IF(SYSTEM-
>CNTR_GRAND<=1,1,SYSTEM->PAGENUMBER)


                         Summary values

Q    Whenever I print a report from my set my summary totals are
always higher than I expected. What could be causing this?

A    Whenever you link three or more databases into a set and at
least two of the links is a one-to-many link (Link to? set to
"All") you take the risk of creating more virtual records than
you intended. The reason for this is that Alpha FOUR tries to
show you all the possible combinations of matching records in all
the linked databases. For example, suppose you have three
databases A, B and C. You create a set of these databases with
database A as the primary database and both database B and C
linked directly to database A on the same common field.

If database B and C always have only than one record that matched
the parent record (A) then you would not have a problem. Alpha
FOUR would simply show you one "virtual" record that displayed
the single matching records from database A, B and C on the
screen at one time (one "virtual" record).

Let's take this a step further. Suppose database C has two
matching records for a given record in database A but database B
still has only one match for that record. This causes Alpha FOUR
to show two virtual records; since database C has two records
that match the one record in database A and B, this accounts for
the two virtual records shown by the set.


Finally let's assume that database B now also has two matches
along with the two matches in database C. At first  it seems that
Alpha FOUR should show two virtual records. Looking more closely
you realize that all possible matches must be shown so four
virtual records are displayed. One representing each possible
combination.

In the report, you are actually "feeding" the report 4 virtual
records and this is why the totals are always coming out higher
than you expect. Let's assume that the field you are totaling
comes from the parent (A) database. Remember that one record from
database A will be fed to the report with each virtual record.
Therefore the same value from database A gets fed to the report 4
times even though there is only one physical record in database
A; there are four virtual records. This explains why the totals
are higher than expected.

To better illustrate this, try including the field you are
totaling in the detail section (as a value). When you print this
report you will probably notice that the field value is printed
more than you expected. With two or more child databases a set
linked one-to-many this is actually what is supposed to happen.
Alpha FOUR is printing the field value as many times as there are
virtual records.

One option for getting around this is to make all but one of the
links a one-to-one (link to "first" or "last" rather than all).
In the example above, if you linked database A to B as "first"
and left the A-C link as "all" you would limit Alpha FOUR to
showing only one match (the first match) from database B and this
would show only two virtual records much like the first example
above.

This may not work for some. If you actually need to link more
than one of the databases as one-to-many, realize that you are
potentially creating more virtual records and will need to
account for this in any summary totals.

If you are grouping on a field in the primary database and would
like to increment the total for a field only once per grouping
you could use a calculated field with the expression:
     IF(CNTR_GRP_1=1,FIELD,0)
Then take the total of this calculated field as a summary value
rather than taking a total of the original FIELD. This expression
will only return a value for FIELD for the first record in each
group. All subsequent records in the group will return 0. This
prevents the total from over-incrementing by adding a value for
every record in that group.

 Section        Summarizing the FIELD          Summarizing the
                                              calculated field
 Detail                     FIELD=595           CALC FIELD=595
 Detail                     FIELD=595             CALC FIELD=0
 Detail                     FIELD=595             CALC FIELD=0
 Group1 ftr/Group total of FIELD=1785  Group total of CALC=595
 Detail                     FIELD=645           CALC FIELD=645
 Detail                     FIELD=645             CALC FIELD=0
 Group1 ftr/Group total of FIELD=1290  Group total of CALC=645
 Detail                     FIELD=234           CALC FIELD=234
 Detail                     FIELD=234             CALC FIELD=0
 Group1 ftr/ Group total of FIELD=468  Group total of CALC=234
 Summary    Grand total of FIELD=3543 Grand total of CALC=1474


Q    I have a report that prints information from a relational
set that uses a one-to-many link. How can I get Alpha FOUR to
include the AMOUNT only once for each record in the primary
database, instead of multiple times for each link to a child
record?

A    To accomplish this create a calculated field in the report
that will capture the AMOUNT only once for each primary database
record. Then summarize the calculated field to print the total in
the report's Summary Section.

The calculated field expression uses the System Database subgroup
counter field, so you must first specify one level of grouping on
the (F7) Report Parameters screen in the Report Layout. On the
(F7) Grouping Parameters screen, specify as the group 1 break
field any field in the primary database that will change with
each new record (SYSTEM->REC_NUMBER is good). Next, use the
following expression in a calculated field called AMTCALC (F2,
Define Calculated Field):
     IF(SYSTEM->CNTR_GRP1=1,PRIMARY->AMOUNT,0)
Place the SYSTEM->CNTR_GRP1 field in the above expression by
pressing F2 Fields, System and selecting CNTR_GRP1 from the list
of system database fields. In this example, the primary database
in the set is named PRIMARY and the field to summarize is named
AMOUNT.

Once you have defined the calculated field AMTCALC, press F10 to
return to the Report Layout screen. Position the cursor in the
report's Summary Section where you want the amount total to
appear. Press F2 Fields, Select Field, Calculated and select
AMTCALC. On the summary options list, choose TOTAL. Alpha FOUR
will now total the calculated field AMTCALC which only returns
AMOUNT once for each master record.


Q    How can I total just the last values from each subgroup in a
Report without including every record in that group?

A    This illustrates how to only include the last record from
each subgroup in the total. It ignores, from the total, all
records that are not the last record in the subgroup.

This example assumes that you have at least on level of sub
grouping. First you need to determine the last record of a
subgroup. To do this create a summary field based on the SYSTEM-
>CNTR_GRP1 system field, set the summary column to "Last", set
the Level to "Grand", and set Process to "Pre-processed". Call
this field GRP1LST

Next, create a calculated field in the Report that will test to
determine if the system field SYSTEM->CNTR_GRP1 is equal to the
Preprocessed summary field defined above, SUMARY->GRP1LST. Call
this field CALC1.
     IF(SYSTEM->CNTR_GRP1=SUMMARY->GRP1LST,fieldname,0)
"Fieldname" in the above expression refers to the numeric field
you wish to total. Place CALC1 in the summary section of the
report as a total. This returns a "fieldname" total but includes
only the last record in each subgroup.

                           Subgrouping

Q    How do indexes relate to subgrouping?

A    The index controls the order in which the records are "fed"
to the report. The subgroup simply breaks the report into groups
but it cannot control the order of the records. This is why it is
so important to include the subgroup fields in your index. If the
database records are being fed to the report in record number
order for example you will not have related records grouped
together. The subgroup will then group all the related records
that happen to be next to each other into one group.

Another way to look at it is this. Alpha FOUR simply watches the
subgroup field for a change as records are fed to the report. As
far as the subgroup is concerned, there is nothing in the
database except for the subgroup field in the current record and
that same field from the previous record. When Alpha FOUR sees a
change in this field from the last record it inserts a group
break, but the subgroup cannot move records around if they are
out of order. The index must group all related records together
so that when the subgroup places a break it makes sense.

       Database Indexed on            Database Indexed on
                 Last name            Company

   Anderson, Bob, Alpha Co.           Alpha Co., Anderson,Bob
   Jones, Stephen,Alpha Co.           Alpha Co., Jones,Stephen

  Marchant, Claud, Beta Co.           Alpha Co., Marsh,Gloria

   Marsh, Gloria, Alpha Co.           Beta Co., Marchant,Claud
    Smith, Nancy, Gamma Co.           Beta Co., Via, Marie

Thompson, Howard, Gamma Co.           Gamma Co., Smith,Nancy
                                      Gamma Co., Thompson,Howard
       Via, Marie, Beta Co.           Gamma Co., Wood,Burt

      Wood, Burt, Gamma Co.


Q    How can I subgroup my report by every nth record?

A    Grouping a report by every nth record (every 4th record
represents a new group for example) involves creating a
calculated expression in the report and using this calculated
field as the subgroup field. You want an expression that will
return the same character(s) for 4 consecutive records then
change and remain constant for the next 4 records and so on. The
following expression does this using the INT function:
     INT((SYSTEM_CNTR_GRAND-1)/4))
This expression makes use of the report's counter and returns the
integer portion of the result of subtracting 1 from the system
counter then dividing it by 4 (or any number you want).

SYSTEM_CNTR_GRAND  (SYSTEM_CNTR_GRAND-1)/4  Subgroup looks at
                                            the integer
       1                  0                     0
       2                 0.25                   0
       3                 0.5                    0
       4                 0.75                   0
       5                 1.0                    1
       6                 1.25                   1
       7                 1.5                    1
       8                 1.75                   1
       9                 2.0                    2

Grouping by week: Grouping by the week involves a similar logic.
Create a calculated group break field with the following
expression: INT(DATEFIELD-{DATE_VALUE},/7) Use this field as your
subgroup break field. The key to making this work is the date
that you place in the field DATE_VALUE. This date must be the
same day of the week as the day you want to break on--Sunday,
Monday, etc. and also a date that is at least two weeks earlier
than the first record you will be printing. For example if the
earliest date in your database is 6/1/92 and you want to group by
week starting on Sunday, DATE_VALUE could be 5/10/92 (a Sunday at
least two weeks before 6/1/92).

                          Miscellaneous

Calculated Field Tip: If you are using virtually the same
expression in multiple calculated fields you may want to consider
using the clipboard and pasting the expression multiple times.
Use Alt-F5 to copy the expression to the clipboard and use Alt-F6
to paste. Then simply edit the expression with any changes.