Sorting Columns

In this section:

How to:

Reference:

You can sort report information horizontally using the ACROSS phrase. This creates columns in your report. The total number of ACROSS columns is equal to the total number of ACROSS sort field values multiplied by the total number of display fields.

A request can include up to 128 sort phrases consisting of any combination of BY and ACROSS phrases.

The maximum number of display fields your report can contain is determined by a combination of factors. In general, if a horizontal (ACROSS) sort field contains many data values, you may exceed the allowed width for reports, or create a report that is difficult to read. For details, see Displaying Report Data.

You can produce column totals or summaries for ACROSS sort field values using ACROSS-TOTAL, SUBTOTAL, SUB-TOTAL, RECOMPUTE, and SUMMARIZE. For details, see Including Totals and Subtotals.

Syntax: How to Sort Columns 

ACROSS sortfield

where:

sortfield

Is the name of the sort field.

Reference: Usage Notes for Sorting Columns

  • You cannot use text fields as sort fields. Text fields are those described in the Master File with a FORMAT value of TX.
  • You can use a temporary field created by a DEFINE command, or by the DEFINE attribute in a Master File, as a sort field. However, you cannot use a temporary field created by a COMPUTE command as a sort field. You can accomplish this using the BY TOTAL phrase or indirectly by first creating a HOLD file that includes the field, and then reporting from the HOLD file. HOLD files are described in Saving and Reusing Your Report Output.
  • For an ACROSS phrase, the SET SPACES parameter controls the distance between ACROSS sets. For more information, see Laying Out the Report Page.
  • If you specify several sort fields when reporting from a multipath data source, all the sort fields must be in the same path.
  • In styled output formats (PDF, HTML, DHTML, PPT, PPTX, and XLSX), the width of ACROSS titles and ACROSS values above the data columns is defined as the largest width of all data columns, and associated column titles, within the ACROSS groups. To change the size of the ACROSS groups, apply SQUEEZE, WRAP, or WIDTH definitions to the data columns within each group.
  • Each sort field value is displayed only once in the report unless you change this default using the SET BYDISPLAY command. For example, if there are six employees in the MIS department, a report that declares
    PRINT LAST_NAME ACROSS DEPARTMENT

    prints MIS once, followed by six employee names.

Example: Sorting Columns With ACROSS

The following illustrates how to show the total salary outlay for each department. This request is sorted horizontally with an ACROSS phrase. 

TABLE FILE EMPLOYEE
SUM CURR_SAL ACROSS DEPARTMENT
END

The output is:

DEPARTMENT
MIS                    PRODUCTION
---------------------------------
$108,002.00           $114,282.00

Notice that the horizontal sort displays a column for each sort field (department).

Controlling Display of an ACROSS Title for a Single Field

How to:

Using the SET ACRSVRBTITL command, you can control the display of an ACROSS column title in an ACROSS group. The behavior of the title is determined by the number of verb columns in the ACROSS group. The field count is affected by the following features, which add internal matrix columns to the report:

Syntax: How to Control Display of an ACROSS Title for a Single Field 

SET ACRSVRBTITL = {HIDEONE|ON|OFF}
ON TABLE SET ACRSVRBTITL {HIDEONE|ON|OFF}

where:

HIDEONE

Suppresses the title when there is only one display field, or there is only one display field and the request contains one or more of the features that add internal matrix columns to the report. This value is the default.

ON

Always displays the title even if there is only one display field.

OFF

Suppresses the title when there is only one display field. Displays the title when there is only one display field and the request contains one or more of the features that add internal matrix columns to the report. This is legacy behavior.

Example: Hiding an ACROSS Title With ACRSVRBTITL

The following request against the GGSALES data source has a display field in the heading: 

