x

[ON TABLE] HOLD [AS filename] FORMAT EXL2K [PIVOT] [FORMULA]

where:

EXL2K

Creates an Excel-formatted output file that may include styling based on internal or external StyleSheets features. The file type on VM is XHT; the extension on Windows platforms is .xht;

PIVOT

Creates an output file in Excel PivotTable format with an accompanying PivotTable cache file. The filetype of the Pivot Table file is XML; the extension on Windows platforms is .xml. For more information about this option, see Using the Excel 2000 PIVOT Option .

FORMULA

Creates an XHT output file including appropriate Excel formulas for all FOCUS numeric summary operations. For more information about this option, see Using the Excel 2000 Formula Option.

Top of page

Example: Creating an EXL2K Output File

This example shows how to create a styled report in EXL2K format, with conditional styling based on the contents of CENTORD:

TABLE FILE CENTORD
HEADING    
"LINE COST BY STATE"
SUM LINE_COGS AS 'Cost'
  BY STATE AS 'State'
BY PLANTLNG AS 'Plant'
BY STORENAME AS 'Store Name'
  WHERE TOTAL LINE_COGS GT 10000000
  ON TABLE HOLD AS EXL2K1 FORMAT EXL2K
ON TABLE SET STYLE *
TYPE=REPORT, FONT=ARIAL, TITLETEXT=SALES REPORT, $
TYPE=DATA, COLUMN=LINE_COGS, COLOR=RED, BACKCOLOR=YELLOW,
 WHEN=LINE_COGS GT 20000000,$
TYPE=DATA, COLUMN=STORENAME, BACKCOLOR=YELLOW,
WHEN=LINE_COGS GT 20000000,$
TYPE=HEADING, FONT=ARIAL BLACK, COLOR=RED, BACKCOLOR=SILVER, SIZE=16, $
TYPE=TITLE, FONT=ARIAL, SIZE=12, $
ENDSTYLE   
END

The output is:

You can also adjust column contents in your Excel spreadsheets using the StyleSheets keywords WRAP (for wrapping column contents) and SQUEEZE (for truncating columns).

x

Excel 2000 users can select one of six languages as their default language when generating EXL2K formatted output. In addition to English, which is the automatic default, you can select one of five other options

SET EXL2KLANG=lang

where:

lang

Is one of the following: AME, FRE, SPA, GER, JPN or KOR.

You can code the SET EXL2KLANG in your user profile or include it in a FOCEXEC to override the default setting in the NLSCFG ERRORS file for a specific request.

SET PAGE-NUM=OFF
TABLE FILE CENTORD
"Line Total Report"
"Excel 2000 Spreadsheet"
" "
SUM LINEPRICE
BY STATE AS 'State'
BY PLANTLNG AS 'Plant'
BY STORENAME AS 'Store Name'
WHERE TOTAL LINEPRICE FROM 9000000 TO 20000000
ON TABLE SET BYDISPLAY ON
  ON TABLE HOLD AS EXL2K2 FORMAT EXL2K
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, FONT=TAHOMA, $
TYPE=HEADING, SIZE=14, COLOR=NAVY, $
TYPE=HEADING, LINE=2, SIZE=12, COLOR=RED, $
TYPE=TITLE, JUSTIFY=CENTER, STYLE=BOLD, $
TYPE=DATA, JUSTIFY=CENTER, $
TYPE=DATA, COLUMN=LINEPRICE, JUSTIFY=RIGHT, $
END

SET PAGE-NUM=OFF
DEFINE FILE EMPLOYEE
YRHIRED/YY = HIRE_DATE;
MHIRED/MtYY = HIRE_DATE;
TOTSVC/I4C = 2002 - YRHIRED;
END
TABLE FILE EMPLOYEE
"Employee Service Report for 2002"
"Excel 2000 Spreadsheet"
" "
PRINT FIRST_NAME AS 'First Name'
MHIRED AS 'Month Hired'
TOTSVC AS 'Years of Service'
BY LAST_NAME AS 'Last Name'
ON TABLE SET BYDISPLAY ON
ON TABLE HOLD AS EXL2K3 FORMAT EXL2K
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, FONT=TAHOMA, $
TYPE=HEADING, SIZE=14, COLOR=NAVY, $
TYPE=HEADING, LINE=2, SIZE=12, COLOR=RED, $
TYPE=TITLE, JUSTIFY=CENTER, STYLE=BOLD, $
TYPE=DATA, JUSTIFY=CENTER, $
TYPE=DATA, COLUMN=TOTSVC, COLOR=BLUE, WHEN=TOTSVC GT 20, $
END

x

Warning: The following formats are not supported in EXL2K and may produce unpredictable results when translated into Excel's General Format:

• YY, Y, M, D, JUL, and I2MT.
• Any date format with a Q (quarter).
• Any packed-decimal (P) date formats.
• Any alphanumeric (A) date formats.
• Fixed Dollar (N) formats.
• Multiple format options. Only single format options are supported when using FORMAT EXL2K. For example, the formats I9C and I9B are supported, but I9BC is not.

The following applies to headings and footings with embedded numeric fields:

• If you embed a numeric field in a heading, subheading, footing, or subfooting of a report in Excel 2000 or higher format, the numeric field displays in Excel general format (text). To display a numeric field in Excel number format, you must set HEADALIGN=BODY in the StyleSheet.

