22
Express Spreadsheet Add-In

Chapter summary

This chapter provides an overview of how to use the Express Spreadsheet Add-In with Financial Analyzer in the Windows client environment. For information about the latest functions and features, refer to the release notes for Express Spreadsheet Add-In.

Who should read this chapter

Read this chapter if you have installed Express Spreadsheet Add-In for use with Financial Analyzer.

List of topics

This chapter includes the following topics:

• About the Express Spreadsheet Add-In

• Using Express Spreadsheet Add-In Directly with Financial Analyzer

• Attaching Financial Analyzer Databases

• Using the Data Collection Toolkit

• Implementing the Data Collection Toolkit

• Data Collection Toolkit Technical Reference

About the Express Spreadsheet Add-In

Working with Financial Analyzer data in a spreadsheet

Oracle Express Spreadsheet Add-In (hereinafter referred to as "Express Spreadsheet Add-In") is an add-in to Microsoft Excel. The add-in allows you to fetch data from one or more Financial Analyzer databases and work with the data in a spreadsheet in the Windows client environment.

Methods for using Express Spreadsheet Add-In

You can use the Express Spreadsheet Add-In directly with Financial Analyzer. You can also use it to create a spreadsheet data collection template that can serve as a lightweight data entry mechanism for infrequent users of Financial Analyzer.

Related information

For up-to-date information about Express Spreadsheet Add-In features and functions, see the Express Spreadsheet Add-In release notes.

Using Express Spreadsheet Add-In Directly with Financial Analyzer

Requirements for using Express Spreadsheet Add-In directly

In order to use Express Spreadsheet Add-In directly with Financial Analyzer, the Express Spreadsheet Add-In software must be installed on your PC and in Excel. You must also have access to a Financial Analyzer database.

Creating a query

Installing the Express Spreadsheet Add-In into Financial Analyzer creates an Express item on the Excel menu. From the Express menu, you choose Run Express Wizard to create a query.

Working with a query

Once you create a query, you can use other Express menu options to perform the following functions:

• Rotate the data

• Drill in the data

• Make new data selections

• Work with custom measures

• Save data in the spreadsheet

If you have the proper access, you can also write changes to the database.

Related information

For detailed information about working directly with Financial Analyzer, see the Express Spreadsheet Add-In Help system.

Attaching Financial Analyzer Databases

Database attach modes

You attach Financial Analyzer databases in different "modes," depending on the tasks that you want to perform. The mode in which you can attach a database depends on three things:

• Your current default mode for attaching databases

• Whether you select the Read Only option in the Attach Database dialog box

• Whether you have write-access to the database file at the operating system level

Attaching personal databases

You can attach to a Financial Analyzer personal database in either read-only or read/write mode. Once you attach to a personal database, you can use the Express Wizard in Excel to query Financial Analyzer data.

You can also transfer data from the spreadsheet to the attached personal database if you have write-access to the database and you attach the database in an appropriate mode.

Attaching shared databases

You can attach a shared database in read-only mode.

Financial Analyzer enables you to access data in the shared database based on your operating system user name. Therefore, you need the following to gain access.

• Set your authentication type to Host or Domain in the Express connection file that is used to make a connection to Express from the Express Spreadsheet Add-In.

• Ensure that your operating system user name is associated with a Financial Analyzer user. This name determines your access to financial data items and dimensions.
  • If your operating system user name is associated with more than one Financial Analyzer user, then your access rights will default to the access rights of the first Financial Analyzer user name defined.

  • If your operating system user name is associated with a Financial Analyzer user who is defined as an Administrator then this user will take precedence over any other Financial Analyzer user.

Once you attach to a shared database, you can use the Express Wizard in Excel to query Financial Analyzer data. You can also transfer data from the spreadsheet to the attached shared database but you cannot permanently save changes to the shared database.

Important: Attaching multiple databases

Do not attach multiple databases that use the same object names. Doing so can produce unpredictable results.

Connecting directly to Financial Analyzer

If Financial Analyzer is running on your desktop, then the Express Spreadsheet Add-In will connect directly into your Financial Analyzer session.

• If you are running an Administrator or Budget workstation, you will be directly connected to the Financial Analyzer session with the personal database attached read/write.

  Once you attach to a personal database, you can use the Express Wizard in Excel to query Financial Analyzer data. You can also transfer data from the spreadsheet to the attached personal database.

• If you are running an Analyst workstation you will be directly connected into the Financial Analyzer session with the shared database attached read only.

  Note: The Express Add-In option, Permanently write changes to databases attached read only, does not apply to analyst users and should not be used.

