Inserting results of Hyperion Smart View VBA Function into list box and/or worksheet range

Question

I've seen answers to questions similar to this one, but I haven't been able to find anything that addresses this exact situation.

Goal: Populate a list box and/or a worksheet with the output of a HypQueryMembers function. For example, I would like to get a list of the descendants of account 10100 without having to perform an ad hoc query and zoom in. I know how to get the return code, e.g. 0 if successful, but I want the actual output. I found some code that populated a list box, but I haven't been able to get it to work for me. I receive the error "Could not set the List property. Invalid property array index." My code follows:

Sub TestQueryMbrs()

Dim X As Integer
Dim arrAccounts

X = HypQueryMembers(Empty, "10100", HYP_DESCENDANTS, Empty, Empty, Empty, Empty, arrAccounts)

If X <> 0 Then
    MsgBox "Unable to populate members." & vbCr & vbCr & "Error: " & X, vbCritical + vbOKOnly
Else
    UserForm2.ListBox1.List = arrAccounts
    UserForm2.Show
End If

End Sub

Any idea what I'm doing wrong? Also, I would like to accomplish the same thing, but populate a worksheet rather than a list box. But one step at a time!

Thanks!

Answer 1

Is 10100 the genuine name of the field? I suspect by you calling it account that 10100 should be replaced by Account instead as that parameter seems to be limited to the field name rather than an individual member. However, I have yet to determine how to get descendents of a particular account/cost centre so your way may be the correct way to do this.

I'd suggest trying that change to just "Account" though and then try adding as variant to Dim arrAccounts and then deleting Dim arrAccounts altogether if that doesn't work.

You may also not be able to populate the listbox before Userform2 is shown? You could wrap application.screenupdating =FALSE then TRUE around the Userform2 changes so the user doesn't see the list box being populated if there is a lag.

Answer 2

I had the same issue today and ran across this post - I realize it's years old...

Oracle's documentation on this function is a bit confusing... at least the example they used at the bottom of the page: https://docs.oracle.com/cd/E72988_01/DGSMV/ch12s15.html

For example, you need to enter the integer value for HYP_DESCENDANTS, which is 2 instead of the text shown in your code.

The following is a good snippet of code to verify your array:

Declare PtrSafe Function HypQueryMembers Lib "HsAddin" (ByVal vtSheetName As Variant, ByVal vtMemberName As Variant, ByVal vtPredicate As Variant, ByVal vtOption As Variant, ByVal vtDimensionName As Variant, ByVal vtInput1 As Variant, ByVal vtInput2 As Variant, ByRef vtMemberArray As Variant) As Long
Sub Example_HypQueryMembers()

sts = HypQueryMembers("INSERT WORKSHEET NAME HERE", "INSERT SMARTVIEW MEMBER HERE", 1, Empty, Empty, Empty, Empty, vArray)

If IsArray(vArray) Then
  cbItems = UBound(vArray) + 1
      MsgBox ("Number of elements = " + Str(cbItems))
   For i = 0 To UBound(vArray)
      MsgBox ("Member = " + vArray(i))
   Next
Else
   MsgBox ("Return Value = " + Str(vArray))
End If

End Sub