Using Excel Display Formats: EXL07, EXL2K, EXL97, EXCEL

In this section:

Reference:

You can create a WebFOCUS report as one of several kinds of Microsoft Excel workbook.

The report formats and Excel workbooks that you can create are:

You can specify that a report display as an Excel worksheet when you run the report in WebFOCUS. You can use either:


Top of page

x
Reference: Viewing Excel Workbooks in the Browser vs. the Excel Application

Your desktop settings determine whether Excel output sent to the client is displayed in an Internet Browser window or within the Excel Application. When Excel output has been defined within the Windows environment to Browse in same window, the workbook generated by a WebFOCUS request is opened within an Internet Explorer browser window. When the Browse in same window option is unchecked for the .xls file type, the browser window created by WebFOCUS is blank because the report output is displayed in the standalone Excel application window.

Note: This works the same for both EXL2K and EXL07 formats. The only difference is the selection of file type based on the version of Excel output you will be generating.


Top of page

x
Reference: Redirection and Excel Drilldown Reports

The WebFOCUS Client can use redirection when passing the report output to the client application. When redirection is enabled, the WebFOCUS Client saves report output in a temporary directory when a request is executed. Then, an HTTP call is made from the browser to retrieve the temporary stored output for display. When redirection is disabled, the report output is sent directly to the browser without any buffering.

When redirection is enabled, drilldowns within Excel reports will work differently depending on whether the workbook is opened in the browser or in the Excel application. For information about launching Excel in the browser or as an application, see Viewing Excel Workbooks in the Browser vs. the Excel Application.

For additional information about redirection options, see WebFOCUS Administration Console Client Settings described in the WebFOCUS Security and Administration manual.


Top of page

x
Assigning EXL07 Format to Your Report Output

In this section:

EXL07 (Excel 2007/2010) format generates fully styled reports in Excel 2007/2010 binary display format. The WebFOCUS 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):



x
Overview of EXL07 Format

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). For more information, see Opening EXL07 Report Output in Excel 2000/2003.

However, if a user runs an interactive EXL07 request using PCHOLD, and the WebFOCUS Client Redirection configuration for .xlsx has the Save As option disabled (which is the default), the WebFOCUS Client will send the report output with an Excel 2007/2010 application association instead of the .xlsx extension. Any user that does not have Excel 2007 or 2010 installed will get a message that no application is associated with the content being sent. In this case, there are two choices:



x
Reference: Building the .xlsx Workbook File

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 WebFOCUS 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 Reporting Server performs the xml generation process. The zipping process can be completed either by the client (WebFOCUS Servlet) or the server (JSCOM3):

  • WebFOCUS Servlet. The WebFOCUS Client within the application server performs the zipping process. This can be done within the local client or through a remotely accessed client. The servlet method is the default approach defined for each WebFOCUS client, with the client by default pointing to itself.
  • JSCOM3. The java layer of the Reporting Server performs the zipping operation. This option can be used within secured environments because it does not require URL access to a remote client.


x
Syntax: How to Select the Method for Zipping the .xlsx File

You designate the method and location where the zipping will occur by setting EXCELSERVURL to a URL (for the WebFOCUS Servlet) or to a blank (for JSCOM3). You can set this value for a specific procedure or for the entire environment:

  • For a procedure. Issue the SET EXCELSERVURL command within the procedure.
  • For the entire environment. Edit the IBIF_excelservurl variable in the WebFOCUS Administration Console by selecting:

    Configuration/Client Settings/General/IBIF_excelservurl

    For more information on accessing the WebFOCUS Administration Console and setting the IBIF_excelservurl variable, see the WebFOCUS Security and Administration manual.

The value you assign to EXCELSERVURL determines whether the WebFOCUS Servlet or JSCOM3 performs the zipping operation:

  • Specifying the Servlet. To specify that the WebFOCUS Servlet should be used, set the EXCELSERVURL parameter or the IBIF_variable to the URL of a WebFOCUS 7.7.02 or higher client configuration. For example,

    In a procedure:

    SET EXCELSERVURL = http://servername:8080/ibi_apps

    In the WebFOCUS Administration Console:

    IBIF_excelservurl = http://servername:8080/ibi_apps
  • Specifying JSCOM3. To specify that JSCOM3 should be used within the current Reporting Server, set EXCELSERVURL to blank or an empty string.

    In a procedure:

    SET EXCELSERVURL = ''

    In the WebFOCUS Administration Console:

    IBIF_excelservurl = ''

By default, each WebFOCUS client contains the following URL definition that points to itself:

&URL_PROTOCOL://&SERVER_NAME:&SERVER_PORT&IBIF_webapp


x
Syntax: How to Generate an Excel 2007 or 2010 Workbook

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 {PCHOLD|HOLD} AS name FORMAT EXL07

where:

PCHOLD

Displays the generated workbook in either the browser or the Excel 2007/2010 application, based on your desktop settings. For information, see Viewing Excel Workbooks in the Browser vs. the Excel Application.

HOLD

Saves a workbook with an .xlsx extension to the designated location.

name

Specifies a file name for the generated workbook.

Note: The AS name will be used for PCHOLD only when the Save Report option is set to YES for the .xlsx file extension in the WebFOCUS Client Redirection Settings. When the report is opened in the browser, users will be prompted to open or save report output with the designated file name. For reports opened in the Excel 2007/2010 application, the generated workbook will retain the designated AS name. For more information, see the WebFOCUS Security and Administration manual.



x
Reference: Opening EXL07 Report Output in Excel 2000/2003

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). When the file extension of the file being opened is .xlsx (Excel 2007/2010 workbook), the Microsoft Office Compatibility Pack performs the necessary conversion to allow Excel 2000/2003 to read and open it.

In addition to the Microsoft Office Compatibility Pack, it is important to enable the WebFOCUS Client Redirection Settings Save As option so that Excel 2000/2003 will be able to open the Excel 2007/2010 report output without users first having to save it to their machine with the .xlxs file extension. The WebFOCUS Client processing Redirection Settings Save As option configures how the WebFOCUS Client sends each report output file type to the user's machine. This option can be set as follows:

  • Save As Option disabled (NO). The WebFOCUS Client Redirection Setting Save As is disabled by default. When the Save As option is disabled, the WebFOCUS Client sends report output to the user's machine in memory with the application association specified for the report format in the WF Client Redirection Settings configuration file (mime.wfs).

    The application association for Excel 2007/2010 is:

    application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

    A user's machine that does not have Excel 2007/2010 installed will not recognize the application association for Excel 2007/2010 and will display the following message:

    The Excel 2000/2003 user can select Save and provide a filename with the .xlsx extension to save the report output to their machine. The user can then open the .xlsx file directly from Excel 2000/2003.

  • Save As Option enabled (YES). When the WebFOCUS Redirection Save As option is enabled, the WebFOCUS Client sends the report output to the user as a file with the extension specified in the WebFOCUS Client Redirection Settings configuration file (mime.wfs).

    Upon receiving the file, Windows will display the File Download prompt asking the user to Open or Save the file with the identified application type. As seen in the following image, the File Download prompt displays the Name with the xlsx file extension for the report output that is recognized as an Excel 2007/2010 file type.

    Note: The download prompt will display for all users, including users who have Excel 2007/2010 installed on their machines.

    If an Excel 2000/2003 user chooses to open the file, the Microsoft Office Compatibility Pack will recognize the .xlsx file extension and perform the necessary conversion to allow Excel 2000/2003 to read the Excel 2007/2010 workbook.

    If an Excel 2007/2010 user chooses to open the file, Excel 2007/2010 will recognize the .xlsx file extension and read the Excel 2007/2010 workbook.

For additional information on WebFOCUS Client Redirection Settings, see the WebFOCUS Security and Administration Guide.



x
Formatting Values Within Cells in EXL07 Report Output

WebFOCUS 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 WebFOCUS 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 WebFOCUS. 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.



x
Displaying Formatted Numeric Values in EXL07 Report Output

