Features Added in FOCUS 7.7.03

In this section:

The following features were added as of FOCUS 7.7.03.

Passing FOCUS Dates to Excel 2000/2003

In this section:

By default, when FOCUS creates dates in Excel 2000/2003 (EXL2K) format, the date formats that contain translated values, such as month or day name, are sent as formatted text, preserving the style defined for the report field. Numeric dates are passed to Excel 2000/2003 as standard date values, not as text.

Passing Dates With Translated Text to Excel 2000/2003

How to:

Reference:

Some translated dates can be sent to Excel 2000/2003 as standard date values with format masks, enabling Excel to use them in functions, formulas, and sort sequences. The SET EXL2KTXTDATE command allows you to specify that translated dates should be sent as date values with format masks instead of text values.

Syntax: How to Pass Translated Dates to Excel 2000/2003 as Date Values 

SET EXL2KTXTDATE = {TEXT|VALUE}

where:

TEXT

Passes date values that contain text to Excel 2000/2003 as formatted text. TEXT is the default value.

VALUE

Passes the types of translated date values that contain text and are supported Excel date formats to Excel 2000/2003 as standard date values with text format masks applied.

Reference: Usage Notes for SET EXL2KTXTDATE

  • The following date formats are not supported in EXL2K. They will translate into Excel General format and possibly produce unpredictable results:
    • JUL, YYJUL, and I2MT.
    • Dates stored as a packed or alphanumeric field with date display options.
  • Excel only supports mixed-case, not all uppercase or all lowercase for text dates. When EXL2KTXTDATE is set to VALUE, all FOCUS date formats containing text will present in EXL2K as mixed-case, regardless of the casing parameters defined in the FOCUS format. For example, MTRDY will generate the date string JANUARY 2, 10 in standard FOCUS, but when sent to EXL2K as a date value it will be presented as January 2, 10.
Example: Passing Dates With Translated Text to Excel 2000

The following request against the GGSALES data source creates the date January 1, 2010 and converts it to four date formats with translated text: 

SET EXL2KTXTDATE=TEXT
DEFINE FILE GGSALES
NEWDATE/MDYY = '01/01/2010';
WRMtrDY/WRMtrDY = NEWDATE;
wDMTY/wDMTY = NEWDATE;
wrDMTRY/wrDMTRY = NEWDATE;
wrYMtrD/wrYMtrD = NEWDATE;
END
TABLE FILE GGSALES
SUM DATE NOPRINT
NEWDATE WRMtrDY wDMTY wrDMTRY wrYMtrD
ON TABLE HOLD FORMAT EXL2K
END

The following table shows how the dates should appear with EXL2KTXTDATE set to TEXT and to VALUE:

FOCUS Format

SET EXL2KTXTDATE=TEXT

SET EXL2KTXTDATE=VALUE

EXL2K Displays:

EXL2K Value:

EXL2K Displays:

EXL2K Value:

WRMtrDY

FRIDAY, January 1 10

FRIDAY, January 1 10

Friday, January 1 10

1/1/2010

wDMTY

Fri, 1 JAN 10

Fri, 1 JAN 10

Fri, 1 Jan 10

1/1/2010

wrDMTRY

Friday, 1 JANUARY 10

Friday, 1 JANUARY 10

Friday, 1 January 10

1/1/2010

wrYMtrD

Friday, 10 January 1

Friday, 10 JANUARY 1

Friday, 10 January 1

1/1/2010

With SET EXL2KTXTDATE=TEXT, in Excel 2000/2003 all the cells with month or day translation are sent as text, and all month and day names are in the case specified by the FOCUS format.

The output is:

With SET EXL2KTXTDATE=VALUE, in Excel 2000/2003 all of the cells have a date value with format masks, and all month and day names are in mixed-case, regardless of how the case has been specified in the FOCUS format.

The output is:

Passing Dates Without a Day Component

Date formats that do not specify the day value explicitly are defined as the date value of the first day of the month. Therefore, the value placed in the cell may be different from the day component value in the source data field and may produce unexpected results when used for sorting or date calculations in an Excel formula.