SET ACRSVRBTITL=HIDEONE
TABLE FILE GGSALES
HEADING
"Sales Report for <CATEGORY with ACRSVRBTITL=HIDEONE"
" "
SUM DOLLARS AS Sales
BY CATEGORY
ACROSS REGION
WHERE CATEGORY EQ 'Food'
ON TABLE SET PAGE NOPAGE
ON TABLE SET ACROSSTITLE SIDE
ON TABLE SET ACROSSLINE SKIP

ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, FONT='ARIAL', SIZE=9, SQUEEZE=ON,$
TYPE=TITLE,JUSTIFY=LEFT,BACKCOLOR=RGB(102 102 102),COLOR=RGB(255 255 255),STYLE=BOLD,$
TYPE=HEADING, SIZE=11, STYLE=BOLD,JUSTIFY=CENTER, $
TYPE=ACROSSTITLE,STYLE=BOLD,$
TYPE=ACROSSVALUE,BACKCOLOR=RGB(218 225 232),$
END

Using the default value for ACRSVRBTITL, HIDEONE, suppresses the ACROSS title Sales, even though the heading displays a field value that adds a report column to the internal matrix.

The report output is shown in the following image:

If you change the SUM command to the following: 

SUM DOLLARS/D12CM

the field in the heading and the reformatted dollar sales values add report columns to the internal matrix, but the ACROSS title Sales is still suppressed.

The report output is shown in the following image:

Using the ACRSVRBTITL value ON, without reformatting the dollar sales column, does not suppress the ACROSS title Sales because the heading displays a field value that adds a report column to the internal matrix.

The report output is shown in the following image:

If you change the SUM command to the following: 

SUM DOLLARS/D12CMC

the field in the heading and the reformatted dollar sales values add report columns to the internal matrix, so the ACROSS title Sales is not suppressed.

The report output is shown in the following image:

With the setting ACRSVRBTITL=OFF, the field in the heading adds a report column to the internal matrix, and the ACROSS title Sales is not suppressed.

The report output is shown in the following image:

If you change the SUM command to the following: 

SUM DOLLARS/D12CM

the field in the heading and the reformatted dollar sales values add report columns to the internal matrix, and the ACROSS title Sales is not suppressed.

The report output is shown in the following image:

Positioning ACROSS Titles on Report Output

How to:

Reference:

In a report that uses the ACROSS sort phrase to sort values horizontally across the page, by default, two lines are generated on the report output for the ACROSS columns. The first line displays the name of the sort field (ACROSS title), and the second line displays the values for that sort field (ACROSS value). The ACROSS field name is left justified above the first ACROSS value.

If you want to display both the ACROSS title and the ACROSS values on one line in the PDF, HTML, EXL2K, or XLSX report output, you can issue the SET ACROSSTITLE = SIDE command. This command places ACROSS titles to the left of the ACROSS values. By default, the titles are right justified in the space above the BY field titles. You can change the justification of the ACROSS title by adding the JUSTIFY attribute to the StyleSheet declaration for the ACROSSTITLE component. If there are no BY fields, the heading line that is created by default to display the ACROSS title will not be generated.

This feature is designed for use in requests that have both ACROSS fields and BY fields. For requests with ACROSS fields but no BY fields, the set command is ignored, and the ACROSS titles are not moved.

Note that for certain output formats, you can control whether column titles are underlined using the SET TITLELINE command. SET ACROSSLINE is a synonym for SET TITLELINE. For information, see Using Headings, Footings, Titles, and Labels.

Syntax: How to Control the Position of ACROSS Field Names 

SET ACROSSTITLE = {ABOVE|SIDE}

where:

ABOVE

Displays ACROSS titles above their ACROSS values. ABOVE is the default value.

SIDE

Displays ACROSS titles to the left of their ACROSS values, above the BY columns.

Reference: Usage Notes for SET ACROSSTITLE

  • When the ACROSS value wraps, the ACROSS title aligns with the top line of the wrapped ACROSS values.
  • The ACROSS title spans the width of the BY columns. If the ACROSS title value is larger than the width of the BY columns on the current page, the value is truncated. The first panel may have more BY fields than subsequent panels, if SET BYPANEL is set to a value smaller than the total number of BY fields.
  • This setting will not create a new column within the report for the title placement.
    • If the request does not have any BY fields, the ACROSS title is not moved.
    • With BYPANEL=OFF, the ACROSS title is not displayed on subsequent panels.
  • WRAP is not supported for ACROSSTITLE with SET ACROSSTITLE=SIDE.

Example: Placing the ACROSS Title on the Same Line as the ACROSS Values

