Saving Report Output in Excel XLSX Format

In this section:

Reference:

With Excel® 2007, Microsoft® introduced enhanced spreadsheet functionality in a new workbook file format. Using WebFOCUS, you can retrieve data from any WebFOCUS supported data source and generate a native XLSX format (Excel 2007, Excel 2010, and Excel 2013) workbook for data analysis and distribution. This section applies to Excel 2007, Excel 2010, and Excel 2013, unless otherwise indicated.

The WebFOCUS XLSX/EXL07 format supports the following Microsoft Office software products:

WebFOCUS generates XLSX workbooks based on the Microsoft XLSX standard. These workbooks are accessible through all browsers and mobile applications that support native Microsoft XLSX files.

Overview of EXL07/XLSX Format

In this section:

FORMAT EXL07 and FORMAT XLSX are synonyms and can be used interchangeably. The FILE SAVED message will always display "XLSX FILE SAVED", regardless of the syntax specified.

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 drill downs):

Building the .xlsx Workbook File

How to:

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 pointing to itself, by default.
  • JSCOM3. The Java layer of the Reporting Server performs the zipping operation. This option should be used when the WebFOCUS Servlet is configured on a secured web or application server. This is because JSCOM3 does not require URL access to a remote WebFOCUS Client.

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 TIBCO 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. 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 a 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://&servername:&server_port&IBIF_webapp

Syntax: How to Generate an Excel XLSX Workbook

You can specify that a report should be saved to an XLSX workbook, displayed in the browser, or displayed in the Excel application.

ON TABLE {PCHOLD|HOLD} AS name FORMAT XLSX

where:

PCHOLD

Displays the generated workbook in either the browser or the Excel 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: To assign a file name to the generated workbook, set the Save Report option to YES for the .xlsx file extension in the WebFOCUS Client Redirection Settings. When opened in the Excel application, the generated workbook will retain the designated AS name. For more information, see the TIBCO WebFOCUS® Security and Administration manual.

Opening XLSX Report Output

Reference:

To open XLSX workbooks, Excel 2013, 2010, or 2007 must be installed on the desktop.

Reference: Opening XLSX Report Output in Excel 2000/2003