The following table shows how FOCUS date formats are represented in EXL2K. The table shows how the value is preserved in the cell and how the display is generated using the format mask that corresponds to the FOCUS date format. 

DATEFLD/MDYY = '01/02/2010'

FOCUS Format

SET EXL2KTXTDATE=TEXT

SET EXL2KTXTDATE=VALUE

EXL2K Displays:

EXL2K Value:

EXL2K Displays:

EXL2K Value:

DMYY

02/01/2010

1/2/2010

02/01/2010

1/2/2010

MY

01/10

1/1/2010

01/10

1/1/2010

MTY

JAN, 10

JAN, 10

Jan, 10

1/1/2010

MTDY

JAN 2, 10

JAN 2, 10

Jan 2, 10

1/2/2010

Example: Passing FOCUS Dates With and Without a Day Component to Excel 2000/2003

The following request against the GGSALES data source creates the date January 2, 2010 and passes it to Excel 2000/2003 with formats MDYY, DMYY, MY, and MTDY: 

SET EXL2KTXTDATE=TEXT
DEFINE FILE GGSALES
NEWDATE/MDYY = '01/02/2010';
END
TABLE FILE GGSALES
SUM DATE NOPRINT
NEWDATE AS 'MDYY' NEWDATE/DMYY AS 'DMYY' NEWDATE/MY AS 'MY' 
        NEWDATE/MTY AS 'MTY' NEWDATE/MTDY AS 'MTDY'
ON TABLE HOLD FORMAT EXL2K 
END

With EXL2KTXTDATE=TEXT, columns D and E have text values, not date values. The values are displayed in uppercase, as specified by the FOCUS formats (MTY and MTDY):

With EXL2KTXTDATE=VALUE, columns D and E have actual date values with format masks, displayed by Excel 2000 in mixed-case. Since the MTY format does not have a day component, the date value stored is the first of January 2010 (1/1/2010), not the second of January 2010 (1/2/2010):

Passing Date Components for Use in Excel Formulas

Dates formatted as individual components (for example, D, Y, M, W) are passed to Excel as numeric values that can be used as parameters to Excel date functions. The values are passed as general text format that are recognized by Excel as numbers. These values are passed to Excel in the same format regardless of the setting for EXL2KTXTDATE.

Example: Passing Numeric Date Components to Excel 2000/2003

The following request against the GGSALES data source creates the date January 1, 2010 and extracts numeric date components, passing them to Excel 2000: 

SET EXL2KTXTDATE=VALUE
DEFINE FILE GGSALES
NEWDATE/MDYY = '01/01/2010';
D/D = NEWDATE;
Y/Y = NEWDATE;
W/W = NEWDATE;
w/w = NEWDATE;
M/M = NEWDATE;
YY/YY = NEWDATE;
END
TABLE FILE GGSALES
SUM DATE NOPRINT
NEWDATE D Y W w M YY
ON TABLE HOLD FORMAT EXL2K 
END

With SET EXL2KTXTDATE=VALUE, the output is:

Passing Quarter Formats

Date formats that contain a Quarter component are always passed to Excel as text strings since Excel does not support Quarter formats.

Example: Passing Dates With a Quarter Component to Excel 2000/2003

The following request against the GGSALES data source creates the date January 1, 2010 and converts it to date formats that contain a Quarter component: 

SET EXL2KTXTDATE=VALUE
DEFINE FILE GGSALES
NEWDATE/MDYY = '01/01/2010';
Q/Q = NEWDATE;
QY/QY = NEWDATE;
YBQ/YBQ = NEWDATE;
END
TABLE FILE GGSALES
SUM DATE NOPRINT
NEWDATE Q QY YBQ
ON TABLE HOLD FORMAT EXL2K 
END

Even with SET EXL2KTXTDATE=VALUE, in Excel 2000/2003 the cells containing dates with Quarter components have General format. To see this, open the Format Cells dialog box.

The output is:

Passing Date Components Defined as Translated Text

Date formats that do not contain sufficient information to present the valid date result in Excel are not translated to a value, including formats that do not contain year and/or month information. These dates will continue to be sent to Excel as text regardless of the SET EXL2KTXTDATE setting. In the absence of complete information, the year defaults to the current year, so the value sent would be incorrect if this type of format was passed as a date value. The following formats will not be sent as values:

  • MT, MTR, Mt, Mtr
  • W, w, WR, wr

Note that since these values are always sent as text, the case defined in the FOCUS format is applied in the resulting cell.

Example: Passing Date Components Defined as Translated Text to Excel 2000/2003

The following request against the GGSALES data source creates the date January 1, 2010 and converts it to date formats that are defined as either month name or day name: 

SET EXL2KTXTDATE=VALUE
DEFINE FILE GGSALES
NEWDATE/MDYY = '01/01/2010';
MT/MT = NEWDATE;
MTR/MTR = NEWDATE;
Mtr/Mtr = NEWDATE;
WR/WR = NEWDATE;
wr/wr = NEWDATE;
END
TABLE FILE GGSALES
SUM DATE NOPRINT
NEWDATE MT MTR Mtr WR wr
ON TABLE HOLD FORMAT EXL2K 
END

In Excel 2000/2003, the cells containing the days have General format. To see this, open the Format Cells dialog box.

The output is:

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.

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

  • Active Technologies
  • 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 field value 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 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:

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

Example: Hiding Null Columns With Multiple ACROSS Fields

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:

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

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 the Creating Reports manual.

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

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





Using Overflow Worksheets for EXL2K Report Output

How to:

Reference:

The maximum number of rows supported by Excel 2003 on a worksheet is 65,536 (65K). When you create an EXL2K output file from a FOCUS report, the number of rows generated can be greater than this maximum.

To avoid creating an unusable output file, you can have extra rows flow onto a new worksheet, called an overflow worksheet. The name of each overflow worksheet will be the name of the original worksheet appended with an increment number.

In addition, when the overflow worksheet feature is enabled, you can set a target value for the maximum number of rows to be included on a worksheet. By default, the row limit will be set to the default value for the LINES parameter (57).

Note: When generating EXL2K output, the FOCUS page heading and page footing commands generate worksheet headings and worksheet footings.

Syntax: How to Enable Overflow Worksheets

Add the ROWOVERFLOW=ON attribute to your FOCUS StyleSheet. 

TYPE=REPORT, ROWOVERFLOW=ON, ...

This attribute will work only with EXL2K output, which generates Web archive files (.xht). For all other output types, the ROWOVERFLOW StyleSheet attribute is ignored, and data flow is not affected.

Syntax: How to Set a Target Number of Rows for an EXL2K Worksheet

The ROWOVERFLOW=ON attribute is required in the FOCUS StyleSheet in order to set a target number of rows for the worksheets in the EXL2K output file.

In a FOCEXEC or profile, use the following syntax 

SET LINES = nnnnn

In a request, use the following syntax

ON TABLE SET LINES nnnnn

where:

nnnnn

Is a target value for the maximum number of lines to be included on any worksheet in the EXL2K output file when the overflow worksheet feature is enabled. The value must be less than 65K. Each worksheet will end within a range of the designated limit. The actual row count for each sheet is calculated individually based on user defined heading/footing lines and other system generated lines added to each sheet by the specific report request. If necessary, you can modify how your report flows across worksheets by adding or removing space in subheadings or subfootings.

