WebFOCUS Newsletter July 2003 - The WebFOCUS Connector for Excel

Imagine an application environment that combined Microsoft Excel’s user interface and computational prowess with the high capacity for server side-processing and database access of Information Builders' WebFOCUS.

That’s what you get with the WebFOCUS Connector for Excel. What’s more is that this WebFOCUS solution not only leverages the strengths of Microsoft Excel, but quite seamlessly integrates and delivers a closed-loop business intelligence solution that operates against a database.

Excel spreadsheets reading and writing to a centralized database offer many advantages to more than just the users. For example, after Excel does the number crunching there is no need for manual and redundant data entry to store the resulting data in the corporate database. This integration is made possible in part through the Connector’s facility that maps the database tables and columns into a customized Excel spreadsheet. On the Excel spreadsheet those rows and columns may not have the same structure or design of the database.

In this article we are going to review the Excel Connector IBI-WORK-AREA, which is where this critical data mapping data is defined.

The Excel Connector solution is composed of three main components:

WebFOCUS Maintain

WebFOCUS Reporting

Microsoft Excel

To begin, the user must first open the WebFOCUS Connector for Excel. This file is an Excel document that can be opened from the local computer file system, shared network drive, or even from an e-mail message as an attachment.

Because the Excel Connector has been pre-configured, it is immediately ready for use once opened. By clicking the Load button, the Excel Connector populates the spreadsheet with data that comes directly from a database.

The user can then apply edits to the spreadsheet. To commit any edits to the database the user clicks the Save button.

Behind the Scenes: Loading and Saving

When the user clicks the Load button, the Excel Connector invokes a WebFOCUS report. This report simply issues TABLE FILE syntax to print the database key columns and desired data fields. The output of the report is stored on a dedicated worksheet named IBI-WORK-AREA. It then uses its data mapping information to populate the customer spreadsheet with data from the report. And when the user clicks the Save button, it again uses the data mapping information to copy the custom spreadsheet values back into the IBI-WORK-AREA, where it is then sent to a WebFOCUS Maintain procedure for saving.

Commonly asked questions regarding the Excel connector include the following:

Q: What types of skills are required to use the WebFOCUS Connector for Excel?
A: General knowledge of the Excel user interface is all that is required to use the WebFOCUS Connector for Excel.

Q: Do I need to learn Excel VB programming language in order to configure and set up?
A: No. The Connector for Excel has built-in macros to support a wide array of spreadsheet configurations. To set up the Excel Connector to use another database, general knowledge of WebFOCUS Table and Maintain is all that’s required.

Q: Is there any support for security?
A: Yes. Security measures are available at every juncture.

Q: Does it support multiple tables?
A: Yes. Through the simple use of JOINs, multiple database tables can be read from and written to.

The IBI-WORK-AREA

The IBI-WORK-AREA is a dedicated worksheet within the Excel connector used by the WebFOCUS Connector to temporarily store data and describe its operational environment. The information stored in the IBI-WORK-AREA includes the following:

System Information

Mapping Information

Storage space for temporary data

System Information

The System information described by WebFOCUS Connector for Excel identifies the WebFOCUS Environment and high-level operational data. As depicted in Figure 1, the label appears in column A and the values to be used are stored in column B. All field labels includes a help description explaining their purpose. Placing the mouse pointer above the red triangle will reveal this help text.

Figure 1

The www field identifies the Web server host machine and the protocol to use when connecting to it (either HTTP or HTTPS).

The WebFOCUS CLIENT field identifies the path to the WebFOCUS Client, which is either the CGI or ISAPI implementation.

Node identifies the WebFOCUS Server. This is the node name defined in the WebFOCUS Client’s ODIN.CFG file.

The Application field provides a qualifying application name and path for the WebFOCUS Table and Maintain procedures. When the Excel Connector needs to invoke a WebFOCUS procedure it will use this application path at runtime. If supplying more than one application name, separate them with a space character (not comma or dash).

FOC_WRITE identifies the name of the WebFOCUS Maintain procedure. The Maintain procedure is called when performing save operations. The cell adjacent to it, C5, can be used to pass additional parameters to the Maintain procedure.

The FOC_READ field identifies the name of the WebFOCUS Report. The reporting procedure (Table) is called when performing load operations. The cell adjacent to it, C6, can be used to pass additional parameters to the reporting procedure.