The following example against the GGSALES data source has two ACROSS sort fields, CATEGORY and PRODUCT. SET ACROSSTITLE=SIDE moves the ACROSS title to the left of the ACROSS values. With BYPANEL=ON the ACROSS titles are repeated in the same location on each subsequent panel. 

SET ACROSSTITLE=SIDE
SET BYPANEL=ON
TABLE FILE GGSALES
SUM 
     DOLLARS/I8M AS ''
BY REGION
BY ST
BY CITY
ACROSS CATEGORY
ACROSS PRODUCT
WHERE PRODUCT NE 'Capuccino';
ON TABLE SET PAGE-NUM ON 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     FONT='ARIAL',
     SIZE=10,
     BORDER=LIGHT,
$
TYPE=ACROSSVALUE,
     WRAP=ON,
$
ENDSTYLE
END

The ACROSS title Category displays to the left of the ACROSS values Coffee, Food, and Gifts. The ACROSS title Product displays to the left of the ACROSS values Espresso, Latte, Biscotti, and so on. The ACROSS titles are right-justified above the space occupied by the BY field names Region, State, and City. Notice that the ACROSS value Croissant wraps onto a second line, and the ACROSS title is aligned with the top line. The following shows panel 1:

The following shows panel 2:

Example: ACROSS Title Spacing

The following example against the GGSALES data source has two BY fields and two ACROSS fields. This example does not set borders on and does not enable wrapping of the ACROSS values. SET ACROSSTITLE=SIDE moves the ACROSS title to the left of the ACROSS values. The SET BYPANEL=1 command repeats only the first BY field on the second panel. To prevent the ACROSS titles from being truncated to fit above the BY field on the second panel, the first BY field has an AS name that is longer than the default name: 

SET ACROSSTITLE=SIDE
SET BYPANEL=1
TABLE FILE GGSALES
SUM 
     DOLLARS/I8M AS ''
BY ST AS 'State Code'
BY CITY
ACROSS CATEGORY AS 'Categories'
ACROSS PRODUCT AS 'Products'
WHERE PRODUCT NE 'Capuccino';
ON TABLE SET PAGE-NUM ON 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     FONT='ARIAL',
     SIZE=10,
   $
ENDSTYLE
END

The first panel follows:

Because of the SET BYPANEL=1 command, the space available above the BY fields on the second panel is smaller than the space on the initial panel. The AS name State Code adds space for the ACROSS titles, so the titles are not truncated on the second panel:

Example: Specifying Background Color for ACROSS Values With ACROSSTITLE=SIDE

The following request against the GGSALES data source places the ACROSS titles next to the ACROSS values and sets matching styling of font color and backcolor for the ACROSSTITLES, ACROSSVALUES, and column titles to white text on grey background color. 

SET ACROSSTITLE=SIDE
TABLE FILE GGSALES
SUM DOLLARS/I8M AS ''
BY REGION
BY ST
BY CITY
ACROSS CATEGORY
ACROSS PRODUCT
WHERE CATEGORY EQ 'Coffee' OR 'Food';
ON TABLE SET PAGE-NUM NOPAGE
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
SQUEEZE=ON,UNITS=IN,ORIENTATION=PORTRAIT,$
TYPE=REPORT,FONT='ARIAL',SIZE=10,BORDER=LIGHT,$
TYPE=ACROSSTITLE,COLOR=WHITE, BACKCOLOR=GREY,$
TYPE=ACROSSVALUE,COLOR=WHITE, BACKCOLOR=GREY,$ 
TYPE=TITLE,COLOR=WHITE, BACKCOLOR=GREY,$
ENDSTYLE
END

The output has a grey background color and white text for the ACROSS titles, ACROSS values, and column titles.

Using Multiple Horizontal (ACROSS) Sort Fields

You can sort a report using more than one sort field. When several sort fields are used, the ACROSS phrase order determines the sorting order. The first ACROSS phrase sets the first sort break, the second ACROSS phrase sets the second sort break, and so on. Each successive sort is nested within the previous one.

Example: Sorting With Multiple Horizontal (ACROSS) Phrases

The following request sorts the sum of current salaries, first by department and then by job code. 