Reference: Usage Notes for Excel 2000 Overflow Worksheets

  • The report heading is placed once at the start of the first sheet. The report footing is placed once at the bottom of the last overflow sheet.
  • Worksheet headings and column titles are repeated at the top of the original sheet and each subsequent overflow sheet. Worksheet footings are placed at the bottom of the original sheet and each subsequent overflow sheet. The data values are displayed on the top data row of each overflow sheet as they would be on a standard new page.
  • Report total lines are displayed at the bottom of the last overflow sheet directly above the final page and table footings.
  • Subheadings, subfootings, and subtotal lines display within the data flow as normal. No special consideration is made to retain groupings within a given sheet.
  • For EXL2K output, if the ROWOVERFLOW attribute is specified in the StyleSheet and SET LINES is greater than 65K, the following message is presented and no output file is generated:
    (FOC3313) The line number limit per EXCEL 2003 worksheet is 65536.
  • Output types that contain formula references (EXL2K PIVOT and EXL2K FORMULA) are not supported. Formula references are not automatically updated to reflect placement on new overflow worksheets.
  • The overflow worksheet feature applies to rows only, not columns. A new worksheet will not automatically be created if a report generates more than the Excel 2003 limit of 256 columns.

Example: Creating Overflow Worksheets With EXL2K Report Output

The following request creates EXL2K report output with overflow worksheets. The SET LINES command sets the maximum number of rows in each worksheet to approximately 2000, and the ROWOVERFLOW=ON attribute in the StyleSheet activates the overflow feature. Without this attribute, one worksheet would have been generated instead of three. 

TABLE FILE GGSALES
-* ****Report Heading****
ON TABLE SUBHEAD
"SALES BY REGION, CATEGORY, AND PRODUCT"
" "
-* ****Worksheet Heading****
HEADING
"SALES REPORT WORKSHEET <TABPAGENO"
" "
-* ****Worksheet Footing****
FOOTING
" "
"END OF WORKSHEET <TABPAGENO"
PRINT DOLLARS UNITS BUDDOLLARS BUDUNITS
BY REGION
BY CATEGORY
BY PRODUCT
BY DATE
-* ****Subfoot****
ON REGION SUBFOOT
" "
" End of Region <REGION"
" "
-* ****Subhead****
ON CATEGORY SUBHEAD
" "
" Category <CATEGORY for Region <REGION"
" "
-* ****Report Footing****
ON TABLE SUBFOOT
" "
"END OF REPORT"
ON TABLE SET LINES 2000
ON TABLE HOLD FORMAT EXL2K
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT=EXLOVER, ROWOVERFLOW=ON,$
ENDSTYLE
END

The report heading displays on the first worksheet only, the page heading and column titles display on each worksheet, and the subhead and subfoot display whenever the associated sort field changes value. The following image shows the top of the first worksheet, displaying the report heading, page heading, column titles, and first subhead.

Note that the TITLETEXT attribute in the StyleSheet specified the name EXLOVER, so the three worksheets were generated with the names EXLOVER1, EXLOVER2, and EXLOVER3. If there had been no TITLETEXT attribute, the sheets would have been named SHEET1, SHEET2, and SHEET3.

The worksheet footing displays at the bottom of each worksheet and the report footing displays at the bottom of the last worksheet. The following image shows the bottom of the last worksheet, displaying the last subfoot, the page footing, and the report footing.

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-aligned above the first ACROSS value.

If you want to display both the ACROSS title and the ACROSS values on one line in PDF 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-aligned 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.

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 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 HOLD 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-aligned 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 image shows panel 1.

The following image 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 HOLD 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 following image shows the first panel.

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.

Adding and Configuring Fonts

In this section:

You can add and configure PostScript Type 1 fonts to significantly expand your options for displaying and printing PS and PDF reports, beyond the basic set of fonts distributed with Adobe Reader. Thousands of PostScript fonts are available to make your reports more stylish and useful, including some that support symbols and bar codes.

Note:
  • Starting in this release, the font map files are stored as XML files (fontmap.xml and fontuser.xml). All font mappings in these files are used for both PDF and PostScript report output.
  • If you modified your font maps in earlier releases (where the font map files were separate for PDF and PostScript, and were stored in the files PDF.FMP and PSCRIPT.FMP), you must convert your font mappings to the new format in order to keep using them.

How FOCUS Uses Type 1 Fonts

FOCUS generates a PDF or PS document from scratch. In order to do that it must physically embed all the objects it displays or prints, including images and fonts, in the document itself.

