Before You Begin
Purpose
This tutorial covers customizing Smart View worksheets by using the Visual Basic for Applications (VBA) toolkit.
Time to Complete
Approximately 40 minutes.
Background
This tutorial introduces the VBA toolkit for Smart View. Smart View enables you to customize and automate common tasks by using VBA functions. The menu equivalents are Visual Basic functions that execute the Hyperion menu commands.
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.
Scenario
Your users asked you, a Smart View administrator, to create an easy way for them to connect to, and disconnect from, their most common applications.
What Do You Need?
The following is a list of software requirements:
- Oracle Hyperion Smart View for Office 11.1.2.5
-
Have an existing connection to a data source in Smart View (Reports is used in the tutorial)
-
Know the user name and password of the user who connects to the data source
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.
-
In Excel, click the Office button, select Excel Options, and under Popular, check Show Developer tab in the Ribbon. Click OK.
View ImageThe Developer tab is displayed in Excel.
View Image -
In Excel, on the Developer tab, click Visual Basic.
Visual Basic Editor opens in a separate window.
View Image -
Select File > Import File.
View Image -
Select C:\Oracle\SmartView\bin\smartview.bas, and click Open to import the file.
View ImageThis 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 will use by deleting unused functions in the .bas file.
View Image
Adding VBA Procedures
In this topic, you create a VBA module and add "connect" and "disconnect " procedures to the module.
-
In VBA Editor, select Insert > Module.
View Image -
Select Insert > Procedure.
View Image -
In the Add Procedure dialog box, perform these actions:
- In the Name box, enter ConnectReports. (You can substitute the name of your application for "Reports")
- Under Type, select Sub.
- Click OK.
View ImageA sub procedure is created.
-
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.
View Image -
Select Insert > Procedure.
View Image -
In the Add Procedure dialog box, perform these actions:
- In the Name box, enter DisconnectReports. (You can substitute the name of your application for "Reports")
- Under Type, select Sub.
- Click OK.
View Image -
Within the sub procedure, enter x=HypDisConnect("SheetName",True).
Sheetname is the name of the worksheet to operate on.
View ImageThe Disconnect function is declared.
-
Select File > Close and Return to Microsoft Excel.
View Image
Adding Macro Buttons
In this topic, you create Connect and Disconnect buttons within Excel, and you assign them the VBA functions that you created.
-
In Excel, select the Smart View tab, and click Panel.
View ImageThe Smart View panel is displayed.
-
If any connections are active, select the active connection, and click Disconnect to close it. Click X to close the Smart View panel.
View Image -
In Excel, select the Developer tab.
View Image -
On the sheet tab, select the current name, and type FreeForm.
View Image -
On the Developer tab, click Insert, and select the Button icon.
View Image -
Place the crosshairs in the grid, and drag to draw a button.
The Assign Macro dialog box is displayed.
View Image -
Select the ConnectReports function, and click OK.
View ImageYou are returned to the worksheet.
-
Select the button text, and enter Connect to Reports.
View Image -
Repeat steps 4, 5, and 6 to create another button and assign the DisconnectReports function to it.
View Image -
Select the button text, and enter Disconnect Reports.
View Image -
Click the Connect to Reports button.
View Image -
On the Smart View tab, click Connections > Active Connections to verify that you are connected to the data source.
View Image -
Click the Disconnect Reports button.
View Image -
On the Smart View tab, select Connections > Active Connections to verify that you are disconnected from the data source.
View ImageYou have completed the tutorial.