• Once you attach to a shared database, you can use the Express Wizard in Excel to query Financial Analyzer data. You can also temporarily transfer data from the spreadsheet to the attached shared database, but you cannot permanently save changes to the shared database.

If the Express Spreadsheet Add-In is directly connected to a Financial Analyzer session you cannot use the Express Spreadsheet Add-In Rollback feature.

Related information

For more information about attaching databases, see the following topics in the Express Spreadsheet Add-In Help system:

"Attaching Databases"

"Attach Database Dialog Box"

"Attach Mode Options"

For information about configuring connections, see the Oracle Express Database Administration Guide.

Using the Data Collection Toolkit

Introduction

This section describes the Financial Analyzer data collection toolkit for use with Microsoft Excel. This toolkit enables you to create a simple lightweight data entry mechanism for infrequent users of Financial Analyzer.

Requirement for using data collection toolkit

In order to use the data collection toolkit, a user must have a thin client setup. A thick client cannot make the required connection.

Toolkit Components

The Excel Data Collection toolkit is made up of the following components:

• The Express Spreadsheet Add-In

• Express API callouts to Financial Analyzer functionality

• Standard Excel formatting features and Excel Visual Basic for Applications (VBA)

Toolkit Sample

A sample produced using the toolkit is provided. The sample is composed of the following files:

• SAMPLE.XLS -- An Excel workbook containing a sample data collection worksheet and a macro module containing routines to control functionality.

• SAMPLE.INF -- A text file containing Express programs for recalculating and submitting spreadsheet data. The programs can be incorporated in a Financial Analyzer custom database using the Express INFILE command.

These two files are located in the DOC\ADD_IN folder on the Oracle Financial Analyzer CD-ROM.

General procedure for data collection

Using this toolkit sample, users can collect data using the following procedure.

1. The user opens the workbook template in Excel. He or she is prompted for user id and password to access Financial Analyzer.

2. The user logs in. Financial Analyzer sets the appropriate access to the shared database and attaches the shared database as read-only.

3. Once the shared database is attached, the user can:
   • Enter new data into areas of the workbook specified by the template

   • Apply a Financial Analyzer solve definition to recalculate data in the workbook

   • Page through the query

   • Submit all changes made to the data in the workbook using the standard Financial Analyzer submit process

Implementing the Data Collection Toolkit

Before you begin

Before you can build a data collection template, make sure of the following:

• The Express Spreadsheet Add-In software is installed on your PC and in Excel.

• You have access to an Express Client Connection file to make a connection to the instance of Express Server used by the appropriate Financial Analyzer shared database.

  Note: The Oracle Express Database Administration Guide describes how to configure Express connections.

• The user id for your operating system is associated with a Financial Analyzer user name that has appropriate access to the shared database.

You must also ensure that the following settings on the Database tab are specified in the Options dialog box that is accessed from the Express menu in Excel.

Property	Setting
Default Attach Mode	Read only
Permanently write changes to databases attached read only box	Off

You must also ensure that the following settings on the Format tab are specified in the Options dialog box that is accessed from the Express menu in Excel.

Property	Setting
Show Page Dimension Controls box	On
Preserve Formatting After Drilling/Selection box	On
Resize Columns to Fit Data box	Off

Implementation process

To implement the toolkit, follow these general steps.

1. Create an Excel workbook template as described in the topic, "Step 1: Creating an Excel workbook template.

2. Define the query or queries using the Express Spreadsheet Add-In as described in the topic, "Step 2: Defining queries using the Express Spreadsheet Add-In.

3. Insert an Excel macro module in the workbook as described in the topic, "Step 3: Inserting an Excel macro module in the workbook.

4. Define routines in the macro module as described in the topic, "Step 4: Defining routines in the macro module.

5. Create a custom Financial Analyzer database as described in the topic, "Step 5: Creating a primary custom Financial Analyzer database.

6. Define Express programs in the custom database to handle recalculate and submit procedures as described in the topic, "Step 6: Defining Express programs.

7. Clear each query in the workbook as described in the topic, "Step 7: Clearing the queries.

8. Install the workbook template on each users' desktop as described in the topic, "Step 8: Installing the template on users' desktops.

Step 1: Creating an Excel workbook template

To implement the toolkit, you must create an Excel workbook template to use for data collection. By incorporating Excel VBA routines in the workbook, you determine what functionality the template makes available to users.

Using the new template, users can directly access their Financial Analyzer shared database. The users' profiles in Financial Analyzer determines the data to which they have access. Users cannot use the template if they disconnect from the server where the shared database is located.

