In this section: |
You can create a FOCUS report as one of several kinds of Microsoft Excel workbook.
EXL2K format generates styled reports in Excel 2000/2003 HTML format for use on other platforms and on the Web. Feature options enable FOCUS users to also download all fields mentioned in their requests in an Excel PivotTable, or include interactive Excel formulas for FOCUS aggregation operations for performing additional "what if" analyses on their data within Excel 2000/2003.
EXL97 is an HTML-based HOLD format for generating formatted Excel 97 spreadsheets. EXL97 is a full StyleSheet driver for accurately rendering all report elements (such as headings and subtotals, for example) as well as applying StyleSheet syntax (such as, conditional styling). You must have Microsoft Excel 97 or higher installed on your computer to display an Excel 97 report.
The report formats and Excel workbooks that you can create are:
In addition, FOCUS supports two EXL2K variations: EXL2K FORMULA and EXL2K PIVOT. When any of these formats is specified, additional processing is done.
In this section: |
XLSX (Excel 2007/2010) format generates fully styled reports in Excel 2007/2010 binary display format. The FOCUS procedure generates a new workbook containing a single worksheet with the report output containing your defined report elements (headings and subtotals), as well as StyleSheet syntax (such as conditional styling and drilldowns):
In order to open Excel 2007/2010 workbooks generated in either the browser or the Excel application, you must have Excel 2007 or greater installed on the desktop.
Excel 2000 and Excel 2003 can be updated to read Excel 2007 or 2010 workbooks using the Microsoft Office Compatibility Pack available from the Microsoft download site (http://www.microsoft.com/downloads/en/default.aspx).
Microsoft changed the format and structure of the Excel workbook in Excel 2007. The new .xlsx file is a binary compilation of a group of XML files. Generating this new file format using FOCUS is a two step process that consists of generating the XML files containing the report output and zipping the XML documents into the binary .xlsx format.
The zipping process requires code included in the webfocus.war file of the WebFOCUS client, which is deployed as the ibi_apps context root on an application server such as Tomcat.
You must have an installed version of the WebFOCUS Client so that ibi_apps is deployed. The application server must be running when you issue the HOLD FORMAT XLSX command in your report request. The WebFOCUS Client does not have to be running. Your request must point to the URL where ibi_apps is deployed using the SET EXCELSERVURL command:
SET EXCELSERVURL = http://servername:8080/ibi_apps
where:
Is the name of the machine where the application server is running.
Is the default port used by the WebFOCUS Client to communicate with the application server.
After the XLSX output file is generated, you must FTP it to your PC in binary mode.
Note: This setting is also required for generating PPTX output files.
The following request generates an XLSX FORMULA output file. The host name for the machine on which the application server is running is myserver.
DYNAM ALLOC DD HOLD DA USER1.HOLD.XLSX SHR REU SET EXCELSERVURL = http://myserver:8080/ibi_apps TABLE FILE GGSALES SUM DOLLARS UNITS BY CATEGORY ON TABLE COLUMN-TOTAL ON TABLE HOLD FORMAT XLSX FORMULA END
The output is shown in the following image. The Excel formula generated for the UNITS column-total is shown in the formula field at the top of the Worksheet:
You can specify that a report should be saved to an Excel 2007/2010 workbook, displayed in the browser, or displayed in the Excel 2007/2010 application.
ON TABLE HOLD AS name FORMAT XLSX
where:
Specifies a file name for the generated workbook.
FOCUS formats defined in Master Files or within a FOCEXEC will be represented in the resulting cells in an Excel 2007 or 2010 worksheet. Where possible, the FOCUS formats are translated to custom Excel formats and applied to values passed as raw data. Each data value passed to a cell in Excel 2007/2010 is defined with a value and a format mask pair. The data format is associated with the cell rather than embedded in the value. This technique provides enhanced support for editing worksheets generated by FOCUS. New values entered into existing cells will retain the cell formats and continue to display in the style defined for the column within the report.
The following type of data can be passed to Excel 2007/2010:
Note: This behavior is a change from EXL2K format, where cells containing dates and more complex numeric formats were passed as formatted text.
Each numeric FOCUS format is translated to a custom numeric Excel format. The numeric value is displayed in the Excel formula bar for the selected cell. Within the actual cell, the value with the format mask applied displays.
The FOCUS formats for the following numeric data types are translated into Excel 2007/2010 format masks supporting full editing within the resulting workbook:
In the following example, the DOLLARS field is assigned different numeric formats to demonstrate different available options. The column titles have been edited to display the FOCUS format options that have been applied:
TABLE FILE GGSALES SUM DOLLARS/D12.2 AS 'D12.2' DOLLARS/D12C AS 'D12C' DOLLARS/D12CM AS 'D12CM' BY REGION BY CATEGORY ON TABLE HOLD FORMAT XLSX ON TABLE SET BYDISPLAY ON END
In the resulting worksheet, notice that cell C2 containing the DOLLAR value for Midwest Coffee presents the value with the FOCUS format D12.2, which presents the comma and two decimal places. On the formula bar, the actual value is presented without any formatting. Examine each of the DOLLAR values in each row to see that the value as displayed in the formula bar remains the same, and only the display values presented in each cell change.
Also notice that with SET BYDISPLAY ON, the BY field values are repeated for every row on the worksheet. This creates fully qualified data rows that can be used with various data sorting, filtering, and table features in Excel without losing valuable information. This setting is recommended as a best practice for all worksheets:
The following example uses Fixed Dollar (N) format, as well as multiple combined format options. Each FOCUS format option is translated to the appropriate Excel 2007/2010 format mask and applied to the cell value:
TABLE FILE GGSALES SUM BUDDOLLARS/D12N DOLLARS/D12M COMPUTE OVERBUDGET/D12BMc=BUDDOLLARS-DOLLARS; AS 'Over Budget' BY REGION BY CATEGORY ON TABLE HOLD FORMAT XLSX ON TABLE SET BYDISPLAY ON END
Notice the fixed numeric format defined for the BUDDOLLARS column (Column C) presents the local currency symbol in a fixed position within each cell, regardless of the size of the data value. On the formula bar, the values in the Over Budget calculated field is passed as a negative value where appropriate. In the actual cells, the bracketed styling is applied to the negative values as part of the custom Excel 2007/2010 format mask:
By default, headings and footings are passed to Excel as a single character string. Spot markers are not supported for positioning within each line. Numeric fields and dates passed in headings and footings are passed as text strings within the overall heading or footing contents.
To display numeric fields and dates within headings and footings as numeric or date values, use HEADALIGN=BODY in the StyleSheet to define each of the items in the heading as an individual cell. Each cell containing numeric or date values will then be passed as the appropriate value with the associated format mask.
For data columns, all currency formats are translated using the Excel 2007/2010 format masks that use the punctuation rules defined by the regional settings of the desktop.
In languages that use Continental Decimal Notation, the currency definitions designate that a comma is used as the decimal separator, and a period is used as the thousands separator, so D12.2CM may present the value as $ 9.999,99 rather than the English (United States) value $ 9,999.99. In headings and footings, you can designate that punctuation should be converted to Continental Decimal notation by issuing the SET CDN=ON command. With this setting in effect, the data embedded within heading and footing text strings will be formatted using the converted punctuation. Specify HEADALIGN=BODY to delineate items as individual cells and to retain the numeric formatting within the field which will follow the same rules as the report data within the data columns.
Most translated and smart dates can be sent to Excel 2007/2010 as standard date values with format masks, enabling Excel to use them in functions, formulas, and sort sequences.
The following request against the GGSALES data source creates the date January 1, 2010 and converts it to four date formats with translated 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 XLSX END
The following table shows how the dates should appear:
FOCUS Format | XLSX Displays | XLSX Value |
---|---|---|
WRMtrDY | Friday, January 1 10 | 1/1/2010 |
wDMTY | Fri, 1 Jan 10 | 1/1/2010 |
wrDMTRY | Friday, 1 January 1 | 1/1/2010 |
wrYMtrD | Friday, 10 January 1 | 1/1/2010 |
In Excel 2007/2010, 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:
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 XLSX. 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 | XLSX Displays: | XLSX Value |
---|---|---|
DMYY | 02/01/2010 | 1/2/2010 |
MY | 01/10 | 1/1/2010 |
MTY | Jan, 10 | 1/1/2010 |
MTDY | Jan 2, 10 | 1/2/2010 |
The following request against the GGSALES data source creates the date January 2, 2010 and passes it to Excel 2007/2010 with formats MDYY, DMYY, MY, and MTDY:
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 XLSX END
Columns D and E have actual date values with format masks, displayed by Excel 2007/2010 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):
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 format that are recognized by Excel as numbers.
The following request against the GGSALES data source creates the date January 1, 2010 and extracts numeric date components, passing them to Excel 2007/2010:
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 XLSX END
The output is:
Date formats that contain a Quarter component are always passed to Excel as text strings since Excel does not support Quarter formats.
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:
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 XLSX END
In Excel 2007 or 2010, the cells containing dates with Quarter components have General format. To see this, open the Format Cells dialog box.
The output is:
Date formats that do not contain sufficient information to present a 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 be sent to Excel as text. 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:
When date formats are passed to Excel 2007/2010 with format masks, all month and day names are in mixed-case, regardless of how the case has been specified in the FOCUS format. However, since the values in this example are always sent as text, the casing defined in the FOCUS format is applied in the resulting cell.
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:
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 XLSX END
In Excel 2007 or 2010, the cells containing the days have General format. To see this, open the Format Cells dialog box.
The output is:
FOCUS supports the insertion of images into Excel headers and footers and the definition of key page settings within the Excel Worksheet Page Setup to support the placement of these images in relationship to the overall worksheet and Excel generated page breaks. This access to the Excel page functionality is designed to enhance overall usability of the worksheets for users who will be printing these reports. Page settings including orientation, page size, and page margins will directly affect the layout of each Excel page based on values defined within the FOCEXEC. Images can be included on headers and footers on every printed page, on the first page of the report only, or only on all subsequent pages. The FOCUS headings and footings continue to display within the worksheet. With this new feature, FOCUS can insert logos to be printed once at the top of a report and watermark images that need to be displayed on every printed page.
[TYPE=REPORT,] XLSXPAGESETS={ON|OFF} [,PAGESIZE={pagesize|LETTER}] [,ORIENTATION={PORTRAIT|LANDSCAPE}] [,TOPMARGIN=n] [,BOTTOMMARGIN=m],$
where:
ON causes the page settings defined in the FOCUS request to be applied to the Excel Worksheet page settings. OFF retains the default page settings defined in the standard Excel workbook. OFF is the default value.
Defines the top margin for the worksheet in the units identified by the UNITS parameter (inches, by default). The default value is .25.
Defines the bottom margin for the worksheet in the units identified by the UNITS parameter (inches, by default). The default value is .25.
Is one of the PAGESIZE values supported in a FOCUS StyleSheet. LETTER is the default page size.
PORTRAIT displays the report across the narrower dimension of a vertical page, producing a page that is longer than it is wide. PORTRAIT is the default value.
LANDSCAPE displays the report across the wider dimension of a horizontal page, producing a page that is wider than it is long.
TYPE={PAGEHEADER|PAGEFOOTER},OBJECT=IMAGE, IMAGE=imagename, JUSTIFY={LEFT|CENTER|RIGHT} [,DISPLAYON={FIRST|NOT-FIRST}] [,SIZE=(w h)],$
where:
Places the image in the worksheet header.
Places the image in the worksheet footer.
Is the name of a valid image file to be placed in the header or footer. The image must be available to FOCUS. The image types supported are GIF and JPEG.
Identifies the area in the header or footer to contain the image and the justification or placement within that defined area.
Defines whether the image should be placed on the first page only or on all pages except the first. Omit this attribute to place the image on all pages.
Valid values are:
FIRST places the image only on the first page.
NOT-FIRST places the image on every page except the first page.
Is the size of the image. By default, an image is added at its original size.
w is the width of the image, expressed in the unit of measurement specified by the UNITS parameter.
h is the height of the image, expressed in the unit of measurement specified by the UNITS parameter.
The following request against the GGSALES data source places the image, ibi_logo.gif, on the left header area of the first page and the right header area of every other page of the resulting worksheet. It places the image webfocus1.gif in the center area of the footer on every page:
TABLE FILE GGSALES SUM DOLLARS UNITS BUDDOLLARS BUDUNITS BY REGION BY ST BY CATEGORY BY PRODUCT ON TABLE SET BYDISPLAY ON ON TABLE HOLD FORMAT XLSX ON TABLE SET STYLE * FONT=ARIAL,SIZE=12, XLSXPAGESETS=ON,TOPMARGIN=1,BOTTOMMARGIN=1,ORIENTATION=LANDSCAPE, PAGESIZE=LETTER,$ TYPE=TITLE, COLOR=WHITE, BACKCOLOR=GREY,$ TYPE=PAGEHEADER, OBJECT=IMAGE, JUSTIFY=LEFT, IMAGE=IBI_LOGO.GIF, DISPLAYON=FIRST,$ TYPE=PAGEHEADER, OBJECT=IMAGE, JUSTIFY=RIGHT, IMAGE=IBI_LOGO.GIF, DISPLAYON=NOT-FIRST,$ TYPE=PAGEFOOTER, OBJECT=IMAGE, JUSTIFY=CENTER, IMAGE=webfocus1.gif, $ END
The first page of output has the image ibilogo.gif in the left area of the header and the image webfocus1.gif in the center area of the footer.
The second page of output has the image ibilogo.gif in the right area of the header and the image webfocus1.gif in the center area of the footer.
Watermark images can be placed into the Excel headers to display on every printed page of the generated worksheet.
Excel places images on the page starting in the header from left to right and then the footer from left to right. Large images placed in the header may overlap images before them in the presentation order and will overlay the previous images. For page layouts with a logo in the left area and watermark centered on the page, watermark image background should be transparent so it does not overlay the logo image.
In Excel, images are placed first on the page. All other contents of the worksheet are then placed on top of the images. Text in cells and styling such as background color and drawing objects are placed on top of the images. Excel supports transparency in drawing objects and images, but not in cell background color. BACKCOLOR will cover over images placed on the page.
The following request against the GGSALES data source uses the image internaluseonly.gif as a watermark to display in the background of every page of the worksheet. Although the image is placed in the center area of the header, it is large enough to span the entire worksheet page. It has a transparent background, so it does not cover the logo images placed at the left in the header and the center in the footer:
TABLE FILE GGSALES SUM DOLLARS UNITS BUDDOLLARS BUDUNITS BY REGION BY ST BY CATEGORY BY PRODUCT ON TABLE SET BYDISPLAY ON ON TABLE HOLD FORMAT XLSX ON TABLE SET STYLE * XLSXPAGESETS=ON, TOPMARGIN=1,BOTTOMMARGIN=1,LEFTMARGIN=1, RIGHTMARGIN=1, ORIENTATION=LANDSCAPE,PAGESIZE=LETTER,$ TYPE=PAGEHEADER, OBJECT=IMAGE, JUSTIFY=LEFT, IMAGE=IBI_LOGO.GIF, DISPLAYON=FIRST, $ TYPE=PAGEHEADER, OBJECT=IMAGE, JUSTIFY=CENTER, IMAGE=INTERNALUSEONLY.GIF, $ TYPE=PAGEFOOTER, OBJECT=IMAGE, JUSTIFY=RIGHT, IMAGE=WEBFOCUS1.GIF, $ END
The first page of the generated worksheet shows the watermark image beneath the data. This image is displayed on every page of the worksheet.
The maximum number of rows supported by Excel 2007 and 2010 on a worksheet is 1,048,576 (1MB). When you create an XLSX output file from a FOCUS report, the number of rows generated can be greater than this maximum.
To avoid creating an incomplete 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: By default, when generating XLSX output, the FOCUS page heading and page footing commands generate only worksheet headings and worksheet footings.
Add the ROWOVERFLOW attribute to your FOCUS StyleSheet
TYPE=REPORT, ROWOVERFLOW={ON|OFF|PBON}, [ROWLIMIT={n|MAX}...
where:
Enables overflow worksheets.
Disables overflow worksheets. OFF is the default value.
Inserts FOCUS page breaks that display the page heading, footing, and column titles at the appropriate places within the worksheet rows. This option does not cause a new worksheet to start when a FOCUS page break occurs.
Sets a target value for the number of rows to be included on a worksheet to n rows. The default value is the LINES value (by default, 57).
Sets a target value for the number of rows to be included on a Worksheet to 1,048,000 rows for XLSX output.
This attribute works with all Excel output formats. For all other output types, the ROWOVERFLOW StyleSheet attribute is ignored, and data flow is not affected.
(FOC3338) The row limit for EXCEL XLSX worksheets is 1048576.
The following request creates XLSX report output with overflow worksheets. 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 REGION SUBFOOT " " " End of Region <REGION" " " -* ****Report Footing**** ON TABLE SUBFOOT " " "END OF REPORT" ON TABLE HOLD FORMAT XLSX ON TABLE SET STYLE * TYPE=REPORT, TITLETEXT=EXLOVER, ROWOVERFLOW=ON, ROWLIMIT=2000,$ 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.
The following request creates XLSX report output with overflow worksheets. The ROWOVERFLOW=PBON attribute in the StyleSheet activates the overflow feature, and the ROWLIMIT=250 sets the maximum number of rows in each worksheet to approximately 250. Without this attribute, one worksheet would have been generated. The PRODUCT sort phrase specifies a page break.
TABLE FILE GGSALES -* ****Report Heading**** ON TABLE SUBHEAD "SALES BY REGION, CATEGORY, AND PRODUCT" " " PRINT DOLLARS UNITS BUDDOLLARS BUDUNITS BY REGION BY HIGHEST CATEGORY BY PRODUCT PAGE-BREAK BY DATE WHERE DATE GE '19971001' -* ****Page Heading**** HEADING " Product: <PRODUCT in Category: <CATEGORY for Region: <REGION" -* ****Page Footing**** FOOTING " " -* ****Report Footing**** ON TABLE SUBFOOT " " "END OF REPORT" ON TABLE SET BYDISPLAY ON ON TABLE HOLD FORMAT XLSX ON TABLE SET STYLE * INCLUDE=endeflt,TITLETEXT=EXLOVER, ROWOVERFLOW=PBON, ROWLIMIT=250, $ ENDSTYLE END
The report heading displays on the first worksheet only, the page heading, footing, and column titles display on each worksheet and at each FOCUS page break (each time the product changes), and the subhead and subfoot display whenever the associated sort field changes value. The following image shows the top of the first worksheet.
Excel Table of Contents (TOC) enables you to generate a multiple worksheet report in which a separate worksheet is generated for each value of the first BY field in the FOCUS report.
The syntax is identical to that of HTML TOC reports, except that only a single BY field is allowed in XLSX, while multiple BY fields are allowed in HTML.
ON TABLE HOLD FORMAT XLSX BYTOC
Since only one level of TOC is allowed for XLSX reports, the optional number following the BYTOC keyword can only be 1.
As with HTML, 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 generates separate 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.
The BYTOC feature is not supported with the XLSX TEMPLATE format.
The following request against the GGSALES data source creates separate tabs based on the REGION sort field:
TABLE FILE GGSALES SUM UNITS/D12C DOLLARS/D12CM BY REGION NOPRINT BY CATEGORY BY PRODUCT HEADING "<REGION Region Sales" ON TABLE HOLD FORMAT XLSX ON TABLE SET BYDISPLAY ON ON TABLE SET COMPOUND BYTOC ON TABLE SET STYLE * TYPE=REPORT, FONT=ARIAL,SIZE=9,$ TYPE=HEADING, SIZE=12,$ TYPE=TITLE, BACKCOLOR=GREY,COLOR=WHITE, $ ENDSTYLE END
The output is:
Format XLSX does not support the following features currently supported for EXL2K:
The command ON TABLE HOLD FORMAT EXL2K generates a fully styled Excel report in your browser, with conditional styling capability.
The EXL2K (Excel 2000/2003) format is a full StyleSheet driver that renders all report elements (for example, headings and subtotals) as well as StyleSheet syntax (such as, conditional styling).
EXL2K format accurately displays formatted dates and numeric values and controls column width and wrapping in Excel 2000/2003. See Displaying Formatted Numeric Values in EXL2K Report Output, Passing FOCUS Dates to Excel 2000/2003 and Controlling Column Width and Wrapping in EXL2K Report Output.
In addition, the format variation EXL2K FORMULA enables you to convert summed information (such as column totals, row totals, subtotals) and calculated values to interactive formulas in an Excel 2000/2003 worksheet. See Generating Native Excel Formulas in EXL2K Report Output. Another format variation, EXL2K PIVOT, enables you to analyze different views of your data. See Using PivotTables in EXL2K Report Output.
EXL2K is supported only in Excel 2000 or 2003. It does not work with any previous releases of Excel. You can invoke format EXL2K reports using any browser supported by FOCUS.
By default, when you choose EXL2K as your display format, the report opens in an Excel 2000/2003 worksheet, identified in a tab at the bottom of the worksheet as Sheet1, Sheet2, and so on. You can change the name of a Sheet tab to make it more descriptive of your report's content.
The following example illustrates how to create a report in EXL2K (Excel 2000/2003) format with a styled heading, conditional styling on SALES, and drill-downs on COUNTRY:
TABLE FILE CAR HEADING "SALES BY COUNTRY" SUM SALES BY COUNTRY BY CAR BY MODEL WHERE (COUNTRY EQ 'ENGLAND') OR (COUNTRY EQ 'ITALY') OR (COUNTRY EQ 'FRANCE'); ON TABLE HOLD FORMAT EXL2K ON TABLE SET STYLE * TYPE=REPORT, FONT=ARIAL, TITLETEXT=SALES REPORT, $ TYPE=DATA, COLUMN=COUNTRY, FOCEXEC=DRILLFEX (PARAM1=COUNTRY PARAM2=CAR),$ TYPE=DATA, COLUMN=SALES, COLOR=RED, BACKCOLOR=YELLOW, WHEN=SALES GT 10000,$ TYPE=DATA, COLUMN=MODEL, BACKCOLOR=YELLOW, WHEN=SALES GT 10000,$ TYPE=HEADING, FONT=ARIAL BLACK, COLOR=RED, BACKCOLOR=SILVER, SIZE=16, $ TYPE=TITLE, FONT=ARIAL, SIZE=12, $ ENDSTYLE END
The output is:
Notice that the tab name has been changed from the Excel default, Sheet1, to a more descriptive name, which is specified in the TITLETEXT attribute of the StyleSheet. For details on the TITLETEXT attribute see Customizing Tabular Reports.
[ON TABLE] HOLD [AS filename] FORMAT EXL2K [PIVOT] [FORMULA]
where:
Creates an Excel-formatted output file that may include styling based on internal or external StyleSheets features. The extension on Windows platforms is .xht;
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 PivotTables in EXL2K Report Output.
Creates an XHT output file including appropriate Excel formulas for all FOCUS numeric summary operations. For more information about this option, see Generating Native Excel Formulas in EXL2K Report Output.
Excel 2000/2003 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, users can issue a SET command to select one of five other options.
When an EXL2K report is run, and null values are retrieved for one or more fields, blank spaces are displayed by default in each cell of the report output for the empty (null) fields. This behavior is the result of SET EMPTYCELLS ON being set by default in the background of all EXL2K reports. If you want to identify null values with something other than blank spaces, a character string can be used to populate all empty fields in a report.
To identify null values in EXL2K reports, use
ON TABLE SET NODATA character_string
ON TABLE SET EMPTYCELLS [ON|OFF]
where:
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 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 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 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:
Excel 2000/2003 workbooks generated by FOCUS EXL2K format contain the numeric formatting specified in the Master File for the data source or in a temporary field. FOCUS numeric values (such as currency) are translated to supported Excel formats and display properly in Excel 2000/2003.
When translating numeric formats from FOCUS to Excel, there must be a corresponding Excel format to translate to. If there is no corresponding format, then the value will be formatted in the closest matching Excel format or in Excel General format. For details, see Usage Notes for Numeric Formats.
The following example illustrates how formatted numeric data displays in a worksheet when using the EXL2K format. Note that the format for the Sales field (D16.2M—which represents floating point double-precision with two decimal places and a floating dollar sign) is translated to the corresponding Excel format.
DEFINE FILE GGSALES NEWDOLL/D16.2M = DOLLARS; END
SET PAGE-NUM=OFF TABLE FILE GGSALES "Dollar Sales" "Excel 2000 Spreadsheet" " " SUM NEWDOLL AS 'Sales' BY REGION AS 'Area' BY CATEGORY BY PRODUCT WHERE REGION EQ 'Midwest' OR 'Northeast' ON TABLE SET BYDISPLAY ON ON TABLE HOLD AS EXL2KNUM 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=NEWDOLL, JUSTIFY=RIGHT, $ END
The output is:
Notice that the values of the sort fields are repeated in the output; this presentation, which is particularly desirable in a worksheet, is controlled by the command ON TABLE SET BYDISPLAY ON. For details, see Sorting Tabular Reports.
The following formats are not supported in EXL2K. They will translate into Excel General format and possibly produce unpredictable results:
The following applies to headings and footings with embedded numeric fields:
Excel 2000/2003 workbooks generated by FOCUS EXL2K format contain the numeric formatting specified in the Master File for the data source or in a temporary field. FOCUS date formats (such as Smart Dates) are translated to supported Excel formats and display properly in Excel 2000/2003.
When translating date formats from FOCUS to Excel, there must be a corresponding Excel format to translate to. If there is no corresponding format, then the value will be formatted in the closest matching Excel format or in Excel General format. For details, see Usage Notes for Date Formats.
By default, when FOCUS creates dates in Excel 2000 (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. For information about passing translated date formats to Excel 2000/2003 as date values with format masks, see Passing Dates With Translated Text to Excel 2000/2003.
The following example illustrates how customized dates display in a worksheet when using the EXL2K format.
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 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
The output is:
The command ON TABLE SET BYDISPLAY ON ensures that sort fields are repeated in each worksheet cell. For details, see Sorting Tabular Reports.
The following formats are not supported in EXL2K. They will translate into Excel General format and possibly produce unpredictable results:
In order to use a "-" as a separator between month, day, and year in Excel 2000/2003, you must change the default date separator for Windows. This setting can be located under Regional Options in the Control Panel.
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.
SET EXL2KTXTDATE = {TEXT|VALUE}
where:
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 1 | 1/1/2010 |
wrYMtrD | Friday, 10 January 1 | Friday, 10 JANUARY 1 | Friday, 10 January 1 | 1/1/2010 |
With SET EXL2KTXTDATE=TEXT, in EXL2K report output 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 EXL2K report output 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:
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 report output. 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 |
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 a 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):
Dates formatted as individual components (for example, D, Y, M, W) are passed to Excel 2000/2003 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 2000/2003 as numbers. These values are passed to Excel in the same format regardless of the setting for EXL2KTXTDATE.
The following request against the GGSALES data source creates the date January 1, 2010 and extracts numeric date components, passing them to Excel 2000/2003:
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:
Date formats that contain a Quarter component are always passed to Excel as text strings since Excel does not support Quarter formats.
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 the EXL2K report output, the cells containing dates with Quarter components have General format. To see this, open the Format Cells dialog box.
The output is:
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 2000/2003 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:
Note that since these values are always sent as text, the casing defined in the FOCUS format is applied in the resulting cell.
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:
Data wrapping, column width, and the scroll area can be controlled in an Excel 2000/2003 worksheet when using FORMAT EXL2K. You can:
TYPE=REPORT, [COLUMN=column,] WRAP=value, $
where:
This setting implies ON. However, the column width is set to the specified width unless the data is wider than the column width, in which case, wrapping will occur as for ON.
TYPE=REPORT, [COLUMN=column,] SQUEEZE={ON|OFF|n}, $
where:
This is the most commonly used SQUEEZE setting in an EXL2K report.
Note:
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 CAR MYDATE/MDY='10/22/60'; RCD/D14.3=RETAIL_COST; VERYLONG/A80='Subtract dealer cost from retail cost to calculate profit.'; END
TABLE FILE CAR PRINT MYDATE RCD VERYLONG AS 'Default' VERYLONG AS 'WRAP=OFF' VERYLONG AS 'WRAP=4.1' VERYLONG AS 'WRAP=2' VERYLONG AS 'SQUEEZE=2' SALES BY COUNTRY ON TABLE HOLD 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:
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:
Using StyleSheet attributes, you can lock Excel workbook values so they are read-only. These attributes apply to all EXL2K formats including EXL2K, EXL2K PIVOT, and EXL2K FORMULA.
To enable locking, use the following attributes:
TYPE=REPORT, PROTECTED={ON|OFF}, [LOCKED={ON|OFF}],$
where:
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 worksheet, add the LOCKED=ON attribute to the StyleSheet declaration for the cells you want to lock.
TYPE=type, [ COLUMN=columnspec ] ,LOCKED={ON|OFF},$
where:
The following request locks the entire workbook 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 HOLD 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 worksheet. Any attempt to do so displays a message that the sheet is protected:
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 HOLD 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:
When you display or save a tabular report request using EXL2K FORMULA, the resulting worksheet contains an Excel formula that computes and displays the results of any type of summed information (such as column totals, row totals, subtotals, and calculated values), rather than static numbers. Worksheets saved using the EXL2K FORMULA format are interactive, allowing for "what if" scenarios that immediately reflect any additions or modifications made to the data.
The EXL2K FORMULA format is supported for the FOCUS TABLE commands: ROW-TOTAL, COLUMN-TOTAL, SUB-TOTAL, SUBTOTAL, SUMMARIZE, RECOMPUTE, and COMPUTE, and for calculations performed by functions. See Translation Support for FORMAT EXL2K FORMULA.
EXL2K FORMULA is not supported with PivotTables (EXL2K PIVOT), with Excel 97 (EXL97), or with financial reports created with the Financial Report Painter or the underlying Financial Modeling Language (FML).
Add the following syntax to your request to take advantage of Excel formulas in your workbook:
ON TABLE HOLD FORMAT EXL2K FORMULA
where:
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:
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:
This request totals the columns for retail cost and dealer cost and calculates the value of a field called PROFIT by subtracting the dealer cost from the retail cost.
The formula for the calculated values is generated by translating the internal form of the FOCUS expression (COMPUTE PROFIT/D12.2 = RC - DC;) into an Excel formula. In this example, the formulas appear in cells B14, C14, and D14.
TABLE FILE CAR ON TABLE SET PAGE-NUM OFF SUM RC AND DC AND COMPUTE PROFIT/D12.2 = RC - DC; BY CAR HEADING "Profit By Car" " " ON TABLE COLUMN-TOTAL ON TABLE HOLD FORMAT EXLL2K 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 following output highlights the formula that calculates for the column total of PROFIT: D14=SUM(D4:D13).
The following illustrates how functions are translated to Excel 2000/2003 reports. The function DMOD divides ACCTNUMBER by 1000 and returns the remainder to LAST3_ACCT. The Excel formula corresponds to this, =TRUNC((MOD($C3,(1000)))).
TABLE FILE EMPLOYEE PRINT ACCTNUMBER AS 'Account Number' AND COMPUTE LAST3_ACCT/I3L = DMOD(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 FORMAT EXL2K FORMULA ON TABLE SET STYLE * TYPE=TITLE, SIZE=12, STYLE=BOLD, $ END
The output is:
If your report contains a calculated value (generated by the COMPUTE or RECOMPUTE command), all of the fields referenced by the calculated value must be displayed in the report in order for cell references to be included in the formula. If a referenced column is not displayed in the workbook, the data value will be placed in the formula, rather than a cell reference. In the case of recompute, the value used may be an incorrect value from the last detail record of the sort break.
The following request computes the difference (DIFF) by subtracting budgeted dollars from dollar sales. The budgeted dollars field used in the expression is not included in the SUM command. The value of DIFF is recomputed on the region level:
TABLE FILE GGSALES HEADING "Profit By Region" " " SUM DOLLARS COMPUTE DIFF/I9=DOLLARS - BUDDOLLARS; BY REGION BY CATEGORY ON REGION RECOMPUTE ON TABLE HOLD FORMAT EXL2K FORMULA END
The output shows that the formula is subtracting a data value that is not displayed on the worksheet. It is actually the BUDDOLLARS value from the previous row:
If you change the SUM command to the following, the formula can be recomputed correctly:
SUM DOLLARS BUDDOLLARS
The formula generated with the new SUM command contains cell references for both fields used in the calculation:
The IS-PRESENT, IS-MISSING, CONTAINS, and OMITS operators are not supported. In addition the logical operators (AND, OR) are not supported within IF/THEN/ELSE statements.
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. The EDIT function is not supported for editing strings.
If you use the COMPUTE command with an unsupported function, an error message is displayed.
EXL2K FORMULA output supports prefix operators that are used on summary lines generated by FOCUS commands, such as SUBTOTAL and RECOMPUTE. Where a corresponding formula exists in Excel, these prefix operators are translated into the equivalent Excel summarization formula. The results of prefix operators used directly against retrieved data continue to be passed to Excel as values, not formulas.
The following table identifies the prefix operators supported by EXL2K FORMULA when used on summary lines, and the Excel formula equivalent placed in the generated worksheet.
Prefix Operator |
Excel Formula Equivalent |
---|---|
SUM. |
=SUM() |
AVE. |
=AVERAGE() |
CNT. |
=COUNT() |
MIN. |
=MIN() |
MAX. |
=MAX() |
The following prefix operators are not translated to formulas when used on summary lines in EXL2K FORMULA.
Note:
For example, in the following aggregating display command, the AVE. prefix operator operates on the DEALER_COST field:
SUM AVE.DEALER_COST
In the following summary command, the MAX. prefix operator operates on the DOLLARS field at the REGION sort break. Note the required blank space between the prefix operator and the field name:
ON REGION RECOMPUTE MAX. DOLLARS
In the following request against the GGSALES data source, the RECOMPUTE command for the REGION sort field calculates the maximum of the aggregated DOLLARS field and the minimum of the aggregated BUDDOLLARS field:
TABLE FILE GGSALES SUM UNITS DOLLARS BUDDOLLARS AND COMPUTE DIFF/I10= DOLLARS-BUDDOLLARS; BY REGION BY CATEGORY WHERE CATEGORY EQ 'Food' OR 'Coffee' WHERE REGION EQ 'West' OR 'Midwest' ON REGION RECOMPUTE MAX. DOLLARS MIN. BUDDOLLARS DIFF ON TABLE HOLD FORMAT EXL2K FORMULA END
On the output, the cell that represents the recomputed DOLLARS for the Midwest region has been generated as the formula =MIN(E2:E3).
The power of EXL2K format derives in large measure from its ability to take advantage of PivotTables. The PivotTable is a tool used in Microsoft Excel to analyze complex data. It allows you to drag data fields within a PivotTable, providing different views of the data, such as sorting across rows or columns. You can also create dimensional hierarchies, similar to those created using WITHIN syntax, by using the PAGEFIELDS command.
Report requests can be created in FOCUS and sent as output to a fully formatted Excel PivotTable. The ON TABLE HOLD FORMAT EXL2K PIVOT command will generate an Excel PivotTable in your browser.
When FORMAT EXL2K PIVOT is enabled, two data streams are created:
The PivotTable file (.xht) is an HTML file with embedded XML. The HTML file contains all the information that is displayed in your browser.
The PivotTable cache file is a metadata type of file. It contains all the fields specified in the procedure and links internally to the PivotTable file. The PivotTable cache file can contain data fields called CACHEFIELDS, which populate the PivotTable toolbar, but do not initially display in the report. CACHEFIELDS can be dragged and dropped from the PivotTable toolbar into the PivotTable when required for analysis. The two data streams are packaged into one output file when the WEBARCHIVE parameter is set to ON. ON is the WEBARCHIVE parameter default value.
After creating FOCUS reports formatted as Excel PivotTables you must transfer both the XHT and XML files to Excel 2000 using FTP in ASCII mode or another transfer facility.
The PivotTable toolbar may not automatically display when a PivotTable is created. To display the PivotTable toolbar, use the following procedure:
Excel 2000/2003 displays the PivotTable toolbar, listing the fields available to be dragged into the body of the PivotTable report. A cell within the PivotTable must be selected for the Pivot toolbar to display all fields.
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 depicts PivotTable output with the major elements identified.
The following summary table shows PivotTable elements and the associated FOCUS syntax.
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 displays 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 |
The following table summarizes TABLE syntax elements that are supported in EXL2K PIVOT. The effect of each command on your PivotTable request is listed, along with required usage.
Syntax Element |
Usage |
Effect on PivotTable |
---|---|---|
|
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. |
* You need at least one sort field or a PAGEFIELD for a valid Pivot Table.
The standard HOLD and SAVE syntax for storing the output file on disk is supported for the EXL2K PIVOT file format. When FORMAT EXL2K PIVOT is enabled, two files are created, the PivotTable file (.xht) and a PivotTable cache file (.xml). 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.
You can include the CACHEFIELDS phrase in a request to add fields to the pivot cache that are not initially displayed in the report. The cache file enables you to add available fields from the PivotTable toolbar into the body of the PivotTable by dragging and dropping. You can remove fields from the PivotTable by dragging and dropping them anywhere outside the report. Using these tools, you can quickly vary data views.
The following are syntax variations that you can use to enable FORMAT EXL2K PIVOT.
ON TABLE HOLD FORMAT EXL2K PIVOT AS mypivot
where:
Two files are generated with this syntax:
You should consider the following when writing requests for output to a PivotTable:
Note that the SUM and COUNT commands are not supported for EXL2K PIVOT. Nevertheless, you can use this native Pivot Table behavior in Excel after the data has been output to an Excel PivotTable.
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:
You can specify multiple fields as CACHEFIELDS. These fields will not initially display in the report, but will be available in the pivot cache file if you wish to include them in the report at a later time.
You can then drag these fields in and out of the PivotTable as desired. CACHEFIELDS is an optional phrase; you can generate a PivotTable without a CACHEFIELD.
Fields designated as CACHEFIELDS must be placed immediately after the PIVOT keyword in the ON TABLE HOLD FORMAT EXL2K PIVOT syntax or after a PAGEFIELDS phrase. A field specified as a CACHEFIELD cannot be designated anywhere else in the request. A list of CACHEFIELDS is terminated by the same keywords that terminate a normal report request, such as END or another ON phrase.
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:
You can specify a field in the procedure as an Excel 2000/2003 page field. The page field filters the data for the specified field. Then, using PivotTable functionality, you can choose a single value from the page field drop-down menu (also called a page field item) and immediately see only the data associated with that selection. For example, in a report that shows international sales data for cars, if you specify COUNTRY as your page field and select JAPAN as your page field item, you will see only sales data for TOYOTA or NISSAN. If you then select ENGLAND, you will see the data for JAGUAR and TRIUMPH.
A page field can act as the sort field. A valid PivotTable can be generated without specifying a PAGEFIELD if sorting is handled by either a BY or ACROSS phrase. However, if the PivotTable request contains neither a BY or ACROSS phrase, a PAGEFIELD phrase must be included.
Note:
This example illustrates the use of PAGEFIELDS syntax to make three fields available in the PivotTable toolbar.
TABLE FILE CENTINV HEADING "PivotTable with PAGEFIELDS" PRINT PRICE COST ON TABLE HOLD AS EXL2K11 FORMAT EXL2K PIVOT PAGEFIELDS PRODCAT PRODNAME PRODTYPE ON TABLE SET STYLE * TYPE=DATA, COLUMN=PRODCAT, COLOR=RED,$ TYPE=HEADING, COLOR=BLUE, STYLE=BOLD, SIZE=14,$ ENDSTYLE END
This output is:
An Excel Named Range is a name assigned to a specific group of cells within an Excel worksheet that can be easily referenced by FOCUS applications. FOCUS StyleSheet language facilitates the generation of Named Ranges.
The use of Excel Named Ranges provides many benefits, including the following:
To create Excel Named Ranges, use
TYPE=type, IN-RANGES=rangename, $
where:
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.
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.
Excel Named Ranges are supported for the following Excel formats:
EXL2K, EXL2K FORMULA, EXL2K TEMPLATE
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
Excel Table of Contents (TOC) enables you to generate a multiple worksheet report in which a separate worksheet is generated for each value of the first BY field in the FOCUS report.
Note: This feature can be used only with Excel 2002 or higher releases because it requires the Web Archive file format, which was not available in Excel 2000 and earlier releases.
ON TABLE HOLD FORMAT EXL2K BYTOC
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.
The following request against the GGSALES data source creates separate tabs based on the REGION sort field:
TABLE FILE GGSALES SUM UNITS/D12C DOLLARS/D12CM BY REGION NOPRINT BY CATEGORY BY PRODUCT HEADING "<REGION Region Sales" ON TABLE HOLD FORMAT XLSX ON TABLE SET BYDISPLAY ON ON TABLE SET COMPOUND BYTOC ON TABLE SET STYLE * TYPE=REPORT, FONT=ARIAL,SIZE=9,$ TYPE=HEADING, SIZE=12,$ TYPE=TITLE, BACKCOLOR=GREY,COLOR=WHITE, $ ENDSTYLE END
The output is:
The BYTOC option of FOCUS EXL2K format generates a workbook containing an individual worksheet for each primary sort (BY) field in the report. Each sheet is named with the value of the primary sort field to identify the data it contains.
Excel requires each sheet name to be unique. Excel is case insensitive meaning it evaluates two values as being the same when the values contain the same characters but have different casing. For example, Excel evaluates the values COFFEE and Coffee to be the same value and, therefore, they cannot be used as sheet names for two different sheets.
By default, FOCUS sort processing is case sensitive, so the same field value with different casing is considered to be two different values when used as a sort (BY) field. In an Excel BYTOC report, FOCUS will generate sheets with sheet names for each value of the primary sort (BY) key based on case sensitivity. For sort values that differ in casing only, the initial sheet will receive the sort value, and Excel will have difficulty with any subsequent sheet generated with the same name. The second sheet name will display as Recovered_Sheet1 in place of the value Excel considers a duplicate.
In the following example, FOCUS will generate separate worksheets for the values Coffee and COFFEE. The initial worksheet will be named COFFEE, but for the subsequent worksheet representing the value Coffee, Excel will be unable to use the value as the worksheet name and will display the recovered value.
DEFINE FILE GGSALES SHOWCAT/A15=IF PRODUCT EQ 'Espresso' THEN 'COFFEE' ELSE CATEGORY; END TABLE FILE GGSALES BY SHOWCAT BY PRODUCT ON TABLE SET PAGE-NUM OFF ON TABLE HOLD FORMAT EXL2K ON TABLE SET COMPOUND 'BYTOC 1' END
On the output, the second tab has the name Recovered_Sheet1 instead of the case sensitive value Coffee:
When you know your data contains values with case sensitive differences in the highest level sort field, you will need to massage your data values so that they can be used as valid sheet names in the BYTOC workbook.
Different approaches include:
In the following example, the sheet name is built by adding a unique counter to each unique value of the high level sort field. The new computed field is then used as the first BY field in the request, so it is used by the BYTOC phrase to define the sheet names. The sheet name field can be presented within the report or specified as a NOPRINT field so that it displays on the tab but does not display on the actual report:
DEFINE FILE GGSALES SHOWCAT/A15=IF PRODUCT EQ 'Espresso' THEN 'COFFEE' ELSE CATEGORY; END TABLE FILE GGSALES PRINT SHOWCAT NOPRINT COMPUTE CNTR/I2 = IF SHOWCAT EQ LAST SHOWCAT THEN LAST CNTR ELSE CNTR + 1; NOPRINT BY TOTAL COMPUTE SHOWCAT2/A20 = EDIT(CNTR) | '-' | SHOWCAT; NOPRINT BY SHOWCAT BY PRODUCT ON TABLE SET PAGE-NUM OFF ON TABLE HOLD FORMAT EXL2K ON TABLE SET COMPOUND BYTOC ON TABLE SET STYLE * GRID=OFF, $ ENDSTYLE END
On the output, each tab has a name consisting of a sequence number followed by the sort field value with its correct case.
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 incomplete 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.
Add the ROWOVERFLOW attribute to your FOCUS StyleSheet
TYPE=REPORT, ROWOVERFLOW={ON|OFF|PBON}, [ROWLIMIT={n|MAX}...
where:
Enables overflow worksheets.
Disables overflow worksheets. OFF is the default value.
Inserts FOCUS page breaks that display the page heading, footing, and column titles at the appropriate places within the worksheet rows. This option does not cause a new worksheet to start when a FOCUS page break occurs.
Sets a target value for the number of rows to be included on a worksheet to n rows. The default value is the LINES value (by default, 57).
Sets a target value for the number of rows to be included on a worksheet to 65,000 rows for EXL2K output.
This attribute will work only with EXL2K or XLSX output. For all other output types, the ROWOVERFLOW StyleSheet attribute is ignored, and data flow is not affected.
(FOC3313) The line number limit per EXCEL 2003 worksheet is 65536.
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:
The following request creates EXL2K report output with overflow worksheets. The ROWOVERFLOW=PBON attribute in the StyleSheet activates the overflow feature, and the ROWLIMIT=250 sets the maximum number of rows in each worksheet to approximately 250. Without this attribute, one worksheet would have been generated. The PRODUCT sort phrase specifies a page break.
TABLE FILE GGSALES -* ****Report Heading**** ON TABLE SUBHEAD "SALES BY REGION, CATEGORY, AND PRODUCT" " " PRINT DOLLARS UNITS BUDDOLLARS BUDUNITS BY REGION BY HIGHEST CATEGORY BY PRODUCT PAGE-BREAK BY DATE WHERE DATE GE '19971001' -* ****Page Heading**** HEADING " Product: <PRODUCT in Category: <CATEGORY for Region: <REGION" -* ****Page Footing**** FOOTING " " -* ****Report Footing**** ON TABLE SUBFOOT " " "END OF REPORT" ON TABLE SET BYDISPLAY ON ON TABLE HOLD FORMAT EXL2K ON TABLE SET STYLE * INCLUDE=endeflt,TITLETEXT=EXLOVER, ROWOVERFLOW=PBON, ROWLIMIT=250, $ ENDSTYLE END
The report heading displays on the first worksheet only, the page heading, footing, and column titles display on each worksheet and at each FOCUS page break (each time the product changes), and the subhead and subfoot display whenever the associated sort field changes value. The following image shows the top of the first worksheet.
Excel Compound Reports generate multiple worksheet reports using the EXL2K output format.
The syntax of Excel Compound Reports is identical to that of PDF Compound Reports. By default, each of the component reports from the compound report is placed in a new Excel worksheet (analogous to a new page in PDF). If the NOBREAK keyword is used, the next report follows the current report on the same worksheet (analogous to starting the report on the same page in PDF).
Output is generated in Microsoft Web Archive format. This format is labeled Single File Web Page in the Excel Save As dialog. Excel provides the conventionally given file suffixes: .mht or .mhtml. FOCUS uses the same .xht file type that is used for EXL2K reports.
The components of an Excel compound report can be FORMULA or PIVOT reports (subject to the restrictions). They cannot be Table of Contents (TOC) reports.
Note: Excel 2002 (Office XP) or higher must be installed. Excel Compound Reports will not work with earlier versions of Excel since they do not support the Web Archive file format.
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.
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.)
TYPE=REPORT, TITLETEXT='Summary Report', $
Excel limits the length of worksheet titles to 31 characters. The following special characters cannot be used: ':', '?', '*', and '/'.
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.
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 AS EX1 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 AS EX1 FORMAT EXL2K CLOSE END
The output for each tab in the Excel worksheet is:
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:
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 GGSALES HEADING "Report 1: Coffee - Budget" SUM BUDDOLLARS BUDUNITS COLUMN-TOTAL AS 'Total' BY REGION IF CATEGORY EQ Coffee ON TABLE PCHOLD FORMAT EXL2K OPEN NOBREAK ON TABLE SET STYLE * type=report, font=Arial, size = 10, style=normal, $ type=title, style=bold, $ type=heading, size=12, style=bold, color=blue, $ type=grandtotal, style=bold, $ END
TABLE FILE GGSALES HEADING " " "Report 2: Coffee - Actual " SUM DOLLARS UNITS COLUMN-TOTAL AS 'Total' BY REGION IF CATEGORY EQ Coffee ON TABLE PCHOLD FORMAT EXL2K ON TABLE SET STYLE * type=report, font=Arial, size=10, style=normal, $ type=grandtotal, style=bold, $ type=heading, size=12, style=bold, color=blue, $ END
TABLE FILE GGSALES HEADING "Report 3: Food - Budget" SUM BUDDOLLARS BUDUNITS COLUMN-TOTAL AS 'Total' BY REGION IF CATEGORY EQ Food ON TABLE PCHOLD FORMAT EXL2K NOBREAK ON TABLE SET STYLE * type=REPORT, font=Arial, size=10, style=normal, $ type=HEADING, style=bold, size=12, color=blue, $ type=title, style=bold, $ type=grandtotal, style=bold, $ END
TABLE FILE GGSALES HEADING " " "Report 4: Food - Actual" SUM DOLLARS UNITS COLUMN-TOTAL AS 'Total' BY REGION IF CATEGORY EQ Food ON TABLE PCHOLD FORMAT EXL2K CLOSE ON TABLE SET STYLE * type=report, font=Arial, size=10, $ type=title, style=bold, $ type=heading,size=12, style=bold, color=blue,$ type=grandtotal, style=bold, $ END
The output is:
After creating an EXL2K formatted file, you must transfer it from the mainframe to your PC to view and use it. The following illustrates the process of using FTP in Microsoft Windows to retrieve the files from the mainframe:
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.
How to: Reference: |
The EXL97 format allows you to view reports in Excel 97, which supports full styling.
Format EXL97 is fully compatible with Excel 2000, Excel 2002, and Excel 2003.
Note: Although format EXL97 is available, there are some limitations when compared to the EXL2K formats. See Limitations for FORMAT EXL97. Future enhancements in the area of Excel integration will primarily be made to the EXL2K and XLSX formats. We recommend upgrading to Excel 2003 or higher so you can take full advantage of our Excel integration, as well as all future enhancements.
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:
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.
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.
Information Builders |