Each numeric WebFOCUS 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 WebFOCUS formats for the following numeric data types are translated into Excel 2007/2010 format masks supporting full editing within the resulting workbook:

  • Data types: E, F, D , I, P.
  • Comma edit option (C).
  • Zero suppression (S).
  • Leading zero (L).
  • Floating currency symbol (M).
  • Comma Suppression (c).
  • Right-side minus sign (-).
  • Credit negative (CR).
  • Bracket negative (B).
  • Fixed extended currency symbol (!d, !e, !l, !y).
  • Floating extended currency symbol (!D, !E, !L, !Y).
  • Percent (%).


Example: Passing Numeric Formats to EXL07 Report Output

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 WebFOCUS 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 PCHOLD FORMAT EXL07
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 WebFOCUS 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 WebFOCUS 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 PCHOLD FORMAT EXL07
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:



x
Using Numeric Formats in Report Headings and Footings

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 information about the HEADALIGN attribute, see Aligning a Heading or Footing Element in an HTML, EXL07, EXL2K, or PDF Report.



x
Using Numeric Format Punctuation in Headings and Footings

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.



x
Passing Dates to EXL07 Report Output

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.



Example: Translating WebFOCUS Dates to Excel 2007/2010 Dates

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 PCHOLD FORMAT EXL07
END

The following table shows how the dates should appear:

WebFOCUS Format

EXL07 Displays

EXL07 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 WebFOCUS format. The output is:



x
Passing Dates Without a Day Component

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 WebFOCUS date formats are represented in EXL07. 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 WebFOCUS date format.

DATEFLD/MDYY = '01/02/2010'

WebFOCUS Format

EXL07 Displays:

EXL07 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



Example: Passing WebFOCUS Dates With and Without a Day Component to EXL07 Report Output

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 PCHOLD FORMAT EXL07 
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):



x
Passing Date Components for Use in Excel Formulas

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.



Example: Passing Numeric Date Components to EXL07 Report Output

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 PCHOLD FORMAT EXL07 
END

The output is:



x
Passing Quarter Formats

Date formats that contain a Quarter component are always passed to Excel as text strings since Excel does not support Quarter formats.



Example: Passing Dates With a Quarter Component to Excel 2007

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 PCHOLD FORMAT EXL07 
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:



x
Passing Date Components Defined as Translated Text

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:

  • MT, MTR, Mt, Mtr
  • W, w, WR, wr

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 WebFOCUS format. However, since the values in this example are always sent as text, the casing defined in the WebFOCUS format is applied in the resulting cell.



Example: Passing Date Components Defined as Translated Text to EXL07 Report Output

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 PCHOLD FORMAT EXL07 
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:



x
Reference: Usage Notes for Date Values in EXL07 Report Output
  • The following date formats are not supported in EXL07. They will translate into Excel General format and possibly produce unpredictable results:
    • JUL, YYJUL, and I2MT.
    • Dates stored as a packed or alphanumeric field with date display options.
  • Excel only supports mixed-case, not all uppercase or all lowercase for text dates. All WebFOCUS date formats containing text present in EXL07 as mixed-case, regardless of the casing parameters defined in the WebFOCUS format. For example, MTRDY will generate the date string JANUARY 2, 10 in standard WebFOCUS, but when sent to EXL07 as a date value, it will be presented as January 2, 10.


x
Support for Drilldowns With EXL07 Report Output

Drilldowns are supported within the data elements in a report in EXL07 format in the same manner as they are supported in EXL2K format. Hyperlink connections can be defined in the StyleSheet declaration of any data column to provide access to any external Web source or to execute a FOCEXEC with data-driven parameters for any supported output format, other Excel formats, PDF, HTML, DHTML, and PPT.

The JAVASCRIPT and IMAGE drilldown options are not supported with format EXL07.



x
Reference: WebFOCUS Client Redirection and EXL07 Reports

Redirection is disabled by default for the .xlsx file extension because this enables drilldowns to run successfully whether the user machine is configured to launch Excel in the browser or as an application outside of the browser.

For information on WebFOCUS Client redirection and Excel Drilldown reports, see Redirection and Excel Drilldown Reports.



x
Creating Excel 2007/2010 Worksheets Using Templates

EXL07 report output can be generated based on Excel 2007/2010 templates. This feature allows for the integration of WebFOCUS reports into workbooks containing multiple worksheets, as well as native Excel 2007/2010 formulas, tables, graphs, images, and cell, page, and worksheet formatting. Any native Excel 2007/2010 template can be used to generate a new workbook containing a WebFOCUS report.

WebFOCUS EXL07 TEMPLATE format provides support for basic Excel templates (.xltx) files. These templates do not support macros or other content that Microsoft considers active.

A WebFOCUS EXL07 template procedure generates a native Excel 2007/2010 workbook with the standard .xlsx extension, based on the defined template file. The WebFOCUS request will replace an existing worksheet within the template workbook, and any formulas or references defined in other worksheets to cells within the replaced worksheet will automatically update when the workbook is opened.

The designated worksheet is replaced with each execution of the WebFOCUS procedure. Any content added to the replaced worksheet within the template will not be retained. Content contained on any other worksheets will be retained and updated.

Named ranges can be defined within the procedure using the INRANGES attribute to designate cell groupings that can be referenced by other worksheets. For information on the INRANGES attribute, see the Creating Reports With WebFOCUS Language manual.

An Excel 2007/2010 template can be generated by saving any workbook with the .xltx extension. The template file should be stored within your application path (EDAPATH or APP PATH) rather than the default Excel template directory so that it can be accessed by the Reporting Server when the procedure is executed.

The EXL07 TEMPLATE feature is supported in Version 7 Release 7.02 for basic EXL07 format reports. The following features are not supported with EXL07 TEMPLATE in this release: PIVOT, BYTOC, FORMULA, EXL97, EXCEL, and compound Excel reports.

In most cases, existing Excel 2000/2003 templates created as .mht files can easily be converted to Excel 2007/2010 templates by opening the .mht file in Excel 2007/2010 and resaving the file as an Excel template (.xltx) file. Active content, including macros, will be removed from the file, but native Excel formulas and functionality should be retained.



x
Syntax: How to Create an EXL07 Report Using a Template
ON TABLE PCHOLD FORMAT EXL07 TEMPLATE template_name SHEETNUMBER n

where:

template_name

Is the name of the Excel template file (workbook) residing on the WebFOCUS Reporting Server.

n

Is the number of the Excel worksheet being replaced in the template file (workbook).



x
Creating Excel Table of Contents Reports

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.



x
Syntax: How to Use the Excel Table of Contents Feature

The syntax is identical to that of HTML TOC reports, except that only a single BY field is allowed in EXL07, while multiple BY fields are allowed in HTML.

ON TABLE {HOLD|PCHOLD} FORMAT EXL07 BYTOC

Since only one level of TOC is allowed for EXL07 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 EXL07 TEMPLATE format.



x
Reference: How to Name Worksheets
  • The worksheet tab names are the BY field values that correspond to the data on the current worksheet. If the user specifies the TITLETEXT keyword in the stylesheet, it will be ignored.
  • Excel limits the length of worksheet titles to 31 characters. The following special characters cannot be used: ':', '?', '*', and '/'.
  • If you want to use a date field as the BY field, you can include the - character instead of the / character. The - character is valid in an Excel tab title. However, if you do use the / character, WebFOCUS will substitute it with the - character.


Example: Creating a Simple TOC Report

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 PCHOLD 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:



x
Format EXL07 Limitations

Format EXL07 in WebFOCUS Version 7 Release 7.02 does not support the following features currently supported for EXL2K:


Top of page

x
Assigning EXL2K Format to Your Report Output

In this section:

 

The command ON TABLE PCHOLD FORMAT EXL2K generates a fully styled Excel report in your browser, with conditional styling and drill-down 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 and drill-downs).

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 WebFOCUS 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 WebFOCUS. WebFOCUS can also run directly from Excel without using a browser by creating a hyperlink within Excel that calls the WebFOCUS client with the necessary parameters. For details, see Embedding a WebFOCUS Report in an Excel 2000/2003 Workbook.

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.