TABLE FILE EMPLOYEE
SUM CURR_SAL
ACROSS DEPARTMENT ACROSS CURR_JOBCODE
WHERE CURR_SAL GT 21500
END

The output is:

DEPARTMENT                                                          
                  MIS                               PRODUCTION                   
CURR_JOBCODE
                  A17              B04              A15              A17  
------------------------------------------------------------------------
           $27,062.00       $21,780.00       $26,862.00       $29,700.00

Collapsing PRINT With ACROSS

How to:

Reference:

The PRINT command generates a report that has a single line for each record retrieved from the data source after screening out those that fail IF or WHERE tests. When PRINT is used in conjunction with an ACROSS phrase, many of the generated columns may be empty. Those columns display the missing data symbol.

To avoid printing such a sparse report, you can use the SET ACROSSPRT command to compress the lines in the report. The number of lines is reduced within each sort group by swapping non-missing values from lower lines with missing values from higher lines, and then eliminating any lines whose columns all have missing values.

Because data may be moved to different report lines, row-based calculations such as ROW-TOTAL and ACROSS-TOTAL in a compressed report are different from those in a non-compressed report. Column calculations are not affected by compressing the report lines.

Syntax: How to Compress Report Lines 

SET ACROSSPRT = {NORMAL|COMPRESS}
ON TABLE SET ACROSSPRT{NORMAL|COMPRESS}

where:

NORMAL

Does not compress report lines. NORMAL is the default value.

COMPRESS

Compresses report lines by promoting data values up to replace missing values within a sort group.

Reference: Usage Notes for SET ACROSSPRT

  • Compression applies only to ACROSS fields, including ACROSS … COLUMNS. It has no effect on BY fields.
  • The only data values that are subject to compression are true missing values. If the value of the stored data is either 0 or blank and the metadata indicates that MISSING is ON, that value is not subject to compression.

Example: Compressing Report Output With SET ACROSSPRT

The following request against the GGSALES data source prints unit sales by product across region: 

TABLE FILE GGSALES                           
PRINT UNITS/I5                                     
BY PRODUCT                                   
ACROSS REGION                              
WHERE DATE FROM '19971201' TO '19971231';    
WHERE PRODUCT EQ 'Capuccino' OR 'Espresso';  
ON TABLE SET ACROSSPRT NORMAL 
ON TABLE SET PAGE NOPAGE
END

Each line of the report represents one sale in one region, so at most one column in each row has a non-missing value when ACROSSPRT is set to NORMAL. 

                  Region                                         
                  Midwest     Northeast   Southeast   West       
Product           Unit Sales  Unit Sales  Unit Sales  Unit Sales 
-----------------------------------------------------------------
Capuccino                  .         936           .           . 
                           .         116           .           . 
                           .         136           .           . 
                           .           .        1616           . 
                           .           .        1118           . 
                           .           .         774           . 
                           .           .           .        1696 
                           .           .           .        1519 
                           .           .           .         836 
Espresso                1333           .           .           . 
                         280           .           .           . 
                         139           .           .           . 
                           .        1363           .           . 
                           .         634           .           . 
                           .         406           .           . 
                           .           .        1028           . 
                           .           .        1014           . 
                           .           .         885           .
                           .           .           .        1782
                           .           .           .        1399
                           .           .           .         551

Setting ACROSSPRT to COMPRESS promotes non-missing values up to replace missing values within the same BY group and then eliminates lines consisting of all missing values. 

TABLE FILE GGSALES                           
PRINT UNITS/I5                                     
BY PRODUCT                                   
ACROSS REGION                              
WHERE DATE FROM '19971201' TO '19971231';    
WHERE PRODUCT EQ 'Capuccino' OR 'Espresso';  
ON TABLE SET ACROSSPRT COMPRESS  
ON TABLE SET PAGE NOPAGE 
END

The output is:

                  Region                                        
                  Midwest     Northeast   Southeast   West      
Product           Unit Sales  Unit Sales  Unit Sales  Unit Sales
----------------------------------------------------------------
Capuccino                  .         936        1616        1696
                           .         116        1118        1519
                           .         136         774         836
Espresso                1333        1363        1028        1782
                         280         634        1014        1399
                         139         406         885         551