When you execute a report and specify one of these formats as your display format, FOCUS retrieves the data and begins to format the report. Fonts and images specified in the StyleSheet must be available to FOCUS to create the output file. It reads the font information from the font files and embeds that information into the document. The font itself is stored on z/OS.

To ensure that FOCUS can locate the required information, you must define and map it in the following files:

  • Font file, usually a PFB (Printer Font Binary) file. This file contains the information about the shape to draw for each character of the font. The information in the font file is scalable, which means that a single font file can be used to generate characters of any size. Note, however, that bold and italic variations of the typeface are separate fonts. An alternative ASCII format, PFA, can also be used by FOCUS.
  • Adobe Font Metrics (AFM) file. This file is distributed with all Adobe fonts. It contains information about the size of each character in each font. FOCUS uses this information to lay out the report on the page. The three built-in fonts also have AFM files, which are distributed with FOCUS. However, these fonts do not require font files, since the fonts are built into Adobe Reader.

    Note: A Printer Font Metrics (PFM) file is also available. This file is used by applications, such as Adobe Reader, for laying out text, however, it is not supported by FOCUS. You must use the AFM file.

  • FOCUS Font Map files. These configuration files map the name of a font to the appropriate font metrics and font files (AFM and PFB or PFA). The mapping determines which actual font is used when you specify a font using the FONT attribute in a FOCUS StyleSheet. For example, if your StyleSheet contains the following declaration, FOCUS will search the font map for a font mapping with a matching name and style, and use the font specified by the mapping:
    TYPE=REPORT, FONT=HELVETICA, STYLE=ITALIC, $

Starting with Version 7.7, the separate font map files formerly used for each output format (such as PDF and PS) have been consolidated into two files in a new, XML-based format:

  • The default font map file, fontmap.xml, contains the font definitions for all output formats that are supported with FOCUS, as originally installed. Users should not modify this file.
  • The user font map file, fontuser.xml, contains font definitions added by the user. The following topics describe how to add your fonts to this file.

The user font map should be in a data set concatenated in front of the distributed ERRORS libraries so it is searched before the default font map. Then font definitions in the user map will override definitions of the same font in the default map.

You can also use a variety of utilities to convert Windows True Type fonts (such as Arial and Tahoma) into Type 1 fonts. Verify that you are licensed for this type of font use. Then, after you convert them, you can define and map these fonts for use by FOCUS.

One such utility is TTF2PT1.

For information about the Windows version, go to:

http://gnuwin32.sourceforge.net/packages/ttf2pt1.htm

Adding PostScript Type 1 Fonts for PS and PDF Formats

How to:

Reference:

This section describes how to add PostScript type 1 fonts to the fontuser.xml file.

Procedure: How to Configure Type 1 PostScript Fonts

After you have located the font files you wish to add, you can configure FOCUS to use one or more Type 1 fonts.

  1. Copy the AFM (font metrics) file into a PDS allocated to DDNAME ERRORS in the FOCUS JCL or CLIST. You can copy this file from another machine using FTP in standard ASCII (text) mode. The member name of the AFM file in this PDS will match the metricsfile value in the font map file.

    Note: If the Windows font file names contain underscore characters or are longer than eight characters, you must rename them, since these are not valid for z/OS member names.

  2. You can use either PFB (binary) fonts or PFA (ASCII) fonts:
    • If you are using PFB (binary) fonts, create a partitioned data set, put the PFB file in it (for example, using FTP in BINARY mode), and allocate this data set to DDNAME PFB.

      This PDS should be created with the following DCB attributes: 

      RECFM: VB    LRECL: 1028    BLKSIZE: 27998

      The member name in this PDS should match the fontfile name in the font map file.

      If you copy the PFB font file into the PDS using FTP, you must use BINARY mode. The member name of the PFB file in this PDS will match the fontfile value in the font map file.

    • If you are using PFA (ASCII) font files, create a PDS (separate from the one you use for PFB fonts), put the PFA file in it (for example, using FTP in regular, ASCII mode), and allocate this data set to DDNAME PFA. This PDS should be created with the following DCB attributes:
      RECFM: VB    LRECL: 2044    BLKSIZE: 27998

      The member name in this PDS should match the fontfile value in the font map file. Note that you can use PFB and PFA files simultaneously. The fonttype attribute in the font map file (PFB or PFA) tells FOCUS which PDS to search for the specified member name.

  3. The user font map file is in member FONTUSER in a data set allocated to DDNAME ERRORS. Using a text editor, add your font definition to the user font map using the syntax described in Add Fonts to the Font Map.