One workbook can be created for many users. The template workbook can be distributed to users through electronic mail or via a network. Oracle Corporation recommends that each user have a personal copy of the workbook file. This is preferable to a situation in which multiple users access a shared copy.

Step 2: Defining queries using the Express Spreadsheet Add-In

Queries that are defined using the Express Spreadsheet Add-In are bound to the database in which they were created. Therefore, you must create the queries using the shared database that users will be accessing through the workbook template.

To define queries, follow these steps.

1. Open a new workbook template in Excel.

2. Connect to the appropriate Financial Analyzer shared database and attach it read-only.

3. Run the Express Wizard to define the necessary query or queries.

   For instructions on running the Express Wizard, refer to the Express Spreadsheet Add-In Help system.

4. Format the query or queries as desired using standard Excel formatting features.

   Tip: Although editing the query will preserve formatting in most cases, try to ensure that the query has all the necessary information before beginning formatting.

5. Save the spreadsheet.

Step 3: Inserting an Excel macro module in the workbook

Next, you must create an Excel Macro Module in the workbook. In this module you will define a number of macro routines to control functionality within the workbook.

To insert a macro module in Excel, follow these steps.

1. Select a sheet in the workbook. The module will be inserted to the left of the sheet you select.

2. From the Insert menu, choose Macro, and then choose Module.

Step 4: Defining routines in the macro module

In the macro module, define a number of macro routines to control functionality within the workbook.

The sample workbook template, SAMPLE.XLS, uses the following macro routines to control functionality within the workbook. These routines use Express Spreadsheet Add-In API methods that must be defined within the macro module of the workbook.

Note: For code listings of the sample macros described here, refer to "Data Collection Toolkit Technical Reference.

Auto_Open routine

Auto_Open is a predefined Excel procedure that runs whenever a workbook is opened. Auto_Open ensures that the configuration for the workbook is always the same on startup.

In the sample workbook template, the Auto_Open procedure calls the Startup routine.

Startup Routine

The Startup routine performs the following tasks:

• Makes a connection into Express Server.

  The Startup routine uses the Express Spreadsheet Add-In XPConnect method to establish a connection with Express Server. The login routine is based on the Express Client Connection file for a user, described in the topic "Before you begin.

  If a connection cannot be made, XPConnect returns FALSE and the workbook is closed.

• Sets the Excel environment.

  The Startup routine sets the following Excel options and environment variables:

  • Turns screen updating off to improve macro performance

  • Sets the Calculation option to Manual

  • Turns the Recalculate Before Save option off

  • Turns the Express menu off to limit the functionality available to users

• Runs the Financial Analyzer Startup routine. The Financial Analyzer Startup routine is located in the OFASERVE database. This database must be attached first.

  The Startup routine runs a Financial Analyzer API named OFA.EXT.INIT. The Express Spreadsheet Add-In XPExecute method attaches the OFASERVE database and executes OFA.EXT.INIT.

  OFA.EXT.INIT performs the following tasks:

  • Attaches all code databases

  • Populates temporary Financial Analyzer catalogs

  • Attaches the shared database

  • Applies the appropriate access rights to the shared database based on the operating system user id entered at login

• Checks for outstanding data submissions.

  Because the user is making changes to a shared database that is attached read-only, the changes are permanently written to the shared database only when the user's data submission is processed by the Task Processor.

  If the user exits a session where a submission has been made and then creates a new session against the same shared database before the earlier submission has been processed, then the user will be working with out-of-date data.

  This part of the routine stops the user from entering the workbook if any submissions made by that user are waiting in the Task Processor queue.

  The Financial Analyzer API, TK.CHK.UNPROC, returns TRUE if any tasks for the user are still waiting in the Task Processor queue.

  Note: The internal Financial Analyzer user name is stored in the SHR.USR.ROLE variable in Express during a session.

• Sets Express Spreadsheet Add-In options.

  Certain Express Spreadsheet Add-In options should be set in a particular way. Others can be varied by implementation. The Express Spreadsheet Add-In XPSetOption method can be used to set individual options.

• Registers the shared database.

  Once the shared database has been attached, the Express Spreadsheet Add-In XPDatabaseAttach method registers with the defined active query. After the registration, the query can be refreshed from the database by using the Express Spreadsheet Add-In XPRefresh method.

  Note: When running Express Server on NT, XPDatabaseAttach will only accept a drive path and not a UNC. All other references to the shared database in Financial Analyzer will also need to use a drive path.

  Tip: When you call XPRefresh, use coordinates that reference an active cell of the query.

• Disables access to the Selector through the query.

• Users can access the Selector by double-clicking on a query. If you do not want to allow users to change the status of the query then you can disable this option by setting the OnDoubleClick property to null. If you have queries defined on multiple sheets, you will need to do this for each sheet.

Calculation Routine

In the sample workbook template, the calculation routine writes back changes made to the current page to the shared database and then runs a solve definition in the database.

The Express Spreadsheet Add-In XPSetCustomWriteCommand method sets the Express program that will handle the writing of the changes to Express. Then the Express program RECALC.OFAXSA is called.

The RECALC.OFAXSA program performs the following tasks:

• Applies the changes to the database

• Filters out financial data items that should not be updated

• Runs a solve definition

The Express Spreadsheet Add-In XPWrite method executes the write for any changes made to the page. After the calculation routine has been run, the query refreshes to show the results of the solve definition.

Tip: When calling XPWrite, supply coordinates that reference an active cell in the query.

To execute the Calculation routine, the user chooses buttons created with Excel VBA that have the appropriate macros assigned.

Submit routine

In the sample workbook template, the submit routine takes the data that has been changed by the user in the current session and creates a submit data task.

Initially, the Submit routine checks to see if the user has changed any data for the current page but has not yet run the calculation routine.

If this is the case, the calculation routine is run before the data submission. The Express Spreadsheet Add-In XPGetChangeScript method identifies if a change has been made to the current page that has not been written back to Express.

In the sample the data submission is executed by the Express program SUBMIT.OFAXSA. This program sets the status of the financial data items to be submitted and creates the data submission task.

Tip: When calling XPGetChangeScript, supply coordinates that reference an active cell in the query.

To execute the Submit routine, the user chooses buttons created with Excel VBA that have the appropriate macros assigned.

Auto_Close

Auto_Close is a predefined Excel procedure that is run every time a user closes the workbook. In the sample workbook template, Auto_Close uses the Express Spreadsheet Add-In XPClose method to close any open connection to Express Server. XPClose also calls the Excel Close method that closes the workbook, automatically saving any changes that have been made.

Step 5: Creating a primary custom Financial Analyzer database

The Financial Analyzer custom database should be created in the CODE directory and its name declared in the OFACDCF.CFG file.

Any custom Express programs or objects defined during a Financial Analyzer implementation should be defined in the primary custom database. This database should be created in the code directory and its name declared in the OFACDCF.CFG file.

For example if you have created a primary custom database called CUSTOM, your OFACDCF.CFG file should contain the following information:

 [Primary Custom Database]
OFALCNAME=custom

Step 6: Defining Express programs

You define custom Express programs to handle the recalculate and submit procedures.

The sample file, SAMPLE.INF, contains two Express programs that handle the recalculate and submit procedures. The programs can be incorporated in a Financial Analyzer custom database using the Express INFILE command.

Recalculate Procedure

In the sample workbook template, the recalculate procedure RECALC.OFAXSA is defined as a boolean program in the primary custom database.

The RECALC.OFAXSA program performs the following tasks:

1. Applies the changes to the database

2. Filters financial data items that should not be updated

3. Runs a solve definition

The status of dimensions is tracked through the use of valuesets. Valuesets should be defined for all the dimensions of the financial data items being written and solved. The status of the valuesets is initially be set to null. In the sample, valuesets are defined for the Line, Org, Prod and Time dimensions.

The XPSetCustomWrite method passes to the program a string of qualified data references (QDRs) that reference the cells of the query that have been changed. RECALC.OFAXSA evaluates each QDR to determine if that financial data item should be written to. In the sample, the financial data items to be written to are BUDG and BUD.ASMP99. The status of the dimensions of each QDR for these financial data items is added to the appropriate valueset and the QDR is executed. This routine filters out those financial data items that are present in the query but that should not be altered.

In the sample, only the part of the financial data item that has changed will be solved. The status of the solve is set by the status of the valuesets. In the sample, the only exception to this is the LINE dimension. The solve runs a model on the line dimension and status must be set to all.

Also, only the data relating to the pages of the query that have changed will be submitted. In the sample, the Prod and Org dimensions are the page dimensions. A list of Prod and Org values that have been changed between submissions is kept in two temporary variables named OFAXSA.PRD.LIST and OFAXSA.ORG.LIST. These are defined as single cell text variables in the primary custom database.

The solve definition is run by the Financial Analyzer API named SO.RUN.DFN. SO.RUN.DFN is passed two arguments:

• The solve definition to be run expressed as a value of SO.ENTRY

• The list of valuesets that correspond to the dimensions of the financial data item being solved

Tip: You can find the value of SO.ENTRY you need to pass by looking at SO.DESC.

Submit Procedure

