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.
SET HIDENULLACRS = {ON|OFF}
ON TABLE SET HIDENULLACRS {ON|OFF}
where:
Hides columns with missing data in ACROSS groups within a BY generated page break.
Does not hide columns. OFF is the default value.
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.
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 HOLD 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:
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 HOLD 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:
The following request against the GGSALES data source has two ACROSS fields, CATEGORY and PRODUCT. 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 HOLD 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:
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.
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 HOLD 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:
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:
For information about styling columns, see the Creating Reports manual.
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 HOLD 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:
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.
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 HOLD 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 |