Saving Reports Using Excel Formats

In this section:

How to:

Reference:

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:

For details about displaying reports in Excel formats, see Choosing a Display Format in the Creating Reports With WebFOCUS Language manual.


Top of page

x
Procedure: How to Create an EXL2K Report in Internet Explorer

The following example illustrates how to create a report in EXL2K format:

  1. After selecting the CENTCOMP data source to report against, open the Report Painter.
  2. Type STORE NAME BY STATE in the heading area.
  3. Move the insertion point to the report and add the following fields from the Object Inspector: STATE, REGION, STORE_CODE, and STORENAME.
  4. Select the Store Name column and click the Sum button.
  5. Select the State, Region, and Store ID columns and click they By button.
  6. Select Format from the Report menu. The Report Options dialog box opens at the Format tab.
  7. Set your report output format to Excel 2000 by selecting Excel 2000 (EXL2K) from the Select Format drop-down list.
  8. In the Customize worksheet title field, type State Report.
  9. Click OK, then click Run.

The output is:

Store name by state

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.


Top of page

x
Reference: Font Support for EXL2K Format

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.


Top of page

x
Reference: Unsupported Date and Numeric Formats

The following formats are not supported in EXL2K. They will translate into Excel General format and possibly produce unpredictable results:


Top of page

x
Reference: Using Date Separators in Excel

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


Top of page

x
Using PivotTables in Excel 2000

How to:

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.

PivotTables

When FORMAT EXL2K PIVOT is enabled, two data streams are created:

For more information about PivotTables, see the Creating Reports With WebFOCUS Language manual.



x
Procedure: How to Populate a PivotTable

The following example illustrates how to generate a PivotTable:

  1. After selecting the Car data source to report against, open the Report Painter.
  2. In the heading area, type CAR File PivotTable.
  3. Press the Enter key to add another line to the heading, and type Sum of Sales by Car.
  4. Move the insertion point to the report and add the following fields from the Object Inspector: SALES and CAR.
  5. Select the CAR column and click the By button.

    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.

  6. Select Excel 2000 PivotTable from the Output Format toolbar drop-down list.

    The Object Inspector opens at the Pivot tab and shows the Available Fields list.

    Report Painter

  7. Click COUNTRY in the Available Fields box and drag it to the Page Fields dimension in the Pivot hierarchy.
  8. Click MODEL in the Available Fields box and drag it to the Cache Fields dimension in the Pivot hierarchy.
  9. Run your report.

The report displays the PivotTable in Excel 2000. All fields (including PRINT, BY, and ACROSS fields, and PAGEFIELDS and CACHEFIELDS) appear in the toolbar.

PivotTable

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.


Top of page

x
Viewing a Report in Excel 97 Format

How to:

Reference:

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.



x
Procedure: How to View a Report in EXL97 Format

The following example illustrates how a report appears in EXL97 Format.

  1. After selecting the Centord data source to report against, open the Report Painter.
  2. In the heading area, type Plant Revenue in Excel 97 Format.
  3. Move the insertion point to the report and add the following fields from the Object Inspector: PLANTLNG and LINEPRICE.
  4. Select the Line Total column in the Report Painter window and click the Sum button.
  5. Select the PLANTLNG column and click the By button.
  6. Select Excel 97 from the Output Format toolbar drop-down list.
  7. Run your report. The output is:

    Excel97



x
Reference: Limitations for FORMAT EXL97

WebFOCUS