Tip: If an Excel report is launched within a frame, the Excel toolbar options are not available. To retain access to the toolbar when working with frames, launch the report outside of the frameset, in a new window. For details on using target frames, see Linking a Report to Other Resources.



Example: Creating an EXL2K Report

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 PCHOLD 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 Using Headings, Footings, Titles, and Labels.



x
Reference: Usage Notes for EXL2K Output Files

Certain EXL2K format reports, such as Pivot Tables, generate multiple output files. The main document is returned to the browser (which opens it in Excel), and the secondary file (such as the pivot cache file in a Pivot Table report) is linked through a URL in the main file, to be retrieved by an HTTP request that Excel makes to the WebFOCUS servlet. When used with HOLD instead of PCHOLD, the output is a single web archive file with the same .xht suffix as a normal EXL2K HOLD file. In Excel, this file format is designated in the Save dialog as Single File Web Page.

Note: WEBARCHIVE=ON uses a file format that packages all of the documents into a single file.

For additional information about redirection settings for EXCEL formats with SET WEBARCHIVE=ON, see Microsoft Excel Report Output Considerations in the WebFOCUS Security and Administration Manual.



x
Reference: Font Support for EXL2K Format

The EXL2K (Excel 2000/2003) format supports fonts that are available on the WebFOCUS client. Be sure to verify the existence of a particular font on the client before displaying or saving a report in the EXL2K format.



x
Identifying Null Values in EXL2K Output Files

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.



x
Syntax: How to Identify Null Values in EXL2K Reports

To identify null values in EXL2K reports, use

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

where:

character_string
Is the string of characters displayed in the cells of the report for each field where null values are retrieved from the database. The maximum number of characters is 11. If the number of characters in the string exceeds the length of the output field, the additional characters will not be displayed. If special characters are used, the string must be enclosed in single quotes. SET EMPTYCELLS OFF must also be specified to make the SET NODATA command effective.
ON
Indicates that empty spaces are displayed in the cells of the report for each field where null values are retrieved from the database. ON is the default.
OFF
Indicates that zeros, or the character string specified with the SET NODATA command, will be displayed in the cells of the report for each field where null values are retrieved from the database. OFF must be specified when using SET NODATA.


Example: Identifying Null Values in EXL2K Report Output

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

TABLE FILE CAR
   SUM SALES BY COUNTRY ACROSS SEATS
   ON TABLE PCHOLD 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 PCHOLD 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 PCHOLD 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:



x
Displaying Formatted Numeric Values in EXL2K Report Output

Excel 2000/2003 workbooks generated by WebFOCUS EXL2K format contain the numeric formatting specified in the Master File for the data source or in a temporary field. WebFOCUS numeric values (such as currency) are translated to supported Excel formats and display properly in Excel 2000/2003.

When translating numeric formats from WebFOCUS 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.



Example: Displaying Formatted Numeric Data in EXL2K Report Output

The following example illustrates how formatted numeric data displays in a worksheet when using the EXL2K format. Note that the format for the Fund Balance 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 SHORT
NEWBLNC/D16.2M = BALANCE;
END
SET PAGE-NUM=OFF
TABLE FILE SHORT
"Short Term Investments"
"Excel 2000 Spreadsheet"
" " 
SUM NEWBLNC AS 'Fund Balance' 
BY MANAGER_ID AS 'Fund Manager'
BY TYPE
BY HOLDER 
ON TABLE SET BYDISPLAY ON
ON TABLE PCHOLD 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=NEWBLNC, JUSTIFY=RIGHT, $ END
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.



x
Reference: Usage Notes for Numeric Formats

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

  • Fixed Dollar (N) formats.
  • Multiple format options. Only single format options are supported when using FORMAT EXL2K. For example, the formats I9C and I9B are supported, but I9BC is not.

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

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


x
Passing WebFOCUS Dates to Excel 2000/2003

Excel 2000/2003 workbooks generated by WebFOCUS EXL2K format contain the numeric formatting specified in the Master File for the data source or in a temporary field. WebFOCUS date formats (such as Smart Dates) are translated to supported Excel formats and display properly in Excel 2000/2003.

When translating date formats from WebFOCUS 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 WebFOCUS 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.



Example: Displaying Formatted Dates in EXL2K Report Output

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 PCHOLD 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.



x
Reference: Usage Notes for Date Formats

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

  • YY, Y, M, D, JUL, and I2MT.
  • Any date format with a Q (quarter).
  • Any packed-decimal (P) date formats.
  • Any alphanumeric (A) date formats.


x
Reference: Using Date Separators in Excel

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.



x
Passing Dates With Translated Text to Excel 2000/2003

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.



x
Syntax: How to Pass Translated Dates to Excel 2000/2003 as Date Values
SET EXL2KTXTDATE = {TEXT|VALUE}

where:

TEXT
Passes date values that contain text to Excel 2000/2003 as formatted text. TEXT is the default value.
VALUE
Passes the types of translated date values that contain text and are supported Excel date formats to Excel 2000/2003 as standard date values with text format masks applied.


x
Reference: Usage Notes for SET EXL2KTXTDATE
  • The following date formats are not supported in EXL2K. They will translate into Excel General format and possibly produce unpredictable results:
    • JUL, YYJUL, and I2MT.
    • Dates stored as a packed or alphanumeric field with date display options.
  • Excel only supports mixed-case, not all uppercase or all lowercase for text dates. When EXL2KTXTDATE is set to VALUE, all WebFOCUS date formats containing text will present in EXL2K as mixed-case, regardless of the casing parameters defined in the WebFOCUS format. For example, MTRDY will generate the date string JANUARY 2, 10 in standard WebFOCUS, but when sent to EXL2K as a date value, it will be presented as January 2, 10.


Example: Passing Dates With Translated Text to Excel 2000/2003

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 PCHOLD FORMAT EXL2K
END

The following table shows how the dates should appear with EXL2KTXTDATE set to TEXT and to VALUE.

WebFOCUS 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 WebFOCUS 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 WebFOCUS format. The output is:



x
Passing Dates Without a Day Component

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 WebFOCUS 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 WebFOCUS date format.

DATEFLD/MDYY = '01/02/2010'

WebFOCUS 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



Example: Passing WebFOCUS Dates With and Without a Day Component to Excel 2000/2003

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 PCHOLD 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 WebFOCUS 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):



x
Passing Date Components for Use in EXL2K FORMULA Reports

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.



Example: Passing Numeric Date Components to Excel 2000/2003

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 PCHOLD FORMAT EXL2K 
END

With SET EXL2KTXTDATE=VALUE, the output is:



x
Passing Quarter Formats

Date formats that contain a Quarter component are always passed to Excel as text strings since Excel does not support Quarter formats.



Example: Passing Dates With a Quarter Component to Excel 2000/2003

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:



x
Passing Date Components Defined as Translated Text

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:

  • MT, MTR, Mt, Mtr
  • W, w, WR, wr

Note that since these values are always sent as text, the casing defined in the WebFOCUS format is applied in the resulting cell.



Example: Passing Date Components Defined as Translated Text to Excel 2000/2003

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 PCHOLD 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:



x
Controlling Column Width and Wrapping in EXL2K Report Output

Data wrapping, column width, and the scroll area can be controlled in an Excel 2000/2003 worksheet when using FORMAT EXL2K. You can:

For details on WRAP and SQUEEZE syntax, see Formatting Report Data.



x
Syntax: How to Wrap Data in EXL2K Report Output
TYPE=REPORT, [COLUMN=column,] WRAP=value, $

where:

column
Designates a particular column to apply wrapping behavior to. If COLUMN is not included in the declaration, wrapping will be applied to the entire report.
value
Is one of the following:
ON
Turns on data wrapping. ON is the default value. With this setting, the column width is determined by the client (Excel). Data wraps if it exceeds the width of the column and the row's height expands to meet the new height of the wrapped data.
OFF
Turns off data wrapping. This setting adjusts the column width of the largest data value in the column. Data will not wrap in any cell in the column.
n
Represents a specific numeric value that the column width can be set to. The value represents the measure specified with the UNITS parameter (the default is inches).

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.



x
Syntax: How to Set Column Width in EXL2K Report Output
TYPE=REPORT, [COLUMN=column,] SQUEEZE={ON|OFF|n}, $

