HypExecuteMDXEx

HypExecuteMDXEx() executes an MDX query whose results are output in a data structure but are not displayed on the worksheet. (If you want to display the query results on a worksheet, use HypExecuteQuery instead.)

HypExecuteMDXEx 
(
ByVal vtSheetName As Variant,
ByVal vtQuery As Variant, 
ByVal vtBoolHideData As Variant,
ByVal vtBoolDataLess As Variant,
ByVal vtBoolNeedStatus As Variant,
ByVal vtMbrIDType As Variant, 
ByVal vtAliasTable As Variant, 
ByRef outResult As MDX_AXES_NATIVE
) As Long

vtSheetName: For future use. Currently the active sheet is used.

vtQuery: The MDX query to be executed

vtBoolHideData: The Boolean flag hide or unhide data in the result

vtBoolDataless: The Boolean flag to get or avoid data in the result

vtBoolNeedStatus: The Boolean flag to get or avoid status info in the result

vtMbrIDType: The member type identifier for the result (name or alias)

vtAliasTable: The alias table to be used

outResult: Pointer to a structure of type MDX_AXES. It contains the query output. (See Data Types Specific to HypExecuteMDXEx for data types and support functions for this API)

Returns 0 if successful; otherwise, returns the appropriate error code.

The following data types apply exclusively to HypExecuteMDXEx:

MDX_CELL: The data type corresponding to a cell

MDX_PROPERTY: The data type containing properties info for members and dimensions

MDX_MEMBER: The data type for members information

MDX_DIMENSION: The data type for dimensions information

MDX_CLUSTER: The data type for cluster information

MDX_AXIS: The data type representing an axis

MDX_AXES: The root level structure containing a collection of axes and cells

MDX_AXES_NATIVE: The data type used as an out parameter for HypExecuteMDXEx. This structure should be converted to MDX_AXES using procedure GetVBCompatibleMDXStructure.

Sub GetVBCompatibleMDXStructure (ByRef inStruct As MDX_AXES_NATIVE, ByRef outStruct As MDX_AXES) 

Public Declare Function HypExecuteMDXEx Lib "HsAddin" 
(ByVal vtSheetName As Variant, ByVal vtQuery As Variant, ByVal vtBoolHideData As Variant, ByVal vtBoolDataLess As Variant, ByVal vtBoolNeedStatus As Variant, ByVal vtMbrIDType As Variant, ByVal vtAliasTable As Variant, ByRef outResult As MDX_AXES_NATIVE) As Long

Sub Example_HypExecuteMDXEx ()

Dim Query As Variant
Dim vtBoolHideData As Variant
Dim vtBoolDataLess As Variant
Dim vtBoolNeedStatus As Variant
Dim vtMbrIDType As Variant
Dim vtAliasTable As Variant
Dim result_Native As MDX_AXES_NATIVE   
Dim result_VBCompatible As MDX_AXES   

Query = "select {Jan} on COLUMNS, {Profit} on ROWS from Sample.Basic"
vtBoolHideData = True
vtBoolDataLess = True
vtBoolNeedStatus = True
vtMbrIDType = "alias"
vtAliasTable = "none"

sts = HypConnect(Empty, "system", "password", "SB")

If sts = 0 Then

sts = HypExecuteMDXEx (Empty, Query, vtBoolHideData, vtBoolDataLess, vtBoolNeedStatus, vtMbrIDType, vtAliasTable, result_Native)

sts = GetVBCompatibleMDXStructure (result_Native, result_VBCompatible) --- New support function … More Info under Notes section

sts = HypDisconnect(Empty, True)

Else

End If

End Sub