Migrating Progress OpenEdge ABL Reports (Paged OUTPUT) to Crystal Reports

Progress OpenEdge allows you writing simple reports, and provides basic reporting features such as paging, page headers, footers and grouping that are implemented as a part of ABL language statements and their options.

Reports in ABL

There are several ABL statements and options that simplify writing reports in a typical Progress ABL procedure (.p file):

OUTPUT TO Statement

OUTPUT statement specifies where to send the application output, and you can specify PRINTER or a paged file:

   OUTPUT TO PRINTER.
   OUTPUT TO c:\reports\sales.rep PAGED.

Page Header and Footer

You can define a frame with PAGE-TOP or PAGE-BOTTOM options, and when this frame is active, its content is displayed as the page header or footer.

   /* Frame definition */
   DEFINE FRAME hdr
   HEADER
     "Country Analysis Report" AT 15
     "Page:" AT 60
     PAGE-NUMBER AT 65 SKIP(1)
        WITH PAGE-TOP FRAME hdr. 
 
   /* Activate frame, it will be shown on every new page */
  VIEW FRAME hdr.

BREAK BY Clause, FIRST-OF and LAST-OF Functions

BREAK BY clause specifies a grouping column(s) for the query results, and sorts resulting rows according to these columns.

BREAK BY is more similar to SQL ORDER BY rather than GROUP BY clause.

BREAK BY does not perform aggregation, it returns all rows in sorted order. What makes it different from ORDER BY is that Progress ABL provides functions FIRST-OF and LAST-OF that can help you indicate the start and end of each group, and this feature is very useful for showing groupings in reports.

   FOR EACH sales BREAK BY product-name:
 
     IF FIRST-OF(product-name) THEN
     DO:
       PUT product-name SKIP(1).
     END.
 
     PUT sales-rep.
     PUT sales-qty.
     PUT sales-date.
   END.

In this example, we output all rows from sales table, but the product name is displayed once per product.

PAGE Statement

PAGE statement starts a new output page for PAGED output.

Note. When the paged output is sent to the file, Progress does not format the content of the file to fit pages, instead, it writes the ASCII control character 12 (0xC in hexadecimal) that specifies a page break. When you print the file, you will have the pages as expected.

PAGE-NUMBER Function

PAGE-NUMBER function returns the current page number of the output.

Sample ABL Report (.p Procedure)

In Progress, a report is a typical ABL procedure using specific statements and options.

Assume there is a table cities with the following character columns and data:

city	country	continent
Boston	United States	North America
London	United Kingdom	Europe
Paris	France	Europe

Let's create the following report:

Show header with title, date and page number
For each continent, output its title and list of cities, states from the cities table
After each continent, output the total number of countries
Start each new continent of a new page

    /* Variable to calculate the number of countries */
    DEF VAR qty AS INT INITIAL 0.
 
   /* Header for every new page */
   DEFINE FRAME hdr
   HEADER
     "Country Analysis Report" AT 15
     "Date: " AT 60
      TODAY AT 66 SKIP
      "Page:" AT 60
      PAGE-NUMBER AT 65 SKIP(1)
        WITH PAGE-TOP FRAME hdr. 
 
    OUTPUT TO c:\temp\cities.rep PAGED.
 
    /* Activate report header */
    VIEW FRAME hdr.
 
    FOR EACH cities BREAK BY continent:
    IF FIRST-OF(continent) THEN
    DO:
       PUT continent FORMAT "x(20)" AT 1 SKIP. 
       qty = 0.
    END.
 
    PUT city FORMAT "x(20)" AT 25.
    PUT country FORMAT "x(20)" AT 50.
    qty = qty + 1.
 
    IF LAST-OF(continent) THEN
    DO:
     PUT SKIP(1).
     PUT "Total Number of Countries: " FORMAT "x(28)".
     PUT qty SKIP(1).
     PAGE.
    END.
   END.

When you execute the procedure, you will have the following report in c:\temp\cities.rep file:

Page 1:

              Country Analysis Report                      Date: 09/30/11
                                                                         Page:         1

    Europe              
                        London                   United Kingdom       
                        Paris                      France              

   Total Number of Countries:           2

Page 2:

              Country Analysis Report                      Date: 09/30/11
                                                                         Page:         2


    North America       
                        Boston                   United States                  

   Total Number of Countries:           1

ABL Reports in Real Applications - Typical Procedure Structure