where:

column
Identifies a particular column. If COLUMN is not included in the declaration, default SQUEEZE behavior is applied to the entire report.
n
Represents a specific numeric value that the column width can be set to. The value represents the measure specified with the UNITS parameter (the default is inches).

This is the most commonly used SQUEEZE setting in an EXL2K report.

Note:

  • ON/OFF settings for SQUEEZE are not meaningful for EXL2K, and both produce the default behavior.
  • SQUEEZE = n turns off data wrapping. If a data value is wider than the specified width of the column, it is hidden from view. You can adjust column width in Excel after the worksheet has been generated.
  • SQUEEZE is not supported for columns created with the OVER phrase.


Example: Controlling Column Width and Wrapping in EXL2K Report Output

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 PCHOLD FORMAT EXL2K 
   ON TABLE SET STYLE *
   TYPE=DATA, COLUMN=MYDATE, JUSTIFY=CENTER, $ 
1. TYPE=REPORT, COLUMN=VERYLONG(2), WRAP=OFF, $
2. TYPE=REPORT, COLUMN=VERYLONG(3), WRAP=4.1, $
3. TYPE=REPORT, COLUMN=VERYLONG(4), WRAP=2, $
4. TYPE=REPORT, COLUMN=VERYLONG(5), SQUEEZE=2, $ 
   END

where:

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

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

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

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



x
Locking Columns in EXL2K Report Output

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.



x
Syntax: How to Enable Worksheet Locking

To enable locking, use the following attributes:

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

where:

TYPE=REPORT, PROTECTED=ON
Is necessary to enable worksheet locking. PROTECTED=OFF is the default. If you omit the LOCKED=OFF attribute, the entire worksheet is locked.
LOCKED=ON
Locks the entire worksheet. ON is the default value.
LOCKED=OFF
Unlocks the worksheet as a whole, but enables you to lock or unlock specific cells or groups of cells.


x
Syntax: How to Lock Specific Cells Within a Worksheet

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:

type
Is the type of element that describes the cells to be locked.
columnspec
Is a valid column specification.


Example: Locking an Entire EXL2K Workbook

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 PCHOLD AS EXLFORM1 FORMAT EXL2K          
ON TABLE SET STYLE *                              
TYPE=REPORT, COLOR=BLUE, BACKCOLOR=SILVER, SIZE=9,$
TYPE=REPORT, PROTECTED=ON,            $           
TYPE=HEADING, STYLE=BOLD, SIZE=14, $              
TYPE=TITLE, STYLE=BOLD, SIZE=11,$                 
ENDSTYLE                                          
END                                               

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



Example: Locking a Single Column on an EXL2K Workbook

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

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

The request is:

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

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



x
Generating Native Excel Formulas in EXL2K Report Output

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 WebFOCUS 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).



x
Syntax: How to Save Reports as FORMAT EXL2K FORMULA

Add the following syntax to your request to take advantage of Excel formulas in your workbook:

ON TABLE {PCHOLD|HOLD} FORMAT EXL2K FORMULA

where:

PCHOLD
Displays the output in an EXL2K workbook.
HOLD
Saves the output for reuse in an Excel 2000/2003 worksheet. For details, see Saving and Reusing Your Report Output.


Example: Generating Native Excel Formulas for Column Totals

The following example illustrates how a column total in a report request is translated to an Excel formula when you use the format EXL2K FORMULA. Notice that the formatting of the column total (TYPE=GRANDTOTAL) is retained in the Excel 2000/2003 workbook.

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

TABLE FILE SHORT
HEADING
"Projected Return By Region"
" "
SUM PROJECTED_RETURN AS 'RETURN'
BY REGION AS 'REGION' 
ON TABLE COLUMN-TOTAL 
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 
ON TABLE PCHOLD FORMAT EXL2K FORMULA 
END

The output is:

You can translate any total (subtotal, row total, or column total) to an Excel formula. For related information, see Translation Support for FORMAT EXL2K FORMULA.



Example: Generating Native Excel Formulas for Row Totals

This request calculates totals for returns and balances across continents. The row totals are represented as sums of cell ranges.

TABLE FILE SHORT
HEADING
"Projected Return Across Continent"
" "
SUM PROJECTED_RETURN AS 'RETURN' AND BALANCE AS 'BALANCE'
ACROSS CONTINENT AS 'CONTINENT'
BY REGION AS 'REGION'
ON CONTINENT ROW-TOTAL AS 'TOTAL'
ON TABLE COLUMN-TOTAL AS 'TOTAL'
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
ON TABLE PCHOLD FORMAT EXL2K FORMULA 
END

The following output highlights the formula that calculates the row total in cell I12=C12+E12+G12:



Example: Generating Native Excel Formulas for Calculated Values

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 WebFOCUS 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 PCHOLD 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).



Example: Generating a Native Excel Formula for a Function

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 PCHOLD FORMAT EXL2K FORMULA
ON TABLE SET STYLE *
TYPE=TITLE, SIZE=12, STYLE=BOLD, $
END

The output is:



x
Reference: Translation Support for FORMAT EXL2K FORMULA
  • All standard operators are supported. These include arithmetic operators, relational operators, string operators, IF/THEN/ELSE, and logical operators. However, prefix operators are not supported and column notation is not supported.

    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.

  • The following functions are supported:

    ABS, ARGLEN, ATODBL, BYTVAL, CHARGET, CTRAN, CTRFLD, DECODE DMOD, DOWK, DOWKI, DOWKL, DOWKLI, EDIT (1 argument variant only), EXP, EXPN, FMOD, HEXBYT, HHMMSS, IMOD, INT, LCWORD, LJUST, LOCASE, LOG, MAX, MIN, OVRLAY, POSIT, RDUNIF, RANDOM, RJUST, SQRT, SUBSTR, TODAY, TODAYI, UPCASE, YM. 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 is not supported with the following WebFOCUS commands and phrases:
    • DEFINE.
    • OVER.
    • NOPRINT. 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.
    • Multiple display (PRINT, LIST, SUM, and COUNT) commands.
    • SEQUENCE.
    • RECAP.
    • Prefix operators.
    • SET SUBTOTALS = ABOVE.
  • Formulas for ROW-TOTALs are represented by addition of specific cells, while formulas for COLUMN-TOTALs are represented as sums of cell ranges. For example, =SUM(G2:G10).
  • A formula for a calculated value is generated by translating the internal form of the WebFOCUS expression into an Excel formula.
  • The setting BYDISPLAY ON is recommended, otherwise the sort field value will not be available on all rows for recalculations.
  • Excel 2000/2003 formulas are limited to 1024 characters. Therefore, the translation to an Excel formula will fail if a WebFOCUS total or the result of an expression requires more than 1024 bytes of Excel formula code and an error message will be generated.
  • Conditional styling is based on the values in the original report. If the worksheet values are changed and the formulas are recomputed, the styling will not reflect the updated information.


x
Using PivotTables in EXL2K Report Output

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. You can also create dimensional hierarchies, similar to those created using WITHIN syntax, by using the PAGEFIELDS command.

For information about WITHIN syntax, see the Describing Data With WebFOCUS Language manual.

Report requests can be created in 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.

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

For details on the WEBARCHIVE parameter, see the Developing Reporting Applications manual.

Note: Distributing EXL2K PIVOT reports with ReportCaster requires the WEBARCHIVE parameter to be set to ON.



x
Procedure: How to View the PivotTable Toolbar in Excel 2000/2003

The PivotTable toolbar may not automatically display when a PivotTable is created. To display the PivotTable toolbar, use the following procedure:

  1. Click View in the Excel toolbar.
  2. Highlight Toolbars.
  3. Click PivotTable.

    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.



x
Reference: How TABLE Elements Appear in a Pivot Table

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





x
Enabling PivotTables

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.



x
Syntax: How to Enable the PivotTable

