Sorting Columns

In this section:

How to:

Reference:

You can sort report information horizontally using the ACROSS phrase.

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

The limit on the number of columns per ACROSS phrase is 1151, which is possible only with a single ACROSS phrase. The limit is based on the total number of columns allowed in a request. In the case of ACROSS, it is 1024 columns, plus 127 sort columns maximum. 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.

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.


Top of page

x
Syntax: How to Sort Columns
ACROSS sortfield

where:

sortfield

Is the name of the sort field.


Top of page

x
Reference: Usage Notes for Sorting Columns


Example: Sorting Columns With ACROSS

The following example 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).


Top of page

x
Controlling Display of an ACROSS Title for a Single Field

How to:

By default, when there is only one field displayed for an ACROSS sort group, no column title displays above that field. Using the SET ACRSVRBTITL command, you can control the display of ACROSS column titles when there is one displayed field for an ACROSS group. The field count that determines whether the ACROSS title displays is affected by certain components in the report request, such as calculated fields or fields in headings.

The output is unaffected by SET commands that adjust underlines with titles, adjust the space between columns, or eliminate unpopulated ACROSS columns on the page. The size of the title and the number of lines it occupies also do not affect the output.



x
Syntax: How to Control Display of an ACROSS Title for a Single Field
SET ACRSVRBTITL = {OFF|ON|HIDEONE} 
ON TABLE SET ACRSVRBTITL {OFF|ON|HIDEONE}

where:

OFF

Suppresses the title when there is only one display field.

ON

Displays the title when there is only one display field.

HIDEONE

Suppresses the title when there is only one display field, but the request contains one or more of the following components:

  • Fields in a heading or footing.
  • Fields whose display is suppressed with the NOPRINT phrase.
  • Reformatted fields (which are normally counted twice).
  • A COMPUTE command referencing multiple fields.

This is the default value



Example: Displaying or Suppressing a Single ACROSS Column Title

The following request against the GGSALES data source has one display field, DOLLARS. The ACRSVRBTITL parameter is OFF:

SET ACRSVRBTITL=OFF
TABLE FILE GGSALES
SUM DOLLARS AS Sales
ACROSS REGION
ON TABLE SET PAGE NOPAGE
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END

On the output, there is no column title that indicates what the number in each column represents:

Region                                              
Midwest      Northeast    Southeast    West         
----------------------------------------------------
11514345     11494543     11781285     11674908     

The following is the same request with ACRSVRBTITL=ON:

SET ACRSVRBTITL=ON
TABLE FILE GGSALES
SUM DOLLARS AS Sales
ACROSS REGION
ON TABLE SET PAGE NOPAGE
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END

On the output, the title Sales appears above each field value in the ACROSS group:

Region                                              
Midwest      Northeast    Southeast    West         
   Sales        Sales        Sales        Sales     
----------------------------------------------------
11514345     11494543     11781285     11674908     


Example: Hiding an ACROSS Title With HIDEONE

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

SET ACRSVRBTITL=OFF         
TABLE FILE GGSALES          
HEADING               
"Sales Report for <CATEGORY with ACRSVRBTITL=OFF"
" "                         
SUM DOLLARS AS Sales        
BY CATEGORY                 
ACROSS REGION               
WHERE CATEGORY EQ 'Food'    
ON TABLE SET PAGE NOPAGE    
END                         

With the setting ACRSVRBTITL=OFF, the field in the heading counts and the ACROSS title Sales is not suppressed:

Sales Report for Food with ACRSVRBTITL=OFF                   
                                                             
             Region                                          
             Midwest     Northeast   Southeast   West        
Category        Sales       Sales       Sales       Sales    
-------------------------------------------------------------
Food          4404483     4445197     4308731     4204333    

Changing ACRSVRBTITL to ON produces the same report:

Sales Report for Food with ACRSVRBTITL=ON                    
                                                             
             Region                                          
             Midwest     Northeast   Southeast   West        
Category        Sales       Sales       Sales       Sales    
-------------------------------------------------------------
Food          4404483     4445197     4308731     4204333    

Changing ACRSVRBTITL to HIDEONE suppresses the ACROSS title Sales:

Sales Report for Food with ACRSVRBTITL=HIDEONE               
                                                             
             Region                                          
             Midwest     Northeast   Southeast   West        
Category                                                     
-------------------------------------------------------------
Food          4404483     4445197     4308731     4204333    

Top of page

x
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 Customizing Tabular Reports.



x
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.



x
Reference: Usage Notes for SET ACROSSTITLE


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.


Top of page

x
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

Top of page

x
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.



x
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.



x
Reference: Usage Notes for SET ACROSSPRT


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 NORMALON 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 COMPRESSON 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

Top of page

x
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. It is not supported for Active Technologies.



x
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.



x
Reference: Usage Notes for Hiding Null Columns Within ACROSS Groups


x
Reference: Features Not Supported For Hiding Null ACROSS Columns


x
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:



x
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:



x
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:



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:

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:

The output is:



x
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:


Information Builders