In this section:
You can display a report as one of several kinds of Microsoft Excel spreadsheets. The report opens in Excel within a web browser.
You can display a report as an:
In addition, WebFOCUS supports two Excel 2000 variations: EXL2K FORMULA and EXL2K PIVOT. When either of these formats is specified additional processing is done.
Note: Since only single line (single cell) column titles are supported in format EXCEL reports, any additional column title rows are treated as data. For example, if you have a report with a multi-line (multi-cell) column title and you sort the column, the second (and so on) column title rows will be sorted with the data. To avoid this, only select the data instead of the entire column when you select sorting options in Excel.
For details about displaying reports in Excel formats, see Choosing a Display Format in the Creating Reports With WebFOCUS Language manual.
The following example illustrates how to create a report in EXL2K format:
The output is shown in the following image.
Notice that the tab name has been changed from the Excel default, Sheet1, to a more descriptive name, which you specified in the Customize worksheet title input field on the Format tab of the Report Options dialog box.
The EXL2K format supports fonts that are available on both the server and the client. Be sure to verify the existence of a particular font on both the client and the server before saving a report in the EXL2K format.
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 dash (-) as a separator between month, day, and year in Excel, you must change the default date separator for Windows®. This setting can be located under Regional Options in the Control Panel.
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 much like the OLAP tool in WebFOCUS. 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.
Report requests can be created within WebFOCUS and sent as output to a fully formatted Excel PivotTable. The ON TABLE PCHOLD FORMAT EXL2K PIVOT command will generate an Excel PivotTable in your browser, as shown in the following image.
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 appears 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 appear in the report. CACHEFIELDS can be dragged and dropped from the PivotTable toolbar into the PivotTable when required for analysis.
For more information about PivotTables, see the Creating Reports With WebFOCUS Language manual.
Ensure that the SALES column is a Detail field.
Note: The PRINT command must be used. The PivotTable is generated by the PRINT command in combination with the BY, ACROSS, PAGEFIELDS and CACHEFIELDS phrases. See Choosing a Display Format in the Creating Reports With WebFOCUS Language manual.
The Object Inspector opens at the Pivot tab and shows the Available Fields list, as shown in the following image.
The report displays the PivotTable in Excel 2000. All fields (including PRINT, BY, and ACROSS fields, and PAGEFIELDS and CACHEFIELDS) appear in the toolbar, as shown in the following image.
Note: Text fields and Timestamp fields may not be used in Excel PivotTables. In addition, ensure that there is no missing data for any fields.
The EXL97 format allows you to view reports in Excel 97, which supports full styling and drill-down capability.
When you specify EXL97 format, an HTML-based file is generated with an extension of .e97. The appropriate MIME type is automatically assigned to designate Excel as the active application for this file type.
When using the Microsoft Internet Explorer browser and Excel 97, the Excel client opens in the background and the report launches in your browser. You may see the Excel application open and minimize while viewing your report. Leave Excel open when viewing the spreadsheet.
After the report is generated, you can save the document as an Excel spreadsheet locally on your PC. In Excel 97, you will be prompted to save the document as a Microsoft Excel Workbook with an .xls extension. This will save the file as a binary Excel document.
For more information about Excel 97 format, see Choosing a Display Format in the Creating Reports With WebFOCUS Language manual.
Format EXL97 is fully compatible with Excel 2000 and Excel 2002.
Note: Although format EXL97 is available, there are some limitations when compared to the Excel 2000 (EXL2K) format. See Limitations for FORMAT EXL97. Future enhancements in the area of Excel integration will primarily be made to the EXL2K format. We recommend upgrading to Excel 2000 or higher so you can take full advantage of our Excel integration, as well as all future enhancements.
Page numbers appear by default and are placed in the heading. We recommend that you turn page numbering off (SET PAGE-NUM=OFF).