Hiding Null Columns in ACROSS Groups

In this section:

How to:

Reference:

Report requests that use the ACROSS sort phrase generate a group of columns (one for each display field in the request) under each value of the ACROSS field. In many cases, some of these columns have only missing or null values. You can use the HIDENULLACRS parameter to hide the display of ACROSS groups containing only null columns in styled output formats. If there is a BY field with a PAGE-BREAK option, columns are hidden on each page of output generated by that PAGE-BREAK option. If the request contains no BY page breaks, ACROSS groups that are missing for the entire report are hidden.

Hiding null ACROSS columns is supported for all styled output formats except for the EXL2K PIVOT and EXL2K FORMULA options.

Syntax: How to Hide Null ACROSS Columns 

SET HIDENULLACRS = {ON|OFF}
ON TABLE SET HIDENULLACRS {ON|OFF}

where:

ON

Hides columns with missing data in ACROSS groups within a BY-generated page break.

OFF

Does not hide columns. OFF is the default value.

Reference: Usage Notes for Hiding Null Columns Within ACROSS Groups

  • Aligning items in headings with the associated data columns (HEADALIGN) is not supported for ACROSS reports.
  • Hiding ACROSS columns will not affect items placed in heading elements with spot markers or explicit positioning. This means that after ACROSS group columns are hidden, items may align with the ACROSS columns differently than expected.

Reference: Features Not Supported For Hiding Null ACROSS Columns

  • EXL2K FORMULA.
  • EXL2K PIVOT.
  • OVER.
  • HIDENULLACRS is only supported with page breaks specified in ON byfieldname PAGE-BREAK phrases or BY fieldname PAGE-BREAK phrases. It is not supported with:
    • BY field ROWS value OVER.
    • FML FOR fields (FOR fieldvalue OVER PAGE-BREAK).

Hiding ACROSS Groups and Columns Within BY Page Breaks

Hiding null columns is most useful when a BY sort field has the PAGE-BREAK option, either on the BY phrase itself or in an ON phrase. The change in value of the BY field determines when a page break is generated for that BY field. The change in BY field value defines the limits within which the ACROSS columns will be hidden, even if the BY field value spans multiple physical pages.

There is no way to specify a particular BY field with this setting, so if the request has multiple BY fields with page breaks, the setting applies to all of them. If there are no BY fields with page breaks, an ACROSS column must be missing for the entire report in order to be hidden.

The entire ACROSS group will be hidden either when the ACROSS value is missing or when all of the rows for all of the display columns under that ACROSS value contain null or missing values within the given BY field value.

The set of pages generated for a BY field value with a page break will be hidden if all ACROSS groups within that BY field value are hidden.

When columns are removed from a page or a panel, the existing columns are resituated to fill the missing space.

Example: Hiding Null ACROSS Groups

The following request against the GGSALES data source has a page break on the BY field named REGION and an ACROSS phrase on the CITY field. The display fields in each ACROSS group are UNITS and DOLLARS: 

SET HIDENULLACRS=OFF
TABLE FILE GGSALES
SUM UNITS DOLLARS
BY REGION PAGE-BREAK
BY ST
ACROSS CITY
WHERE CITY LE 'Memphis'
ON TABLE SET HTMLCSS ON
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET STYLE *
TYPE=REPORT, FONT=ARIAL, SIZE=9,$
ENDSTYLE
END

With SET HIDENULLACRS=OFF, all columns display:

Running the request with SET HIDENULLACRS=ON eliminates the ACROSS groups for cities with missing data within each region. For example, the Midwest region has no columns for Atlanta or Boston:

Example: Hiding Columns Within ACROSS Groups

In the following request against the GGSALES data source, REGION is a BY field with a PAGE-BREAK, and PRODUCT is the ACROSS field. The DEFINE command creates a field named SHOWDOLLARS that has missing values for the Espresso column within the ACROSS group Coffee: 

