Customizing Oracle® Hyperion Smart View for Office Worksheets by Using the VBA Toolkit


Purpose


This tutorial covers customizing Smart View worksheets by using the Visual Basic for Applications (VBA) toolkit.

Time to Complete


Approximately 40 minutes.

Topics


This tutorial covers the following topics:

Overview
Scenario
Prerequisites
Importing the Resources File
Adding VBA Procedures
Adding Macro Buttons
Summary
Related information

Viewing Screenshots


Click icon to show all screenshots Click icon to show all screenshots

Note: Alternatively, you can click an individual icon (or image) associated with each step to view (or hide) the screenshot associated with that step.

Overview


This tutorial introduces the VBA toolkit for Smart View. Smart View enables you to customize and automate common tasks by using VBA functions. The VBA functions enable you to execute the Hyperion menu commands from an Excel macro.

In this tutorial, you use the VBA toolkit to create buttons on a Smart View worksheet. The buttons connect to, and disconnect from, a data source.

Back to Topic List

Scenario


Your users have asked you, a Smart View administrator, to create an easy way for them to connect to, and disconnect from, their most common applications.

Back to Topic List

Prerequisites


Before starting this tutorial, you should:

1.

Have an existing connection to a data source in Smart View

2.

Know the user name and password of the user who connects to the data source

Back to Topic List

Importing the Resources File


Before you can use Visual Basic functions in an Excel project, you must declare them in a module. A declaration outlines the necessary elements of the function so that Visual Basic can run it. You can declare only those functions that you plan to use, or you can declare all functions.

In this topic, you declare all VBA functions by importing the smartview.bas file.

1.

In Excel, select Tools > Macro > Visual Basic Editor.

Visual Basic Editor opens in a separate window within Excel.

 

2.

Select File > Import File.


3.

Select c:\Hyperion\SmartView\Bin\smartview.bas, and click Open to import the file.

This file contains all Smart View VBA functions. By importing it, you are declaring all functions ready for use. You can also declare only those functions that you know you will use by deleting unused functions in the file.

 

Back to Topic List

Adding VBA Procedures


In this topic, you create a VBA module and add "Connect" and "Disconnect" procedures to the module.

1.

In VBA Editor, select Insert > Module.

 

2.

Select Insert > Procedure.

 

3.

In the Add Procedure dialog box, perform these actions:

a. In the Name box, type ConnectTraining. (You can substitute the name of your application for "Training.")
b. Under Type, select Sub.
c. Under Scope, leave the default as Public.
d. Click OK.
 

A sub procedure is created.

 

4.

Within the sub procedure, enter x=HypConnect(" SheetName"," Username"," Password"," Connection") .

• Sheetname is the name of the worksheet to operate on.
• Username is the name of a valid user for the data source provider.
• Password is the password for this user.
• Connection is the connection name of the data source.
 

The Connect function is declared.

 

5.

Select Insert > Procedure.

 

6.

In the Add Procedure dialog box, perform these actions:

a. In the Name box, type DisconnectTraining. (You can substitute the name of your application for "Training.")
b. Under Type, select Sub.
c. Under Scope, leave default as Public.
d. Click OK.
 

A sub procedure is created.

 

7.

Within the sub procedure, enter x=HypDisConnect(" SheetName",True).

Sheetname is the name of the worksheet to operate on.

The Disconnect function is declared.

 

8.

Select File > Close and Return to Microsoft Excel.

 

Back to Topic

Adding Macro Buttons


In this topic, you create Connect and Disconnect buttons within Excel, and you assign them the VBA functions that you created.

1.

In Excel, select Hyperion > Data Source Manager.

 

2.

If any connections are active, right-click the active connection and select Disconnect to close it. Click Close to close the Data Source Manager.

 

3.

Select View > Toolbars > Forms.

The Forms toolbar is displayed.


 

4.

Select the Button icon.


 

5.

Place the crosshairs in the grid, and drag to draw a button .

The Assign Macro dialog box is displayed.


 

6.

Select the ConnectTraining function, and click OK.

You are returned to the worksheet.


 

7.

Select the button text, and enter Connect to Training.

 

8.

Repeat steps 4, 5, and 6 to create another button, and assign the DisconnectTraining function to it.

 

9.

Select the button text, and enter Disconnect Training.

 

10.

Click the Connect to Training button.

 

11.

Select Hyperion > Active Connections to verify that you are connected to the data source.

 

12.

Click the Disconnect Training button.

 

13.

Select Hyperion > Active Connections to verify that you are disconnected from the data source.

Congratulations! You have completed the tutorial.

 

Back to Topic List

Summary


In this tutorial, you learned how to:

Import the Smartview.bas file to declare VBA functions
Create a VBA module and add VBA procedures
Assign VBA functions to buttons

Back to Topic List

Related Information


To learn more about VBA for Smart View, refer to the Hyperion Smart View for Office Release 11.1.1 User's Guide

To learn more about Financial Management, refer to additional OBEs.

Back to Topic List

Left Curve
Popular Downloads
 Right Curve
Untitled Document
Left Curve
More Middleware Downloads
 Right Curve