Expanding WebFOCUS Report Output
By Art Greenhaus
How often have you wished you could display aggregate records and allow them to be selectively expanded to display details? This would allow “high-level” views and support examining individual records as well. Expansion, either of columns or rows, would make finding “exceptions” that much easier, wouldn't it?
Starting with release 7.1.3, WebFOCUS has supported the concept of expanding a report output, using the '+' symbol to indicate that additional report information has been hidden, but can be displayed by clicking on the '+'. The feature SET EXPANDABLE=ON allows a report to expand or contract, by hiding or displaying columns.
An example of this feature might look like the following:
SET EXPANDABLE=ON
TABLE FILE CAR
SUM SEATS
BY COUNTRY BY CAR BY MODEL
END
An initial output would look like this:
Which could selectively expand, displaying additional columns, when the '+' is clicked, like this:
Note that after the '+' is clicked, it changes to a '-', allowing the columns to again be hidden.
Windows users may find this a bit confusing, as the Windows convention (as can be seen when running Windows Explorer), is to expand rows (not columns), when the '+' is clicked.
This type of expansion has also been implemented, effective with release 7.7.03. This feature is invoked by:
SET EXPANDBYROW={ON|OFF|n}
When set to ON, output will display the initial sort field only, allowing expansion for further details. It can also be set to a number, indicating the number of sort fields to initially display (ON is synonymous with 1).
Using the prior example, with this new SET:
SET EXPANDBYROW=ON
TABLE FILE CAR
SUM SEATS
BY COUNTRY BY CAR BY MODEL
END
Results in an initial display of:
Which can be selectively expanded, displaying additional rows, when the '+' is clicked, like this:
To implement this feature, what would normally be thought of as SUBTOTALs, are displayed prior to the detail. Internally, this is done by SET SUBTOTAL=ABOVE. The DEFAULT functionality is to add a SUBTOTAL at each sort field, except the last. No SUBTOTAL is added to the lowest sort field, as the current implementation only supports SUM or WRITE, and the SUBTOTAL at the lowest sort field would only include and equal that single record. (This limitation will be lifted in the 7.7.04 release, which will support PRINT.)
SUBTOTALing verb objects is acceptable, if there are no COMPUTEs. If COMPUTEs are present, then – again by default – the COMPUTEd values will be sub-totaled. If, instead, the user needs to do a recalculation based on subtotal values, SUMMARIZE can be used at the appropriate level, by EXPLICIT coding. However, once the user overrides the default totaling, it is the user's responsibility to ensure that a “subtotal” row is created for every sort field.
The reason for SUMMARIZE, rather than RECOMPUTE, is that SUMMARIZE will propagate to all higher sort fields. Since the SUBTOTALs are used to allow expansion, every sort field must have a SUBTOTAL available. RECOMPUTE can be used, but it is then the user’s responsibility to ensure all higher sort fields have either a sub-total (SUBTOTAL or SUB-TOTAL) or recalculation (RECOMPUTE or SUMMARIZE) performed.
As an example, assume the following procedure is to be run:
SET EXPANDBYROW=ON
TABLE FILE CAR
SUM RCOST DCOST COMPUTE PROFIT_PCT = 100 * (RCOST-DCOST)/DCOST ;
BY COUNTRY
BY CAR
BY MODEL
END
When executed, all the defaults are applied, and the result is the following:
Obviously, a profit percentage of 78.36 is suspect. Expanding the report shows what happened:
The COMPUTEd field was sub-totaled, rather than being recalculated.
By changing the request, as follows, we get the correct recalculation:
SET EXPANDBYROW=ON
TABLE FILE CAR
SUM RCOST DCOST COMPUTE PROFIT_PCT = 100 * (RCOST-DCOST)/DCOST ;
BY COUNTRY
BY CAR SUMMARIZE
BY MODEL
END
Producing:
In conclusion, see what happens when the SUMMARIZE is placed on the lowest level sort field (in the above case on MODEL); it becomes:
SET EXPANDBYROW=ON
TABLE FILE CAR
SUM RCOST DCOST COMPUTE PROFIT_PCT = 100 * (RCOST-DCOST)/DCOST ;
BY COUNTRY
BY CAR
BY MODEL SUMMARIZE
END
The output then becomes the following:
Note the duplication of rows. By taking off the SET EXPANDBYROW=ON, the reason becomes obvious:
Without expansion, we see that the subtotal, indicated by '* TOTAL' for the lowest-level sort field (MODEL), is the same as the prior row of detail, because only one row of detail is produced. This is the reason that no SUBTOTAL is inserted (by default) for the lowest sort field.