In the sample workbook template, the submit procedure SUBMIT.OFAXSA is defined as a Boolean program in the primary custom database.

SUBMIT.OFAXSA sets the status of the financial data items to be submitted and creates the data submission task. In the sample, the two temporary page variables named OFAXSA.ORG.LIST and OFAXSA.PRD.LIST contain the list of page dimension values that have been changed. If either temporary page variable is empty then the program exits without submitting.

The dimension status of the financial data items to be submitted is set through the dimension valuesets. In the sample, the status for Line is set to all because a model has been solved on that dimension. The status of Org and Prod are set by the temporary page variables. The status of Time is set according to the values shown in the query.

The submit is run by a Financial Analyzer API named SB.DATA.SHR. SB.DATA.SHR is passed the following two arguments:

• The list of valuesets that correspond to the dimensions of the financial data item being submitted

• The object name of the financial data item to be submitted

Tip: To keep changed data from being submitted more than once, the temporary page variables OFAXSA.ORG.LIST and OFAXSA.PRD.LIST are set to NA at the end of the procedure.

Step 7: Clearing the queries

Before the workbook is ready for distribution each query should be cleared. Clearing the query clears labels and data from the query and prepares it to be opened by the users. When the user opens the workbook the labels and data are placed into the query according to their dimension access definitions.

To clear a query, follow this procedure.

1. In the Excel workbook, position the cursor in any cell that contains data for the query you want to clear.

2. Select Clear Query from the Express menu.

   The Express Spreadsheet Add-In displays a confirmation box.

3. Choose Yes in the confirmation box to clear the query.

Step 8: Installing the template on users' desktops

Before they can use the data collection template, each template user needs the following:

• The Express Spreadsheet Add-In software installed on their PC and in Excel. The Oracle Express Spreadsheet Add-In User's Guide contains information on installing the Express Spreadsheet Add-In. It includes a list of supported versions of Excel and other software and hardware requirements.

• Access to an Express Client Connection file to make a connection into the instance of Express Server used by the appropriate Financial Analyzer shared database. The Oracle Express Database Administration Guide describes how to configure Express connections.

• A Financial Analyzer user name.

• The appropriate dimensions, financial data items, hierarchies, attributes, models and solve definitions distributed to them.

• Note: Access to the data defined in the template will depend on the structure distributed to each Financial Analyzer user name. If more than one Financial Analyzer user name is assigned to an operating system userid, then the first Financial Analyzer user name defined will be used. The exception to this is if the operating system user id is assigned to the Financial Analyzer user name of the Administrator. In that case, this profile will be used.

The workbook template can be distributed to users through electronic mail or via a network. Oracle Corporation recommends that each user have a personal copy of the workbook file. This is preferable to a situation in which multiple users access a shared copy.

Data Collection Toolkit Technical Reference

Introduction

This topic describes Financial Analyzer API calls that are used by the routines defined in the macro module of the sample workbook template.

Financial Analyzer API calls

The following table describes the API call that attaches the Financial Analyzer code and shared databases.

Characteristic	Description
Routine name	OFA.EXT.INIT
Purpose	Runs the Financial Analyzer startup routine. Attaches code databases and populated temporary catalogs. Attaches the shared databases and establishes permit security based on the operating system user id.
Input	Blank Path to the OFASYSCF.CFG file
Returns

The following table describes the API call that checks the Task Queue for any waiting task for a particular user.

Characteristic	Description
Routine name	TK.CHK.UNPROC
Description:	This routine takes as input an internal Financial Analyzer user name and then uses it to limit TK.PROP value USER. It then checks the TK.CATALOG for TK.PROP value STATUS of WAITING or IN PROGRESS.
Input:	Financial Analyzer internal user name (i.e., 'AA' for super user)
Returns:	YES -- Tasks are waiting to be processed for this user NO -- No tasks are waiting

The following table describes the API call that runs a solve definition on a particular financial data item.

Characteristic	Description
Routine name:	SO.RUN.DFN
Description:	This routine sets the status of a financial data item using user defined valuesets and runs a solve definition of that financial data item.
Input:	Solve definition name (SO.ENTRY) Multiline text variable containing valueset names with which to limit the financial data item's dimensions
Returns:	YES -- If successful NO -- If unsuccessful

The following table describes the API call that submits data to the shared database.

Characteristic	Description
Routine name:	SB.DATA.SHR
Description	This routine sets the status of data for the financial data item using user defined valuesets. It also submits data for the financial data item to the task queue to update the shared database
Input	Financial Analyzer financial data item Multiline text variable containing valueset names with which to limit the financial data item's dimensions
Returns	YES -- If successful NO -- If unsuccessful