Using Excel Named Ranges in WebFOCUS Applications
An Excel Named Range is a name assigned to a specific
group of cells within an Excel worksheet that can be easily referenced
by WebFOCUS applications. Generating Named Ranges is facilitated
by the WebFOCUS StyleSheet language.
The use of Excel Named Ranges provides many benefits including
the following:
- Provides advantages
over static cell references including the ability of named range
data areas to expand to include new data added during scheduled
workbook updates.
- Enables easy
setup of Excel worksheets, created by WebFOCUS applications, as an
ODBC (Open Database Connectivity) data source.
- Provides accurate,
consistent data feeds to advanced Excel worksheet applications,
which eliminates manual activities that tend to result in errors.
- Simplifies
the process of referencing data in multiple worksheets. This is
especially useful when named ranges are added to the output of an
Excel Template report.
Excel Named Ranges can be created in Developer Studio by assigning
a range name to data output in Excel format. The Excel 2000 (EXL2K)
and the Excel 2000 Formula (EXL2K FORMULA) report output formats
are supported. You may also use the Named Range feature on an Excel
Template.
x
Procedure: How to Use Excel Named Ranges
-
In Report
Painter, confirm that Excel 2000 (EXL2K) or Excel 2000 Formula (EXL2K
FORMULA) is selected as the output format on the Report Options
dialog box.
-
Select
the worksheet option, Define a range name for the data,
on the Format tab of the Report Options dialog box, and type in
a name.
Note: The name should not contain any spaces and
there is a 256 character limit for named ranges.
-
Select
the Display repeated sort values option on
the Output tab of the Report Options dialog box to prevent discontinuous
data in a report. This is recommended for use with Named Ranges.
-
Click OK and
run the report.
Note
that you will have to select the named range from the Name Box drop-down
list.
Example: Using Excel Named Ranges
The
following example creates one report, in one worksheet of an Excel
workbook. The tabular output is assigned an Excel Named Range, defined
as RegionalSales that begins at cell A1 and continues to cell E4318.
These
steps create the output in this example:
- Open the GGSALES
data source in Report Painter.
- Identify and
format your report columns.
- Identify your
sort columns. Click By on the Report Painter
toolbar and double-click or drag REGION and DOLLARS from
the Fields tab of the Object Inspector.
- Identify your
detail columns. Move the insertion point after the Dollar Sales column.
Click Detail on the Report Painter toolbar
and double-click or drag PRODUCT, DATE,
and UNITS from the Fields tab of the Object
Inspector.
- Confirm and
set up the Excel report output format.
- Select Output from
the Report menu and ensure that Excel 2000 (EXL2K) is selected as
the report output.
- Select the Display
repeated sort values option to prevent discontinuous
data in a report.
- Define an Excel Named Range.
- Select the
worksheet option Define a range name for the data,
on the Format tab of the Report Options dialog box.
- Type RegionalSales in
the Define a range name input field.
- Click OK to
close the Report Options dialog box.
- Run the report
and view select the named range from the Name Box drop-down list.
x
Reference: Support for Excel Named Ranges
The
following items are not supported when using Excel Named Ranges:
- Excel Named
Ranges are not supported with any report that produces discontinuous
data (such as SubHeading and SubFooting), or uses columnar references that
span multiple columns, which include Across, Recap, Recompute, Subtotal,
and Subtotal on all outer sort fields.
- When creating
Compound Excel reports (multiple TABLE requests output to the same
Excel workbook), each report must have a unique range name.