Syntax: How to Add Fonts to the Font Map

Starting with Version 7.7, the font map file has changed to a more compact XML-based syntax. A major difference from previous releases is that there is now only a single font map file for all output formats, not separate files for PDF and PostScript as before. Type 1 PostScript fonts can now be configured for both PDF and PostScript report formats by modifying a single user font map file, as described in this section.

The Type 1 PostScript fonts used with the PostScript and PDF output formats use separate font files for each variant of the font: normal, bold, italic, and bold-italic. This grouping of related fonts is called a font family. The example will use the family name Garamond.

The XML font map syntax uses two XML tags, <family> and <font>, to represent this structure. For example: 

<family name="garamond">
  <font style="normal"
        metricsfile="gdrg" fontfile="gdrg" fonttype="PFB" />
  <font style="bold"
        metricsfile="gdb"  fontfile="gdb" fonttype="PFB" />
  <font style="italic"
        metricsfile="gdi"  fontfile="gdi" fonttype="PFB" />
  <font style="bold+italic"
        metricsfile="gdbi" fontfile="gdbi" fonttype="PFB" />
</family>

The basics of the XML syntax are:

  • Tag names (such as family and font) and attribute names (such as style or metricsfile) must be in lowercase. Attribute values, such as font file names, are case-insensitive.
  • Attribute values (the text after the "=" sign) must be in double quotation marks (for example, "bold")
  • Elements that have no explicit end tag must end with />. (For example, the family tag has the closing tag </family>, but the font tag has no closing tag, so it ends with />.)
  • Comments are enclosed in special delimiters:
    <!-- This is a comment -->
  • Line breaks may be placed between attribute-value pairs.

A more complete description of XML syntax can be found here:

http://en.wikipedia.org/wiki/Xml

The family element

The family element specifies the name of a font family. This family name, specified in the name attribute of the family element, is the name by which the font will be referenced in a StyleSheet. It corresponds to the value of the FONT attribute in the StyleSheet. The end tag </family> closes the family element, and any number of additional family elements may follow.

Font family names should be composed of letters (A-Z, a-z), digits, and limited special characters: minus sign (-), underscore (_), and blank, and have a maximum length of 40 characters. Since the font name is only a reference to a mapping in the font map, it does not need to be related to the actual name of the font (which FOCUS obtains from the mapped AFM file) or the font file name.

Font elements

Nested within each family element are one or more font elements that specify the font files for each font in the family. For example, there may be one font element for the font Garamond Regular (normal), one for Garamond Italic (italic). Since a font element has no child elements, it is closed with "/>".

The actual name of the font as used in the PDF or PostScript document is taken from the font metric file.

Fonts defined in the user font file (fontuser.xml) can override default font definitions in fontmap.xml. Thus, you should be careful to choose family names that do not conflict with existing definitions, unless you actually wish to override these definitions (which should generally not be done).