The following are syntax variations that you can use to enable FORMAT EXL2K PIVOT.

  • To view the output immediately in Excel 2000/2003:
    ON TABLE PCHOLD FORMAT EXL2K PIVOT
  • To store output on disk for later viewing
    ON TABLE HOLD FORMAT EXL2K PIVOT AS mypivot

    where:

    mypivot
    Is a name you assign to the HOLD file.

    Two files are generated with this syntax:

    • MYPIVOT.XHT is the main file that is displayed in the browser or Excel window.
    • MYPIVO$.XML is the Pivot Cache file.


x
Reference: Usage Notes for PivotTable Requests

You should consider the following when writing requests for output to a PivotTable:

  • Requests must use the display command PRINT. This is necessary to extract all data (each individual record) into the pivot cache file. The field that follows the PRINT command is designated as the PivotTable data field and must be numeric.

    Note that the SUM and COUNT commands are not supported for EXL2K PIVOT. Nevertheless, you can use this native Pivot Table behavior in Excel after the data has been output to an Excel PivotTable.

  • A field that is used in a PivotTable cannot be listed more than once in the report request. Each field can function in only one role in a FORMAT EXL2K PIVOT request. For example, a Page field cannot display simultaneously as a column and a row field.
  • Styling is based on the initial state of your report and retained only on a limited basis when you pivot the output.
  • NODATA fields will display as blank cells in the PivotTable.
  • Text fields and Timestamp fields may not be used in Excel PivotTables. In addition, ensure that there is no missing data for any fields.
  • You can include column, row, grand totals, and subtotals in a PivotTable. If no totals are specified in a request, no totals will display in Excel 2000/2003.
  • Borders are not supported for Excel Pivot Tables.



Example: Populating a PivotTable

The following example illustrates how to generate a PivotTable:

TABLE FILE CAR
HEADING
"CAR File PivotTable"
"Sum of Sales by Car Across Model"
PRINT SALES
BY CAR
ACROSS MODEL
ON TABLE COLUMN-TOTAL
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=16, STYLE=BOLD,$
ENDSTYLE 
ON TABLE PCHOLD AS CARPIVOT FORMAT EXL2K PIVOT
PAGEFIELDS COUNTRY
CACHEFIELDS RETAIL_COST TYPE 
END

The command ON TABLE PCHOLD FORMAT EXL2K PIVOT displays the PivotTable in Excel 2000/2003. All fields (including PRINT, BY, and ACROSS fields, and PAGEFIELDS and CACHEFIELDS) appear in the toolbar.



x
Designating CACHEFIELDS in PivotTables

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.



x
Reference: Usage Notes for Specifying CACHEFIELDS

Fields designated as CACHEFIELDS must be placed immediately after the PIVOT keyword in the ON TABLE PCHOLD 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.



Example: Using EXL2K PIVOT With CACHEFIELDS

The following example illustrates how to specify CACHEFIELDS to populate the PivotTable toolbar.

TABLE FILE CAR
HEADING
"My PivotTable"
PRINT SALES 
BY COUNTRY BY CAR
ON COUNTRY SUB-TOTAL
ON TABLE SET STYLE *
TYPE=DATA, COLUMN=COUNTRY, COLOR=RED, $
type=heading,color=blue,style=bold, size=14, $
ENDSTYLE 
ON TABLE PCHOLD FORMAT EXL2K PIVOT
CACHEFIELDS MODEL TYPE 
END

The output is:



x
Designating PAGEFIELDS in PivotTables

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:



Example: Using FORMAT EXL2K PIVOT With PAGEFIELDS

This example illustrates the use of PAGEFIELDS syntax to make three fields available in the PivotTable toolbar.

TABLE FILE CAR
HEADING
"My Pivot Table"
PRINT SALES RETAIL_COST
ON TABLE SET STYLE *
type=data, column=country, color=red, $
type=heading, color=blue, style=bold, size=14,$
ENDSTYLE 
ON TABLE PCHOLD FORMAT EXL2K PIVOT
PAGEFIELDS COUNTRY CAR MODEL
CACHEFIELDS TYPE 
END

This output is:



x
Creating EXL2K Reports Using an Excel Template

Excel templates enable users to create WebFOCUS reports with any new or existing Excel workbook residing on the WebFOCUS Reporting Server. WebFOCUS can integrate with complex Excel workbooks containing macros, graphs, or Visual Basic applications with the use of Excel templates. Users also have access to more advanced Excel functionality including filters, subtotals, page and print settings, and advanced report styling.

To create an Excel template, open a new or existing Excel workbook and designate one worksheet to be replaced with a WebFOCUS report. The worksheet being replaced must be visible (not hidden). Each worksheet in the workbook must not be empty (populated with at least one blank in one cell). A workbook utilizing Excel templates must be saved as a Single File Web Page (Web Archive) with an extension of .mht and stored in the Reporting Server application Path (APP PATH).

After saving, if you do not see the .mht file extension for the Excel/2000/2003 template file you created, uncheck the Hide extensions for known file types option in Windows Folder Options View tab dialog:



x
Reference: Support for Excel Templates With EXL2K Display Formats

EXL2K and EXL2K FORMULA are the only formats supported and Excel templates will only work with Excel 2002 and higher versions.

The following are not supported with Excel templates: EXL2K PIVOT, EXL2K BYTOC reports, EXL97, EXCEL, and compound Excel reports.

With EXL2K TEMPLATE format, WEBARCHIVE is automatically on.

The PCHOLD command returns an .xmh file, which the mid-tier converts to an .mht. HOLD creates an .xht file.



x
Syntax: How to Create a Report Using an Excel Template

To create a report using an Excel template, specify the following:

ON TABLE PCHOLD FORMAT EXL2K TEMPLATE template_name SHEETNUMBER #

where:

template_name
The name of the Excel template file (workbook) residing on the WebFOCUS Reporting Server.
#
The number of the Excel worksheet being replaced in the template file (workbook).


x
Utilizing Excel Named Ranges

An Excel Named Range is a name assigned to a specific group of cells within an Excel worksheet that can be easily referenced by WebFOCUS applications. WebFOCUS StyleSheet language facilitates the generation of Named Ranges.

The use of Excel Named Ranges provides many benefits, including the following:



x
Syntax: How to Use Excel Named Ranges

To create Excel Named Ranges, use

TYPE=type, IN-RANGES=rangename, $

where:

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

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

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

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

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


Example: Using Excel Named Ranges

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

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

The Excel output is:

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



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


x
Reference: Support for Excel Named Ranges

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



x
Creating Excel Tables of Contents Reports

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 WebFOCUS 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.



x
Syntax: How to Use the Excel Table of Contents Feature

The syntax is identical to that of HTML TOC reports, except that only a single BY field is allowed in EXL2K, while multiple BY fields are allowed in HTML.

ON TABLE {HOLD|PCHOLD} FORMAT EXL2K BYTOC

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

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 is burst into worksheets according to the value of the first BY field in the report, the report must contain at least one BY field. The bursting field may be a NOPRINT field.



x
Reference: Limitations of TOC Reports
  • A TOC report cannot be embedded in a compound report.
  • A TOC report cannot be a pivot table report.
  • A TOC report can not be generated against a multi-verb request.

Note: FORMULA is not supported with bursting.



Example: Creating a Simple TOC Report

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 PCHOLD 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:



x
Reference: How to Name Worksheets
  • The worksheet tab names are the BY field values that correspond to the data on the current worksheet. If the user specifies the TITLETEXT keyword in the stylesheet, it will be ignored.
  • Excel limits the length of worksheet titles to 31 characters. The following special characters cannot be used: ':', '?', '*', and '/'.
  • If you want to use date fields as the bursting BY field, you can include the - character instead of the / character. The - character is valid in an Excel tab title. However, if you do use the / character, WebFOCUS will substitute it with the - character.


x
Naming EXL2K Worksheets with Case Sensitive Data

The BYTOC option of WebFOCUS 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, WebFOCUS 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, WebFOCUS 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.



Example: Using Case Sensitive Data in an EXL2K TOC Report

In the following example, WebFOCUS 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 PCHOLD 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:



x
Reference: Using Case Sensitive Data as Worksheet Names

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:

  • Converting your values to a single case so that they group together in WebFOCUS as they do in Excel. You can control the collation sequence with the SET COLLATION=SRV_CI command described in Controlling Collation Sequence.
  • Adding a unique sequence number to your high level sort field values so that they continue to create different tabs but the sheet name values remain unique


Example: Creating Unique Case Sensitive Sheet Names

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 PCHOLD 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:



x
Overcoming the Excel 2003 65K Row Limit Using Overflow Worksheets

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 WebFOCUS 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 WebFOCUS page heading and page footing commands generate Worksheet headings and Worksheet footings.



x
Syntax: How to Enable Overflow Worksheets

Add the ROWOVERFLOW=ON attribute to your WebFOCUS 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.



x
Syntax: How to Set a Target Number of Rows for an EXL2K Worksheet

The ROWOVERFLOW=ON attribute is required in the WebFOCUS 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:

nnnnn
Is a target value for the maximum number of lines to be included on any Worksheet in the EXL2K output file when the overflow Worksheet feature is enabled. The value must be less than 65K. Each Worksheet will end within a range of the designated limit. The actual row count for each sheet is calculated individually based on user defined heading/footing lines and other system generated lines added to each sheet by the specific report request. If necessary, you can modify how your report flows across Worksheets by adding or removing space in subheadings and/or subfootings.


x
Reference: Usage Notes for Excel 2003 Overflow Worksheets
  • The report heading is placed once at the start of the first sheet. The report footing is placed once at the bottom of the last overflow sheet.
  • Worksheet headings and column titles are repeated at the top the original sheet and each subsequent overflow sheet. Worksheet footings are placed at the bottom of the original sheet and each subsequent overflow sheet. The data values are displayed on the top data row of each overflow sheet as they would be on a standard new page.
  • Report total lines are displayed at the bottom of the last overflow sheet directly above the final page and table footings.
  • Subheadings, subfootings, and subtotal lines display within the data flow as normal. No special consideration is made to retain groupings within a given sheet.
  • For EXL2K output, if the ROWOVERFLOW attribute is specified in the StyleSheet and SET LINES is greater than 65K, the following message is presented and no output file is generated:
    (FOC3313) The line number limit per EXCEL 2003 worksheet is 65536.
  • Output types that contain formula references (EXL2K PIVOT and EXL2K FORMULA) are not supported. Formula references are not automatically updated to reflect placement on new overflow worksheets.
  • The overflow Worksheet feature applies to rows only, not columns. A new Worksheet will not automatically be created if a report generates more than the Excel 2003 limit of 256 columns.
  • ROWOVERFLOW is supported for compound layout and BYTOC reports.
  • As named ranges in Excel cannot run across multiple worksheets, the IN-RANGES phrase that defines named ranges in the resulting workbook is not supported with the ROWOVERFLOW feature. When they exist together in the same request, ROWOVERFLOW takes precedence and the IN-RANGES phrase is ignored.


Example: Creating Overflow Worksheets With EXL2K Report Output

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 PCHOLD 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:



x
Providing a Tooltip on a Drilldown Hyperlink for EXL2K Reports

You can use the ALT attribute in a StyleSheet for EXL2K report output to provide informational text (a tooltip) for a drilldown hyperlink.



x
Syntax: How to Provide a ToolTip for a Drilldown Hyperlink

For a drilldown hyperlink to a FOCEXEC:

TYPE=type, [subtype], FOCEXEC=fex[(parameters ...)], [TARGET=frame,]
 [ALT='tooltip_text',] $

For a drilldown hyperlink to a URL:

TYPE=type, [subtype], URL=url[(parameters ...)], [TARGET=frame,] [ALT='tooltip_text',] $

where:

type
Identifies the report component that you select in the Web browser to execute the link. The TYPE attribute and its value must appear at the beginning of the declaration.
subtype
Are any additional attributes, such as COLUMN, LINE, or ITEM, that are needed to identify the report component that you are formatting.
fex
Identifies the file name of the linked procedure to run when you select the report component.
url
Identifies any valid URL, including a URL that specifies a WebFOCUS Servlet program, or the name of a report column enclosed in parentheses whose value is a valid URL to which the link will jump.
parameters
Values that are passed to the report or URL.
frame
Identifies the target frame in the Web page in which the output from the drill-down link is displayed.
tooltip_text
Is the informational text to display when the mouse hovers over the hyperlink.


x
Reference: Usage Notes for Drilldown Hyperlinks With EXL2K Report Output

The JAVASCRIPT and IMAGE drilldown options are not supported with format EXL2K.



Example: Providing a Tooltip for a Drilldown Link in a Footing

The following report request summarizes product sales and sorts the data by region, state, and store code. The store code also displays in the subfootings where links display to detailed reports about the sales for the store (by product or by date). Each line of the subfoot contains two text objects and one embedded field.

TABLE FILE GGSALES
HEADING
"Sales Report"
SUM DOLLARS/I08M
BY REGION BY ST BY STCD
ON STCD SUBFOOT
"View Store <STCD Sales By Product"
" "
"View Store <STCD Sales By Date"
ON TABLE PCHOLD FORMAT EXL2K
ON REGION PAGE-BREAK
ON TABLE SET PAGE-NUM OFF
ON TABLE SET STYLE *
TYPE=HEADING, SIZE=12, STYLE=BOLD, $
TYPE=SUBFOOT, LINE=1, OBJECT=TEXT, ITEM=2, 
FOCEXEC=PRDSALES(STOREID=STCD), ALT='Product Sales',$
TYPE=SUBFOOT, LINE=3, OBJECT=TEXT, ITEM=2, 
FOCEXEC=HSTSALES(STOREID=STCD),ALT='Sales History', $
ENDSTYLE
END

Using StyleSheet declarations, the subfoot phrase Sales By Product links to a second procedure named PRDSALES and passes it the value of STCD displayed in the subfoot. The subfoot phrase Sales By Date links to a procedure named HSTSALES and passes it the value of STCD displayed in the subfoot. The ALT attributes in the StyleSheet declarations for the hyperlinks provide informational messages.

The linked PRDSALES report follows:

TABLE FILE GGSALES
SUM UNITS
BY STCD
BY PRODUCT
WHERE STCD = '&STOREID'
ON TABLE SET PAGE-NUM OFF
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, $
ENDSTYLE
END

The linked HSTSALES report follows:

TABLE FILE GGSALES
SUM UNITS
BY STCD
BY DATE
WHERE STCD = '&STOREID'
ON TABLE SET PAGE-NUM OFF
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, $
ENDSTYLE
END

The first page of output for the main report follows. If your mouse hovers over the Sales By Date link, the informational text Sales History displays:



x
Embedding a WebFOCUS Report in an Excel 2000/2003 Workbook

Using an optional feature of Excel called Microsoft Web Query, you can embed a WebFOCUS report in a customized Excel worksheet that already contains styling, formulas, and macros.

Note: You can also embed a WebFOCUS report in an Excel workbook using WebFOCUS Quick Data. WebFOCUS Quick Data is a Microsoft Office Add-in that enables you to connect Microsoft Excel directly to WebFOCUS reporting tools where you can access and analyze all of your enterprise data. For information about installing and using WebFOCUS Quick Data, see TM4693: Using the WebFOCUS Quick Data Add-In.

Microsoft Web Query uses a URL to embed external data or an HTML page into the worksheet. Since you can easily construct a URL that calls the WebFOCUS engine, you can use this URL as the source of your query. Then, instead of following the typical reporting path in which you execute a request from the browser and display the output in an Excel worksheet that conforms to the formatting of the report request, you can execute a request that returns HTML from a query inside an existing Excel worksheet whose content and layout you can control in Excel. Said another way, instead of having WebFOCUS push the report into the worksheet, you can have Excel pull the report into the worksheet. This "pull" technique supports the delivery of real time data directly into your own customized worksheets.

The process has two parts:

You must have a web browser installed on your PC to take advantage of this technique.

The following is a customized worksheet that was created using this technique. Notice that it includes logos (images), a report, formulas, and an Excel graph. There are two formulas added outside of the query range that sum and average the data brought back by the query. The report is generated as the result of a web query in the area of the worksheet that has been designated to contain it. The graph is also based on the results of this query. Each time the query is refreshed, the formulas and the graph update as well.

