UNIT 6

Formatting Your Reports

Without the Report Designer, SQL/PRO only creates a simple listing using a default report format, which prints the values of the columns of the SELECT statement in the order they occur in the statement. The column names are used as heading constants above each column. This is the extent of the default report format.

Now with SQL/PRO' report Designer you can quickly and easily create sophisticated reports. The SQL/PRO Report Designer allows you to format the results of any SQL SELECT statement. You can also create summary information and perform several arithmetic operations. The Report Designer supports most of the requirements for a standard production report:

With some advance techniques you can produce reports that are much more sophisticated than you might imagine. For example:

When a query is saved the report formatting for the query is also saved.

The following sample report gives you an idea of what can be done with SQL/PRO report formatting. We will lead you through each step of the format process that would create this report so you can get an overview of how it's done. The format function is identified by the labels to the right of the report. As we go through each of the functions in detail, refer to this report.

To begin designing a report, select F22 (Edit report format) from the main SQL/PRO screen and you will be transferred to the Report Designer main screen where you can specify your report functions and format. Initially SQL/PRO will create a default form for you. A report format record will be created for each column specified in your SELECT statement. If you use the '*' in your SELECT statement, a record will be created for every column in the table. On subsequent requests for report format editing (F22), within the same session, SQL/PRO will present a window informing you that a form already exists. Online help is available from anywhere within the Report Designer. Press the help key or F1 to access the online help. The online help is cursor sensitive, so position your cursor on the phrase in question before accessing help.

You can choose to change the existing form or re-create a new default form. Typically you will change an existing form unless you change the file from which you are retrieving your information in which case you would want to recreate a default form.

The Edit report format function presents the Report Designer panel.

In this example we are creating a control group for District and summarizing the total order amount for each group.

To add a column use F6. A blank column will be inserted after the column in which the cursor is located. To delete a column use F14. The column in which the cursor is located will be deleted. There must be at least one column in a report format (the system will not let you delete a column if it is the last one in the report format).

F7 displays the Design Report Header window and allows you to enter up to five lines of header text. You can see the prompt at the top of the window requesting for the number of lines to skip before and after the header text prints. If these are left blank the header text will simply print on line one. For every text line entered, a "Justify" option of LEFT, CENTER or, RIGHT must be selected to specify the text alignment on the page.

F8 presents the Design Report Footer screen. This screen is exactly the same as the report header except that the text lines entered will print at the bottom of the report. Here, we are placing the name of the saved query.

To create control group header text use F10. You will be prompted for break header text for each column with a control break specified in the USASGE column (a maximum of six groups can be created, BREAK1 to BREAK6). Again you are prompted for the number of blank lines to skip before and after the text prints, but you also have two new options: Print on new page and repeat column text. These both default to NO but entering YES in the first option will cause a page break and a YES in the second will reprint the column heading text immediately following the break header. Typically, if YES is specified in Print on new page you would leave the repeat column text at NO since the column text is printed automatically on every new page anyway.

Take particular notice of the '&1' in the break text. The ampersand followed by a number tells the report format to print the value of the nth data column from the SQL SELECT statement. In this case the first data column contains the break field DISTRICT. Any number of data columns may be inserted in this way and is not limited to the break fields.

F11, like F10, will prompt you for footer text for each break field. Again, the example has an embedded variable, '&1', to pull in the DISTRICT. The justification LEFT tells the report format to print the break footer text to the immediate left of the first summary column. RIGHT, then, will print to the right of the last summary column.

The report final text (F9), again, works essentially the same as the other report sections except that the final text allows for ten lines of text instead of the header and footer limitation of five. Function key 22 brings up the Modify Standard Report Defaults screen. You will rarely use this screen if you are happy with the defaults specified. Review the previous report example to see what the standard defaults look like.

Now that you know how to create report headers and footers, let's go over the formatting of the detail data. The main screen has a record for each column in the SQL SELECT statement. Most of the editing can be done from the main report designer screen except that the column header text entry field allows for only 27 characters to be entered. Whenever a column heading exceeds 27 characters or is to list on multiple lines of text, use the Extended Column Edit screen accessible with a 2 option.

