Essbase Excel Add-in and SmartView free form retrieval 2

This entry was posted in Essbase Excel Add-in Smart View on by

I was once working on automating some Essbase retrievals and one of my sheet was pulling values based on UDA. 

I knew that I could use Pattern search (by making use of Subset option), however I don’t want any User Interaction and had to look for a different option.

Many of you might have used Free Form retrieval mode of EXCEL Add-in. 

For those who have not used it yet.

Free-Form mode provides additional retrieval capabilities. First, it enables you to enter member names into a random location in the worksheet. In this mode, Essbase scans the names in a worksheet and picks a default view for you. In addition, Free-Form mode enables you to use Essbase report script commands to retrieve data into a worksheet. The report script commands are most useful for defining member range references that can dynamically bring back the most current member information.

In Free-Form mode, Essbase retrieves data with these characteristics:
  • Essbase interprets the member names and creates a default view based on the location of the labels.
  • Essbase removes blank rows and columns on a retrieval action.

So I created a VBA program which will change the mode to Free form mode and retrieve the report script.
‘
‘ Change retrieval mode to free form
‘

’15 Enable template retrieve mode Boolean
’16 Enable free form/Version-2.x mode Boolean

sts = EssVSetSheetOption(Null, 15, True)
sts = EssVSetSheetOption(Null, 16, True)

range(“A1”).Value = “

 application.StatusBar = “Retrieving Small Markets…”
sts = EssMenuVRetrieve()

‘

‘ Change to advanced interpretation mode

‘
sts = EssVSetSheetOption(Null, 15, False)
sts = EssVSetSheetOption(Null, 16, False)
For those who are looking for a similar option in SmartView can use the below VBA code for the same.
SmartView you’ve to change report scripts to MDX queries.
Sub Sample_HypExecuteQuery()
Dim vtQuery As Variant
vtQuery = “SELECT {Year} ON COLUMNS, {UDA([Market],””Small Market””)} ON ROWS FROM Sample.Basic”
sts = HypConnect(Empty, “system”, “password”, “private connection”)
sts = HypExecuteQuery(Empty, vtQuery)
sts = HypDisconnect(Empty, True)
End Sub

HTH

About Celvin Kattookaran

I’m an EPM Consultant, my primary focus is on Hyperion Planning and Essbase. Some of you from Hyperion Support team might recognize me or have seen my support articles, I was with the WebAnalysis Support Team. I'm an Independent Consultant with “Intekgrate Corporation” based out of Aurora office. I’m from God’s Own Country (Kerala, India), lived in all southern states of India, Istanbul and Johannesburg (and of course United States). I’m core gamer :) and an avid reader. I was awarded Oracle ACE Director for my contributions towards EPM community.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

2 thoughts on “Essbase Excel Add-in and SmartView free form retrieval”

  • Reply ↓
    Anonymous

    When I try to HypExeucteQuery it was gives the error code -9 which as per the oracle guide means Operation cancelled. What am I missing..

    vtQuery = “SELECT {([Jan])} on COLUMNS, {([A_UOC_Volume])} on ROWS from APMR.MR ”
    sts = HypConnect(“Sheet2”, “uName”, “Password”, “MR_TEst”)
    sts = HypExecuteQuery(“Sheet2”, vtQuery)
    sts = HypDisconnect(“Sheet2”, True)