The example above is very simple, it just demonstrates ABL features for reports. In real applications reports are more advanced and typically have the following structure:

Report Parameters

Report (.p procedure) typically displays a window to request the report parameters from user. For example, the user may need to specify start and end date, output type (printer, file i.e.).

Preparing Data

Once parameters are set, there may be some procedural logic (loops, conditions, calculations etc.) to prepare the report data. Data can be selected from different tables, processed and written to a temporary table as the input data for the report (raw input data, not report calculations such as totals and subtotals).

Report Calculations and Output

Although ABL language provides BREAK BY clause, FIRST-OF and LAST-OF functions to facilitate grouping in report, you still have to do calculations for totals and subtotals in the code.

Crystal Reports

Crystal Reports stores report definitions in binary .rpt files that can be created and modified in Crystal Reports tools, Microsoft Visual Studio or programmatically using Crystal Reports SDK.

Each Crystal report contains the following sections:

Report Header for information displayed once at the beginning of the report.

This section is usually used for the report title, company logo, report date and time etc.

Page Header for information displayed at the beginning of each new page. This section is usually used show column names on each page (for multi-page table output), page number, report date etc.

Group Header (if group, summary or subtotal is defined) for information displayed at the beginning of each new group. This section is usually contains the name of the group.

Details for information displayed for each new record. This section contains the main report data.

Group Footer (if group, summary or subtotal is defined) for information displayed at the end of each group. This section is usually contains the summary information for the group.

Page Footer for information displayed at the bottom of each page.

Report Footer for information displayed at the end of the report. This section is usually contains summary, grand totals for the entire report.

Report Items in Crystal Reports

In Crystal Reports, you can insert various items into the report:

Special Fields such as Page Number, Print Date, Total Page Count etc.
Text Objects to add titles, labels, descriptions etc.

Cross Tabs to represent data in rows and columns (matrix)

Formulas to display calculated values
Images to add the company logo i.e.
Charts and Maps to visualize information

Converting Sample ABL Report to Crystal Reports

Let's implement the sample ABL report described above in Crystal Reports.

Page Header

In Progress ABL code, we had a frame defined with PAGE-TOP attribute that means that the content of this frame is displayed on each page.

In Crystal Reports we need to put this information in the Page Header section, and we will use Special Fields PageNumber and PrintDate.

Progress ABL code to define the Page Header:

   DEFINE FRAME hdr
   HEADER
     "Country Analysis Report" AT 15
     "Date: " AT 60
      TODAY AT 66 SKIP
      "Page:" AT 60
      PAGE-NUMBER AT 65 SKIP(1)
        WITH PAGE-TOP FRAME hdr.

In Crystal Report (Design View):

Report Destination

In ABL, we used OUTPUT TO statement to send the report content to file or printer. In Crystal Reports Viewer you can specify to print the report, or choose various Export options including .RPT (Crystal Reports file with data), PDF, CSV, Excel, Word etc.

Define Data

In ABL code, we used data from cities table and performed grouping by continent field:

    FOR EACH cities BREAK BY continent:
    ...
    END.

In Crystal Reports, we need to perform the following actions:

Create a new data source and select table cities
Create a group based on continent column

Group Header

Once we created a grouping based on continent column, Crystal Reports automatically inserts Group Header and Group Footer sections to the report.

We will put Database Field continent (group value) into Group Header, so it is displayed once before all rows with the same continent.

This is equivalent to ABL code that checks the start of the new group using FIRST-OF function:

   IF FIRST-OF(continent) THEN
    DO:
       PUT continent FORMAT "x(20)" AT 1 SKIP. 
       qty = 0.
    END.

Group Footer

In the Group Footer we will place the total number of countries for the continent.

To do this, you can insert a summary (Insert → Summary):

You can also add a Running Total field that calculates the number of countries for each record and automatically resets its value when the group value changes:

This is equivalent to ABL code that checks the end of the new group using LAST-OF function (the number of countries is calculated in the main loop and reset when a new group starts):

   IF LAST-OF(continent) THEN
    DO:
     PUT SKIP(1).
     PUT "Total Number of Countries: " FORMAT "x(28)".
     PUT qty SKIP(1).
     PAGE.
   END.

Report Layout

As the result we will have the following layout for our report in Crystal Report (Design View):

Running Report

When we run the report, the following content is displayed:

Migration Resources

Progress OpenEdge White Papers, Articles and Reference