"SBase Personal - 42. GENERATING REPORTS" 42 GENERATING REPORTS Overview Procedure Summary Example Report Form Example Report Program Editing Report Forms Sorting Filter Field Selection Placing Fields in a Select Box Sizing the Select Box Heading Report Heading Column Headings Date, Time, and Page Number Footing Sorting by Group Multiple Groups Before Group Subtotals and Other Report Functions Report Function Syntax Global Before Report After Report Options Page on Record Page on Group Page before Report Page before Totals Summarize Test Saving the Report Form Formatting Report Output Running a Report Adding Filter Conditions Adding Order Conditions Print/Display Option Modifying a Report Program Output Device Filter and Order 42 GENERATING REPORTS (Only in SBase Professional 4) SBase provides many alternative ways of obtaining output from database files. You can print single records, list file data record by record, or print forms that reproduce the graphics created with the Form Designer. For many users, though, the process of retrieving and outputting data is thought of as `reporting.' In SBase, reporting is considered as an extension of the program's query facilities, in which explanatory text, such as headings and footings, is combined with analytical functions, such as subtotaling and mean calculation, to produce more highly structured and informative output. The Form Designer Pro provides a set of commands for generating reports. You can use the easy point-and-click techniques of the Form Designer Pro to position fields, calculations, report functions and text where you want them to appear in the actual report. When you save the report form, the Form Designer generates a SBase DML program and stores it on disk. This program may be run from within the database, and may also be edited to add further details if necessary. The report form itself may be edited in the Form Designer Pro if you wish to change the report layout using editing techniques rather than programming. Overview The commands for creating reports are on the Report Generator menu. This menu is only enabled if the following conditions exist: 1. An existing report form has been opened, OR 2. You have used the Project New command to create a new form AND you have opened a database file. On the Report Generator menu, there are commands for defining headings and footings, commands for specifying group and global analysis features, and a command for selecting the fields to be printed. Procedure Summary When you create a report, you follow the same general procedure each time. Within this, of course, there is room for infinite variation, and you will need to understand the sections that follow this overview to appreciate the range of facilities available. 1. Enable the Report Generator menu as explained above. 2. Open all the database files you wish to use in the report. 3. Choose the Report Generator Select command to create a SELECT box for the body of the report. 4. Place fields in the SELECT box in the usual way, by selecting the FLD object type and clicking where you want the field to appear. - You may also place calculations (derived columns) and text in the SELECT box. 5. Choose the Report Generator Heading command to create a HEADING box in which page headings will appear. 6. Place text in the HEADING box in the usual way. - Calculations may be used to show date, time, or page numb - er. 7. Repeat the last step using the Report Generator Footing command. 8. If you want your report to be grouped on the contents of a particular field, choose the Report Generator Group command to create a BEFORE GROUP and AFTER GROUP pair of boxes. 9. Specify text, fields, and report functions - the FNC object type - in the BEFORE GROUP and AFTER GROUP boxes. Subtotals are defined in the AFTER GROUP box. 10. Repeat steps 8 and 9 for each group field in a report. 11. For pre-report and post-report features like those available in the BEFORE and AFTER GROUP boxes, choose the Report Generator Global command to create BEFORE REPORT and AFTER REPORT boxes. 12. Use Define SB Links to establish a link between the form and its database files. 13. Save the report form. - The Form Designer creates both an SBV form file and an SBP SBase DML program file. When you want to run the report, exit from the Form Designer and run SBase, then open the form in the usual way. Example Report Form *** See Fig 42-1 *** Example Report Program Saving the example report above would generate a program like this: OPEN FILE "SUPPLIER" REQUEST "REPORT TO PRINTER?","",1,a% IF a% THEN PRINT ; REPORT Current_Balnce.SUPPLIER AFTER REPORT ? @31;"END OF REPORT" ? ? @9;"Report Count:";@23;&2.0 COUNT ;@50;"Report Total:"; @65; &10 SUM Current_Balnce.SUPPLIER END REPORT HEADING ? @26;"SUPPLIERS LIST ON";dat$;@66;"Page: ";pag$ ? ? @1;"Code";@10;"Name";@44; "---- Last Paid ---- " ;@68;"Balance" ? @44;"Date";@57;"Amount" ? END HEADING GROUP SupState.SUPPLIER, Current_Balnce.SUPPLIER BEFORE GROUP SupState.SUPPLIER ? ? @1;"Suppliers for State: ";@23;&2SupState.SUPPLIER ? END GROUP AFTER GROUP SupState.SUPPLIER ? ? @2;"Number of Suppliers: "; &2.0 COUNT ;@46; "Balance Subtotal: "; @65; &10 SUM Current_Balnce.SUPPLIER ? END GROUP SELECT @2;&8Supplier_Code.SUPPLIER; @11; &30Supplier_Name.SUPPLIER;@44;&8Last_Paid_Date.SUPPLIER; @53; &10Last_Paid_Amt.SUPPLIER; lnce.SUPPLIER NEWLINE ORDER SupState.SUPPLIER ASK Objects on Report Forms The Form Designer allows only a subset of object types on the form: Text, Fields, Calculations, and Report Functions. The report form is displayed in the system font. Editing Report Forms You can edit the boxes on a report form by selecting them and using the Size tools or the Clear command on the Edit menu. Objects on report forms may be assigned underlined or italic text styles. Sorting A default sorting order for the report is generated automatically when necessary. The Form Designer derives this from the sequence of Groups in the report structure, and builds an ORDER line, which is placed in the SBase report program. When you run the report program from within SBase, you will be asked whether you wish to add any extra fields to the ORDER line. You might wish to sort within the records by date or amount, without actually specifying a group for such fields. All the fields used for sorting the report are sorted in ascending order. If you wish to change this you can alter the ORDER line when it is presented at runtime. See Modifying a Report Program at the end of this chapter. Filter A report that outputs fields from more than one file must have its files linked. The stored file links are used to generate a default WHERE statement in the SBase DML report program that is created when you save the form. The WHERE line contains only the information needed to relate files together when more than one file has been used in a report. The information is taken from the field links that you should define before you save the report form. If only one file has been used or no links have been defined, no WHERE line is created. When you run the report program from within SBase itself, the program displays a filter requester, so you can add extra conditions to the automatically generated filter line for use at runtime. You can easily make these temporary extra conditions permanent by editing the report program and saving it, under a separate name if you prefer. See Modifying a Report Program at the end of this chapter. Field Selection The fields defined in the Select box will be printed on each line of the body of the report. In a report on a single file, the contents of the Select box will be fields from that file, and perhaps calculations used as derived columns. Where the report uses fields from more than one file, the Select box represents the `many' part of a one-to-many relationship. The `one' part is represented by a Group box. For example, suppose you wish to print a report of customer orders sorted by customer, and the data is held in two files, Customer and Orders (many Orders to one Customer). You would print details of each customer's orders as the body of the report, defining those fields here in the Select box. However, details about the customer would appear in a Group box which only prints out when it is time to change to the next customer. Placing Fields in a Select Box 1. Choose the Select command from the Report Generator menu. The Form Designer creates a box across the screen with the keyword SELECT to identify it. - The box is three lines deep in the system font size. 2. Choose the Field (FLD) object type from the toolbox. 3. Click in the Select box where you want a field to appear. 4. Choose a field from the list of fieldnames. Click the one you want, then OK, and the field is placed in the Select box. - To change to a list of fields from another open file, click the arrow button next to the filename. - If you choose fields from more than one open file, you must link the files so that SBase will know how to join them when the report is run. The file links are used to generate a default WHERE statement in the SBase DML report program that is created when you save the form. - If you want to Sort the report output by group, you should NOT place the group fields in the Select box. Create Before Group and After Group boxes for this purpose with the Group command. You may include text objects and calculations in the Select box, but not commands. To place some text in the Select box, select the Text object type in the toolbox, click in the Select box, and type the text in the usual way. To place a calculation in the Select box, create one as described in Chapter 39 Form Calculation Formulas. Sizing the Select Box You can resize the Select box from its default three line depth. If you leave a blank line above or below the fields in the box, this will be reproduced in the report. - By making the Select box very large, you can obtain page oriented output. Heading If you want a heading to print on each page you must define the text in a Heading box. To print a separate title page, use the Global command. When you select the Heading command, the Form Designer creates a Heading box three lines deep in the system font. You may resize the Heading box to accommodate multiple lines and column headings. Report Heading To create a report heading, choose the Text object type in the toolbox, click where you want the text to appear, and type in the text. Report text may use any of the text styles. Column Headings You will probably need to resize the Heading box. 1. Select the Size tool in the toolbox. 2. Drag the bottom edge of the box downwards to make it larger. 3. Select the Text object type in the toolbox. 4. Click where you want the text to appear. 5. Type the text, pressing ENTER to finish. Date, Time, and Page Number You may include the system date and time and the page number in the Heading box. Create a calculation (see Chapter 7) and supply the system variables TODAY, NOW, and PG as appropriate. You may format the page number with the formula STR$(PG,"99"). Footing If you want a footing to print on each page you must define the text in a Footing box. When you select the Footing command, the Form Designer creates a Footing box three lines deep in the system font size. You may resize the Footing box to accommodate multiple lines. Follow the same procedure as for Headings when typing in the text for the Footing box or placing a calculation in it. Sorting by Group Report output must often be sorted into groups, such as lists of addresses by state or employees by department. The Group command allows you to select fields on which to sort the report. The fields you select are used to generate a default ORDER statement in the SBase DML report program that is created when you save the form. When you select the Group command, the Form Designer presents a requester from which you choose a field to group on. In some other programs this is called a `break' or `control break' field. Click the field you want, changing file if necessary to display the fieldnames for each file, then click OK. The Form Designer creates two boxes, one above and one below the Select box: the Before Group and After Group boxes. The two boxes are paired, and the name of the Group field is shown on the top line of each box to indicate this. Multiple Groups There is no practical limit to the number of groups you can create. Each pair of Group boxes is placed inside the previously created pair, so you should set up the groups in order from largest (e.g. Employee Company) to smallest (e.g. Employee section). Before Group If your report groups employee data by department, you should place fields to print details about the department in the Before Group box to avoid having to duplicate them in every line of the Select box. The Before Group box contains text and data that you want to appear on every change of group. You can include field names that do not appear in the Select box. This is the way to produce reports that follow a transactional structure: Customer number, customer name Order number, Order date, Order amount Order number, Order date, Order amount Customer number, customer name Order number, Order date, Order amount Order number, Order date, Order amount Customer number, customer name Order number, Order date, Order amount Order number, Order date, Order amount Order number, Order date, Order amount Here, the Customer data is placed in a Before Group box for the Customer name or account number, and the Transaction data appears as a group of fields in the Select box. Subtotals and Other Report Functions The After Group box may contain text and fields that you want to appear when a group ends. This is where you analyze the report data with functions for totaling, averaging, etc. To add a Report Function, choose the Function object type from the toolbox and click where you want the function output to appear. For example, you would probably want to align a subtotal result with the field in the Select box to which it applied. You may, however, position Report Functions anywhere within an After Group or After Report box, as they always specify the field to which they apply. You will probably want to add some text next to the function to identify its purpose. 1. Select the Report function object type (FNC) from the toolbox. 2. Click where you want the results of the function to appear. 3. When the Report function requester appears, click one of the keyword buttons, then click the field name to which it is to apply: SUM Balance.CLIENTS 4. Click OK to complete the definition. - You can place only one fieldname after the function keyword (only one result can appear where you clicked), so repeat this procedure for each function. In report forms, Report functions may be placed only in the After Group and After Report boxes. Report Function Syntax The following functions may be used in Reports. SUM (field) Gives the field total for all the records in a group or report. MIN (field) Returns the minimum value of the specified field among the records in a group or the report. COUNT Returns the number of records in a group or the report. MAX (field) Returns the maximum value of the specified field among the records in a group or for the report. MEAN (field) Returns the average value of the field data in a group or in the whole report. VAR (field) Provides the Variance measure of the spread of the data from its mean. SD (field) Provides the Standard Deviation measure of the spread of the data from its mean for the field data in a group or over the entire report. - Apart from COUNT, the report functions may only be used with numeric fields. - You may combine report functions with each other and with other functions to derive more complex reporting functions. - If you want to force a new page after a group, choose Page on Group in the Report Generator Options requester. - When a group keyword changes, the group field no longer contains the data for the old group. For example, when grouping on the field State and the state changes from `Alabama' to `Alaska' the value `Alabama' is no longer accessible in State. However, you may use the keyword GROUP in a calculation to retrieve the old value for use in an After Group box. Global If you wish to print grand totals for a report at the end, you need to use a separate box to define the text and functions you wish to see. Choose the Global command. The Form Designer creates a pair of boxes, the Before Report and After Report boxes. Before Report Add any text that you want to print as a once only title for the report. If you want the title page to be printed separately, choose Page before Report in the Report Generator Options requester. After Report To create totals, etc., use the techniques described above in Subtotals and Other Report Functions. If you want the totals page to be printed separately, choose Page before Totals in the Report Generator Options requester. Options The Options command on the Report Generator menu allows you to specify paging and summarization details. *** See Fig 42-2 *** Page on Record You may want to force your report to start a new page for each record. If so, click this item so that the box is checked before you click OK. Page on Group You may want to force your report to change to a new page at the end of certain groups. For example, you might want to print the details of employees in each department separately. When you click this item, the Form Designer shows a list of existing groups. Select a group and click OK. The Form Designer places the word EJECT in the top line of the relevant AFTER GROUP box. This command works as a toggle, so to switch it off go through the actions again for the same group. Page before Report If you want to print a separate title page, define the title text in a Before Report box, and click this option. Page before Totals If you want to print a separate totals page, define the contents of the After Report box, and click this option. Summarize If you want your report to omit the details in the Select box, usually in order to obtain the results of After Group or After Report without the full print out, select this option. It may be convenient for you to save two versions of the same report under different names, one with and one without the Summarize options selected. Test The Test command on the Report Generator menu enables you to test a report form on the printer before you save it. Selecting this command gives you a preview of what the output will look like when the report form is used in SBase. If you find that is not as you planned, you can then go back and edit the form. The preview shows how the report form will structure and lay out fields and text over one page. It restricts itself to six lines of repeated fields, and uses the Field character (as set in the Print Options requester) in place of the field data. When the report is output from SBase, each page may contain data from many more than six records, so the preview may not look exactly the same; but it will show whether the fields and text are positioned correctly. Saving the Report Form When you have specified all the elements of the report form, save it in the usual way, using Project Save. The Form Designer generates the two files that the system will need to allow you to execute the report or load it again into the Form Designer for modification. - A new SBP report program is generated every time you save the SBV form file. If you have modified the program code with the SBase Program Editor, you should rename the program if you do not want it to be overwritten by the newly generated version. Formatting Report Output Calculations are especially useful for formatting text and numbers in the report output. Thus to format a numeric field, you can use the function STR$. The field should be included in a calculation formula instead of being added to the form as a separate object. For example, the calculation Balance$ could be used to format the numeric field Balance, using a formula like this: STR$(Balance,"$999999.99") Another example, which shows how text can be formatted, would be a formula such as: Firstname + " " + Lastname Here the formula ensures that first and last names are correctly spaced in the report output. Running a Report The Form Designer is used to create a report form. To run the report, you need to return to SBase. Report forms are different from other forms. When you open a report form from within SBase what you see is not the form but its output, either on the screen or the printer. This is because a report form is only a way of generating a report program. Opening the report form causes SBase to execute the program. There are two ways of running a report: opening the report form, or executing the report program. If you simply want the report to run without any further actions, choose the Project Open Form command, and then select the report you want. You are offered the option to run the report program automatically. The other choice is to treat the report like any other program. Select the DML Open command, then the name of the report program you want. It will be in the list of SBase programs in the current directory. Once the report program is loaded, you can either run it immediately, or use the program editor to examine or modify the report program code. To do the latter, select DML Edit. Adding Filter Conditions The Form Designer generates a WHERE line in the report program which contains the default relational information derived from the report form's internal structure. The default relational information is only generated if more than one file is used in the report form. It always takes the form: WHERE Field1.FILEA = Field2.FILEB ASK where the fields contain the information on which a join can be made, such as an account number held in both files. Access to the default filter is automatically provided when you run the report program by the ASK statement at the end of the line. When SBase executes this instruction, it displays the standard Query Filter Definition requester, as obtainable from the Process Query menu command. You may either click on OK, if you wish to make no changes, or add to the Filter Command Line by clicking on fields and entering values and functions to build up a more complex set of search conditions. It would be unwise to remove the relational join, as SBase may begin outputting extremely repetitive and confusing data, and continue for a very long time (the process won't harm your data). Click OK to call up the Order requester. Adding Order Conditions The report generator derives a default sorting order from the groups in the report form. When you run the report, SBase executes an ORDER line which includes the command ASK. This causes the presentation of the Query Order requester. You may add extra fields to the Order line, or change sorting order from Ascending to Descending for any particular field. Click OK to confirm your selections. Print/Display Option Before beginning output, the report program executes an instruction to select Screen or Printer as the output device. Indicate your preference and click on OK. The report program executes the rest of its instructions. Modifying a Report Program It is quite likely that you will want to make some changes to the way your report program executes. The main areas where changes are required are: - Specifying a fixed output device - Specifying fixed or extra default filter conditions - Specifying fixed or extra default order conditions Output Device The output device is selected with the line: REQUEST "REPORT TO PRINTER?","",1,A% : IF A% THEN PRINT; To fix the output device as the printer, remove this line, and add the statement TO PRINTER to the end of the report program (before the END SELECT statement if there is one). To fix the output device as the screen, remove the whole line: the default output device is the screen. To fix the output device as a disk file with the name `tempfile,' remove the line as above, and add TO FILE "tempfile" to the end of the report program (before the END SELECT statement if there is one). Filter and Order You may want to extend the range of the default commands, while keeping the ability to add different parameters each time you run the report. Edit the report program. Add any conditions you want to the WHERE line, removing the ASK command only if you don't want the command to add conditions at runtime. Do the same for the ORDER line to change or extend the sorting parameters for the report.