After you understand the rudiments of this technique, you will be able to adapt it to suit your preferred way of working with Excel. For example, you can:

You can implement this technique, with some variations, in Excel 2000/2003 and Excel 97. For illustrations, see Embedding a WebFOCUS Report in an Excel 2000/2003 Workbook and Embedding a WebFOCUS Report in an Excel 97 Spreadsheet. For details on how to set up a web query in Excel 2000 or Excel 97, see your Microsoft Excel documentation.

Note: Microsoft Web Query may not have been installed by default. If you have not already installed it, you will be prompted to install Web Query the first time you attempt to access the feature. Just follow the install instructions provided.



Example: Embedding a WebFOCUS Report in a Customized Excel 2000/2003 Worksheet

This example creates the HTML report, then uses Web Query to set up the customized worksheet to receive it.

Step 1: Create the WebFOCUS report

Create and save the following request, webq.fex, which you will embed in a customized Excel 2000/2003 worksheet: The numbers to the left of each line of code correspond to annotations that follow the request.

1.  TABLE FILE CENTORD 
2.  SUM LINEPRICE AS 'Sales' 
3.  BY PRODCAT 
4.  BY PRODNAME 
5.  ON TABLE PCHOLD FORMAT HTML 
6.  ON TABLE SET HTMLCSS ON 
7.  ON TABLE SET BYDISPLAY ON 
8.  ON TABLE SET PAGE-NUM TOP 
9.  ON TABLE SET STYLESHEET * 
10. TYPE=REPORT, FONT=ARIAL, GRID=OFF, BACKCOLOR=YELLOW, $ 
11. TYPE=TITLE, STYLE=BOLD, $ 
12. TYPE=DATA, COLOR=RED, WHEN=LINEPRICE LT 15000000, $ 
13. ENDSTYLE 
14. END

Line 1 identifies the sample data source, CENTORD, which contains sales data for the Century corporation.

Lines 2-4 define display and sorting requirements for the report.

Lines 5-6 define the output format as HTML and turn on Cascading Style Sheets, a feature that increases the efficiency and overall styling capabilities of HTML.

Important: The report must be in HTML format and not EXL2K. This is because Web Query imports fully formatted HTML pages or individual HTML tables into a worksheet. Executing an EXL2K request from a query will not return any data to the worksheet.

Line 7 sets BYDISPLAY to ON. This optional setting ensures that repeated sort values in the report are populated with data.

By default, repeated sort values in vertical columns (or BY fields) are suppressed in a WebFOCUS report, leaving blank fields in the report. This behavior is not desirable for Excel reports, which are designed to work with data that is repeated in every row to which it applies. A blank column or row can produce misleading results when sorting data.

Line 8 turns off page numbering and removes any extra lines generated above the column titles.

Lines 9-13 specify styling attributes for the report: background color, conditional styling, and some other miscellaneous styling have been designated. Styling, including drill-downs, carry over into Excel.

Step 2: Designate the area in the worksheet into which Web Query will pull the report

In your worksheet, you designate the area where you want your WebFOCUS report to be displayed. You can designate a cell and let Excel determine how much space to allot to the report or you can specify a range of cells to limit the placement. For some techniques that may help you evaluate and control space requirements, see Tips for Populating Excel Worksheets With WebFOCUS Reports.

  1. Open a worksheet and highlight the cell or range.
  2. From the Data menu, select Get External Data, then select New Web Query.

    The New Web Query dialog box opens.

  3. In this dialog box:
    1. Specify a URL that points to the WebFOCUS server. The URL needs to be constructed as a call to the WebFOCUS Servlet and contain any appropriate parameters that need to be passed.

      In the illustration, the code

      http://servername/ibi_apps/WFServlet?IBIF_ex=webq

      calls the WebFOCUS Servlet and requests the procedure called webq.fex. This is the report created in Step 1.

      Keep in mind that you may also need to pass a user ID and password to get to a secure server. You must add parameters to the query manually. To do this, save the query as an .iqy file and manually add parameters to the URL request. For details, see How to Create a Web Query (IQY) File.

    2. Specify whether the entire page or only the tables should be extracted from the requested file. In this example, select the entire page.

      Since WebFOCUS HTML reports are generated using HTML tables, you can select either The entire page or Only the tables. (At the present time, you cannot identify individual tables in a WebFOCUS HTML report.)

    3. Select Full HTML formatting to ensure that all styling transfers to the worksheet. This step is important if you want a styled report to be carried over to Excel.
    4. Click OK.

    The Returning External Data dialog box opens.

  4. Indicate the range of cells into which you want the data to be returned in your worksheet.

  5. Click the Properties button in the Returning External Data dialog box. The External Data Range Properties dialog box opens.

    Name the query and set its refresh behavior. For example, you can set the query to automatically refresh every time the worksheet opens. (Note that you can also set these properties after the initial execution.)

    Click OK to run the web query. The report is pulled into the area of the worksheet that has been designated to contain it.

  6. Save the worksheet with the embedded query. (Note that if you click the Save Query button, the query information will be saved as an .iqy file that you can use again with other worksheets.)
  7. After the query is created and executed, you can customize your worksheet as much as you like.

Note: You only need to run Web Query once to embed a query in a worksheet. After it is executed, the static data stays in the worksheet as a placeholder for the query. However, as long as you are connected to your network, the internet, or whatever other connection mechanism is in affect for your application, when you open your local worksheet, WebFOCUS delivers the latest information directly into your own customized worksheet. You can also click the Refresh Data option at anytime to update the information.

If you wish to delete a query, delete all the query data in the worksheet.

For an illustration of a customized worksheet that contains the report created in step 1, see Embedding a WebFOCUS Report in an Excel 2000/2003 Workbook. The worksheet includes logos (images), a report, formulas, and an Excel graph. The graph is also based on the results of this query. Each time the query is refreshed, the formulas and the graph update as well.



x
Reference: Tips for Populating Excel Worksheets With WebFOCUS Reports

In addition to the method described in the example Embedding a WebFOCUS Report in an Excel 2000/2003 Workbook, you can employ other techniques to take advantage of Web Query in your worksheet. For example, if you have an existing worksheet with a lot of pre-defined elements, you may need to run the query in a separate worksheet and see how much space it will take up before adding it to the main "working area" of the worksheet. It is a good idea to designate one particular worksheet for your query and have everything else refer to the data via formulas on another worksheet.

Another approach is to include everything in the same worksheet. Other surrounding elements in the worksheet should automatically reposition when the data comes back, but you may want to leave a buffer area around the query to ensure sufficient space for the report output.


Top of page

x
Viewing a Report in EXL97 Format

In this section:

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.

WebFOCUS can also run directly from Excel without using a browser by creating a hyperlink within Excel that calls the WebFOCUS client with the necessary parameters. For details, see Embedding a WebFOCUS Report in an Excel 2000/2003 Workbook.

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.



Example: Viewing a Report in EXL97 Format

The following example illustrates how a report displays when using PCHOLD FORMAT EXL97. The request includes drill-downs to another procedure and formatting options, including 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 SET ACCESSIBLE 508 
ON TABLE PCHOLD FORMAT EXL97 
ON TABLE SET STYLE *
TYPE=HEADING, COLOR=NAVY, SIZE=14, $
TYPE=HEADING, LINE=2, COLOR=RED, $
TYPE=DATA, BACKCOLOR=AQUA, STYLE=BOLD, WHEN=LINEPRICE GT 200000, $
TYPE=TITLE, STYLE=BOLD, $
TYPE=DATA, COLUMN=ORDER_NUM, FOCEXEC=DETAILS, $
END

The output is:



x
Reference: Limitations for FORMAT EXL97


x
Embedding a WebFOCUS Report in an Excel 97 Spreadsheet

WebFOCUS can integrate with Excel 97 to give you a completely customizable spreadsheet with styling, drill-downs, and formulas.