x

In order to use a "-" as a separator between month, day, and year in Excel, you must change the default date separator for Windows®. This setting can be located under Regional Options in the Control Panel.

x

TYPE=REPORT, [COLUMN=column,] WRAP=value, $

where:

column

Identifies a particular column. If COLUMN is not included in the declaration, the column width specified with SQUEEZE is applied to the entire report.

value

Is one of the following:

ON

Turns on data wrapping. ON is the default. With this setting, the column width is determined by the client (Excel). Data wraps if it exceeds the width of the column and the row’s height expands to meet the new height of the wrapped data.

OFF

Turns off data wrapping. This setting adjusts the column width of the largest data value in the column. Data will not wrap in any cell in the column.

n

Represents a specific numeric value for the column width. The value represents the measure specified with the UNITS parameter (the default is inches). This is the most commonly used SQUEEZE setting in an Excel 2000 report.

x

TYPE=REPORT, [COLUMN=column,] SQUEEZE=n, $

where:

column

Identifies a particular column. If COLUMN is not included in the declaration, the column width specified with SQUEEZE is applied to the entire report.

n

Represents a specific numeric value for the column width. The value represents the measure specified with the UNITS parameter (the default is inches). This is the most commonly used SQUEEZE setting in an Excel 2000 report.

Note: SQUEEZE=(ON/OFF), which turns data wrapping on and off, is not supported for EXL2K, so if a data value is wider than the specified column width, it will be hidden from view. However, you can adjust column widths in Excel after you generate a spreadsheet.

Top of page

Example: Controlling Column Width and Wrapping in Excel 2000

The following example illustrates how to turn on and turn off data wrapping in a column and how to set the column width for a particular column. The UNITS in this example are set to inches (the default).

DEFINE FILE CENTORD
MYDATE/MDY='10/22/60';
RCD/D14.3=LINE_COGS;
VERYLONG/A80='Multiply quantity times line_cost to'|
' calculate line_cost_of_goods';
END
TABLE FILE CENTORD
SUM   MYDATE RCD
VERYLONG AS 'Default' VERYLONG AS 'WRAP=OFF'
VERYLONG AS 'WRAP=4.1' VERYLONG AS 'WRAP=2'
VERYLONG AS 'SQUEEZE=2' LINE_COGS
BY REGION AS 'Region'
  ON TABLE HOLD AS EXL2K4 FORMAT EXL2K
ON TABLE SET STYLE *
TYPE=DATA, COLUMN=MYDATE, JUSTIFY=CENTER, $
1. TYPE=REPORT, COLUMN=VERYLONG(2), WRAP=OFF, $
2. TYPE=REPORT, COLUMN=VERYLONG(3), WRAP=4.1, $
3. TYPE=REPORT, COLUMN=VERYLONG(4), WRAP=2, $
4. TYPE=REPORT, COLUMN=VERYLONG(5), SQUEEZE=2, $
END

where:

1. Identifies the column titled "WRAP=OFF" and turns off data wrapping for that column.
2. Identifies the column titled "WRAP=4.1" and sets the column width to 4.1 inches with data wrapping on.
3. Identifies the column titled "WRAP=2" and sets the column width to 2 inches with data wrapping on.
4. Identifies the column titled "SQUEEZE=2" and sets the column width to 2 inches with data wrapping off.

Note: The column titled "Default" illustrates the default column width and wrapping behavior.

Since the output is wider than this page, it is shown in two sections. The following output displays the "Default", "WRAP=OFF", and "WRAP=4.1" columns:

The following output displays the "WRAP=2", and "SQUEEZE=2" columns:

x

To enable locking, use the following attributes:

TYPE=REPORT, PROTECTED={ON|OFF}, [LOCKED={ON|OFF}],$

where:

TYPE=REPORT, PROTECTED=ON

Is necessary to enable spreadsheet locking. PROTECTED=OFF is the default. If you omit the LOCKED=OFF attribute, the entire spreadsheet is locked.

LOCKED=ON

Locks the entire spreadsheet. ON is the default value.

LOCKED=OFF

Unlocks the spreadsheet as a whole, but enables you to lock or unlock specific cells or groups of cells.

x

Once you include the following declaration in your StyleSheet, you can specify the LOCKED attribute for specific cells or groups of cells:

TYPE=REPORT, PROTECTED=ON, LOCKED=OFF,$

To lock specific parts of the spreadsheet, add the LOCKED=ON attribute to the StyleSheet declaration for the cells you want to lock.

TYPE=type, [ COLUMN=columnspec ] ,LOCKED={ON|OFF},$

where:

type

Is the type of element that describes the cells to be locked.

columnspec

Is a valid column specification.

Top of page

Example: Locking an Entire Excel Spreadsheet

The following request locks the entire spreadsheet because the StyleSheet declarations include the following declaration:

TYPE=REPORT, PROTECTED=ON, $

The request is:

TABLE FILE CAR                                    
HEADING                                           
"Profit By Car "                                  
" "                                               
SUM RETAIL_COST AND DEALER_COST AND                                 
COMPUTE PROFIT/D12.2 = RETAIL_COST - DEALER_COST;                   
BY CAR                                            
ON TABLE SET PAGE-NUM OFF                         
ON TABLE PCHOLD AS EXLFORM1 FORMAT EXL2K          
ON TABLE SET STYLE *                              
TYPE=REPORT, COLOR=BLUE, BACKCOLOR=SILVER, SIZE=9,$
TYPE=REPORT, PROTECTED=ON,            $           
TYPE=HEADING, STYLE=BOLD, SIZE=14, $              
TYPE=TITLE, STYLE=BOLD, SIZE=11,$                 
ENDSTYLE                                          
END                                               

You cannot edit any value on the spreadsheet. Any attempt to do so displays a message that the sheet is protected:

Top of page

Example: Locking a Single Column on an Excel Spreadsheet

The following request locks the second column (RETAIL_COST) because the StyleSheet declarations include the following declarations

TYPE=REPORT, PROTECTED=ON, LOCKED=OFF, $
TYPE=DATA, COLUMN=2, LOCKED=ON,$

The request is:

TABLE FILE CAR                                     
HEADING                                            
"Profit By Car "                                   
" "                                                
SUM RETAIL_COST AND DEALER_COST AND                                  
COMPUTE PROFIT/D12.2 = RETAIL_COST - DEALER_COST;                    
BY CAR                                             
ON TABLE SET PAGE-NUM OFF                          
ON TABLE PCHOLD AS EXLFORM2 FORMAT EXL2K           
ON TABLE SET STYLE *                               
TYPE=REPORT, COLOR=BLUE, BACKCOLOR=SILVER, SIZE=9,$
TYPE=REPORT, PROTECTED=ON, LOCKED=OFF,$            
TYPE=HEADING, STYLE=BOLD, SIZE=14, $               
TYPE=TITLE, STYLE=BOLD, SIZE=11,$                  
TYPE=DATA, COLUMN=2, LOCKED=ON,$                   
ENDSTYLE                                           
END                                                

You cannot edit any value in column 2, although you can edit values in other columns. Any attempt to edit a value in column 2 displays a message that the cells are protected:

x

Add the following syntax to your request to include Excel formulas in your spreadsheet:

ON TABLE HOLD FORMAT EXL2K FORMULA

Top of page

Example: Generating Native Excel Formulas for Column Totals

The following example illustrates the translation of a column total in a report request into an Excel formula when using format EXL2K FORMULA. Note that the formatting of the column total (TYPE=GRANDTOTAL) is retained in the Excel 2000 spreadsheet.

When you select the total in the report, the equation =SUM(B4:B7) appears in the formula bar, representing the column total as a sum of cell ranges.

TABLE FILE CENTORD
HEADING
"Projected Return By Region"
" "
SUM LINE_COGS AS 'RETURN'
BY REGION AS 'REGION'
ON TABLE COLUMN-TOTAL
ON TABLE HOLD AS EXL2K5 FORMAT EXL2K FORMULA
ON TABLE SET STYLE *
TYPE=REPORT, COLOR=BLUE, BACKCOLOR=SILVER, SIZE=9,$
TYPE=HEADING, STYLE=BOLD, SIZE=14,$
TYPE=TITLE, STYLE=BOLD+UNDERLINE, SIZE=10,$
TYPE=GRANDTOTAL, STYLE=BOLD,$
ENDSTYLE
END

The output is:

Top of page

Example: Generating Native Excel Formulas for Row Totals

This request calculates totals for line price and quantity across regions. The row totals are represented as sums of cell ranges.

TABLE FILE CENTORD
HEADING
"Projected Line Cost Across Region"
" "
SUM LINEPRICE        AND QUANTITY
ACROSS REGION AS 'Region'
BY STORENAME
WHERE REGION EQ 'EAST' OR 'NORTH'
ON REGION ROW-TOTAL AS 'TOTAL'
ON TABLE COLUMN-TOTAL AS 'TOTAL'
ON TABLE HOLD AS EXL2K6 FORMAT EXL2K FORMULA
ON TABLE SET STYLE *
TYPE=REPORT, COLOR=BLUE, BACKCOLOR=SILVER, SIZE=9,$
TYPE=HEADING, STYLE=BOLD, SIZE=14,$
TYPE=TITLE, STYLE=BOLD, SIZE=11,$
TYPE=SUBTOTAL, STYLE=BOLD, $
TYPE=GRANDTOTAL, STYLE=BOLD, SIZE=11,$
TYPE=ACROSSTITLE, STYLE=BOLD, SIZE=11, JUSTIFY=LEFT,$
TYPE=ACROSSVALUE, STYLE=BOLD, SIZE=10, JUSTIFY=CENTER,$
ENDSTYLE
END

The output highlights the formula that calculates the row total in cell G11=C11+E11:

Top of page

Example: Generating Native Excel Formulas for Calculated Values

This request totals the columns for line price and quantity and calculates the value of a field called LINECOST by multiplying the line price by the quantity.

The formula for the calculated values is generated by translating the internal form of the FOCUS expression (COMPUTE LINECOST/P24.2MC=QUANTITY*LINEPRICE;) into an Excel formula.

TABLE FILE CENTORD
ON TABLE SET PAGE-NUM OFF
SUM LINEPRICE AND QUANTITY AS 'Quantity'
COMPUTE LINECOST/P24.2MC = QUANTITY * LINEPRICE; AS 'Total Cost'
BY REGION AS 'Region'
HEADING
"Line Cost of Goods by Region"
" "
ON TABLE COLUMN-TOTAL
  ON TABLE HOLD AS EXL2K7 FORMAT EXL2K FORMULA
