Dense Data or No Data

Dense Data or No Data

By Anthony Alsford

By default WebFOCUS only displays data that exists within the returned dataset. However, there are occasions when the end user requires that the end report shows all possible data values within a grouping, irrespective of whether they exist – i.e., show dense data rather than no data.

This article will discuss how to produce such a report and work through the requirements to the ultimate solution.

Requirement: Produce a report grouping Car Models within Country and Bodytype.

• All countries must be included.
• All bodytypes must be shown for each country whether they exist or not.

Of course, WebFOCUS can handle this easily by forcing ROWS within a BY phrase or COLUMNS within an ACROSS phrase. The code that is required to produce this could be:

SET NODATA=NONE
TABLE FILE CAR
  SUM CAR
   BY COUNTRY
   BY BODYTYPE
      ROWS 'CONVERTIBLE' OVER 'HARDTOP' OVER 'SEDAN' OVER 'ROADSTER'
   BY CAR NOPRINT
END

(Thanks to Francis Mariani.)

However, even though this provides the required end result, it is rather restrictive and a possible maintenance overhead.

Take the situation when a new value is entered into the data for a bodytype of 'CABRIOLET' or the fact that 'COUPE' is missing from the forced list even though it already exists within the data. Each of these situations will require that the code is changed, together with any change-control requests that might be necessary!

It would be more productive if the code was written to cope with expanding data values, thereby "future-proofing" the code. All that would be required is to extract the data values and use them to build the 'ROWS' syntax dynamically.

By using Dialogue Manager within WebFOCUS, this becomes an easy process.

First we require all the current values:

TABLE FILE CAR
BY BODYTYPE
ON TABLE SAVE AS BODYTYPES FORMAT ALPHA
END
-RUN

We use SAVE instead of HOLD, as we do not require the master file description to be generated.

Next, we read all the values and construct our 'ROWS' syntax. Remember basic file-handling techniques using read ahead.

1. -SET &Rows = 'ROWS ';
2. -READ BODYTYPES, NOCLOSE &Bodytype
3. -REPEAT Read_It WHILE (&IORETURN EQ 0);
4. -SET &Rows = &Rows | '''&Bodytype.EVAL''';
5. -READ BODYTYPES, NOCLOSE &Bodytype
6. -SET &Rows = IF &IORETURN EQ 0 THEN &Rows | ' OVER ' ELSE &Rows;
7. -Read_It
8. -RUN

An explanation of the process follows:

1. Initialize a variable, &Rows, that we will use to construct our ROW statement.
2. Read the saved file in a read ahead operation to prime the Bodytype and IORETURN (system) variables.
3. Initiate the read loop and make it dependant upon a successful read operation (&IORETURN EQ 0).
4. Build the &Row variable with each successive Bodytype variable value.
5. Reread the saved values for the next value in our list.
6. The important piece here is that is we have a successful read. Then we have a further value to add and, therefore, we will require the "AND" syntax joiner. If &IORETURN doesn't equal 0, then we have reached the end of the saved values and we do not require the "AND" syntax.
7. This line marks the end of the loop sequence.

Items to note about this Dialogue Manager code:

	The value for the system variable, &IORETURN, will remain at zero while a read has been successful.
	The comma after the filename (BODYTYPES) negates the need to specify variable length or predeclaring a variable value.
	The NOCLOSE option keeps the status of the file open and read pointers intact between read operations.

All that is needed now is to plug our newly formed variable into our report code:

SET NODATA=NONE
TABLE FILE CAR
  SUM CAR
   BY COUNTRY
   BY BODYTYPE
&Rows
   BY CAR NOPRINT
END

To produce the final report:

You can see the method of building the &Rows variable by viewing the 'ECHO'ed source:

TABLE FILE CAR
   BY BODYTYPE
   ON TABLE SAVE AS BODYTYPES FORMAT ALPHA
   END
   -RUN
  0 NUMBER OF RECORDS IN TABLE=       18  LINES=      5
    
    
    ALPHANUMERIC RECORD NAMED  BODYTYPE
  0 FIELDNAME                         ALIAS         FORMAT        LENGTH
    
    BODYTYPE                          TYPE          A12            12
    BODYTYPE                          TYPE          A12            12
    
    TOTAL                                                          24
   -SET &Rows = 'ROWS ';
   -READ BODYTYPES, NOCLOSE &Bodytype
   -      &Read_It=      (       0 EQ 0);
   -SET &Rows = ROWS  | '''' || CONVERTIBLE || '''';
   -READ BODYTYPES, NOCLOSE &Bodytype 
   -SET &Rows = IF        0 EQ 0 THEN ROWS 'CONVERTIBLE' | ' OVER ' ELSE ROWS 
   'CONVERTIBLE';
   -      &Read_It=      (       0 EQ 0);
   -SET &Rows = ROWS 'CONVERTIBLE' OVER  | '''' || COUPE || '''';
   -READ BODYTYPES, NOCLOSE &Bodytype 
   -SET &Rows = IF        0 EQ 0 THEN ROWS 'CONVERTIBLE' OVER 'COUPE' | ' OVER ' 
   ELSE ROWS 'CONVERTIBLE' OVER 'COUPE';
   -      &Read_It=      (       0 EQ 0);
   -SET &Rows = ROWS 'CONVERTIBLE' OVER 'COUPE' OVER  | '''' || HARDTOP || '''';
   -READ BODYTYPES, NOCLOSE &Bodytype 
   -SET &Rows = IF        0 EQ 0 THEN ROWS 'CONVERTIBLE' OVER 'COUPE' OVER 
   'HARDTOP' | ' OVER ' ELSE ROWS 'CONVERTIBLE' OVER 'COUPE' OVER 'HARDTOP';
   -      &Read_It=      (       0 EQ 0);
   -SET &Rows = ROWS 'CONVERTIBLE' OVER 'COUPE' OVER 'HARDTOP' OVER  | '''' || 
   ROADSTER || '''';
   -READ BODYTYPES, NOCLOSE &Bodytype 
   -SET &Rows = IF        0 EQ 0 THEN ROWS 'CONVERTIBLE' OVER 'COUPE' OVER 'HARDTOP' 
   OVER 'ROADSTER' | ' OVER ' ELSE ROWS 'CONVERTIBLE' OVER 'COUPE' OVER 'HARDTOP'
    OVER 'ROADSTER';
   -      &Read_It=      (       0 EQ 0);
   -SET &Rows = ROWS 'CONVERTIBLE' OVER 'COUPE' OVER 'HARDTOP' OVER 'ROADSTER' OVER  
   | '''' || SEDAN || '''';
   -READ BODYTYPES, NOCLOSE &Bodytype 
   -SET &Rows = IF        1 EQ 0 THEN ROWS 'CONVERTIBLE' OVER 'COUPE' OVER 'HARDTOP' 
   OVER 'ROADSTER' OVER 'SEDAN' | ' OVER ' ELSE ROWS 'CONVERTIBLE' OVER 'COUPE' 
   OVER 'HARDTOP' OVER 'ROADSTER' OVER 'SEDAN';
   -      &Read_It=      (       1 EQ 0);
   -Read_It
   -RUN
   TABLE FILE CAR
   SUM CAR
   BY COUNTRY
   BY BODYTYPE
   ROWS 'CONVERTIBLE' OVER 'COUPE' OVER 'HARDTOP' OVER 'ROADSTER' OVER 'SEDAN'
   BY CAR NOPRINT
   END
   -EXIT
  0 NUMBER OF RECORDS IN TABLE=       18  LINES=     13

Anthony has been working as a contractor in the UK market for 12 years but has been developing in WebFOCUS (formerly FOCUS) for 20 years. He has supplied services to the London office of Information Builders in both the technical support and professional services departments.