The Reserved field can be used for two distinct purposes. When its value is set to "version," the product version number of the Excel Connector appears when the user clicks the Load button. When the Reserved field has a value of "debug" both load and save operations provide more output to the user, such as the parameter and values that are passed to the WebFOCUS Client.

Max_Row_Count controls the size of the transaction block sent to WebFOCUS Maintain during save operations. Its number represents the number of database rows that are passed to the Maintain procedure at a time.

The Authenticate field controls the prompting for the user name and password during load and save operations. When it has a value of “Yes,” the user will be prompted for a name and password.

Starting_foc_error_row tells the Excel Connector where it should look to identify WebFOCUS Error codes within the IBI-WORK-AREA worksheet.

Mapping Information

The Mapping information describes how information from the WebFOCUS report is stored in the custom spreadsheet (sheet1). The Excel Connector does not require mapping information for all columns in the WebFOCUS report. However, it requires an entry for each column in the report representing a database key field, and each database column representing data on the custom spreadsheet (sheet1).

The column mapping information defined in the IBI-WORK-AREA starts at Cell D14 for the first column, Cell E14 for the second column, Cell F14 for the third column and so on. Each mapped column has properties used by the WebFOCUS Connector, and these property values appear in their respective column on different rows. The column property names are in Cells C14 thru C23 (Name, Source, Destination, Delta X, etc.). See Figure 2.

Figure 2

The Name property represents the actual column name and should appear exactly as it does in the database/report.

The Source property identifies the first cell containing data for its column in the IBI-WORK-AREA temporary storage. The work-area temporary storage is used to hold output from the WebFOCUS report. It appears after the load operation.

Destination property identifies the first cell in the custom spreadsheet (sheet1) where the named column data is to be mapped to. It is important to note that an Excel named range can be used for this type of reference. If a named range is moved to another part of the spreadsheet, the WebFOCUS Excel Connector mapping information still applies, and does not require change.

To create a named range, navigate to the desired destination position on sheet1 and select the Insert->Name->Define menu item. Enter the desired name and click the OK button. The destination value in the IBI-WORK-AREA can then be entered exactly as follows (where Type_of_service is the named Range):
=CELL("address",Type_of_Service)

The Delta X property determines the horizontal direction of data for the named column on the customized spreadsheet. Its value represents the number of horizontal cell positions to travel from the Destination cell to store the data series from the report (rows in the WebFOCUS Report for said column).

Delta Y determines the vertical direction of data for the named column on the customized spreadsheet. Its value represents the number of vertical cell positions to travel from the Destination cell to store the data series (rows in the WebFOCUS Report for said column).

As the Excel Connector identifies the Destination cell by applying the Delta.X and Delta.Y, that new position becomes the next Destination cell to apply the deltas to as it traverses thru the data series.

When the Delta X and Delta Y properties have a value of zero, no data will be mapped to the customized spreadsheet for that column.

The Distinct property is used when repeated data in the named column should not continuously appear in the customized spreadsheet (much like when the BY keyword is used in a Table report). When its value is set to "Yes," only unique values for the column in the IBI-WORK-AREA will be mapped to the customized spreadsheet. For this feature to be most effective, the report should print the column with sorted values.

The Reserved property currently has no production uses.

Save determines if data for the named column is sent to the Maintain procedure to be committed to the database during the save operation. All database key columns must be sent for the save operation to be successful. A property value of "Yes" indicates that the named column’s data is sent during save operations.

Destination.X and Destination.Y are without a doubt the most difficult to comprehend. Here goes… These two properties can be left blank, but their applications do offer some practical value. How practical? When to use? By using these properties the WebFOCUS Connector for Excel attempts to track the data that has been mapped to the customized worksheet (sheet1). It does this by designating a column that is mapped to sheet1 as being an internal key column. When the end user deletes or moves it from sheet1, the Excel Connector knows it.

To designate a column as the internal key column for data mapping, enter a value of "Set" into its Destination.X property when its customized data travels horizontally. Use Destination.Y if its customized data series travels vertically on sheet1. Then enter the value of "Get" into all other mapped columns.

In a future article we will discuss more aspects of the WebFOCUS Connector for Excel and demonstrate how easily the available templates can be customized to work with any database.