SET HIDENULLACRS=OFF
SET BYPANEL=2
DEFINE FILE GGSALES
SHOWDOLLARS/I8M MISSING ON = IF (PRODUCT EQ 'Espresso') THEN MISSING ELSE DOLLARS;
END
TABLE FILE GGSALES
HEADING
"Page <TABPAGENO "
SUM SHOWDOLLARS AS ''
BY REGION
BY ST
BY CITY
ACROSS PRODUCT
WHERE REGION EQ 'Midwest' OR 'Northeast'
WHERE CATEGORY EQ 'Coffee';
ON REGION PAGE-BREAK
ON TABLE SET PAGE-NUM ON
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=9,
$
ENDSTYLE
END

Running the request with SET HIDENULLACRS=OFF displays the Espresso column and any other column containing missing values within the Coffee group:

Running the request with SET HIDENULLACRS=ON hides columns with missing data within each region. On page 1 (Midwest), both the Capuccino and Espresso columns are hidden, while on page 2 (Northeast), only the Espresso column is hidden:

Example: Hiding Null Columns With Multiple ACROSS Fields

The following request against the GGSALES data source has two ACROSS fields, PRODUCT and CATEGORY. The BY field with the page break is REGION. The DEFINE command creates a field named SHOWDOLLARS that has missing values for the Espresso column within the ACROSS group Coffee and for the entire ACROSS group Gifts. 

SET HIDENULLACRS=OFF
DEFINE FILE GGSALES
SHOWDOLLARS/I8M MISSING ON = IF (PRODUCT EQ 'Espresso' OR 
   CATEGORY EQ 'Gifts') THEN MISSING ELSE DOLLARS;
END
TABLE FILE GGSALES
SUM SHOWDOLLARS AS ''
BY REGION
BY ST
BY CITY
ACROSS CATEGORY
ACROSS PRODUCT
WHERE REGION EQ 'Midwest' OR 'Northeast'
ON REGION PAGE-BREAK
HEADING
"Page <TABPAGENO /<TABLASTPAGE "
ON TABLE SET PAGE-NUM OFF
ON TABLE SET BYPANEL ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     PAGESIZE='Letter',
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     HEADPANEL=ON,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=8,
$
ENDSTYLE
END

Running the request with SET HIDENULLACRS=OFF displays all of the columns:

Running the request with SET HIDENULLACRS=ON hides the Espresso product and the entire Gifts category within each region. On page 1 (Midwest), the Gifts group and the Espresso and Capuccino columns are hidden, while on page 2 (Northeast), the Gifts group and the Espresso column are hidden:

Generating Summary Lines and Hiding Null ACROSS Columns

If an entire ACROSS group is hidden, so are the totals generated for the associated BY field value. If any of the columns for the ACROSS value contain non-missing data, the ACROSS group will display with the non-missing columns.

Summary elements remain tied to their ACROSS group columns. If an ACROSS group is hidden, the associated summary value will be hidden, and subsequent values will realign with their ACROSS columns.

Summary lines generated at BY field breaks display at the end of the final page for that BY field value. All ACROSS groups that contain any non-null data within the entire BY value (even if they were hidden on some pages within the BY value) will display on the summary lines so that associated summary values can be displayed.

Grand totals can contain ACROSS columns that have been hidden on some pages within a BY field value. Therefore, they are always placed on a new page and presented for all ACROSS groups and columns that displayed on any page within the report, regardless of what was hidden on other pages.

Summary lines defined for BY fields outside of the innermost BY page break may also contain ACROSS columns that have been hidden for some of the internal BY fields. For this reason, these summary lines will always present all available ACROSS columns and will be presented on a new page.

All totals calculated in columns (ACROSSTOTAL, ROWTOTAL) will be hidden if all of the column totals are missing.

Example: Generating Column Totals and Hiding Null ACROSS Columns

In the following request against the GGSALES data source, REGION is a BY field with a PAGE-BREAK, and PRODUCT is the ACROSS field. The DEFINE command creates a field named SHOWDOLLARS that has missing values for the Espresso column within the ACROSS group Coffee. Column totals are generated at the end of the report: 

