Applying Selection Criteria to the Internal Matrix Prior to COMPUTE Processing

How to:

Reference:

WHERE TOTAL tests are applied to the rows of the internal matrix after COMPUTE calculations are processed in the output phase of the report. WHERE_GROUPED tests are applied to the internal matrix values prior to COMPUTE calculations. The processing then continues with COMPUTE calculations, and then WHERE TOTAL tests. This allows the developer to control the evaluation, and is particularly useful in recursive calculations.

Syntax: How to Apply WHERE_GROUPED Selection Criteria 

WHERE_GROUPED expression

where:

expression

Is an expression that does not refer to more than one row in the internal matrix. For example, it cannot use the LAST operator to refer to or retrieve a value from a prior record.

Example: Using a WHERE_GROUPED Test

The following request has two COMPUTE commands. The first COMPUTE checks to see if the business region value has changed, incrementing a counter if it has. This allows us to sequence the records in the matrix. The second COMPUTE creates a rolling total of the days delayed within the business region. 

TABLE FILE WF_RETAIL_LITE
SUM  DAYSDELAYED AS DAYS
COMPUTE CTR/I3 = IF BUSINESS_REGION EQ LAST BUSINESS_REGION THEN CTR+1 ELSE 1;
COMPUTE NEWDAYS = IF BUSINESS_REGION EQ LAST BUSINESS_REGION THEN NEWDAYS+DAYSDELAYED ELSE DAYSDELAYED;
BY BUSINESS_REGION AS Region
BY TIME_MTH
WHERE BUSINESS_REGION NE 'Oceania'
ON TABLE SET PAGE NOPAGE
END

The output is shown in the following image.

The following version of the request adds a WHERE TOTAL test to select only those months where DAYSDELAYED exceeded 200 days. 

TABLE FILE WF_RETAIL_LITE
SUM  DAYSDELAYED AS DAYS
COMPUTE CTR/I3 = IF BUSINESS_REGION EQ LAST BUSINESS_REGION THEN CTR+1 ELSE 1;
COMPUTE NEWDAYS= IF BUSINESS_REGION EQ LAST BUSINESS_REGION THEN NEWDAYS+DAYSDELAYED ELSE DAYSDELAYED;
BY BUSINESS_REGION AS Region
BY TIME_MTH
WHERE BUSINESS_REGION NE 'Oceania'
WHERE TOTAL DAYSDELAYED GT 200
ON TABLE SET PAGE NOPAGE
END

The output is shown in the following image. The COMPUTE calculations for CTR and NEWDAYS were processed prior to eliminating the rows in which TOTAL DAYSDELAYED were 200 or less, so their values are the same as in the original output. This does not correctly reflect the sequence of records and the rolling total of the values that are actually displayed on the output. To do this, we need to select the appropriate months (DAYSDELAYED GT 200) before the COMPUTE expressions are evaluated. This requires WHERE_GROUPED.

The following version of the request replaces the WHERE TOTAL test with a WHERE_GROUPED test. 

TABLE FILE WF_RETAIL_LITE
SUM  DAYSDELAYED AS DAYS
COMPUTE CTR/I3 = IF BUSINESS_REGION EQ LAST BUSINESS_REGION THEN CTR+1 ELSE 1;
COMPUTE NEWDAYS= IF BUSINESS_REGION EQ LAST BUSINESS_REGION THEN NEWDAYS+DAYSDELAYED ELSE DAYSDELAYED;
BY BUSINESS_REGION AS Region
BY TIME_MTH
WHERE BUSINESS_REGION NE 'Oceania'
WHERE_GROUPED DAYSDELAYED GT 200
ON TABLE SET PAGE NOPAGE
END

The output is shown in the following image. The COMPUTE calculation for NEWDAYS was processed after eliminating the rows in which TOTAL DAYSDELAYED were 200 or less, so its values are based on fewer rows than the calculations in the original request. This is verified by the CTR values, which are now in a continuous sequence. The rolling total now reflects the values that are actually displayed on the report output.

Reference: Usage Notes for WHERE_GROUPED

  • If the expression refers to multiple rows in the internal matrix, the following message is generated and processing stops.
    (FOC32692) WHERE_GROUPED CANNOT REFER TO OTHER LINES OF REPORT
  • A COMPUTE that does not reference multiple lines will be evaluated prior to WHERE_GROUPED tests, and may, therefore, be used in an expression and evaluated as part of a WHERE_GROUPED test.
  • WHERE_GROUPED can be optimized for SQL data sources by creating a GROUP BY fieldname HAVING expression clause, where the expression is the WHERE_GROUPED selection criteria.