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.
- 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)
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 VariantvtQuery = “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 SubHTH
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)
Is that running if you run that query in execute MDX window of Smartview?
Is Hypconnect working?