In an effort to integrate Microsoft Excel with the web, Microsoft introduced a host of new capabilities in Excel 97, including the ability to import HTML directly into Excel spreadsheets with formulas, styling, and AutoFilters. In addition, Excel 97 offers an optional feature of Excel, Web Query, which enables users to query specific web pages or servers in order to pull live data from the web into their spreadsheets while maintaining worksheet layout and formulas. A query can be set to automatically refresh and even prompt the user for parameters.

In Excel 97, web queries are text files that are created in an editor and saved in the appropriate directory with the extension .iqy. (Unlike Excel 2000/2003, there is no graphical environment in which to create queries. For details, see Embedding a WebFOCUS Report in an Excel 97 Spreadsheet.)

A web query consists of three or four lines of text separated by carriage returns. The IQY file is only needed the first time a query is executed to establish the data location and parameters. After a query is run inside a spreadsheet and the spreadsheet is saved, the query information is always present and can be refreshed at any time.

The process of embedding a WebFOCUS report into an Excel 97 spreadsheet has three parts:



x
Syntax: How to Create a Web Query (IQY) File

An IQY file is constructed as follows

Type_of_query     [CR] 
Version_of_query  [CR] 
URL               [CR]
{POST|GET} parameters   [CR]

where:

Type_of_query
Is Web. Since Web is the default value, this is an optional entry.
Version_of_query
Is the current version of the Web Query software. 1 is the default value. This entry is optional.
URL
Is a required entry that points to the WebFOCUS server. The URL needs to be constructed as a call to the WebFOCUS Servlet and contain any appropriate parameters that need to be passed.
POST parameters
Is the preferred method of retrieving large amounts of data and sending it data back as a separate line of text. For example:
http://WFserver/ibi_apps/WFServlet[CR]
PARAM1=value & PARAM2=value
GET parameters
Is the preferred method of retrieving smaller amounts of data and appending the data to the URL. For example:
http://server/ibi_apps/WFServlet?PARAM1=value&PARAM2=value
[CR]
Designates required carriage returns.


Example: Embedding a WebFOCUS Report in an Excel 97 Spreadsheet

Step 1: Create the WebFOCUS Report

Create and save the following fully styled WebFOCUS report as webq97. You will embed this report in an Excel 97 spreadsheet. (For proper styling and execution of the request in Excel 97, ensure that Cascading Style Sheets are not in effect; SET HTMLCSS=OFF is the default value.)

The numbers to the left of each line of code correspond to annotations that follow the request.

1.  TABLE FILE CENTORD 
2.  SUM LINEPRICE AS 'Sales' 
3.  BY YEAR AS 'Year' 
4.  BY PRODCAT AS 'Category' 
5.  BY PRODNAME AS 'Product' 
6.  WHERE YEAR EQ '&YEAR'; 
7.  ON TABLE SET BYDISPLAY ON 
8.  ON TABLE PCHOLD FORMAT HTML 
9.  ON TABLE SET PAGE-NUM TOP 
10. ON TABLE SET STYLE * 
11. TYPE=REPORT, FONT=ARIAL, SIZE=10, GRID=OFF, BACKCOLOR=YELLOW, $ 
12. TYPE=TITLE, SIZE=11, STYLE=BOLD, $ 
13. TYPE=DATA, COLOR=RED, WHEN=LINEPRICE LT 15000000, $ 
14. TYPE=DATA, COLUMN=PRODCAT, FOCEXEC=webq2(PRODCAT=N1), $ 
15. ENDSTYLE 
16. END

Line 1 identifies the sample data source, CENTORD, which contains sales data for the Century Corporation.

Lines 2-5 define display and sorting requirements for the report.

Line 6 specifies selection criteria using a variable, &YEAR.

Line 7 sets BYDISPLAY to ON. This setting ensures that repeated sort values in the report are populated with data.

By default, repeated sort values in vertical columns (or BY fields) are suppressed in a WebFOCUS report, leaving blank fields in the report. This behavior is not desirable for Excel reports, which are designed to work with data that is repeated in every row to which it applies. A blank column or row can produce misleading results when sorting data.

Lines 8 defines the output format as HTML.

Important: The report must be in HTML format and not EXL97. This is because Microsoft Query imports fully formatted HTML pages or individual HTML tables into a spreadsheet. Executing an EXL97 request from a query will not return any data to the spreadsheet.

Line 9 turns off page numbering and removes any extra lines generated above the column titles.

Lines 10-15 specify styling attributes for the report: background color, conditional styling, a drill-down to another procedure, and some other miscellaneous styling have been designated. Styling, including drill-downs, carries over into Excel.

Step 2: Create a Web Query (IQY) File

Open a text editor and create the following web query file

1.  WEB 
2.  1 
3.  http://WFserver/ibi_apps/WFServlet?IBIF_ex=webq97
       &YEAR=["YEAR","Select Year"]

where:

Line 1 identifies this as a Web query.

Line 2 identifies the current version of Microsoft Query.

Line 3 is a typical WebFOCUS request using the GET method. The name of the procedure is webq97. The URL line passes a parameter of YEAR into the procedure. The user will be prompted to supply a value for YEAR. Of course, passing parameters is optional. In the example above, we have a dynamic parameter, but the parameters can be static as well. In other words, hard coded in the IQY file.

Note that carriage returns are required between lines in this file.

After you have constructed the query, save the document with an .iqy extension in the Queries directory under Microsoft Office. For example,

<drive>:\Program Files\Microsoft Office\Queries\webq97.iqy

Now that the IQY file is ready, you can create the spreadsheet and embed the query.

Step 3: Embed the Query in an Excel 97 Spreadsheet

This example starts with a blank spreadsheet, embeds the query, and then adds the bells and whistles.

  1. Open a blank spreadsheet. From the Data menu, select Get External Data, followed by Run Web Query. The Run Query dialog box opens.
  2. Locate the IQY file, webq97.icy, and click the Get Data button. The Run Query dialog box opens.

  3. Specify where the data will go in your spreadsheet. You can choose a range in the current spreadsheet or you can choose to put the data into a new worksheet. In this case, specify the range as indicated:

    There is no need to set the range in the case of a blank worksheet, however, you may want to output the data in a new worksheet and use formula references to work with the data.

  4. Click the Properties button in the Returning External Data dialog box. The External Data Range Properties dialog box opens. Name the query and set its behavior. For example, you can set the query to automatically refresh every time the worksheet opens. (Note that you can also set these properties after the initial execution.)

    Click OK.

  5. Click the Parameters button in the Returning External Data dialog box to define the behavior for any parameters you may have in your query. You can prompt the user, set a static value, or use a value from a cell in the worksheet. In this example, you will prompt the user for the YEAR.

    Enter the value 1999.

  6. After you select the range and set all of the query properties, click the OK button.

    The query executes and imports the data. The following message is displayed in the spreadsheet:

    webq97: Getting Data...

    The data appears in the spreadsheet:

  7. Now that the data has been pulled into the spreadsheet, you can add features such as formulas, images, charts, macros, etc.

You can now save this spreadsheet locally on your PC, save it to a shared network drive where multiple users can access the information, or deploy it within your web application. See Deploying a Worksheet With a WebFOCUS Web Query.


Top of page

x
Deploying a Worksheet With a WebFOCUS Web Query

In both Excel 97 and Excel 2000/2003, you can deploy a customized worksheet containing a WebFOCUS Web Query to the web server and access it via a hyperlink. This technique enables you to deploy an Excel "template" that is pumped with live data each time it is accessed by a user. It is intended to work with Excel 97 versions and higher.



Example: Deploying a Worksheet With a WebFOCUS Web Query to a Web Server

You must create a worksheet and enable the query to be refreshed when the file opens.

Select Refresh data on file open under the Refresh Control in the External Data Range Properties dialog box.

To ensure that the query is embedded in the worksheet, execute the Query and save the worksheet before deploying.

To set up the launch page, create a link that calls the worksheet. For example,

<a href=http://server/ibi_html/webq97.xls>Excel Sales Report</a>

Now, multiple users can benefit from a deployed WebFOCUS/Excel Web Query if running Excel 97 or higher.

Note: To take advantage of this feature, Excel (with Query) needs to be installed on the end-user (client) machine.


WebFOCUS