GENERATING REPORTS Pirouette's report generator is extremely powerful, enabling you to design virtually any report you wish. You can declare "zones" for the information you wish to print, such as at the top of every page. You can also work with several databases in the same report, "looking up" information from database other than the primary one when necessary. When you select GENERATE Reports from the opening menu, you will then be given a choice of the existing report specifications and "New Spec". If you select "New Spec", you will be prompted for a file name. Spec file names follow the same rules as database file names (see Creating a Database). Regardless of whether you select an existing spec or a new one, the Report Parameters window will appear. REPORT PARAMETERS The Report Parameters window is where you specify both the physical information about the printer and paper size you will be printing on, and the scope of the information to be included. The specific parameters are as follows: PRIMARY DATABASE. One way to think of the primary database is as the one which will be contributing most of the information to the report. A more literal definition would be "the database containing the fields you wish to sort on." To see a list of the available database files, press . SORT FIELDS. Pirouette allows you to declare up to three fields on which to sort your primary database, for the purposes of this report. To see a list of the fields in your primary database, press . FILTER CONDITION. This is a logical expression, just like that used when filtering the database under VIEW and Revise Data. See Appendix B for rules and regulations about these. PAGE LENGTH. The number of lines on the page. This will usually be 66, unless you're on a laser printer, in which case it will probably by 60. If you're printing labels, it will depend upon the size of the label. 1" labels will be 6 lines long, 1.5" labels will be 9 lines, and so on. TOP & BOTTOM MARGINS. The number of lines to use as margins on the top and bottom of the page/label. -32- PAGE WIDTH. The total number of spaces across on your paper. This will, of course, depend on the size type you use. If you are printing 10 cpi (characters per inch), normal size paper will give you a width of 85. If you're printing 12 cpi, you width will be 102. LEFT & RIGHT MARGINS. The number of blank spaces on either side of your report. Again, this will depend upon the size type you use. At 10 cpi, a left margin of 15 will give you an actual margin of 1.5". PRINTER SETUP STRING. A character string to initialize your printer to the desired type style. This is specified in the same way that Lotus specifies setup strings: \### signifies ASCII character ###, other characters signify themselves. For instance, if you wished to print bold face on an Epson printer, you would want to send the printer the Escape character (ASCII code 27), followed by a capital letter "G". This would be specified as: \027G More involved printer control is possible within the report itself; something which is discussed later in this chapter. OUTPUT DESTINATION. The name of a DOS device or file. Allowable devices are PRN, the parallel ports LPT1, LPT2, and LPT3, and the serial ports COM1, COM2, and COM3. (Depending upon the configuration of your system, not all of these ports will be available.) To send the output to a text file, just specify the name of the file, including the drive and directory. After you have specified all applicable parameters for the report, just press to Go -- that is, to begin printing the report. The report can be aborted during generation by pressing . Before you run the report, though, you will probably want to design it; i.e., declare the items you want included. This topic consumes the balance of this chapter. THE REPORT EDITING WINDOW To create the actual report design (or Edit the layout of an existing report), press from the Report Parameters window. This will open the Editing Window. The Editing Window has few differences from the Formatting Window used to create the input screens. One difference is that the window scrolls both vertically and horizontally, so that you can create reports which are longer or wider than the computer screen. There are also a few additional commands available. -33- The cursor control commands, however, are virtually identical to those in the input window, with the exception of the added "scrolling" keys: Editing Window - Navigation Keys Key Result ---------- ---------- cursor Down down one line cursor Up up one line cursor Left left one column cursor Right right one column Ctrl-Left scroll left ten columns Ctrl-Right scroll right ten columns PgDn down ten lines PgUp up ten lines Home move to first column End move to last column Tab left 15 columns space insert one column Del delete one column Ctrl-Enter insert a new line Ctrl-Y delete the current line change print ZONE As mentioned above, each line of the report has an associated "Print Zone". These zones are indicated to the left of each line in the report. Print Zones allow you to control when each line is printed. The choices are: Intro Print only at the beginning of the report Header Print at the top of each page Body Print once for every specified record Footer Print at the bottom of each page Sub-3 Print whenever the tertiary sort field changes in value Sub-2 Print whenever the secondary sort field changes in value Sub-1 Print whenever the primary sort field changes in value Total Print at the end of the report -34- Pirouette will allow the zones to appear only in this same order, although you may omit any given zone for a particular report. In fact, the way to get a summary report, showing only totals with no detail, is to omit body lines from your report. enter TEXT If you wish to add text to your report, move the cursor to the desired location and press . LIST data fields To include a data field on your report, move the cursor to the desired location and press . A list of the databases in the primary database will appear. If you select one of these, a block will be places on the window to indicate where the field will be printed. You can also pick one of several other items from this List window. These items are as follows: Page #. Selecting Page # will make the page number of the report appear. This is used primarily in headers and footers. PgBreak. PgBreak creates a page break in your report. This is generally used in "Subtot" lines when you wish each group of records to appear on a separate page. Count. When placed in a Subtot line, Count prints the number of records in the group which was just printed. When placed in a Totals line, Count displays the total number of records included in the report. Lookup. The final option allows you to look up a value from another, related database. When you select "Lookup", a window will pop up listing the other databases in the current DOS directory. When you select one of these, another window will open, listing the fields in that database. You will observe that the second database also offers the option of "Lookup", enabling you to repeat the process. In this way, you can look up one database, which looks up another, which may yet look up another. One of Pirouette's unique features is that it allows you to use data which is up to three levels distant from your primary database. -35- enter a CALCULATION Just as you can display the result of a calculation on the input window, you can display the result of a calculation as part of the report. Report calculations are even more powerful, in fact, because you can create formulas using fields from several databases. As you build your formula, you can press to see the same field list described above -- including the choice of "Lookup". This will place a Lookup() function into your formula. (Don't worry about the syntax of this function, just use whenever you need to include it.) In general, though, report calculations just follow the same rules as all Pirouette expressions (described in Appendix B). HINT: If you wish to include the date on your report, just create a calculation consisting of DATE(). This function returns your computer's system date in the format MM/DD/YY. If you'd like the date written out, use a more complicated formula: STR(DAY(DATE()),2) + " " CMONTH(DATE()) + " " + STR(YEAR(DATE()),4) You can use another function, TRANSFORM(), to format numeric output. See Appendix B. EDIT an object To edit an existing piece of text or a calculation, move the cursor to the appropriate piece of text and press . Everything in the report window, incidentally, is either text or a calculation. When you press to include an isolated field, the field is still regarded as a calculation, albeit a simple one. KILL an object To remove an existing field or piece of text from the report, move the cursor to that object and press . DRAG an object To move an existing field or piece of text to another location, move the cursor into the object and press . The object will begin to flash. Move the cursor to the desired location and press . You may use and to scroll the screen left or right during this process. -36- Totals and Subtotals. Numeric fields or calculations show a special behavior when placed in a Subtot or Totals line. What appears on the report is not the value for the current record, but a subtotal or total for the designated field. If a non-numeric field is placed in a Totals or Subtot line, the value displayed will simply be the value of the last record. QUIT report edit When you are satisfied with the appearance of your report, press . This will exit the editor and return you to the Report Parameters window. GETTING FANCY Using the CHR() function, it is possible to create remarkably complex reports, utilizing different fonts, bold-facing, italics, etc. The only real limitations, really, are your level of effort and imagination. Suppose you wish to have your Intro in bold, with the rest of the report in regular typeface. If you're using an Epson or Epson-compatible printer, just create a Calculation at the beginning of the Intro, using the following expression: CHR(27)+"G" Because Esc-G is the character sequence which causes an Epson printer to begin printing double-strike, all further characters will be printed bold. Then, at the end of the Intro section, create another calculation to cancel the double-strike printing. The character sequence for this is Esc-H, which would be coded as: CHR(27)+"H" Now let's take it one step further. Suppose you have a field (PROFIT, let's say) which you wish to print boldface when it's negative, and in regular type when it's positive. You can use the IF() statement in combination with CHR(), like so: IF(PROFIT<0, CHR(27)+"G"+STR(PROFIT)+CHR(27)+"H", PROFIT) If the PROFIT field is less than zero, this expression will print the Esc-G (to begin double-strike), followed by the value of the field PROFIT (converted to a character string, to avoid a data type conflict), followed by Esc-H (to cancel the double-strike). If PROFIT is greater than or equal to zero, the field PROFIT is printed with no special printer controls. -37-