ON TABLE SET STYLE *
TYPE=REPORT, COLOR=BLUE, BACKCOLOR=SILVER, SIZE=9,$
TYPE=HEADING, STYLE=BOLD, SIZE=14,$
TYPE=TITLE, STYLE=BOLD, SIZE=11,$
TYPE=GRANDTOTAL, STYLE=BOLD, SIZE=11,$
ENDSTYLE
END

The top line in the spreadsheet output highlights the formula.

Top of page

Example: Generating Native Excel Formulas for Functions

The following illustrates how functions are translated to Excel 2000 reports. The function IMOD divides ACCTNUMBER by 1000 and returns the remainder to LAST3_ACCT. The Excel formula corresponds to this, =(MOD($C2,1000)).

TABLE FILE EMPLOYEE
PRINT ACCTNUMBER AS 'Account Number' AND COMPUTE
  LAST3_ACCT/I3L = IMOD(ACCTNUMBER, 1000, LAST3_ACCT);
BY LAST_NAME AS 'Last Name' BY FIRST_NAME AS 'First Name'
WHERE (ACCTNUMBER NE 000000000) AND (DEPARTMENT EQ 'MIS')
  ON TABLE HOLD AS EXL2K8 FORMAT EXL2K FORMULA
ON TABLE SET STYLE *
TYPE=TITLE, SIZE = 12, STYLE=BOLD,$
END

The output is:

x

• All standard operators are supported. These include arithmetic operators, relational operators, string operators, IF/THEN/ELSE, and logical operators.
• The IS-MISSING operator is not supported.
• The following functions are supported: ABS, ARGLEN, ATODBL, BYTVAL, CHARGET, CTRAN, CTRFLD, DECODE DMOD, DOWK, DOWKI, DOWKL, DOWKLI, EDIT (1 argument variant only), EXP, EXPN, FMOD, HEXBYT, HHMMSS, IMOD, INT, LCWORD, LJUST, LOCASE, LOG, MAX, MIN, OVRLAY, POSIT, RDUNIF, RANDOM, RJUST, SQRT, SUBSTR, TODAY, TODAYI, UPCASE, YM.
• If you use the COMPUTE command with an unsupported function, an error message appears.
• EXL2K FORMULA is not supported with the following FOCUS commands and phrases:
  • DEFINE.
  • OVER.
  • NOPRINT. If your report contains a calculated value (generated by the COMPUTE command), all of the fields referenced by the calculated value must be displayed in the report.
  • Multiple display (PRINT, LIST, SUM, and COUNT) commands.
  • SEQUENCE.
  • RECAP.
  • Prefix operators.
• Formulas for ROW-TOTALs and COLUMN-TOTALs are represented as sums of cell ranges. For example, =SUM(G2:G10).
• A formula for a calculated value is generated by translating the internal form of the FOCUS expression into an Excel formula.
• Excel formulas are limited to 1024 characters. Therefore, the translation to an Excel formula will fail if a FOCUS total or the result of an expression requires more than 1024 bytes of Excel formula code. An error message will be generated.
• Conditional styling is based on the values in the original report. If the spreadsheet values are changed and the formulas are recomputed, the styling will not reflect the updated information.

x

ON TABLE HOLD FORMAT EXL2K PIVOT AS mypivot 

where:

mypivot

Is a name you assign to the HOLD file.

Two files are generated with this syntax:

• MYPIVOT.XHT is the main PivotTable spreadsheet file that will be displayed in the browser or Excel window.
• MYPIVOT$.XML is the Pivot Cache file.

Standard HOLD and SAVE syntax is supported for EXL2K PIVOT. The PivotTable cache file contains all the fields specified in the procedure and links internally to the PivotTable file. All available fields can be viewed in the PivotTable toolbar.

x

Keep these considerations in mind when preparing output for a PivotTable:

• Requests must include the display command PRINT. This is necessary to extract all data (each individual record) into the pivot cache file. The field following the PRINT command is designated the PivotTable Data field and must be numeric. Note that the SUM and COUNT commands are not supported for EXL2K PIVOT. Nevertheless, you can use this native PivotTable behavior in Excel once the data has been output to an Excel PivotTable.
• Fields used in a PivotTable cannot appear more than once in the report request. Each field can function in only one role in a FORMAT EXL2K PIVOT request. For example, a Page field cannot appear simultaneously as a column and a row field.
• Styling is based on the initial state of your report and is retained only on a limited basis when you pivot the output.
• NODATA fields appear as blank cells in the PivotTable.
• You can include column, row, grand totals, and subtotals in a PivotTable. If no totals are specified in a request, no totals will display in Excel 2000.

Top of page

Example: Using the EXL2K PIVOT Option

This simple example shows how to populate and generate PivotTables:

TABLE FILE CENTINV
HEADING
"CENTINV File PivotTable"
"Sum of Price by Product Across Category"
PRINT PRICE
BY PROD_NUM
ACROSS PRODCAT
ON TABLE COLUMN-TOTAL
  ON TABLE HOLD AS EXL2K9 FORMAT EXL2K PIVOT
  PAGEFIELDS PRODNAME
  CACHEFIELDS COST QTY_IN_STOCK
