|Oracle financial Analyzer User's Guide
Part No. A87522-01
Express Spreadsheet Add-In , 6 of 7
Before you can build a data collection template, make sure of the following:
Note: The Oracle Express Database Administration Guide describes how to configure Express connections.
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.
Default Attach Mode
Permanently write changes to databases attached read only box
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.
Show Page Dimension Controls box
Preserve Formatting After Drilling/Selection box
Resize Columns to Fit Data box
To implement the toolkit, follow these general steps.
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.
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.
For instructions on running the Express Wizard, refer to the Express Spreadsheet Add-In Help system.
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.
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.
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 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.
The Startup routine performs the following tasks:
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.
The Startup routine sets the following Excel options and environment variables:
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:
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.
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.
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.
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:
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.
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 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.
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:
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.
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:
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:
Tip: You can find the value of SO.ENTRY you need to pass by looking at SO.DESC.
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:
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.
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.
The Express Spreadsheet Add-In displays a confirmation box.
Before they can use the data collection template, each template user needs the following:
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.