Excel 2000 and Excel 2003 can be updated to read Excel XLSX 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 (XLSX 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 XLSX 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 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 machine in memory with the application association specified for the report format in the WebFOCUS Client Redirection Settings configuration file (mime.wfs).

    A user machine that does not have Excel 2007/2010 installed will not recognize the application association for Excel 2007/2010 and Excel will display a message.

    The Excel 2000/2003 user can select Save and provide a file name 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. The File Download prompt displays the Name with the .xlsx file extension for the report output that is recognized as an Excel XLSX 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 XLSX workbook.

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

For additional information on WebFOCUS Client Redirection Settings, see the TIBCO WebFOCUS® Security and Administrationmanual.

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

Your Operating System and 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 stand-alone Excel application window.

  • In Windows XP and earlier, file type specific settings are managed on the desktop within Windows Explorer by selecting Tools/Folder Options, clicking the File Types tab, selecting the extension (.xls or .xlsx), clicking the Advanced button, and checking the Browse in same window box.
  • In Windows 7, Microsoft removed the desktop settings that support opening worksheets in the browser. This means that to change this behavior, you can no longer simply navigate to the Folder Options dialog box, but that you must change a registry setting.

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

Formatting Values Within Cells in XLSX Report Output

In this section:

Reference:

WebFOCUS formats defined in Master Files or within a FOCEXEC will be represented in the resulting cells in an Excel XLSX 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 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 types of data can be passed to Excel:

Note: This behavior is a change from EXL2K format, where cells containing dates and more complex numeric formats were passed as formatted text.

Displaying Formatted Numeric Values in XLSX 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 XLSX 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 XLSX 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 XLSX
ON TABLE SET BYDISPLAY ON
END

In the resulting worksheet, notice that cell C2 containing the DOLLAR value for Midwest Coffee presents the value with the 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 XLSX 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 XLSX
ON TABLE SET BYDISPLAY ON
END

Notice the fixed numeric format defined for the BUDDOLLARS column (Column C) presents the local currency symbol in a fixed position within each cell, regardless of the size of the data value. On the formula bar, the values in the Over Budget calculated field is passed as a negative value where appropriate. In the actual cells, the bracketed styling is applied to the negative values as part of the custom Excel XLSX format mask.

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.

Using Numeric Format Punctuation in Headings and Footings

For data columns, all currency formats are translated using the Excel XLSX 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.

Reference: Usage Note for Sorting an XSLX Report That Contains a Footing

In XLSX format, the report footer is included as a part of the data table in Excel. This is not the same behavior in EXL2K. In EXL2K format, the footer is not included as a part of the table.

For example, if you run the following procedure and sort the data table, the report footer is part of the data table, as shown in the image below the request:

TABLE FILE WF_RETAIL_LITE
PRINT COUNTRY_NAME AS Country
STATE_PROV_NAME AS State
PRODUCT_CATEGORY AS Category
WHERE RECORDLIMIT EQ 10
FOOTING 
"" 
"TEST FOOTING TEST FOOTING" 
ON TABLE PCHOLD FORMAT XLSX 
ON TABLE SET STYLE * 
ENDSTYLE 
END

The output is:

The workaround is to add a named data range to the procedure, as shown in the following procedure:

TABLE FILE WF_RETAIL_LITE
PRINT COUNTRY_NAME AS Country
STATE_PROV_NAME AS State
PRODUCT_CATEGORY AS Category
WHERE RECORDLIMIT EQ 10
FOOTING 
"" 
"TEST FOOTING TEST FOOTING" 
ON TABLE PCHOLD FORMAT XLSX 
ON TABLE SET STYLE * 
TYPE=DATA, IN-RANGES=DATA, $ 
TYPE=TITLE, IN-RANGES=DATA, $ 
ENDSTYLE 
END

The report footer is not part of the data table, as shown in the following image:

Passing Dates to XLSX Report Output

Most translated and smart dates can be sent to Excel as standard date values with format masks, enabling Excel to use them in functions, formulas, and sort sequences.

Excel 2007 only supports mixed-case date text strings so all month and day names are displayed in mixed-case, regardless of how the case has been specified in the WebFOCUS format. For example, the WebFOCUS date format WRYMTRD presents the date text information in uppercase in all non-Excel formats. Excel transforms this value to mixed-case automatically.

In HTML, the date format displays as:

In XLSX, the date format displays as:

Example: Translating WebFOCUS Dates to Excel XLSX 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 XLSX
END

The following table shows how the dates should appear.

WebFOCUS Format

WebFOCUS Display

XLSX Display

XLSX Value

WRMtrDY

FRIDAY, January 1 10

Friday, January 1 10

1/1/2010

wDMTY

Fri, 1 JAN 10

Fri, 1 Jan 10

1/1/2010

wrDMTY

Friday, 1 JANUARY 10

Friday, 1 January 10

1/1/2010

wrYMtrD

FRIDAY, 10 JANUARY 1

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:

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 XLSX. The table shows how the value is preserved in the cell and how the display is generated using the format mask that corresponds to the WebFOCUS date format.

DATEFLD/MDYY = '01/02/2010'

WebFOCUS Format

XLSX Display

XLSX Value

DMYY

02/01/2010

1/2/2010

MY

01/10

1/1/2010

MTY

Jan, 10

1/1/2010

MTDY

Jan 2, 10

1/2/2010

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

The following request against the GGSALES data source creates the date January 2, 2010 and passes it to Excel 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 XLSX 
END

Columns D and E have actual date values with format masks, displayed by Excel 2007/2010 in mixed-case. Since the MTY format does not have a day component, the date value stored is the first of January 2010 (1/1/2010), not the second of January 2010 (1/2/2010):

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 XLSX 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 XLSX 
END

The output is:

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 XLSX Report Output

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 XLSX 
END

In XLSX, the cells containing dates with Quarter components have General format. To see this, open the Format Cells dialog box.

The output is:

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 XLSX 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 XLSX 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 XLSX 
END

In Excel 2007 or 2010, the cells containing the days have General format. To see this, open the Format Cells dialog box.

The output is:

Reference: Usage Notes for Date Values in XLSX Report Output

  • The following date formats are not supported in XLSX. 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.

Passing Date-Time to XLSX

Most WebFOCUS date-time formats can be sent to XLSX as standard date/time values with format masks, enabling Excel to use them in functions, formulas, and sort sequences.

As with the Date formats, Excel only supports mixed-case to date-time fields, so if the date-time format contains text and is supported by Excel, the text will be in mixed-case, regardless of the casing defined within the WebFOCUS format.

Example: Passing Date-Time to XLSX

The following request shows an example against the GGSALES data source.

DEFINE FILE GGSALES
DT1/HYYMDm WITH REGION = DT(20100506 16:17:01.993876);
DPT1/HDMTYYm = DT1;
ALPHA_DATE1/A30 = HCNVRT(DT1,'(HYYMDm)',30,'A30');
END
TABLE FILE GGSALES
PRINT
ALPHA_DATE1
DT1 AS 'HYYMDm'
DPT1 AS 'HDMTYYm'
DT1/HdMTYYBS   AS 'HdMTYYBS' 
DT1/HdMTYYBs   AS 'HdMTYYBs'  
ON TABLE SET SPACES 1
IF RECORDLIMIT EQ 1
ON TABLE PCHOLD FORMAT XLSX 
END

The output is:

Note: Minutes by themselves are not supported in Excel and will be sent as an integer to XLSX with a Custom format.

Also, Excel time formats only support to the milliseconds. WebFOCUS formats that display microseconds will send the value to Excel, but the value will be rounded to milliseconds within the worksheet if the cell is edited.

The following table shows how the date-time values appear.

WebFOCUS Format

XLSX Displays

XLSX Value

HYYMDm

2010/05/06 16:17:01.993

5/6/2010 4:17:02 PM

HDMTYYm

06 May 2010 16:17:01.993

5/6/2010 4:17:02 PM

HdMTYYBS

6 May 2010 16:17:01

5/6/2010 4:17:01 PM

HdMTYYBs

6 May 2010 16:17:01.993

5/6/2010 4:17:02 PM

Generating Native Excel Formulas in XLSX Report Output

In this section:

When you display or save a tabular report request using XLSX 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. A formula for a calculated value is generated by translating the internal form of the WebFOCUS expression into an Excel formula. Worksheets saved using the XLSX FORMULA format are interactive, allowing for "what if" scenarios that immediately reflect any additions or modifications made to the data.

Understanding Formula Versus Value

How to:

Reference:

The XLSX FORMULA format will generate formulas rather than values for the following WebFOCUS TABLE commands: ROW-TOTAL, COLUMN-TOTAL, SUB-TOTAL, SUBTOTAL, and SUMMARIZE, as well as for calculations performed by functions.

  • A DEFINE field will always generate a constant value and not a formula.
  • COMPUTE will generate the formula, except when the COMPUTE is equal to a single variable. In that case, the constant is placed and not the formula.
  • If your report contains a calculated value (generated by the COMPUTE or RECOMPUTE command), all of the fields referenced by the calculated value must be displayed in the report in order for a cell reference to be included in the formula. If the referenced column is not displayed in the workbook, the data value will be placed in the formula, rather than a cell reference. Additionally, if the value cannot be reliably calculated based on the information passed to Excel, the value, rather than an expression, will be used. For example, using the LAST function in WebFOCUS cannot be translated correctly into Excel. In this instance, the LAST value is used in the expression, rather than a cell reference.

XLSX FORMULA is not supported with financial reports created with the Report canvas or the underlying Financial Modeling Language (FML).

For more information, see Translation Support for FORMAT XLSX FORMULA.

Reference: Translation Support for FORMAT XLSX FORMULA

This topic describes translation support for FORMAT XLSX FORMULA. Use of unsupported WebFOCUS features may produce unreliable results.

  • All standard operators are supported. These include arithmetic operators, relational operators, string operators, IF/THEN/ELSE, and logical operators. However, column notation is not supported.

    The IS-PRESENT, IS-MISSING, IS-FROM, FROM, NOT-FROM, IS-MORE-THAN, IS-LESS-THAN, CONTAINS, and OMITS operators are not supported.

    The logical operators AND and OR are not supported in conditional (IF-THEN-ELSE) or logical expressions.

  • The following functions are supported:

    ABS, ARGLEN, ATODBL, BYTVAL, CHARGET, CTRAN, DMOD, DOWK, DOWK, DOWKL, EXP, FMOD, HEXBYT, HHMMSS, IMOD, LCWORD, LOCASE, LOG, MAX, MIN, OVRLAY, POSIT, RDUNIF, SQRT, SUBSTR, TODAY, and UPCASE. The EDIT function is supported for converting formats (one argument variant). It is not supported for editing strings.

    The functions CTRFLD, LJUST, and RJUST are not recommended for justifying data in Excel columns. With the use of Excel proportional fonts, the StyleSheet JUSTIFY attribute is more appropriate.

    Be cautious when using functions that use decimal values as an argument (BYTVAL, CTRAN, HEXBYT). Based on whether the operating environment is EBCDIC or ASCII, the results may be different.

  • XLSX FORMULA is not supported with the following WebFOCUS commands and phrases:
    • DEFINE
    • OVER
    • FOR
    • NOPRINT
    • Multiple display (PRINT, LIST, SUM, and COUNT) commands
    • SEQUENCE StyleSheet attribute
    • RECAP
    • SET HIDENULLACRS
    • SET SUBTOTALS = ABOVE
    • LAST
  • The BYDISPLAY ON setting is recommended to allow the sort field value to be available on all rows for recalculations.
  • If an expression requires more than 1024 characters, WebFOCUS will place the value into the cell, and not the formula.
  • 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.

Syntax: How to Save Reports as FORMAT XLSX FORMULA

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

ON TABLE {PCHOLD|HOLD} FORMAT XLSX FORMULA

where:

PCHOLD

Displays the output in an XLSX workbook.

HOLD

Saves the output for reuse in an Excel 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 XLSX FORMULA format. Notice that the formatting of the column total (TYPE=GRANDTOTAL) is retained in the Excel 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 PCHOLD FORMAT XLSX FORMULA
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, FONT='ARIAL', SIZE=9, TITLETEXT=’By Region’,$
TYPE=TITLE, BACKCOLOR=RGB(102 102 102), COLOR=RGB(255 255 255),$
TYPE=HEADING, SIZE=12, STYLE=BOLD, JUSTIFY=CENTER,$
TYPE=GRANDTOTAL, BACKCOLOR=RGB(210 210 210), STYLE=BOLD,$
END

The output is:

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

Example: Generating Native Excel Formulas for Row Totals

The following 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 PCHOLD FORMAT XLSX FORMULA
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, FONT='ARIAL', SIZE=9, TITLETEXT=’Across Continent’,$
TYPE=TITLE, BACKCOLOR=RGB(102 102 102), COLOR=RGB(255 255 255),$
TYPE=HEADING, SIZE=12, STYLE=BOLD, JUSTIFY=CENTER,$
TYPE=ACROSSTITLE, STYLE=BOLD,$
TYPE=GRANDTOTAL, BACKCOLOR=RGB(210 210 210), STYLE=BOLD,$
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

The following request totals the columns for retail cost and dealer cost, and calculates the value of a field called PROFIT by subtracting the DOLLARS from the BUDDOLLARS.

The formula for the calculated values is generated by translating the internal form of the WebFOCUS expression (PROFIT/D12.2MC = BUDDOLLARS - DOLLARS;) into an Excel formula. In this example, the formulas appear in cells B8, C8, and D8.

All fields referenced in the calculation should be displayed in the report for a valid formula to be created using cell references. Otherwise, it may be created using values not in the report. If the fields used in the calculation are not present in the report and there is a subsequent RECOMPUTE, the formula created for the RECOMPUTE will not be correct.

TABLE FILE GGSALES
ON TABLE SET PAGE-NUM OFF
SUM BUDDOLLARS/I8MC AND DOLLARS/I8MC
COMPUTE PROFIT/D12.2MC = BUDDOLLARS - DOLLARS;
BY REGION
HEADING
"Profit By Region"
" "
ON TABLE COLUMN-TOTAL
ON TABLE PCHOLD FORMAT XLSX FORMULA
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, FONT='ARIAL', SIZE=9, TITLETEXT=’By Region’,$
TYPE=TITLE, BACKCOLOR=RGB(102 102 102), COLOR=RGB(255 255 255),$
TYPE=HEADING, SIZE=12, STYLE=BOLD, JUSTIFY=CENTER,$
TYPE=GRANDTOTAL, BACKCOLOR=RGB(210 210 210), STYLE=BOLD,$
END

The following output highlights the formula that calculates for the column total of PROFIT: D8=SUM(D4:D7).

Example: Generating a Native Excel Formula for a Function

The following example illustrates how functions are translated to Excel reports. The function IMOD divides ACCTNUMBER by 1000 and returns the remainder to LAST3_ACCT. The Excel formula corresponds to =TRUNC((MOD($C3,(1000)))). TRUNC is used when the answer returned from an equation is being placed into an Integer field, to be sure there are no decimals.

TABLE FILE EMPLOYEE
PRINT ACCTNUMBER AS 'Account Number'
COMPUTE LAST3_ACCT/I3L = IMOD(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 XLSX FORMULA
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, FONT='ARIAL', SIZE=9,$
TYPE=TITLE, BACKCOLOR=RGB(102 102 102), COLOR=RGB(255 255 255), STYLE=BOLD,$
END

The output is:

Reference: Generating a Formula With Recomputed Values

If your report contains a calculated value (generated by the COMPUTE or RECOMPUTE command), all of the fields referenced by the calculated value must be displayed in the report in order for cell references to be included in the formula. If a referenced column is not displayed in the workbook, the data value will be placed in the formula, rather than a cell reference. In the case of RECOMPUTE, the value used may be an incorrect value from the last detail record of the sort break.
Example: Generating a Formula With Recomputed Values

The following request computes the difference (DIFF) by subtracting budgeted dollars from dollar sales. The budgeted dollars field used in the expression is not included in the SUM command. The value of DIFF is recomputed on the region level.

TABLE FILE GGSALES
HEADING
"Profit By Region"
" "
SUM DOLLARS/I8CM 
COMPUTE DIFF/I8CM=DOLLARS - BUDDOLLARS;
BY REGION
BY CATEGORY
ON REGION RECOMPUTE
ON TABLE PCHOLD FORMAT XLSX FORMULA
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, FONT='ARIAL', SIZE=9, TITLETEXT=’By Region’,$
TYPE=TITLE, BACKCOLOR=RGB(102 102 102), COLOR=RGB(255 255 255),$
TYPE=HEADING, SIZE=12, STYLE=BOLD, JUSTIFY=CENTER,$
TYPE=SUBTOTAL, BACKCOLOR=RGB(210 210 210),$
TYPE=GRANDTOTAL, BACKCOLOR=RGB(166 166 166), STYLE=BOLD,$
END

The output shows that the formula is subtracting a data value that is not displayed on the worksheet. It is actually the BUDDOLLARS value from the current hardcoded value, since there is no cell reference.

If you add the BUDDOLLARS column to the request, the formula can be recomputed correctly.

SUM DOLLARS/I8MC BUDDOLLARS/I8MC

The formula generated with the new SUM command contains cell references for both fields used in the calculation.

Using XLSX FORMULA With Prefix Operators

XLSX FORMULA output supports prefix operators that are used on summary lines generated by WebFOCUS commands, such as SUBTOTAL and RECOMPUTE. Where a corresponding formula exists in Excel, these prefix operators are translated into the equivalent Excel summarization formula. The results of prefix operators used directly against retrieved data continue to be passed to Excel as values, not formulas.

The following table identifies the prefix operators supported by XLSX FORMULA when used on summary lines, and the Excel formula equivalent placed in the generated worksheet.

Prefix Operator

Excel Formula Equivalent

SUM.

=SUM()

AVE.

=AVERAGE()

CNT.

=COUNT()

MIN.

=MIN()

MAX.

=MAX()

The following prefix operators are not translated to formulas when used on summary lines in XLSX FORMULA.

  • ASQ.
  • FST.
  • LST.

Note:

  • When using a prefix operator on a field specified directly against retrieved data, there is no space between the prefix operator and the field on which it operates.

    For example, in the following aggregating display command, the AVE. prefix operator operates on the DOLLARS field.

    SUM AVE.DOLLARS
  • When using a prefix operator on a summary line, you must leave a space between the prefix operator and the aggregated field on which it operates.

    In the following summary command, the MAX. prefix operator operates on the DOLLARS field at the REGION sort break. Note the required blank space between the prefix operator and the field name.

    ON REGION RECOMPUTE MAX. DOLLARS

Example: Using a Summary Prefix Operator With FORMAT XLSX FORMULA

In the following request against the GGSALES data source, the RECOMPUTE command for the REGION sort field calculates the maximum of the aggregated DOLLARS field and the minimum of the aggregated BUDDOLLARS field.

TABLE FILE GGSALES
SUM UNITS DOLLARS/I8MC BUDDOLLARS/I8MC
AND COMPUTE DIFF/I8MC= DOLLARS-BUDDOLLARS;
BY REGION
BY CATEGORY
WHERE CATEGORY EQ 'Food' OR 'Coffee'
WHERE REGION EQ 'West' OR 'Midwest'
ON REGION RECOMPUTE MAX. DOLLARS MIN. BUDDOLLARS DIFF
ON TABLE PCHOLD FORMAT XLSX FORMULA
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, FONT='ARIAL', SIZE=9,$
TYPE=TITLE, BACKCOLOR=RGB(102 102 102), COLOR=RGB(255 255 255),$
TYPE=SUBTOTAL, BACKCOLOR=RGB(210 210 210),$
TYPE=GRANDTOTAL, BACKCOLOR=RGB(166 166 166), STYLE=BOLD,$
END

In the output, shown in the following image, the cell that represents the recomputed DOLLARS for the Midwest region has been generated as the following formula.

=MIN(E2:E3)

Example: Using a Prefix Operator on a Display Command With FORMAT XLSX FORMULA

In the following request against the GGSALES data source, the CNT., AVE., and PCT. Prefix operators are used in the SUM display command.

TABLE FILE GGSALES
SUM UNITS
CNT.UNITS
AVE.UNITS
PCT.UNITS
BY REGION 
BY ST 
ON TABLE PCHOLD FORMAT XLSX FORMULA
END

The output shows that the prefix operators were not passed to Excel as formulas. They were passed as data values.

NODATA With Formulas

Reference:

Support for full Excel functionality requires that only valid numeric values are placed into cells that will be used for formula references.

The null value (NODATA='') is supported for calculations. When cells containing the default NODATA symbol (.) are used in a formula, they will cause a formula error.

For example:

SET NODATA=''
TABLE FILE GGSALES
SUM DOLLARS/D12CM UNITS/D12C AND ROW-TOTAL AND COLUMN-TOTAL
COMPUTE REVENUE/D12CM=DOLLARS*UNITS; AS 'Revenue'
BY LOWEST GGSALES.SALES01.CATEGORY
BY GGSALES.SALES01.PRODUCT
ACROSS REGION 
ON TABLE PCHOLD FORMAT XLSX FORMULA
END
------------------------
SET NODATA=''
DEFINE FILE GGSALES
DOLLARMOD/D12CM MISSING ON=IF REGION GT 'V' THEN MISSING ELSE DOLLAR;
END
TABLE FILE GGSALES
SUM DOLLARMOD/D12CM UNITS/D12C AND ROW-TOTAL AND COLUMN-TOTAL
COMPUTE REVENUE/D12CM=DOLLARMOD*UNITS; AS 'Revenue'
BY REGION
BY LOWEST GGSALES.SALES01.CATEGORY
BY GGSALES.SALES01.PRODUCT
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE PCHOLD FORMAT XLSX FORMULA
END

Reference: Usage Notes for XLSX With Formulas

  • Formulas are defined within a single worksheet. They will not be assigned across worksheets.
  • BYTOC compound workbooks can contain formulas. A separate worksheet/tab is generated for each primary key value and formulas are defined with references within that sheet. In BYTOC compound workbooks, a separate tab is generated for overall grand totals. These will not contain formula references to the component worksheets.

Controlling Column Width and Wrapping in XLSX Report Output

How to:

Syntax: How to Set Column Width in XLSX Report Output

TYPE=REPORT, [COLUMN=column,] SQUEEZE=value,$

where:

column

Identifies a particular column. If COLUMN is not included in the declaration, default SQUEEZE behavior is applied to the entire report.

value

Is one of the following:

ON

Automatically sizes the columns based on the largest data value in the column. This is the default behavior.

OFF

Sizes the columns based on the maximum size defined for the field in the Master File or Define.

n

Represents a specific numeric value for which the column width can be set. The value represents the measure specified with the UNITS parameter (the default is inches). This is the most commonly used SQUEEZE setting in an XLSX report. This turns off data wrapping.

Note:

  • SQUEEZE can be applied to the entire report by using the ON TABLE SET SQUEEZE ON command.
  • SQUEEZE is not supported for columns created with the OVER phrase or with TABLEF.

Syntax: How to Wrap Data in XLSX 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 height expands to meet the new height of the wrapped data.

OFF

Turns off data wrapping. 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.

Note: WRAP is not supported for Date format fields.

Example: Controlling Column Width and Wrapping in XLSX 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 GGSALES
PROFIT/D14.3 = BUDDOLLARS-DOLLARS;
DESCRIPTION/A80 = 'Subtract Total Sales Quota from Reported Sales to calculate profit.';
END
TABLE FILE GGSALES
SUM 
DESCRIPTION AS 'DEFAULT' 
DESCRIPTION AS 'WRAP = 2'
DESCRIPTION AS 'WRAP = OFF'
DESCRIPTION AS 'SQUEEZE = 1.5' 
PROFIT
BY REGION NOPRINT
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET STYLE *
TYPE=REPORT, COLUMN=DESCRIPTION(2), WRAP=2,$
TYPE=REPORT, COLUMN=DESCRIPTION(3), WRAP=OFF,$
TYPE=REPORT, COLUMN=DESCRIPTION(4), SQUEEZE=1.5,$
END

where:

  1. The column titled "DEFAULT" illustrates the default column width and wrapping behavior.
  2. The column titled "WRAP=2" sets the column width to 2 inches with data wrapping on.
  3. The column titled "WRAP=OFF" turns off data wrapping for that column.
  4. The column titled "SQUEEZE=1.5" sets the column width to 1.5 inches with data wrapping off.

Since the output spans two pages, the output is shown below in two separate images.

The following XLSX output displays the different behavior for the "DEFAULT" and "WRAP=2" columns.

The following XLSX output displays the output for the "WRAP=OFF" and "SQUEEZE=1.5" columns.

Synchronizing WebFOCUS Page Breaks With Excel Page Breaks

When using the BY_field PAGE-BREAK phrase, WebFOCUS page breaks are automatically synchronized with Microsoft Excel page breaks.

Example: Synchronizing WebFOCUS Page Breaks With Excel Page Breaks in Format XLSX Report Output

The following request generates format XLSX report output with WebFOCUS page breaks that are inserted using the BY REGION PAGE-BREAK phrase.

TABLE FILE GGSALES
HEADING
"Sales Report by Region"
" "
SUM UNITS BUDUNITS DOLLARS BUDDOLLARS
BY REGION PAGE-BREAK
BY DATE
BY CATEGORY
WHERE CITY LE 'Memphis'
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET STYLE *
TOPMARGIN=1.25, BOTTOMMARGIN=1, $
TYPE=REPORT, FONT=ARIAL, SIZE=9, $
TYPE=TITLE, STYLE=BOLD, SIZE=10, $
TYPE=HEADING, STYLE=BOLD, SIZE=12, $
ENDSTYLE
END

Using Print Preview in Excel, output for pages 1 and 2 for the Midwest Region are shown in the following images. The default Excel page breaks are synchronized with the page breaks specified in the WebFOCUS request. The page heading and column titles are displayed only when the BY value changes.

Page 1 Output

Page 2 Output

To repeat the page heading and column titles on each printed page, use the BY_field PAGE-BREAK phrase in combination with the XLSXPAGETITLES=ON StyleSheet attribute, as shown in the following procedure.

Note: You can also use XLSXPAGETITLES as a SET command.

TABLE FILE GGSALES
HEADING
"Sales Report by Region"
" "
SUM UNITS BUDUNITS DOLLARS BUDDOLLARS
BY REGION PAGE-BREAK
BY DATE
BY CATEGORY
WHERE CITY LE 'Memphis'
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET STYLE *
XLSXPAGETITLES=ON,
TOPMARGIN=1.25, BOTTOMMARGIN=1, $
TYPE=REPORT, FONT=ARIAL, SIZE=9, $
TYPE=TITLE, STYLE=BOLD, SIZE=10, $
TYPE=HEADING, STYLE=BOLD, SIZE=12, $
ENDSTYLE
END

Using Print Preview in Excel, output for pages 1 and 2 for the Midwest Region are shown in the following images. Notice that the page heading and column titles are repeated on each printed page.

Page 1 Output

Page 2 Output

Note: If your report contains OVER or ACROSS phrases, use the BY_field PAGE-BREAK phrase in combination with the XLSXPAGETITLES=ALL StyleSheet or SET command.

Preserving Leading and Internal Blanks in Report Output

How to:

The SHOWBLANKS command allows you to preserve leading blanks in data cells and headings in XLSX reports. In XLSX, internal blanks will always be retained, but leading and trailing blanks in data fields are removed. You can use the SHOWBLANKS command to retain leading and trailing blanks.

Since XLSX is not HTML-based like EXL2K, setting SHOWBLANKS OFF will not affect internal blanks. By default, EXL2K reduces all embedded blanks to a single blank, while XLSX preserves all embedded blanks. This difference in spacing may cause additional differences in how fields wrap within a cell.

SET SHOWBLANKS

XLSX (not HTML-based)

EXL2K (HTML-based)

SET SHOWBLANKS = ON

Leading and embedded blanks are preserved.

Leading and embedded blanks are preserved.

SET SHOWBLANKS = OFF

Leading blanks are removed, but embedded blanks are respected.

Leading and embedded blanks are removed.

Blanks are handled differently in headings:

Syntax: How to Preserve Leading and Internal Blanks in XLSX Reports

In a FOCEXEC or in a profile, use the following syntax:

SET SHOWBLANKS = {OFF|ON}

In a request, use the following syntax

ON TABLE SET SHOWBLANKS {OFF|ON}

where:

OFF

Removes leading blanks and preserves internal blanks in XLSX report output. OFF is the default value.

ON

Preserves leading and internal blanks in XLSX report output. Also preserves trailing blanks in heading, subheading, footing, subfooting lines that use the default heading or footing alignment.

Example: Preserving Leading and Internal Blanks in XLSX Report Output

The following request creates a variable called SHOWVAR that contains leading, internal, and trailing blanks.

SET SHOWBLANKS = OFF

-SET &SHOWVAR= '  AB  C  ';
DEFINE FILE CAR
SHOWFIELD/A9 = '  AB  C  ';
END

TABLE FILE CAR
ON TABLE SUBHEAD
"SHOWBLANKS OFF"
"/&SHOWVAR/"
""
HEADING
"In Heading:"
"SHOWVAR<+0>&SHOWVAR"
"SHOWFIELD<+0><SHOWFIELD"
""
"In DATA":
PRINT SHOWFIELD
BY COUNTRY 
WHERE RECORDLIMIT EQ 1;
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET STYLE *
HEADALIGN=BODY,SQUEEZE=ON,$
TYPE=TABHEADING,COLSPAN=2,$
END

The following outputs show the differences in XLSX generated using SET SHOWBLANKS = OFF and SET SHOWBLANKS = ON.

SET SHOWBLANKS = OFF with HEADALIGN=BODY (no leading blanks or trailing blanks)

SET SHOWBLANKS = OFF without HEADALIGN=BODY (preserved blanks and concatenated heading items)

SET SHOWBLANKS = ON with HEADALIGN=BODY (leading blanks and trailing blanks)

SET SHOWBLANKS = ON without HEADALIGN=BODY (preserved blanks and concatenated heading items)

Support for Drill Downs With XLSX Report Output

In this section:

Drill downs are supported within the data elements in a report in XLSX 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. Drill downs to FOCEXECs can contain data-driven parameters and can generate any of the supported output formats, including XLSX, PDF, HTML, DHTML, and PPT.

Drill downs within text embedded in headings, subheadings, subfootings, and footings will be implemented for XLSX format in a future release.

Note:

Redirection and Excel Drill-Down 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.

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

When redirection is enabled, drill downs within Excel reports will work differently depending on whether the workbook is opened in the browser (only applies to Windows XP) 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 workbooks opened outside the browser in the Excel application: The current security context and any previously established session-related cookies are not retained, changing the user authorization, so drill-down reports will not have the information required to access the redirected files. The initial workbook will open within Excel, but the target drill-down workbook will not open and you will receive a message stating You are not allowed to access this viewer file. The drill-down feature in Microsoft Office products functioned in WebFOCUS Release 7.7.x because anonymous drill-down access was permitted.

    The following options are available to allow the feature in WebFOCUS Release 8.x:

    • Configure WebFOCUS authentication to allow anonymous access. For more information, see the TIBCO WebFOCUS® Security and Administration manual.
    • Use SSO with IIS/Tomcat Integrated Windows Authentication. Renegotiation occurs automatically and the Excel and PowerPoint reports display correctly.
    • As of WebFOCUS Release 8.x, the Remember Me feature can be enabled on the Sign-in page. If the end user uses the Remember Me feature, a persistent cookie is used.
  • For workbooks opened in the browser (only applies to Windows XP): Drill downs will work with redirection enabled because the browser session has access to the HTTP header and/or cookies that need to be sent with the HTTP request to the WebFOCUS Client in order to obtain the redirected target workbook file.

    Note: For Windows 7, Excel applications no longer display in a browser window.

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

Excel Page Settings

How to:

Excel page settings for the XLSX workbook default to the WebFOCUS standards:

To customize these page settings, turn the XLSXPAGESETS attribute ON and define individual attributes.

If XLSXPAGESETS is turned on, but the page margin attributes are not defined within the procedure, the values will be set to the WebFOCUS default of .25 inches.

Syntax: How to Define Excel Page Settings

[TYPE=REPORT,] XLSXPAGESETS={ON|OFF} [,PAGESIZE={pagesize|LETTER}]
 [,ORIENTATION={PORTRAIT|LANDSCAPE}] [,TOPMARGIN=n] [,BOTTOMMARGIN=m],$

where:

XLSPAGESETS={ON|OFF}

ON causes the page settings defined in the WebFOCUS request to be applied to the Excel worksheet page settings. OFF retains the default page settings defined in the standard Excel workbook. OFF is the default value.

n

Defines the top margin for the worksheet in the units identified by the UNITS parameter (inches, by default). The default value is .25.

m

Defines the bottom margin for the worksheet in the units identified by the UNITS parameter (inches, by default). The default value is .25.

pagesize

Is one of the PAGESIZE values supported in a WebFOCUS StyleSheet. LETTER is the default page size.

PORTRAIT|LANDSCAPE

PORTRAIT displays the report across the narrower dimension of a vertical page, producing a page that is longer than it is wide. PORTRAIT is the default value.

LANDSCAPE displays the report across the wider dimension of a horizontal page, producing a page that is wider than it is long.

Adding an Image to a Report

In this section:

WebFOCUS supports the placement of images within each area or node of the report on the worksheet. An image, such as a logo, gives corporate identity to a report, or provides visual appeal. Data specific images can be placed in headers, footers, and data columns to provide additional clarity and style.

The image must reside on the WebFOCUS Reporting Server in a directory named on EDAPATH or APPPATH. If the file is not on the search path, supply the full path name.

Inserting Images Into Excel XLSX Reports

How to:

Images can be placed in any available WebFOCUS reporting node or element of a worksheet. Supported image formats include .gif and .jpg.

Usage Considerations

  • All images will be placed in the top-left corner of the first cell of the defined area, based on the top and left gap. Defined explicit positioning and justification have not been implemented yet.
  • Standard page setting keywords can be used in conjunction with XLSXPAGESETS to control the page layout in standard reports (not compound).
  • Images placed within a report cell in a row or column is anchored to the top-left corner of the cell. The cell is automatically sized to the height and width to fit the largest image (SQUEEZE=ON).
  • Additional lines may need to be added within a heading, footing, subhead, or subfoot to accommodate the placement of the image.

Syntax: How to Insert Images Into WebFOCUS Report Elements in XLSX Reports

TYPE={REPORT|heading|data}, IMAGE={url|file|(column)} [,BY=byfield] [,SIZE=(w h)] ,$

where:

REPORT

Embeds an image in the body of a report. The image appears in the background of the report. REPORT is the default value.

heading

Embeds an image in a heading or footing. Valid values are TABHEADING, TABFOOTING, FOOTING, HEADING, SUBHEAD, and SUBFOOT. Provide sufficient blank space in the heading or footing so that the image does not overlap the heading or footing text. You may also want to place heading or footing text to the right of the image using spot markers.

data

Defines a cell within a data column to place the image. Must be used with COLUMNS= attributes to identify the specific report column where the image should be anchored.

url

Is the URL of the image file.

file

Is the name of the image file. It must reside on the WebFOCUS Reporting Server in a directory named on EDAPATH or APPPATH. If the file is not on the search path, supply the full path name. When specifying a GIF file, you can omit the file extension.

column

Is an alphanumeric field in the data source that contains the name of an image file. Enclose the column in parentheses ( ). The field containing the file name or image must be a display field or BY field referenced in the request. Note that the value of the field is interpreted exactly as if it were typed as the URL of the image in the StyleSheet. If you omit the suffix, .GIF is supplied, by default. You can use the SET BASEURL command for supplying the base URL of the images. This way, the value of the field does not have to include the complete URL. This syntax is useful, for example, if you want to embed an image in a SUBHEAD, and you want a different image for each value of the BY field on which the SUBHEAD occurs.

byfield

Is the sort field that generates the subhead or subfoot.

SIZE

Is the size of the image. By default, an image is added at its original size.

w

Is the width of the image, expressed in the unit of measurement specified by the UNITS parameter. Enclose the w and h values in parentheses. Do not include a comma between them.

h

Is the height of the image, expressed in the unit of measurement specified by the UNITS parameter.

Example: Adding a GIF Image to a Single Table Request

In the following request, since the referenced images are not part of the existing GGSALES table, the image files (.gif) are being built in the DEFINE and then referenced in the TABLE request. You can NOPRINT fields if you do not want them to display as columns, but the fields must be referenced in the table to include them in the internal matrix. This will allow the images to be placed in the headings, footings, or data cells. The specific location is defined using StyleSheet definitions for attaching the image based on field value.

DEFINE FILE GGSALES
SHOWCAT/A100=CATEGORY || '.GIF';
END
TABLE FILE GGSALES
SUM DOLLARS/D12CM UNITS/D12C 
BY LOWEST CATEGORY NOPRINT
BY SHOWCAT NOPRINT
BY PRODUCT
ACROSS REGION    
WHERE CATEGORY NE 'Gifts'
ON CATEGORY SUBHEAD
" "
“Image in SUBHEAD for Category <CATEGORY "
" "
ON TABLE SUBHEAD
" "
" "
" "
" Report Heading "
" "
" "
ON CATEGORY SUBFOOT
"ON CATEGORY SUBFOOT"
ON TABLE SUBFOOT
"Report Footing"
" "
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE SET ACROSSTITLE SIDE
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, FONT='ARIAL', SIZE=9, TITLETEXT='Food and Coffee',$
TYPE=REPORT, COLUMN=PRODUCT, SQUEEZE=1,$
TYPE=TITLE, BACKCOLOR=RGB(90 90 90), COLOR=RGB(255 255 255), STYLE=BOLD,$
TYPE=ACROSSTITLE, STYLE=BOLD, BACKCOLOR=RGB(90 90 90), 
COLOR=RGB(255 255 255),$
TYPE=ACROSSVALUE, BACKCOLOR=RGB(218 225 232), STYLE=BOLD, JUSTIFY=CENTER,$
TYPE=HEADING, STYLE=BOLD, COLOR=RGB(0 35 95), SIZE=12, JUSTIFY=Center,$
TYPE=FOOTING, BACKCOLOR=RGB(90 90 90), SIZE=12, COLOR=RGB(255 255 255), STYLE=BOLD, JUSTIFY=CENTER,$
TYPE=SUBHEAD, SIZE=12, STYLE=BOLD, BACKCOLOR=RGB(218 225 232), JUSTIFY=CENTER,$
TYPE=SUBHEAD, IMAGE=(SHOWCAT), SIZE=(.6 .6),$
TYPE=SUBFOOT, SIZE=10, STYLE=BOLD, JUSTIFY=CENTER,$
TYPE=TABHEADING, SIZE=12, STYLE=BOLD, JUSTIFY=CENTER,$
TYPE=TABHEADING, IMAGE=gglogo.gif,$
TYPE=TABFOOTING, SIZE=12, STYLE=BOLD, JUSTIFY=RIGHT,$
TYPE=TABFOOTING, IMAGE=tibco.gif, SIZE=(1.0 .6),$
END

The output is shown in the following image.

Example: Adding a GIF Image to a Compound Request

Note: Compound Layout syntax cannot contain hidden carriage return or line feed characters. For purposes of presenting this example, line feed characters have been added so that the sample code wraps to fit within the printed page. To run this example in your environment, copy the code into a text editor and delete any line feed characters within the Compound Layout syntax by going to the end of each line and pressing Delete. In some instances, you may need to add a space to maintain the structure of the string.

APP PATH IBISAMP
SET HTMLARCHIVE=ON
*-HOLD_SOURCE
COMPOUND LAYOUT PCHOLD FORMAT XLSX
UNITS=IN,$
SECTION=section1, LAYOUT=ON, METADATA='prop_with_names, Margins_Left=0.5,
Margins_Top=0.5, Margins_Right=0.5, Margins_Bottom=0.5,
thumbnailscale=4', MERGE=OFF, ORIENTATION=LANDSCAPE, PAGESIZE=Legal,
SHOW_GLOBALFILTER=OFF,$
PAGELAYOUT=1, NAME='Page layout 1', text='Page layout 1', TOC-LEVEL=1, 
BOTTOMMARGIN=0.5, TOPMARGIN=0.5, METADATA='BOTTOMMARGIN=0.5, TOPMARGIN=0.5,
LEFTMARGIN=0,RIGHTMARGIN=0,',$
COMPONENT='report1', TEXT='report1', TOC-LEVEL=2, POSITION=(0.650 0.917),
DIMENSION=(7.250 3.000), BYTOC=0,  ARREPORTSIZE=DIMENSION,
METADATA='left: 0.65in; top: 0.917in; width: 7.25in; height: 3in;
position: absolute; z-index: 1;',$
COMPONENT='chart1', TEXT='chart1', TOC-LEVEL=2, POSITION=(0.735 4.332),
DIMENSION=(7.167 2.917), COMPONENT-TYPE=GRAPH,  ARREPORTSIZE=DIMENSION,
METADATA='left: 0.735in; top: 4.332in; width: 7.167in; height: 2.917in;
position: absolute; z-index: 2;',$
END
SET COMPONENT='report1'
-*component_type report
DEFINE FILE GGSALES
SHOWCAT/A100=CATEGORY || '.GIF';
SHOWDATEQ/Q=DATE;
SHOWDATEY/YY=DATE;
SHOWDATEQY/YYQ=DATE;
END
TABLE FILE GGSALES
SUM DOLLARS/D12CM AS 'Dollars' 
BY REGION AS ''
BY LOWEST CATEGORY
BY SHOWCAT AS 'Data Image'
ACROSS SHOWDATEY AS ''
ACROSS SHOWDATEQ AS ''
WHERE REGION NE 'Midwest' OR 'West'
ON TABLE SET HIDENULLACRS ON
HEADING
" "
"Image in Page Heading "
ON REGION SUBHEAD
" <+0> SUBHEAD: <REGION"
FOOTING
" "
"Image in Page Footing"
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT XLSX 
ON TABLE SET STYLE *
TYPE=REPORT, SIZE=10, BACKCOLOR=NONE, GRID=OFF, FONT='ARIAL',
XLSXPAGESETS=ON, TOPMARGIN=1, BOTTOMMARGIN=1, ORIENTATION=LANDSCAPE,
PAGESIZE=LEGAL, TITLETEXT='With Images',$
TYPE=REPORT, COLUMN=REGION, SQUEEZE=1.5, JUSTIFY=CENTER,$
TYPE=DATA, BACKCOLOR=NONE,$
TYPE=DATA, COLUMN=SHOWCAT, IMAGE=(SHOWCAT), SIZE=(.5 .5),$
TYPE=TITLE, BACKCOLOR=RGB(218 225 232), BORDER=LIGHT, 
STYLE=-UNDERLINE+BOLD,$
TYPE=HEADING, IMAGE=GGLOGO.GIF, SIZE=(.65 .65),$
TYPE=HEADING, SIZE=12, STYLE=BOLD, JUSTIFY=CENTER,$
TYPE=SUBHEAD, SIZE=10, STYLE=BOLD, BORDER-TOP=LIGHT,$
TYPE=SUBHEAD, BY=1, JUSTIFY=CENTER, BORDER-TOP=LIGHT,$
TYPE=SUBFOOT, STYLE=BOLD,$
TYPE=FOOTING, SIZE=12, STYLE=+BOLD, JUSTIFY=CENTER,$
TYPE=FOOTING, IMAGE=tibco.gif, SIZE=(1.0 .6),$
TYPE=ACROSS, JUSTIFY=CENTER, BORDER=LIGHT,$
TYPE=ACROSSTITLE, STYLE=-UNDERLINE+BOLD,$
TYPE=ACROSSVALUE, BACKCOLOR=RGB(218 225 232), STYLE=-UNDERLINE+BOLD,$
END
SET COMPONENT='chart1'
ENGINE INT CACHE SET ON
-DEFAULTH &WF_STYLE_UNITS='PIXELS';
-DEFAULTH &WF_STYLE_HEIGHT='1005.0';
-DEFAULTH &WF_STYLE_WIDTH='1070.0';
-DEFAULTH &WF_TITLE='WebFOCUS Report';
GRAPH FILE GGSALES
HEADING
"Sales Graph"
SUM 
GGSALES.SALES01.DOLLARS
BY SHOWDATEY AS Year
BY GGSALES.SALES01.REGION
ON GRAPH PCHOLD FORMAT XLSX
ON GRAPH SET VZERO OFF
ON GRAPH SET HTMLENCODE ON
ON GRAPH SET GRAPHDEFAULT OFF
ON GRAPH SET GRWIDTH 1
ON GRAPH SET UNITS &WF_STYLE_UNITS
ON GRAPH SET HAXIS 1000
ON GRAPH SET VAXIS 1000
ON GRAPH SET GRMERGE ADVANCED
ON GRAPH SET GRLEGEND 0
ON GRAPH SET GRXAXIS 2
ON GRAPH SET LOOKGRAPH HBAR
ON GRAPH SET STYLE *
*GRAPH_SCRIPT
setPieDepth(0);
setPieTilt(0);
setDepthRadius(0); 
setCurveFitEquationDisplay(false); 
setPlace(true); 
setPieFeelerTextDisplay(1); 
setUseSeriesShapes(true);
setMarkerSizeDefault(50); 
setScaleMustIncludeZero(getX1Axis(), false);
setScaleMustIncludeZero(getY1Axis(), false);
setScaleMustIncludeZero(getY2Axis(), false);
setMarkerSizeDefault(60); 
*END
INCLUDE=IBFS:/FILE/IBI_HTML_DIR/javaassist/intl/EN/ENIADefault_combine.sty,$
TYPE=REPORT, TITLETEXT='Graph Over Time',$
*GRAPH_SCRIPT
setReportParsingErrors(false);
setSelectionEnableMove(false);
*END
ENDSTYLE
END
COMPOUND END

The output is shown in the following images.

Example: Adding a GIF Image to a BYTOC Compound Request

The following syntax is a portion of the code from the previous example to show the COMPOUND BYTOC syntax. By adding the ON TABLE SET COMPOUND BYTOC command to the compound report above, you can turn the report into a Compound Table of Contents report. The BYTOC syntax can be added to a stand-alone request or to a component of a compound document.

TABLE FILE GGSALES
SUM DOLLARS/D12CM AS 'Dollars'
SHOWREG
NOPRINT
BY REGION AS ''
BY LOWEST CATEGORY
BY SHOWCAT AS 'Data Image'
WHERE REGION NE 'Midwest' OR 'West'
ACROSS SHOWDATEY AS '' ACROSS SHOWDATEQ AS ''
ON TABLE SET HIDENULLACRS ON 
HEADING
"Image in Page Heading" 
ON REGION SUBHEAD
"<+0> Image in SUBHEAD:<REGION" 
FOOTING
" "
"Image in Page Footing"
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT XLSX 
ON TABLE SET COMPOUND BYTOC 
ON TABLE SET STYLE *

The output is:

Inserting Text and Images Into XLSX Workbook Headers and Footers

How to:

Reference:

WebFOCUS supports the insertion of text and images into Excel headers and footers and the definition of key page settings to support the placement of text and images in relationship to the overall worksheet and the Excel generated page breaks. This access to the Excel page functionality is designed to enhance overall usability of the worksheets for users who will be printing these reports. Page settings including orientation, page size, and page margins will directly affect the layout of each Excel page based on values defined within the FOCEXEC. Images and text can be included on headers and footers on every printed page, on the first page of the report only, or only on all subsequent pages. The WebFOCUS headings and footings continue to display within the worksheet. With this feature, WebFOCUS can insert logos to be printed once at the top of a report and watermark images that need to be displayed on every printed page.

Syntax: How to Insert Text and Images Into XLSX Workbook Headers and Footers

To place images in XLSX Workbook headers and footers, the syntax is:

TYPE={PAGEHEADER|PAGEFOOTER},OBJECT=IMAGE,
 IMAGE=imagename, JUSTIFY={LEFT|CENTER|RIGHT} 
 [,DISPLAYON={FIRST|NOT-FIRST}] [,SIZE=(w h)],$

To place text in XLSX Workbook headers and footers, the syntax is:

TYPE={PAGEHEADER|PAGEFOOTER},OBJECT=STRING,
 TEXT=text, JUSTIFY={LEFT|CENTER|RIGHT} 
 [,DISPLAYON={FIRST|NOT-FIRST}] ,$

where:

PAGEHEADER

Places the text or image in the worksheet header.

PAGEFOOTER

Places the text or image in the worksheet footer.

imagename

Is the name of a valid image file to be placed in the header or footer. The image must be located in the defined application path on the Reporting Server. The image types supported are GIF and JPEG.

text

Is the text to be placed in the header or footer.

JUSTIFY={LEFT|CENTER|RIGHT}

Identifies the area in the header or footer to contain the text or image and the justification or placement within that defined area.

DISPLAYON

Defines whether the text or image should be placed on the first page only or on all pages except the first. Omit this attribute to place the text or image on all pages.

Valid values are:

FIRST places the text or image only on the first page.

NOT-FIRST places the text or image on every page, except the first page.

SIZE=(w h)

Is the size of the image. By default, an image is added at its original size.

w is the width of the image, expressed in the unit of measurement specified by the UNITS parameter.

h is the height of the image, expressed in the unit of measurement specified by the UNITS parameter.

Example: Inserting Images in Excel Headers and Footers and Defining Page Settings

The following request against the GGSALES data source places the image tibco.gif on the left header area of the first page and the right header area of every subsequent page of the resulting worksheet. It places the image gglogo1.gif in the center area of the footer on every page.

TABLE FILE GGSALES
SUM DOLLARS UNITS BUDDOLLARS BUDUNITS 
BY REGION
BY ST
BY CATEGORY
BY PRODUCT
ON TABLE SET BYDISPLAY ON
ON TABLE PCHOLD FORMAT XLSX 
ON TABLE SET STYLE *
FONT=ARIAL,SIZE=12,
XLSXPAGESETS=ON,TOPMARGIN=2,BOTTOMMARGIN=2,ORIENTATION=LANDSCAPE,
PAGESIZE=LETTER,$
TYPE=TITLE, COLOR=WHITE, BACKCOLOR=GREY,$
TYPE=PAGEHEADER, OBJECT=IMAGE, JUSTIFY=LEFT, IMAGE=tibco.GIF, DISPLAYON=FIRST,$
TYPE=PAGEHEADER, OBJECT=IMAGE, JUSTIFY=RIGHT, IMAGE=tibco.GIF, DISPLAYON=NOT-FIRST,$
TYPE=PAGEFOOTER, OBJECT=IMAGE, JUSTIFY=CENTER, IMAGE=gglogo.GIF,$
END

The first page of output has the image tibco.gif in the left area of the header and the image gglogo.gif in the center area of the footer.

The second page of output has the image tibco.gif in the right area of the header and the image gglogo.gif in the center area of the footer.

Reference: Usage Notes for Inserting Text and Images Into XLSX Worksheet Headers and Footers

  • In Microsoft Excel, the maximum number of characters in a page header or page footer is 255. This limit is for the entire page header or page footer (across the left, center, and right), and includes symbols or any other characters that Microsoft Excel needs to use, in addition to the text string itself. Any text string that exceeds this limit will be truncated.
  • The Excel headers and footers are not automatically sized based on contents of the areas. Define page margins within the page settings (XLSPAGESETS) to account for the space required to display the images within each page of the report.
  • The image sizing based on the specified height and width is not proportional. Sizing may cause image distortion.
  • BLOB image fields are not supported in this release.

Reference: Displaying Watermarks on XLSX Report Output

Watermark images can be placed into the Excel headers to display on every printed page of the generated worksheet.

Excel places images on the page starting in the header from left to right and then the footer from left to right. Large images placed in the header may overlap images before them in the presentation order. For page layouts with a logo in the left area and watermark centered on the page, watermark image background must be transparent so it does not overlay the logo image.

In Excel, images are placed first on the page. All other contents of the worksheet are then placed on top of the images. Text in cells and styling, such as background color and drawing objects, are placed on top of the images. Excel supports transparency in drawing objects and images, but not in cell background color. BACKCOLOR will cover over images placed on the page.

Example: Placing a Watermark in an XLSX Header

The following request against the GGSALES data source uses the image internaluseonly.gif as a watermark to display in the background of every page of the worksheet. Although the image is placed in the center area of the header, it is large enough to span the entire worksheet page. It has a transparent background, so it does not cover the logo images placed at the left in the header and the center in the footer.

TABLE FILE GGSALES
SUM DOLLARS UNITS BUDDOLLARS BUDUNITS 
BY REGION
BY ST
BY CATEGORY
BY PRODUCT
ON TABLE SET BYDISPLAY ON
ON TABLE PCHOLD FORMAT XLSX 
ON TABLE SET STYLE *
XLSXPAGESETS=ON,
TOPMARGIN=1,BOTTOMMARGIN=1,LEFTMARGIN=1, RIGHTMARGIN=1, ORIENTATION=LANDSCAPE,PAGESIZE=LETTER,$
TYPE=PAGEHEADER, OBJECT=IMAGE, JUSTIFY=LEFT, IMAGE=TIBCO.GIF, DISPLAYON=FIRST,$
TYPE=PAGEHEADER, OBJECT=IMAGE, JUSTIFY=CENTER, IMAGE=WFINTERNALUSEONLY.GIF,$
TYPE=PAGEFOOTER, OBJECT=IMAGE, JUSTIFY=RIGHT, IMAGE=GGLOGO.GIF,$
END

The first page of the generated worksheet shows the watermark image beneath the data. This image is displayed on every page of the worksheet.

Creating Excel XLSX Worksheets Using Templates

How to:

Reference:

XLSX report output can be generated based on Excel templates. This feature allows for the integration of WebFOCUS reports into workbooks containing multiple worksheets. Any native Excel template can be used to generate a new workbook containing a WebFOCUS report.

The following Excel file types can be used as template files to generate XLSX workbooks.

Template File Type

Output Workbook Generated

Template (.xltx)

Workbook (.xlsx)

Macro-Enabled Template (.xltm)

Macro-Enabled workbook (.xlsm)

Workbook (.xlsx)

Workbook (.xlsx)

Macro-Enabled workbook (.xlsm)

Macro-Enabled workbook (.xlsm)

WebFOCUS XLSX TEMPLATE format provides support for basic Excel templates (.xltx) files. These templates cannot contain macros or other content that Microsoft considers active, as well as templates with active content (XLTM/XLSM). Additionally, macro-enabled templates (.xltm) allow for the inclusion of active content (macros and VB script) into templates.

A WebFOCUS EXL07 template procedure generates a native Excel workbook with the standard Excel 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.

Since the template feature replaces existing worksheets, the designated worksheet must exist in the template workbook. Any content on 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.

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 for basic EXL07 format reports. The following features are not supported with EXL07 TEMPLATE in this release: FORMULA, EXL97, EXCEL, and compound Excel reports.

In most cases, existing Excel 2003/2000 templates created as .mht files can easily be converted to Excel 2013/2010/2007 templates by opening the .mht file in Excel 2013/2010/2007 and resaving the file as either an Excel template (.xltx) or a macro enabled (.xltm) file. Native Excel formulas and functionality should be retained within these templates. Use .xltms to retain active content, including macros. This new XLTX template can be used with XLSX procedures.

Syntax: How to Create an XLSX Report Using Any Supported Template File Type

To support the expanded template files types, the template file name attribute has been enhanced to allow for the inclusion of the file extension. If no extension is specified within the template name, the file extension will default to .XLTX.

ON TABLE PCHOLD FORMAT XLSX TEMPLATE template_name SHEETNUMBER n

where:

template_name

Is the name of the Excel template file (workbook), up to 64 characters including the file name and extension, residing on the WebFOCUS Reporting Server application directory search path. For example, IPOLICY.XLTX, PRINTSHEETS.XLTM, or DASHBOARD.XLSM. If the extension is not provided, it defaults to .XLTX.

n

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

Reference: Usage Notes for XLSX Templates

The workbook template used by the WebFOCUS procedure must contain valid worksheets.

  • The worksheet that is updated must exist in the workbook, as WebFOCUS is replacing the worksheet rather than inserting a worksheet. If the sheet designated does not exist, the procedure will return an error.
  • In any template file, at least one of the sheets in the workbook must contain a cell with a valid value (blank or any other value). To replace a worksheet in a template that contains only empty worksheets, replace one of the cells in any of the sheets with a space and save. This will instantiate the worksheets so they are accessible to WebFOCUS for updating.
  • The supported file name length has been extended to 64 characters. Any procedure referencing a template with a longer file name produces a message.

Creating Excel Table of Contents Reports

In this section:

How to:

Reference:

Excel Table of Contents (BYTOC) enables you to generate a separate worksheet within an instance of the report for each value of the first BY field in the WebFOCUS report.

Syntax: How to Use the Excel Table of Contents Feature

There are three different ways that BYTOC can be invoked:

ON TABLE {HOLD|PCHOLD} FORMAT XLSX BYTOC
SET COMPOUND=BYTOC
ON TABLE SET COMPOUND BYTOC

Since a BYTOC 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 primary BY field may be a NOPRINT field.

The BYTOC feature is not supported with the XLSX TEMPLATE format.

Example: Creating a Simple BYTOC 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 XLSX
ON TABLE SET BYDISPLAY ON
ON TABLE SET COMPOUND BYTOC
ON TABLE SET STYLE *
TYPE=REPORT, FONT=ARIAL, SIZE=9,$
TYPE=HEADING, SIZE=12,$
TYPE=TITLE, BACKCOLOR=GREY, COLOR=WHITE,$
ENDSTYLE
END

The output is:

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.

Naming XLSX Worksheets With Case Sensitive Data

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 WEST and West to be the same value. WebFOCUS XLSX format identifies duplicate names and adds a unique number to the name to allow Excel to maintain both 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. To account for this, XLSX has been enhanced to add counters where duplicate tab names are found in the data to ensure the names are unique.

For example, if the report had EAST and East as the values for the Region, each worksheet would be displayed as EAST(1) and East(2), as shown in the following image.

Overcoming the Excel 2007/2010 Row Limit Using Overflow Worksheets

How to:

Reference:

The maximum number of rows supported by Excel 2007/2010 on a worksheet is 1,048,576 (1MB). When you create an XLSX 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: By default, when generating XLSX output, the WebFOCUS page heading and page footing commands generate only worksheet headings and worksheet footings.

Syntax: How to Enable Overflow Worksheets

Add the ROWOVERFLOW attribute to your WebFOCUS StyleSheet

TYPE=REPORT, ROWOVERFLOW={ON|OFF|PBON}, [ROWLIMIT={n|MAX},]$

where:

ON

Enables overflow worksheets.

OFF

Disables overflow worksheets. OFF is the default value.

PBON

Inserts WebFOCUS page breaks that display the page heading, footing, and column titles at the appropriate places within the worksheet rows. This option does not cause a new worksheet to start when a WebFOCUS page break occurs.

ROWLIMIT=n

Sets a target value for the number of rows to be included on a worksheet to n rows. The default value is the LINES value (by default, 57).

ROWLIMIT=MAX

Sets a target value for the number of rows to be included on a worksheet to 1,048,000 rows for XLSX output.

Reference: Usage Notes for XLSX 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.
  • Unless the PBON setting is used, worksheet headings and column titles are repeated at the top of 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.
  • If ROWOVERFLOW=PBON, the page headings and footings and column titles display within the worksheet when a WebFOCUS command causes a page break.
  • For XLSX output, if the ROWOVERFLOW attribute is specified in the StyleSheet and ROWLIMIT is greater than 1MB, the following message is presented and no output file is generated:
    (FOC3338) The row limit for EXCEL XLSX worksheets is 1048576.
  • Output types that contain formula references (EXL2K PIVOT and EXL2K FORMULA) are not supported, as 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 2007/2010 limit or 16,384 columns.
  • ROWOVERFLOW is supported for BYTOC reports for XLSX.
  • 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

The following request creates XLSX report output with overflow worksheets. The ROWOVERFLOW=ON attribute in the StyleSheet activates the overflow feature. Without this attribute, one worksheet would have been generated instead of three.

TABLE FILE GGSALES
-* ****Report Heading****
ON TABLE SUBHEAD
"SALES BY REGION, CATEGORY, AND PRODUCT"
" "
-* ****Worksheet Heading****
HEADING
"SALES REPORT WORKSHEET <TABPAGENO"
" "
-* ****Worksheet Footing****
FOOTING
" "
"END OF WORKSHEET <TABPAGENO"
PRINT DOLLARS UNITS BUDDOLLARS BUDUNITS
BY REGION
BY CATEGORY
BY PRODUCT
BY DATE
-* ****Subfoot****
ON REGION SUBFOOT
" "
" End of Region <REGION"
" "
-* ****Subhead****
ON REGION SUBHESD
" "
"Category <CATEGORY for Region <REGION"
" "
-* ****Report Footing****
ON TABLE SUBFOOT
" "
"END OF REPORT"
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT=EXLOVER, ROWOVERFLOW=ON, ROWLIMIT=2000,$
ENDSTYLE
END

The report heading displays on the first worksheet only, the page heading and column titles display on each worksheet, and the subhead and subfoot display whenever the associated sort field changes value. The following image shows the top of the first worksheet, displaying the report heading, page heading, column titles, and first subhead.

Note that the TITLETEXT attribute in the StyleSheet specified the name EXLOVER, so the three worksheets were generated with the names EXLOVER1, EXLOVER2, and EXLOVER3. If there had been no TITLETEXT attribute, the sheets would have been named SHEET1, SHEET2, and SHEET3.

The worksheet footing displays at the bottom of each worksheet and the report footing displays at the bottom of the last worksheet. The following image shows the bottom of the last worksheet, displaying the last subfoot, the page footing, and the report footing.

Example: Creating Overflow Worksheets With WebFOCUS Page Breaks

The following request creates XLSX report output with overflow worksheets. The ROWOVERFLOW=PBON attribute in the StyleSheet activates the overflow feature, and the ROWLIMIT=250 sets the maximum number of rows in each worksheet to approximately 250. Without this attribute, one worksheet would have been generated. The PRODUCT sort phrase specifies a page break.

TABLE FILE GGSALES
-* ****Report Heading****
ON TABLE SUBHEAD
"SALES BY REGION, CATEGORY, AND PRODUCT"
" "
PRINT DOLLARS UNITS BUDDOLLARS BUDUNITS
BY REGION 
BY HIGHEST CATEGORY 
BY PRODUCT PAGE-BREAK
BY DATE
WHERE DATE GE '19971001'
-* ****Page Heading****
HEADING
" Product: <PRODUCT in Category: <CATEGORY for Region: <REGION"
-* ****Page Footing****
FOOTING
" "
-* ****Report Footing****
ON TABLE SUBFOOT
" "
"END OF REPORT"
ON TABLE SET BYDISPLAY ON
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET STYLE *
INCLUDE=IBFS:/FILE/IBI_HTML_DIR/javaassist/intl/EN/ENIADefault_combine.sty,$ 
TITLETEXT=EXLOVER, ROWOVERFLOW=PBON, ROWLIMIT=250,
$
ENDSTYLE
END

The report heading displays on the first worksheet only, the page heading, footing, and column titles display on each worksheet and at each WebFOCUS page break (each time the product changes), and the subhead and subfoot display whenever the associated sort field changes value. The following image shows the top of the first worksheet.

Excel Compound Reports Using XLSX

Reference:

Excel compound reports generate compound workbooks that can contain multiple worksheet reports using the XLSX output format.

You can use standard Compound Layout syntax to generate XLSX compound workbooks. By default, each of the component reports from the compound report is placed in a new Excel worksheet (analogous to a new page in PDF).

The components of an Excel compound report can include standard tables, Table of Content (BYTOC), and ROWOVERFLOW reports.

Component graphs will be added to worksheets as images.

Reference: Usage Notes for Excel Compound Reports Using XLSX

  • Images and graphs can be embedded within a component, but images and drawing objects (lines, boxes, strings) defined in Compound Layout syntax on a page layout will not be included in the generated workbook.
  • Graphs and images are not supported in Excel headers and footers within XLSX compound workbooks.
  • Coordinated compound reports that generate individual instances of the overall report for each unique primary key are not available in XLSX.

Note: Since multiple tables are generated, WebFOCUS will ensure that each tab name is unique.

Example: Compound Excel Report including Table of Contents (BYTOC)

SET PAGE-NUM=OFF
COMPOUND LAYOUT PCHOLD FORMAT XLSX
SECTION=Example, LAYOUT=ON, MERGE=OFF,$ 
PAGELAYOUT=1,$ 
COMPONENT=R1, TYPE=REPORT,TEXT='report1', POSITION=(0.833 0.729), DIMENSION=(6.250 1.771),$
COMPONENT=R2, TYPE=REPORT,TEXT='report2', POSITION=(0.833 2.917), DIMENSION=(6.250 1.875),$
COMPONENT=R3, TYPE=REPORT,TEXT='report3', POSITION=(0.938 5.313), DIMENSION=(6.250 1.354),$
COMPONENT=R4, TYPE=REPORT,TEXT='report4', POSITION=(0.938 7.083), DIMENSION=(6.042 1.146),$
END 
SET COMPONENT=R1 
TABLE FILE GGSALES 
HEADING CENTER 
"Gotham Grinds Sales to Information Builders" 
" "
"Report 1"
"Sales Summary by Region"
" "
SUM UNITS/D12C BUDUNITS/D12C DOLLARS/D12CM BUDDOLLARS/D12CM
BY REGION
ON TABLE HOLD FORMAT XLSX
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT=Region Summary,$
TYPE=REPORT, TOPMARGIN=1.5, BOTTOMMARGIN=1, PAGESIZE=LETTER,$
TYPE=TITLE, COLOR=WHITE, BACKCOLOR=GREY,$
TYPE=HEADING, LINE=1, OBJECT=TEXT, COLOR=PURPLE, JUSTIFY=CENTER, STYLE=BOLD,$
TYPE=HEADING, LINE=3, OBJECT=TEXT, COLOR=BLUE, JUSTIFY=CENTER, STYLE=BOLD,$
TYPE=HEADING, LINE=4, OBJECT=TEXT, COLOR=PURPLE, JUSTIFY=CENTER, STYLE=BOLD,$
ENDSTYLE
END

SET COMPONENT=R2
TABLE FILE GGSALES
SUM UNITS/D12C DOLLARS/D12CM
BY REGION BY CATEGORY BY PRODUCT
HEADING CENTER
"Gotham Grinds Sales to Information Builders"
" "
"Report 2"
"Sales Detail By Region"
ON REGION SUBHEAD
"<REGION Region Sales"
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET BYDISPLAY ON
ON TABLE SET COMPOUND BYTOC
ON TABLE SET STYLE *
TYPE=REPORT, TOPMARGIN=1.5, BOTTOMMARGIN=1, PAGESIZE=LETTER,$
TYPE=REPORT, TITLETEXT='Region-',$
TYPE=TITLE, COLOR=WHITE, BACKCOLOR=GREY,$
TYPE=HEADING, LINE=1, COLOR=PURPLE, JUSTIFY=CENTER,STYLE=BOLD,$
TYPE=HEADING, LINE=3, COLOR=BLUE, JUSTIFY=CENTER, STYLE=BOLD,$
TYPE=HEADING, LINE=4, COLOR=PURPLE, JUSTIFY=CENTER, STYLE=BOLD,$
ENDSTYLE
END
SET COMPONENT=R3
TABLE FILE GGSALES
HEADING CENTER
"Gotham Grinds Sales to Information Builders"
" "
"Report 3"
"Sales Summary by Category"
" "
SUM UNITS/D12C BUDUNITS/D12C DOLLARS/D12CM BUDDOLLARS/D12CM
BY CATEGORY
ON TABLE HOLD FORMAT XLSX
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT=Sales Summary,$
TYPE=REPORT, TOPMARGIN=1.5, BOTTOMMARGIN=1, PAGESIZE=LETTER,$
TYPE=TITLE, COLOR=WHITE, BACKCOLOR=GREY,$
TYPE=HEADING,LINE=1,COLOR=PURPLE, JUSTIFY=CENTER, STYLE=BOLD,$
TYPE=HEADING,LINE=3,OBJECT=TEXT,COLOR=BLUE, JUSTIFY=CENTER, STYLE=BOLD,$
TYPE=HEADING,LINE=4,OBJECT=TEXT,COLOR=PURPLE, JUSTIFY=CENTER, STYLE=BOLD,$
ENDSTYLE
END
SET COMPONENT=R4
TABLE FILE GGSALES
HEADING CENTER
"Gotham Grinds Sales to Information Builders"
" "
"Report 4"
"Sales Detail Report By Category"
" "
SUM UNITS/D12C BUDUNITS/D12C DOLLARS/D12CM BUDDOLLARS/D12CM
BY CATEGORY BY PRODUCT BY REGION
ON TABLE SET BYDISPLAY ON
ON TABLE HOLD FORMAT XLSX
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT=Sales Detail,$
TYPE=REPORT, TOPMARGIN=1.5, BOTTOMMARGIN=1, PAGESIZE=LETTER,$
TYPE=TITLE, COLOR=WHITE, BACKCOLOR=GREY,$
TYPE=HEADING,LINE=1,OBJECT=TEXT,COLOR=PURPLE, JUSTIFY=CENTER, STYLE=BOLD,$
TYPE=HEADING,LINE=3,OBJECT=TEXT,COLOR=BLUE, JUSTIFY=CENTER, STYLE=BOLD,$
TYPE=HEADING,LINE=4,OBJECT=TEXT,COLOR=PURPLE, JUSTIFY=CENTER, STYLE=BOLD,$
ENDSTYLE
END
COMPOUND END

The output is:

Report 1: Summary Report by Region

Report 2: BYTOC Reports by Region

The following image shows the worksheet fot the first region.

Report 3: Sales Summary Report by Category

Report 4: Sales Detail Report by Category

Reference: Guidelines for Using the Legacy OPEN, CLOSE, and NOBREAK Keywords and SET COMPOUND

The keywords OPEN, CLOSE, and NOBREAK are used to control Excel compound reports. They can be specified with the HOLD or PCHOLD command or with a separate SET COMPOUND command.

  • OPEN is used on the first report of a sequence of component reports to specify that a compound report should be started.
  • CLOSE is used to designate the last report in a compound report.
  • NOBREAK specifies that the next report be placed on the same worksheet as the current report. If it is not present, the default behavior is to place the next report on a separate worksheet.
  • When used with the HOLD or PCHOLD syntax, the compound report keywords OPEN, CLOSE, and NOBREAK must appear immediately after FORMAT XLSX. For example, you can specify:
    • ON TABLE PCHOLD FORMAT XLSX OPEN
    • ON TABLE HOLD AS MYHOLD FORMAT XLSX OPEN NOBREAK
  • As with PDF compound reports, compound report keywords can be alternatively specified using SET COMPOUND:
    • SET COMPOUND = OPEN
    • SET COMPOUND = 'OPEN NOBREAK'
    • SET COMPOUND = NOBREAK
    • SET COMPOUND = CLOSE

Reference: Guidelines for Producing Excel Compound Reports Using XLSX

  • Naming of Worksheets. The default worksheet tab names will be Sheet1, Sheet2, and so on. You have the option to specify a different worksheet tab name by using the TITLETEXT keyword in the StyleSheet. For example:
    TYPE=REPORT, TITLETEXT='Summary Report',$

    Excel limits the length of worksheet titles to 31 characters. The following special characters cannot be used: ':', '?', '*', and '/'.

  • File Names and Formats. The output file name (AS name, or HOLD by default) is obtained from the first report of the compound report (the report with the OPEN keyword). Output file names on subsequent reports are ignored.

    The HOLD FORMAT syntax used in the first component report in a compound report applies to all subsequent reports in the compound report, regardless of their format.

  • NOBREAK Behavior. When NOBREAK is specified, the following report appears on the row immediately after the last row of the report with the NOBREAK. If additional spacing is required between the reports, a FOOTING or an ON TABLE SUBFOOT can be placed on the report with the NOBREAK, or a HEADING or an ON TABLE SUBHEAD can be placed on the following report. This allows the most flexibility, since if blank rows were added by default, there would be no way to remove them.

Example: Creating a Simple Compound Report Using XLSX

SET PAGE-NUM=OFF
TABLE FILE GGSALES
HEADING
"Report 1: Coffee - Budget"
" "
SUM BUDDOLLARS BUDUNITS COLUMN-TOTAL AS 'Total'
BY REGION
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT=Coffee Budget,$
TYPE=HEADING, SIZE=14,$
ENDSTYLE
ON TABLE PCHOLD AS EX1 FORMAT XLSX OPEN
END
TABLE FILE GGSALES
HEADING
"Report 2: Coffee - Actual "
SUM DOLLARS UNITS COLUMN-TOTAL AS 'Total'
BY REGION
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT=Coffee Actual,$
TYPE=HEADING, SIZE=14,$
ENDSTYLE
END
TABLE FILE GGSALES
HEADING
"Report 3: Food - Budget"
SUM BUDDOLLARS BUDUNITS COLUMN-TOTAL AS 'Total'
BY REGION
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT=Food Budget,$
TYPE=HEADING, SIZE=14,$
ENDSTYLE
ON TABLE PCHOLD FORMAT XLSX CLOSE
END

The output is:

Example: Creating a Compound Report Using NOBREAK

In this example, the first two reports are on the first worksheet, and the last two reports are on the second worksheet, since NOBREAK appears on both the first and third reports.

TABLE FILE GGSALES
HEADING
"Report 1: Coffee - Budget"
SUM BUDDOLLARS BUDUNITS COLUMN-TOTAL AS 'Total'
BY REGION 
IF CATEGORY EQ Coffee
ON TABLE PCHOLD FORMAT XLSX OPEN NOBREAK
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT=Coffee, FONT=ARIAL, SIZE=10, STYLE=NORMAL,$
TYPE=TITLE, STYLE=BOLD,$
TYPE=HEADING, SIZE=12, STYLE=BOLD, COLOR=BLUE,$
TYPE=GRANDTOTAL, STYLE=BOLD,$
END
TABLE FILE GGSALES
HEADING
" "
"Report 2: Coffee - Actual "
SUM DOLLARS UNITS COLUMN-TOTAL AS 'Total'
BY REGION 
IF CATEGORY EQ Coffee
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET STYLE *
TYPE=REPORT, FONT=ARIAL, SIZE=10, STYLE=NORMAL,$
TYPE=GRANDTOTAL, STYLE=BOLD,$
TYPE=HEADING, SIZE=12, STYLE=BOLD, COLOR=BLUE,$
END
TABLE FILE GGSALES
HEADING
"Report 3: Food - Budget"
SUM BUDDOLLARS BUDUNITS COLUMN-TOTAL AS 'Total'
BY REGION 
IF CATEGORY EQ Food
ON TABLE PCHOLD FORMAT XLSX NOBREAK
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT=Food, FONT=ARIAL, SIZE=10, STYLE=NORMAL,$
TYPE=HEADING, STYLE=BOLD, SIZE=12, COLOR=BLUE,$
TYPE=TITLE, STYLE=BOLD,$
TYPE=GRANDTOTAL, STYLE=BOLD,$
END
TABLE FILE GGSALES
HEADING
" "
"Report 4: Food - Actual"
SUM DOLLARS UNITS COLUMN-TOTAL AS 'Total'
BY REGION 
IF CATEGORY EQ Food
ON TABLE PCHOLD FORMAT XLSX CLOSE
ON TABLE SET STYLE *
TYPE=REPORT, FONT=ARIAL, SIZE=10,  $
TYPE=TITLE, STYLE=BOLD,$
TYPE=HEADING, SIZE=12, STYLE=BOLD, COLOR=BLUE,$
TYPE=GRANDTOTAL, STYLE=BOLD,$
END

Report output is displayed in two separate tabs.

Using XLSX FORMULA With Compound Reports

In new compound syntax, the implementation of compound workbooks with XLSX FORMULA can be activated in either of the following ways. Each of these approaches will generate a workbook with all of the component reports in FORMULA mode.

  1. Add FORMAT XLSX FORMULA to the compound syntax header, as shown in the following syntax:
    COMPOUND LAYOUT PCHOLD FORMAT XLSX FORMULA
    UNITS=IN, $
    SECTION=section1, LAYOUT=ON, METADATA='prop_with_names, Margins_Left=0.5,
    Margins_Top=0.5, Margins_Right=0.5, Margins_Bottom=0.5, thumbnailscale=4,
    MERGE=OFF, ORIENTATION=PORTRAIT, PAGESIZE=Letter, SHOW_GLOBALFILTER=OFF, $
    PAGELAYOUT=1, NAME='Page layout 1', text='Page layout 1', TOC-LEVEL=1,
    BOTTOMMARGIN=0.5, TOPMARGIN=0.5, METADATA='BOTTOMMARGIN=0.5,
    TOPMARGIN=0.5,LEFTMARGIN=0,RIGHTMARGIN=0,', $
    COMPONENT='report1', TEXT='report1', TOC-LEVEL=2, POSITION=(0.567 0.667),
    DIMENSION=(6.883 2.314), BYTOC=0, ARREPORTSIZE=DIMENSION,
    METADATA='left: 0.567in; top: 0.667in; width: 6.883in; height: 2.314in;
    position: absolute; z-index: 1;', $
    COMPONENT='report2', TEXT='report2', TOC-LEVEL=2, POSITION=(0.567 3.250),
    DIMENSION=(7.000 2.833), BYTOC=0, ARREPORTSIZE=DIMENSION,
    METADATA='left: 0.567in; top: 3.25in; width: 7in; height: 2.833in;
    position: absolute; z-index: 2;', $
    END
  2. Define XLSX FORMULA as the output setting for the first component, as shown in the following syntax:
    SET COMPONENT='report1'
    -*component_type report
    -*File: IBFS:/localhost/EDA/9999/APPPATH/xlsx2015/Report1.fex 
    -*Created by WebFOCUS AppStudio
    DEFINE FILE GGSALES
    D_UOVERBUD/D12C=GGSALES.SALES01.UNITS - GGSALES.SALES01.BUDUNITS;
    END
    TABLE FILE GGSALES
    SUM 
         GGSALES.SALES01.UNITS
         GGSALES.SALES01.BUDUNITS
         GGSALES.SALES01.DOLLARS
         GGSALES.SALES01.BUDDOLLARS
         GGSALES.SALES01.D_UOVERBUD
         COMPUTE C_DOVERBUD/D12.2CM = GGSALES.SALES01.DOLLARS -
           GGSALES.SALES01.BUDDOLLARS;
    BY GGSALES.SALES01.REGION
    BY GGSALES.SALES01.CATEGORY
    HEADING
    "XLSX FORMULA - the difference betweem DEFINE & COMPUTES"
    ON TABLE SET PAGE-NUM NOLEAD
    ON TABLE SET ASNAMES ON
    ON TABLE COLUMN-TOTAL AS 'TOTAL'
    ON TABLE PCHOLD FORMAT XLSX FORMULA
    ON TABLE SET HTMLCSS ON
    ON TABLE SET STYLE *
         INCLUDE = endeflt,
    $
    ENDSTYLE

Note:

WebFOCUS Pivot Support for XLSX

The WebFOCUS XLSX format can generate a workbook based on a template that contains predefined pivot tables. These pivot tables can be built based on data fed from a report and/or exist independently of the WebFOCUS data on other worksheets.

Example: Feeding Data From a WebFOCUS Report Into a Pivot Table and Pivot Chart

The following sample procedure shows how to feed data from a WebFOCUS report into a pivot table and pivot chart within an existing Excel template called wf2pivot.xltx.

Note: Template names containing embedded blanks must be enclosed in single quotation marks.

TABLE FILE GGSALES
PRINT
UNITS/D12C DOLLARS/D12CM 
BUDUNITS/D12C BUDDOLLARS/D12CM
BY LOWEST REGION
BY LOWEST ST
BY HIGHEST CATEGORY 
BY LOWEST PRODUCT
ON TABLE SET BYDISPLAY ON
ON TABLE PCHOLD AS PIVOTWITHCHART FORMAT XLSX TEMPLATE wf2pivot.xltx SHEETNUMBER 2
ON TABLE SET STYLE *
TYPE=DATA,IN-RANGES='DATAwithHEADERS',$
TYPE=TITLE,IN-RANGES='DATAwithHEADERS',$
ENDSTYLE
END

The wf2pivot.xltx template file must be in the Reporting Server path. The following images show the default of the first and second worksheets in the wf2pivot.xltx template, before executing the sample procedure.

The first worksheet, PivotTablewithChart, contains an empty pivot table and pivot chart. It also contains an empty PivotTableFieldList. The first worksheet is shown in the following image.

The second worksheet, Source Data, contains one column called FieldsToBeAdded, for which there is initially no data. The second worksheet is shown in the following image.

When you run the sample procedure, a pivotwithchart.xlsx workbook is generated, with the WebFOCUS report data stored in the second worksheet.

The following images show the first and second worksheets in the pivotwithchart.xlsx workbook after you run the sample procedure.

First Worksheet

Second Worksheet

In the PivotTablewithChart worksheet, note that the PivotTableFieldList is populated with the fields from the WebFOCUS report. There is one check box for each field in the report procedure. All the check boxes are not selected, by default.

The data used to populate the check boxes is obtained from the Source Data worksheet, where the data from the WebFOCUS report was saved upon executing the sample procedure.

To start building a pivot report and pivot chart, you can select the check boxes for the desired fields in the PivotTableFieldList. For example, selecting the check boxes for Product, Unit Sales, and Budget Units will automatically feed the data from the Source Data worksheet into the pivot table and pivot chart in the PivotTablewithChart worksheet. The resulting pivot table and pivot chart are shown in the following image.

The wf2pivot.xltx template is provided, by default, with the WebFOCUS Reporting Server installation as part of the Legacy Samples. For information on how to download the Reporting Server Legacy Samples, see the TIBCO WebFOCUS® Reporting Server Administration manual.

You can use the WebFOCUS XLSX template as is, or you can customize it to meet the your business requirements.

Note: The wf2pivot.xltx template includes a pivot table and pivot chart on the same worksheet, but you can use a macro-enabled template to generate a pivot table and a pivot chart on separate worksheets, which are linked to a common data source.

FORMAT XLSX Limitations

Format XLSX does not support the following features, currently supported for EXL2K:

For additional support on the implementation of features supported by the XLSX format, see WebFOCUS XLSX Format Supported Features Roadmap, located at the following link:

https://techsupport.informationbuilders.com/tech/wbf/wbf_rln_formatXLSX_support.html