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.