The extended column edit allows you to enter up to four lines of column heading per data column. There is, however, a limitation of a total of 62 characters per column heading.

The Usage edit is the real power option to the report designer. Here is where you specify the break fields (which should correspond tot he ORDER BY fields on the SQL SELECT statement) and the summary functions. The usage options are:

OMIT, AVG, COUNT, FIRST, LAST, MAX, MIN, SUM, BREAK1 - BREAK6

Most of these are obvious and there is help text available if you position the cursor in the usage field and press F1 or HELP. I will however mention two of the more obscure options: OMIT and COUNT. Omit simply specifies that a column is not to appear on the report. COUNT lists the total number of detail lines that were a part of the summary data group break. Notice also that only one usage can be specified per column.

The edit code is where you definitely will use the F1 Help function. These SQL edit codes are not the same ones that AS/400 programmers are familiar with from SDA, RPG, and Query/400, but the help text is very straightforward. The edit codes include numeric, date, time and several interesting character edits.

The field length and decimal position represents the width to display on the report - it does not have to correspond to the actual physical data length in the file. If a numeric value exceeds the width specified, the report will display all asterisks in that data column.

The column indent is the number of spaces from one data column to the previous data column.

The column sequence allows you to override the placement of a data column on a report. You may have keyed a long SQL SELECT statement and no longer want the report column sequence to default to the column placement from the SQL. With the sequence number you can easily move columns on a report without changing the SQL statement.

The data type is set on the automatic build of the form but on column adds (F6) you need to specify the appropriate type. Don't worry about whether not the field is packed or zoned - the report designer sees all numbers as NUMERIC. Version 2 enthusiasts will also be happy to know that DATE and TIME data types are available with SQL/PRO' Report Designer.

The extended column edit screen will automatically be displayed when you select the add function (F6). Remember: The number and data type of report format columns must always agree with that of the SQL SELECT statement. This is where the Display SQL Function (F3) comes in handy. Again, if you add or delete a column from the SQL statement you must also add (F6) or delete (F14) from the report form.

Advanced Report Formatting Techniques

Multi-level reports

 Many databases have files with one-to-many relationships. An example of a one-to-many relationship is a sales order header and sales order detail. Formatting a report that joins two such files is a problem since the header information is duplicated for every detail record. Forget trying to create a clean report of this nature with Query/400, but with SQL/PRO's Report Designer and a few simple techniques it can be fairly easy. Using a Sales Order Picklist as an example the following steps describe how to create a multi-level report. On the SQL SELECT:

On the SQL SELECT:

  1. Duplicate the break fields.

  2. Concatenate a blank to the beginning of each break field. If the break is numeric use the SQL digits() function to convert it to character.

SELECT ORDNBR, ' ' ||ORDNBR, ORDDTE, LINITM, LINQTY, LINPRC, LINQTY *
LINPRC
     FROM ORDHDR, ORDDTL
     WHERE ORDHDR.ORDNBR = ORDDTL.ORDNBR
     ORDER BY ORDNBR

