Executing Smart View Retrievals using VBA

Author: Lydia

When working with Hyperion systems, Smart View is a key tool used for reporting, as well as inputting data. It provides a valuable link between Hyperion and Excel, which allows users to retrieve data from the database straight into Excel with ease. Smart View functions are available through the Smart View toolbar and can be used with Essbase, HFM, Planning or Cloud connections.



Occasionally, it is necessary to perform Smart View functions behind the scenes using VBA code, rather than manually using the Smart View toolbar. For example, a spreadsheet which will refresh all the Essbase retrieval sheets on opening, so that users can view the most up to date data, without having to remember to refresh each of the retrieval sheets themselves.

This blog will be cover how to retrieve data using a Smart View refresh function in VBA. For more information about Smart View Excel VBA functions, go to the Oracle Hyperion Smart View Developers Guide


System Requirements

There are a couple of things which must be set up before Smart View functions can be called using VBA code. The first is that Smart View must be installed on the local machine which will be use to run the VBA code.

The second requirement is that the Smart View functions must be declared in the Excel spreadsheet. To do this:

  1. In Visual Basic Editor, select File > Import File.
  2. From the Import File dialog box, select the file smartview.bas located in your Oracle\SmartView\bin directory.

The file is copied into the module, allowing you to use any Smart View Excel VBA function in your program. This must be done in every excel file which will be referencing Smart View functions.


Refreshing a Retrieval Sheet

The function used to retrieve the latest data from Hyperion is HypMenuVRefresh(). This command is the equivalent of using the Refresh button on the Smart View Excel toolbar. 

The refresh function will use the last connection details that were used on that sheet and will ask the user to enter their login details for the first retrieval. Once a user has logged in, retrievals can be performed on multiple other sheets without having to login again.

Before the sheet can be refreshed using the HypMenuVRefresh function, the retrieval sheet must be set to visible (it is not possible to refresh a hidden sheet) and it must be the active sheet in the workbook. The last thing is to make sure that no ranges of cells are selected as this brings back an error. To do this, select a specific cell on the sheet (e.g. cell A1).

The following VBA procedure will perform a refresh of a specified retrieval sheet called Ess_Retrieve.


In the above code, if the refresh was unsuccessful, the status (sts variable) will return an error code and display an error message. If it was successful, then the status is equal to 0.

If necessary, the retrieval sheet can be hidden again, by using code:

For more Hyperion tips and tricks from me, go to http://lydia-maksoud-epm.blogspot.co.uk/.