SET HIDENULLACRS=ON
DEFINE FILE GGSALES
SHOWDOLLARS/I8M MISSING ON = IF (PRODUCT EQ 'Espresso') THEN MISSING ELSE DOLLARS;
END
TABLE FILE GGSALES
SUM SHOWDOLLARS AS ''
BY REGION
BY ST
BY CITY
ACROSS PRODUCT
ON REGION PAGE-BREAK
HEADING
"Page <TABPAGENO /<TABLASTPAGE "
WHERE CATEGORY EQ 'Coffee';
ON TABLE SET PAGE-NUM OFF
ON TABLE SET BYPANEL ON
ON TABLE COLUMN-TOTAL AS 'TOTAL'
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     PAGESIZE='Letter',
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     HEADPANEL=ON,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=9,
$
ENDSTYLE
END

Running the request hides the null columns within each REGION page break and generates a separate page for the column totals.

The following shows pages one through three. On page 1, the Espresso and Capuccino columns are hidden. On pages 2 and 3, the Espresso column is hidden:

The following shows pages four and five. On page 4, the Espresso column is hidden. Page 5 is the totals page. The Espresso column is hidden since it was hidden on every detail page. However, Capuccino is not hidden since it appeared on some pages:

Using Column Styling and Hiding Null ACROSS Columns

Column styling remains attached to the original column, regardless of whether the column remains in the same place on the report output because of hiding null columns. In particular:

  • BORDERS and BACKCOLOR will readjust to fit the resulting panel or page layout after the columns are hidden.
  • Styling specified for a designated column will remain attached to the designated column and be unaffected by the hidden columns. For example, if the third ACROSS column is defined with conditional styling, and the second ACROSS column is hidden, the formatting will remain on the column that was initially third, even though it becomes the second column on the output.

For information about styling columns, see Identifying a Report Component in a WebFOCUS StyleSheet.

Example: Using Column Styling and Hiding Null ACROSS Columns

In the following request against the GGSALES data source, REGION is a BY field with a PAGE-BREAK and PRODUCT is the ACROSS field. The DEFINE command creates a field named SHOWDOLLARS that has missing values for the Capuccino column in the Midwest region, the Thermos column in the Northeast region, the Scone column in the Southeast region, and the entire West region. Column totals, row totals, and a subtotal for each region are generated.

Some of the columns are assigned background colors:

  • Column C5 has BACKCOLOR=WHEAT. C5 is the fifth column counting display fields from left to right, but not counting BY fields or ROW-TOTAL fields. Column C5 corresponds to the Croissant column in the Coffee group.
  • Column P5 has BACKCOLOR=THISTLE. P5 is the fifth column counting display fields, BY fields, and ROW-TOTAL fields, but not NOPRINT fields. Column P5 corresponds to the Espresso column in the Coffee group.
  • Column N7 has BACKCOLOR=MEDIUM GOLDENROD. N7 is the seventh column counting display fields, BY fields, ROW-TOTAL fields, and NOPRINT fields. Column N7 corresponds to the Biscotti column in the Food group.
  • Column B3 has BACKCOLOR=GOLDENROD. B3 is the third BY field, counting all BY fields, even if not printed. Column B3 corresponds to the CITY sort field.
  • Column SHOWDOLLARS(6) has BACKCOLOR=SILVER. SHOWDOLLARS(6) is the sixth occurrence of the SHOWDOLLARS field and corresponds to the Scone column in the Food group.

The request follows: 