Each font element contains the following attributes:

  • style. This attribute specifies the style of the font and corresponds to the STYLE attribute in the StyleSheet. The allowed values are "normal", "bold", "italic", and "bold+italic". For example, the font defined in the following bold italic font element:
    <font style="bold+italic" metricsfile="gdbi" fontfile="gdbi" fonttype="PFB" />

    could be referenced in the StyleSheet like this: 

    TYPE=REPORT, FONT=GARAMOND, STYLE=BOLD+ITALIC, $

    Although most fonts have a font file for each of the four styles, some specialized fonts, such as bar code fonts, might only have a single style (usually "normal"). Only the styles that exist for a particular font need to be specified in the font map file.

    The actual names of the fonts may vary. Some fonts may be called oblique rather than italic, or heavy rather than bold. However, the font map and StyleSheet always use the keywords normal, bold, italic and bold+italic.

  • metricsfile. This attribute specifies the name of the Adobe Font Metrics (AFM) file that provides the measurements of the font. The name refers to a member in a PDS allocated to DDNAME ERRORS. For information about file locations, see How to Configure Type 1 PostScript Fonts.

    File names should be composed of letters and numbers, and should not contain blanks.

  • fonttype. This attribute specifies the type of the font file. The allowed values are "PFA" or "PFB".
  • fontfile. This attribute specifies the name of the PFB or PFA file that contains the font itself. As with metricsfile, the value specifies a member in the appropriate PDS (the fonttype attribute specifies the type).

Additional items of XML syntax include the XML header on the first line of the file and the <fontmap> and <when> elements that enclose all of the family elements. These tags are essential to the XML font map syntax and should not be modified. In particular, the <when> tag allows the same font mappings to be used for both PDF and PostScript reports.

The following is a complete example of a user font map: 

<?xml version="1.0" encoding="UTF-8" ?>
<!-- Example of a user font map file with two font families. -->
<fontmap version="1">
    <when format="PDF PS">
        <family name="garamond">
            <font style="normal"
                  metricsfile="gdrg" fontfile="gdrg" fonttype="PFB" />
            <font style="bold"
                  metricsfile="gdb"  fontfile="gdb"  fonttype="PFB" />
            <font style="italic"
                  metricsfile="gdi"  fontfile="gdi"  fonttype="PFB" />
            <font style="bold+italic"
                  metricsfile="gdbi" fontfile="gdbi" fonttype="PFB" />
        </family>
        <!-- This font only has a "normal" style, others omitted. -->
        <family name="ocra">
            <font style="normal"
                  metricsfile="ocra" fontfile="ocra" fonttype="PFB" />
        </family>
    </when>
</fontmap>
Example: FOCUS StyleSheet Declaration

Once the font map files have been set up, the newly mapped fonts can be used in a FOCUS StyleSheet. For example, to use the Garamond fonts: 

ON TABLE SET STYLE *
type=report, font=garamond, size=12, $
type=title, font=garamond, style=bold, color=blue, $
ENDSTYLE

Since the style attribute has been omitted for the report font in the StyleSheet, it defaults to normal. Attributes, such as size and color, can also be applied.

Reference: Editing the Font Map File

There is a byte order mark (BOM) at the beginning of the user font map file (fontuser.xml), which must be preserved for this file to be read correctly.

If you are using a Unicode-aware editor, such as Notepad on Windows, to edit the file, the BOM will not be visible, but you can preserve it by making sure that you select an encoding of "UTF-8" in the Save As dialog. In most other editors, such as the ISPF editor under z/OS, the BOM will display as three or four strange-looking characters at the beginning of the file. As long as you do not delete or modify these characters, the BOM will be preserved.

Reference: The FOCUS Default Font Map

Since the user font map is searched before the FOCUS default font map, font definitions in the user font map file will override mappings of the same font in the default font map file. Since you usually would not want to override existing font mappings, you can check which font names are already used by FOCUS by examining the default font map file.

It is in the FONTMAP member of a partitioned data set allocated to DDNAME ERRORS. Unlike the user font map file, this file has separate sections containing definitions for PS, PDF and DHTML formats. (The DHTML mappings are used for the DHTML and PowerPoint output formats, which do not support user-added fonts.)

Since the font mappings in the default font map file are for fonts that are already assumed to exist on the user machines (for example, built-in Adobe Reader fonts, standard PostScript printer fonts, or standard Windows fonts), they do not reference font files, only font metrics files. Fonts provided by the user should reference both font files and metrics files.

AFM files for the default fonts are also members of a PDS allocated to DDNAME ERRORS.

Information Builders