On the Report Design:

  1. Specify the concatenated ORDER BY duplicate as the break field and set the column width of the break field to 1 so that only the blank portion of the break will list on the report (you can't omit a break column)

  2. Set USAGE to OMIT for all other header fields

  3. Design a Break header with the header columns specified to print in the header.

Date Edits

 Dates are a bit of a problem with Report Designer. We do not have the old six digit numeric date edit of Y available to produce a formatted date. But there are two other methods of editing a date:

Method One

The first way to edit a date is to substring it into its individual month, day, and year fields in the SQL SELECT statement and concatenate. For instance, to edit a year-month-day date to MM/DD/YY use:

substr (datefld, 3,2) | |'/'| |substr (datefld, 5, 2) | |'/| |substr (datefld, 1,2)

Method Two

Another way is to use the date edits provided with the Report designer. These edits, through, only work with the new OS/400 Version 2 date types. But you don't have to wait for date and time fields to be in your database. What you do is convert your old format date into a Version 2 date field by combining Method One to create a date that matches your system date format and then enclose the manually edited date with the SQL DATE () function. Then select any of myriad of date edits available in the Report Designer (see the F1 field text on the main screen).

Why go to all this trouble since it uses Method One anyway? By converting your database dates into Version 2 date data type you can then use date math capabilities. This makes calculating such things as the number of days between dates and the maturation of notes a breeze.

Address Formatting

 A neat trick that the Report Designer will do for you is to format an address. How often have you done lists where a full address was required? But two or three address lines; a name; and the city, state, and zip code takes up too many columns on the report. This leaves too few columns available on the report to print anything else. How would you like to print something like this?:

This technique requires the following steps:

On the SQL SELECT

  1. Find the longest address line. For example: address line one may be 40 characters but the City, State, and Zip might exceed 50 with the embedded blanks.

  2. In the SQL concatenate all fields together adding blank strings so each address line is the length of the longest line.

For instance, if the name, address line 1, and address line 2 are 40 bytes and the city/state/zip combination is 50 including the concatenated comma and blanks:

Select name ||' '||
              ad1||' '||
              ad2||' '||
              strip (city) ||','||state||' '||zip,
              purch, amtpaid, amtdue

(Notice the column function we use with the city: strip(city). This strips the trailing blanks from the city.)

On the Report Format

  1. Change the column heading for the concatenated column to something meaningful since by default it will contain concatenation characters. Here, we change it to Customer address.

  2. Set the field width to longest field address line (50 in the example).

  3. Specify the CT character edit code to concatenated address.

The CT character edit will then wrap the columns to the next line of the report when the column width has been exceeded (50 in the example).

Standard Headers

 The Report Designer defaults to automatically printing the current date, time, and page number on the bottom of the report. This works quite well but many users are so used to having the date, time and page at the top of the report that they now expect it. To do this with the Report Designer is easy; simply enter &DATE, &TIME and &PAGE (all caps) in the report header. The problem with the report formatter though, is that the justify option prints to the left, right, or the center of the report. Users want the date, time, and page at opposite ends of the report. So - fake it. Put the &DATE and &TIME on the first line of the report header left justified. Then put the &PAGE on the second line of the report right justified. Finally, put your report title on line three and center it.

Remember, through, if you use &DATE, &TIME and &PAGE on a report for a saved query and later run the query with the STRQMQRY command, you could end up with date, time and/or page on the report twice (wherever you place it and at the bottom of each page). This is because the STRQMQRY command will place the date, time, and page number at the bottom of every page of any query report if the DATETIME and PAGE parameter values are set to *YES (the default).

Dynamic Reports

 One of the most powerful features of SQL/PRO is its support of variables allowing us to create dynamic reports. All that is required is to create an SQL SELECT statement that has variable selection criterion in the WHERE clause.

SELECT CUSNBR, NAME, ADDRESS
    FROM CUSMAS
    WHERE &WHERE

Any word prefixed by an ampersand is seen as a variable to SQL/PRO. The Variable is then set at run time in the STRQMQRY command by keying the variable name in the VAR parameter (make sure to use the same case as is specified in the SQL but without the ampersand) and the dynamic selection in the VALUE parameter.

Note that triple quotes are required for character strings; numeric data should have NO quotes or else the system will see the value as a string.

STRQMQRY QMQRY (YOURQRY) QMFORM (YOURQRY) VAR (WHERE) +
        VALUE ('''AMTDUE > 100.00 ''')

This powerful feature allows you to create reports where the user can dynamically set the selection criterion. But the report header should contain the selection criterion or else the user will not know what it is. The solution to this problem is to list the selection criterion on the report by putting the variable on the report header or footer. To do this you need to add the variable to the column list of the SQL SELECT since the Report Designer only supports variable data from the SELECT list.

SELECT CUSNBR, NAME, ADDRESS, PHONE
    FROM CUSMAS
    WHERE &PHONE

Then put that data column in the report header or footer and OMIT in the USAGE column in the detail data. The dynamic selection will then always print on the report. You can make slick use of this technique by coding a command or HLL screen with a wide range of prompts to assist the user through the entry of the selection logic.