ON TABLE SET STYLE *
TYPE=HEADING, LINE=1, FONT='ARIAL', COLOR=PURPLE, SIZE=16, STYLE=BOLD,$
TYPE=HEADING, LINE=2, FONT='ARIAL', COLOR=PURPLE, SIZE=12, STYLE=BOLD,$
TYPE=DATA, FONT='ARIAL', COLOR=PURPLE,$
TYPE=GRANDTOTAL, FONT='ARIAL', COLOR=PURPLE, SIZE=12, STYLE=BOLD,$
ENDSTYLE
END

The output is:

x

The PivotTable is generated by the PRINT command in combination with the BY, ACROSS, PAGEFIELDS, and CACHEFIELDS phrases. It contains all options used to design and format the report, as well as fields specified in the PIVOT request. Fields can be dragged into the report from the toolbar. The following graphic and summary table depicts PivotTable output with major elements and associated FOCUS syntax.

x

This table summarizes the effects of TABLE request syntax elements within EXL2K PIVOT operations. You must include at least one sort field or a PAGEFIELD to have a valid Pivot Table request.

Syntax Element	Usage	Effect on PivotTable
PRINT	Required	Designates the data field in a PivotTable.
BY	Optional	Designates row field in a PivotTable.
ACROSS	Optional	Designates a column field in a PivotTable.
CACHEFIELDS	Optional	Places fields in the Pivot cache file and makes them available from the Pivot toolbar.
PAGEFIELDS	Optional	Designates a Page field in a PivotTable.

x

PivotTable Element	Contains...	Function	Generating Syntax
Page field	Field that controls view of the entire page (worksheet).	A filtering mechanism to conduct a high level sort.	PAGEFIELDS phrase
Page field item	The value for a page field item appears in a drop-down list.	Selecting a page field item summarizes data for the entire report.	PAGEFIELDS phrase
Data field	Numeric data that is available to be summarized	Holds data available to be summarized	PRINT command
Column field	Horizontal sort data	Sorts data horizontally.	ACROSS command
Row field	Vertical sort data	Sorts data vertically.	BY command

x

Fields designated as CACHEFIELDS must immediately follow the PIVOT keyword in the ON TABLE HOLD FORMAT EXL2K PIVOT syntax or follow a PAGEFIELDS phrase. A CACHEFIELD cannot be designated elsewhere in the request. Lists of CACHEFIELDS are terminated by the same keywords that terminate normal report requests, such as END or another ON phrase.

Top of page

Example: Using CACHEFIELDS With EXL2K PIVOT

This example shows how to specify CACHEFIELDS to populate the PivotTable toolbar.

TABLE FILE CENTINV
HEADING
"PivotTable with CACHEFIELDS"
"Sum of Price by Product Across Category"
PRINT PRICE
BY PRODCAT BY PRODTYPE
ON PRODCAT SUB-TOTAL
  ON TABLE HOLD AS EXL2K10 FORMAT EXL2K PIVOT
  CACHEFIELDS COST PRODNAME
ON TABLE SET STYLE *
TYPE=DATA, COLUMN=PRODCAT, COLOR=RED,$
TYPE=HEADING, COLOR=BLUE, STYLE=BOLD, SIZE=14,$
ENDSTYLE
END

The output is:

x

To create Excel Named Ranges, use

TYPE=type, IN-RANGES=rangename, $

where:

type

Identifies the FOCUS report component to be included in the range. Normally, both of the following are used together:

DATA adds the DATA element of the report to the named range (excludes heading, footing, and column titles).

TITLE adds the TITLE element of the report to the named range (includes all column titles).

Note: Multiple elements can be added to the same named range.

rangename

Is the name assigned to the output in the Excel workbook your application is creating, and is also the name that will be referenced by other FOCUS applications.

Top of page

Example: Using Excel Named Ranges

This example creates one report in one worksheet of an Excel workbook. The code specific to Excel Named Ranges appears in bold in the following syntax.

TABLE FILE GGSALES
PRINT
     PRODUCT
     DATE
     UNITS
BY REGION
BY DOLLARS
ON TABLE SET PAGE-NUM OFF
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
ON TABLE HOLD FORMAT EXL2K
ON TABLE SET STYLE *
     UNITS=IN, SQUEEZE=ON, ORIENTATION=PORTRAIT, $
TYPE=REPORT, FONT='ARIAL', SIZE=9, COLOR='BLACK', BACKCOLOR='NONE',
     STYLE=NORMAL, $ 
TYPE=DATA, IN-RANGES='RegionalSales', $
TYPE=TITLE, STYLE=BOLD, IN-RANGES='RegionalSales', $ 
ENDSTYLE
END

The Excel output is:

The name assigned to this Excel Named Range is RegionalSales. If additional rows of data are added, or columns of data are inserted, the named range will stretch to contain both new and existing data.

x

• The Excel data area associated with a named range must be continuous and cannot contain any breaks in the data. Examples of report components containing breaks in the data that cannot be part of a named range include SUBHEAD and SUBFOOT.
• It is recommended that you use ON TABLE SET BYDISPLAY ON. This activates the option to display repeated sort values, which produces continuous output with no breaks in the data.
• Two different worksheets from the same workbook cannot have the same range name.
• When creating Compound Excel reports (multiple TABLE requests output to the same Excel workbook), each report must have a unique range name that is stored at the workbook level.

x

Excel Named Ranges are supported for the following Excel formats:

EXL2K, EXL2K FORMULA

Excel Named Ranges are not supported for the following Excel formats:

EXL2K BYTOC, EXCEL PIVOT

Excel Named Ranges are not supported with any report syntax that produces discontinuous data or uses columnar references that span multiple columns, which includes the following:

ACROSSCOLUMN, RECAP, RECOMPUTE, SUBHEAD, SUBFOOT, SUBTOTAL, SUB-TOTAL

x

Outside of a report request, use the following syntax

SET NODATA = character_string 
SET EMPTYCELLS = [ON|OFF]

In a report request, use the following syntax

ON TABLE SET NODATA character_string 
ON TABLE SET EMPTYCELLS [ON|OFF]

where:

character_string

Is the string of characters displayed in the cells of the report for each field where null values are retrieved from the database. The maximum number of characters is 11. If the number of characters in the string exceeds the length of the output field, the additional characters will not be displayed. If special characters are used, the string must be enclosed in single quotes. SET EMPTYCELLS OFF must also be specified to make the SET NODATA command effective.

ON

Indicates that empty spaces are displayed in the cells of the report for each field where null values are retrieved from the database. ON is the default.

OFF

Indicates that zeros, or the character string specified with the SET NODATA command, will be displayed in the cells of the report for each field where null values are retrieved from the database. OFF must be specified when using SET NODATA.

Top of page

Example: Identifying Null Values in EXL2K Report Output

The following syntax utilizes the default behavior of ON TABLE SET EMPTYCELLS ON, which is set in the background:

TABLE FILE CAR   
SUM SALES BY COUNTRY ACROSS SEATS   
ON TABLE HOLD FORMAT EXL2K
ON TABLE SET STYLE *
TYPE = ACROSSTITLE, STYLE=BOLD,$TYPE = TITLE, STYLE = BOLD,$   
ENDSTYLE                        
END

The following output displays empty spaces in the cells of the report for each field where null values are retrieved from the database:

The following syntax utilizes the SET NODATA command:

TABLE FILE CAR   
SUM SALES BY COUNTRY ACROSS SEATS   
ON TABLE SET NODATA 'n/a'   
ON TABLE SET EMPTYCELLS OFF   
ON TABLE HOLD FORMAT EXL2K
ON TABLE SET STYLE *
TYPE = ACROSSTITLE, STYLE=BOLD,$TYPE = TITLE, STYLE = BOLD,$    
ENDSTYLE                        
END

Note: If you do not add SET EMPTYCELLS OFF, the SET NODATA command will be ignored.

The following output displays 'n/a' in the cells of the report for each field where null values are retrieved from the database:

The following syntax turns off the default SET EMPTYCELLS behavior and does not use SET NODATA, which makes it impossible to distinguish null values from zero quantities:

TABLE FILE CAR   
SUM SALES BY COUNTRY ACROSS SEATS   
ON TABLE SET EMPTYCELLS OFF   
ON TABLE HOLD FORMAT EXL2K
ON TABLE SET STYLE *
TYPE = ACROSSTITLE, STYLE=BOLD,$TYPE = TITLE, STYLE = BOLD,$    
ENDSTYLE                        
END

The following output displays zeros in the cells of the report for each field where either null values are retrieved from the database or the quantity is zero:

x

Only a single BY field is allowed in an EXL2K Table of Contents report.

ON TABLE HOLD FORMAT EXL2K BYTOC

Since only one level of TOC is allowed for EXL2K reports, the optional number following the BYTOC keyword can only be 1.

The SET COMPOUND syntax, which precedes the TABLE command, may also be used to specify that a TOC be created:

SET COMPOUND=BYTOC

Since a TOC report is burst into worksheets according to the value of the first BY field in the report, the report must contain at least one BY field. The bursting field may be a NOPRINT field.

x

• The worksheet tab names are the BY field values that correspond to the data on the current worksheet. If the user specifies the TITLETEXT keyword in the stylesheet, it will be ignored.
• Excel limits the length of worksheet titles to 31 characters. The following special characters cannot be used: ':', '?', '*', and '/'.

x

• A TOC report cannot be embedded in a compound report.
• A TOC report cannot be a pivot table report.

Top of page

Example: Creating a Simple TOC Report

SET COMPOUND=BYTOC
TABLE FILE CAR
PRINT SALES BY COUNTRY NOPRINT BY CAR
ON TABLE HOLD FORMAT EXL2K
ON TABLE SET STYLE *
type=report, style=bold, color=yellow, backcolor=black, $
type=data, backcolor=red, $type=data, column=car, color=blue, backcolor=yellow, $
END

The output is:

x

As with PDF, the keywords OPEN, CLOSE, and NOBREAK are used to control Excel compound reports. They can be specified with the HOLD command or with a separate SET COMPOUND command.

• OPEN is used on the first report of a sequence of component reports to specify that a compound report be produced.
• CLOSE is used to designate the last report in a compound report.
• NOBREAK specifies that the next report be placed on the same worksheet as the current report. If it is not present, the default behavior is to place the next report on a separate worksheet.

  NOBREAK may appear with OPEN on the first report, or alone on a report between the first and last reports. (Using CLOSE is irrelevant, since it refers to the placement of the next report, and no report follows the final report on which CLOSE appears.)

• When used with the HOLD syntax, the compound report keywords OPEN, CLOSE, and NOBREAK must appear immediately after FORMAT EXL2K, and before any additional keywords, such as FORMULA or PIVOT. For example, you can specify:
  • ON TABLE HOLD FORMAT EXL2K OPEN
  • ON TABLE HOLD AS MYHOLD FORMAT EXL2K OPEN NOBREAK
  • ON TABLE HOLD FORMAT EXL2K NOBREAK FORMULA
  • ON TABLE HOLD FORMAT EXL2K CLOSE PIVOT PAGEFIELDS COUNTRY
• As with PDF compound reports, compound report keywords can be alternatively specified using SET COMPOUND:
  • SET COMPOUND = OPEN
  • SET COMPOUND = 'OPEN NOBREAK'
  • SET COMPOUND = NOBREAK
  • SET COMPOUND = CLOSE

x

• Pivot Tables and NOBREAK. Pivot Table Reports may appear in compound reports, but they may not be combined with another report on the same worksheet using NOBREAK.
• Naming of Worksheets. The default worksheet tab names will be Sheet1, Sheet2, and so on. You have the option to specify a different worksheet tab name by using the TITLETEXT keyword in the stylesheet. For example:

  TYPE=REPORT, TITLETEXT='Summary Report', $

  Excel limits the length of worksheet titles to 31 characters. The following special characters cannot be used: ':', '?', '*', and '/'.

• File Names and Formats. The output file name (AS name, or HOLD by default) is obtained from the first report of the compound report (the report with the OPEN keyword). Output file names on subsequent reports are ignored.

  The HOLD FORMAT syntax used in the first component report in a compound report applies to all subsequent reports in the compound report, regardless of their format.

• NOBREAK Behavior. When NOBREAK is specified, the following report appears on the row immediately after the last row of the report with the NOBREAK. If additional spacing is required between the reports, a FOOTING or an ON TABLE SUBFOOT can be placed on the report with the NOBREAK, or a HEADING or an ON TABLE SUBHEAD can be placed on the following report. This allows the most flexibility, since if blank rows were added by default there would be no way to remove them.

Top of page

Example: Creating a Simple Compound Report Using EXL2K

SET PAGE-NUM=OFF
TABLE FILE CAR
HEADING
"Sales Report"
" "
SUM SALES
BY COUNTRY
ON TABLE SET STYLE *
type=report, titletext='Sales Rpt', $
type=heading, size=18, $
ENDSTYLE
ON TABLE HOLD AS EX1 FORMAT EXL2K OPEN
END

TABLE FILE CAR
HEADING
"Inventory Report"
" "
SUM RC
BY COUNTRY
ON TABLE SET STYLE *
type=report, titletext='Inv. Rpt', $
type=heading, size=18, $
ENDSTYLE
ON TABLE HOLD FORMAT EXL2K
END

TABLE FILE CAR
HEADING
"Cost of Goods Sold Report"
" "
SUM DC
BY COUNTRY
ON TABLE SET STYLE *
type=report, titletext='Cost Rpt', $
type=heading, size=18, $
ENDSTYLE
ON TABLE HOLD FORMAT EXL2K CLOSE
END

The output for each tab in the Excel worksheet is:

Top of page

Example: Creating a Compound Report With Pivot Tables and Formulas

SET PAGE-NUM=OFF
TABLE FILE CAR
HEADING
"Sales Report"
" "
PRINT RCOST
BY COUNTRY
ON TABLE SET STYLE *
type=report, titletext='Sales Rpt', $
type=heading, size=18, $
ENDSTYLE
ON TABLE HOLD AS PIV1 FORMAT EXL2K OPEN
END

TABLE FILE CAR
HEADING
"Inventory Report"
" "
PRINT SALES
BY COUNTRY
ON TABLE SET STYLE *
type=report, titletext='Inv. Rpt', $
type=heading, size=18, $
ENDSTYLE
ON TABLE HOLD AS PPPP FORMAT EXL2K PIVOT
PAGEFIELDS TYPE SEATS
CACHEFIELDS MODEL MPG RPM 
END

TABLE FILE CAR
SUM RCOST
BY COUNTRY BY CAR BY MODEL BY TYPE BY SEATS SUMMARIZE
ON MODEL SUB-TOTAL
ON TABLE HOLD AS XFOCB FORMAT EXL2K FORMULA
END

TABLE FILE CAR
HEADING
"Cost of Goods Sold Report"
" "
PRINT DCOST
BY COUNTRY
ON TABLE SET STYLE *
type=report, titletext='Cost Rpt', $
type=heading, size=18, $
ENDSTYLE
ON TABLE HOLD AS ONE FORMAT EXL2K CLOSE PIVOT
PAGEFIELDS RCOST
CACHEFIELDS MODEL TYPE SALES ACCEL SEATS
END

The output for each tab in the Excel worksheet is:

Top of page

Example: Creating a Compound Report Using NOBREAK

In this example, the first two reports are on the first worksheet, and the last two reports are on the second worksheet, since NOBREAK appears on both the first and third reports.

TABLE FILE CAR
HEADING
"Report 1: England"
PRINT DCOST BY COUNTRY BY CAR
IF COUNTRY EQ ENGLAND
ON TABLE HOLD FORMAT EXL2K OPEN NOBREAK
ON TABLE SET STYLE *
type=report, color=red, $
type=data, backcolor=yellow, $
type=heading, color=blue, $
END

TABLE FILE CAR
HEADING
" "
" "
"Report 2: France"
PRINT RCOST BY COUNTRY
IF COUNTRY EQ FRANCE
ACROSS SEATS
ON TABLE HOLD FORMAT EXL2K
ON TABLE SET STYLE *
type=report, color=lime, backcolor=fuschia, style=bold, $
type=title, color=black, style=bold+italic, $
type=heading, backcolor=black, $
END

TABLE FILE CAR
FOOTING
"Report 3 - All"
PRINT SALES BY COUNTRY BY CAR
ON TABLE HOLD FORMAT EXL2K NOBREAK
ON TABLE SET STYLE *
type=report, backcolor=yellow, style=bold, $
type=title, color=blue, $
type=footing, backcolor=aqua, $
END

TABLE FILE CAR
HEADING
" "
" " 
"Report 4"
PRINT SALES BY COUNTRY BY CAR
ON TABLE HOLD FORMAT EXL2K CLOSE
ON TABLE SET STYLE *
type=report, color=yellow, backcolor=black, style=bold, $
END

The output is:

C:\temp\work>ftp ibimvs
Connected to ibimvs.ibi.com.
220-FTPD1 IBM FTP CS V2R10 at IBIMVS.IBI.COM, 15:24:50 on 2003-11-06.
220 Connection will close if idle for more than 5 minutes.
User (ibimvs.ibi.com:(none)): userid1
331 Send password please.
Password:
230 USERID1 is logged on.  Working directory is "USERID1.".
ftp> get pivotmvs.xht pivot.xht
200 Port request OK.
125 Sending data set USERID1.PIVOTMVS.XHT
250 Transfer completed successfully.
ftp: 8387 bytes received in 0.14Seconds 59.48Kbytes/sec.
ftp> get pivotmvs.xml pivot.xml
200 Port request OK.
125 Sending data set USERID1.PIVOTMVS.XML
250 Transfer completed successfully.
ftp: 1940 bytes received in 0.16Seconds 12.44Kbytes/sec.
ftp> by
221 Quit command received. Goodbye.

x

• You must transfer the file as an ASCII file.
• Whatever name you give the file ("HOLD" or an "asname" assigned with a HOLD AS phrase) must be kept for the transferred file. The HOLD EXL2K PIVOT operation actually produces two files; an "asname.XHT" data file and an "asname$.XML" PivotTable file.
• Before the FTP operation on z/OS, issue a DYNAM FREE for both the "asname" and the "asname$" ddnames.

x

To produce an Excel 97 spreadsheet, create a FOCUS report using the Excel 97 HOLD option and then transfer the output file to your browser and open it in Excel 97. The HOLD syntax is

[ON TABLE] HOLD [AS filename] FORMAT EXL97

where:

EXL97

Creates an Excel-formatted HTML file, with an extension of .HTM, which may include styling based on FOCUS StyleSheet features. The MIME type assigned automatically designates Excel as the active application for this file type. Before you can see or work with this file you must transfer it to your PC.

Top of page

Example: Creating an EXL97 Output File

The following example shows how to create a report in EXL97 format based on the contents of CENTORD, with conditional styling:

TABLE FILE CENTORD
HEADING 
"Order Revenue"
"Styled Report in Excel 97"
" "
SUM ORDER_DATE LINEPRICE AS 'Order,Total:'
BY HIGHEST 10 ORDER_NUM
ON TABLE SET PAGE-NUM OFF 
ON TABLE HOLD FORMAT EXL97 
ON TABLE SET STYLE *
TYPE=HEADING, COLOR=NAVY, SIZE=10, $
TYPE=HEADING, LINE=2, COLOR=RED, $
TYPE=DATA, BACKCOLOR=AQUA, STYLE=BOLD, WHEN=LINEPRICE GT 200000, $
TYPE=TITLE, STYLE=BOLD, $
END 

The output is:

When you use Microsoft Internet Explorer and Excel 97, the Excel client opens in the background and the report launches in your browser. Depending on browser settings, you may see the Excel application open and minimized while viewing your report. Leave Excel open when viewing the spreadsheet. In Excel 97, you will be prompted to save the document as a Microsoft Excel Workbook with an .xls extension. This saves the file as a binary Excel document.

x

• This format is compatible only with Excel 97 or higher. It is not compatible with Excel 95 or versions of Excel prior to Excel 95.
• While Excel 97 supports styling (StyleSheets), it does not support Cascading Style Sheets (CSS). Styling specified in a report that uses CSS (SET HTMLCSS=ON) will not be respected in Excel 97. The WRAP=n feature is not supported with EXL97, since this feature requires CSS.
• Numeric and date formatting options not supported for EXL2K are also not supported for EXL97. In addition, negative numbers displayed with brackets and trailing zeroes after the decimal are not supported. Note that dates are typically translated into Excel's General format, which may cause problems with sorting and other Excel features.
• PivotTables are not supported with EXL97. (EXL2K PIVOT is the only valid PivotTable format.)