SET HIDENULLACRS=OFF
DEFINE FILE GGSALES
SHOWDOLLARS/I8M MISSING ON =
IF ((PRODUCT EQ 'Capuccino' AND REGION EQ 'Midwest') OR
(PRODUCT EQ 'Coffee Grinder' AND REGION EQ 'Northeast') OR
(PRODUCT EQ 'Scone' AND REGION EQ 'Southeast') OR
(REGION EQ 'West')) THEN MISSING ELSE DOLLARS;
END
TABLE FILE GGSALES
SUM SHOWDOLLARS AS ''
BY REGION
BY ST
BY CITY
ACROSS CATEGORY
ACROSS PRODUCT
ON REGION SUBTOTAL AS '*TOTAL'
ON REGION PAGE-BREAK
HEADING
" Page <TABPAGENO "HEADING
" Capuccino Missing in Coffee Group "
WHEN REGION EQ 'Midwest';
HEADING
" Coffee Grinder Missing in Gifts Group "
WHEN REGION EQ 'Northeast';
HEADING
" Scone Missing in Food Group "
WHEN REGION EQ 'Southeast';
WHERE CATEGORY EQ 'Coffee' OR 'Food'
ON TABLE SET PAGE-NUM OFF
ON TABLE SET BYPANEL ON
ON TABLE ROW-TOTAL AS 'TOTAL'
ON TABLE COLUMN-TOTAL AS 'TOTAL'
ON TABLE SET HTMLCSS ON
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET STYLE *
UNITS=IN,PAGESIZE='Letter',SQUEEZE=ON,ORIENTATION=PORTRAIT,$
TYPE=REPORT,HEADPANEL=ON,GRID=OFF,FONT='ARIAL',SIZE=6,$
TYPE=HEADING, style=bold, size=8,$
TYPE=DATA, COLUMN = C5, BACKCOLOR=WHEAT,$
TYPE=DATA, COLUMN = P5, BACKCOLOR=THISTLE,$
TYPE=DATA, COLUMN = N7, BACKCOLOR=MEDIUM GOLDENROD,$
TYPE=DATA, COLUMN = B3, BACKCOLOR=GOLDENROD,$
TYPE=DATA, COLUMN = SHOWDOLLARS(6), BACKCOLOR=silver,$
ENDSTYLE
END

Running the report with SET HIDENULLACRS=OFF shows all columns. A page is generated for the West region and subtotals are calculated, even though all of the values are missing:

Running the report with SET HIDENULLACRS=ON, shows:

  • On page 1, the Capuccino column is hidden and, therefore, the Espresso column is no longer P5 on the report, but it still has BACKCOLOR=THISTLE. Similarly, the Biscotti column has MEDIUM, GOLDENROD, the Croissant column has WHEAT, and the Scone column has SILVER.
  • The subtotals for each region are calculated only for columns that display for that region.
  • No page is generated for the West region since all of its values are missing.
  • Every column is represented on the page with the grand totals.

The output is:

Hiding Null ACROSS Columns in an FML Request

An FML request always has a FOR field that defines the order of specific rows. The FOR field cannot be used to trigger hiding of null ACROSS columns. However, the request can also have a BY field with a PAGE-BREAK option and this can be used to hide null ACROSS columns.

Example: Hiding Null ACROSS Columns in an FML Request

The following FML request against the GGSALES data source has a BY field named REGION with the PAGE-BREAK option and an ACROSS field named QTR. The FOR field is PRODUCT. The DEFINE command creates the QTR field and contains missing values for Q4 in the Midwest region, Q2 in the Northeast region, and for all quarters in the Southeast region. 

SET HIDENULLACRS=ON
DEFINE FILE GGSALES
QTR/Q=DATE;
SHOWDOLLARS/D12CM MISSING ON = 
          IF REGION EQ 'Midwest' AND QTR EQ 'Q4' THEN MISSING
     ELSE IF REGION EQ 'Northeast' AND QTR EQ 'Q2' THEN MISSING
     ELSE IF REGION EQ 'Southeast' THEN MISSING 
     ELSE DOLLARS;
END
TABLE FILE GGSALES
SUM SHOWDOLLARS
BY REGION
ACROSS QTR
FOR PRODUCT
'Biscotti' AS 'Biscotti' LABEL R1 OVER
'Capuccino' AS 'Capuccino' LABEL R2 OVER
'Latte' AS 'Latte' LABEL R3 OVER
'Mug' AS 'Mug' LABEL R4 OVER
'Coffee Pot' AS 'Coffee Pot' LABEL R5 OVER
RECAP R6/D12.2=R1+R2+R3+R4+R5; 
 AS ''
ON REGION PAGE-BREAK
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,$
TYPE=REPORT,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=9,$
TYPE=TITLE,
     STYLE=BOLD,$
TYPE=ACROSSTITLE,
     STYLE=BOLD,$
ENDSTYLE
END

Running the request with SET HIDENULLACRS=OFF generates all columns and a page for all regions, including the Southeast regions where all values are missing:

Running the request with SET HIDENULLACRS=ON hides column Q4 for the Midwest region, Q2 for the Northeast region, and the entire page for the Southeast region: