In this section: |
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.
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 PCHOLD 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:
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.
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 and drop 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 EXL07 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=ON attribute to your FOCUS StyleSheet
TYPE=REPORT, ROWOVERFLOW=ON, ...
This attribute will work only with EXL2K output, which generates web archive files (.XHT). For all other output types, the ROWOVERFLOW StyleSheet attribute is ignored, and data flow is not affected.
The ROWOVERFLOW=ON attribute is required in the FOCUS StyleSheet in order to set a target number of rows for the Worksheets in the EXL2K output file.
In a FOCEXEC or profile, use the following syntax
SET LINES = nnnnn
In a request, use the following syntax
ON TABLE SET LINES nnnnn
where:
(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:
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 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:
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 EXL07 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 |