Returning MDX query results in an Excel matrix formula

In my last post I had an example for a function to return a single value from an MDX query which has certain performance drawbacks when being used in many Excel fields.

Since Excel supports matrix formulas to do a computation on multiple values, this can also be used for MDX query results. Another benefit for this approach is, that dimension members that are added later on, are also displayed in the query result.

Before I post the function code, here's the result. For my test, I used this MDX query on the Adventure Works cube:

SELECT
  {
    [Measures].[Internet Sales Amount],
    [Measures].[Internet Order Quantity],
    [Measures].[Internet Gross Profit]
  } ON 0,
  [Product].[Category].[Category] ON 1
FROM [Adventure Works]

The result, when being called from SQL Server Management Studio, looks like this:

The following screenshot shows how this result looks like in Excel using the matrix MDX formula:

Now, what are the #N/A entries about? The matrix formula measures the size of the resulting cell area and returns a two dimensional array of exactly that size. When entering the formula I chose a larger area on the worksheet in order to be prepared for additional entries on both axes. Of course you could think of returning a larger matrix from the function in order to properly fill out the unused cell. The best approach would be to supply width and height as parameters.

So, this is how the formula is to be entered:

Be sure to press CTRL+ALT+ENTER when entering the formula in order to turn it into a matrix formula.

The MDXGetMDX function takes five parameters:

Parameter	Description	Example
Server	Hostname of the SSAS server	localhost
InitalCatalog	Name of the SSAS database	Adventure Works DW
Cube	Name of the SSAS cube	Adventure Works
mdx	MDX code of the query	select ... on 0, ... on 1 from cubename
WithCaption	When true, captions are printed	true

Finally, here's the code I used for the MDGetMDX(...) function:

Function MDGetMDX(Server As String, InitialCatalog As String, Cube As String, mdx As String, WithCaption As Boolean) As Variant
    On Error GoTo errorhandler:
    Dim cset As New ADOMD.Cellset
    Dim conn As New ADODB.connection
    Dim x As Variant
    Dim i As Integer, j As Integer
    Dim i0 As Integer, j0 As Integer ' begin of the data area
    Dim i1 As Integer, j1 As Integer ' size of the data area
    conn.Open "Data Source=" & Server & ";Provider=MSOLAP;Initial Catalog=" & CatalogName & "" & InitialCatalog & ""
    cset.Open mdx, conn
    If cset.Axes.Count > 2 Then
        MDGetMDX = "More than 2 axes are not allowed!"
        Exit Function
    End If
    If cset.Axes.Count > 0 Then i1 = cset.Axes(0).Positions.Count Else j1 = 0
    If cset.Axes.Count > 1 Then j1 = cset.Axes(1).Positions.Count Else j1 = 0
    If WithCaption Then
        ' column headings are displayed as rows
        If cset.Axes.Count > 1 Then i0 = cset.Axes(1).DimensionCount Else i0 = 0
        ' row headings are displayed as columns
        If cset.Axes.Count > 0 Then j0 = cset.Axes(0).DimensionCount Else j0 = 0
    Else
        i0 = 0
        j0 = 0
    End If
    If cset.Axes.Count = 2 Then
        ReDim x(j0 + j1 - 1, i0 + i1 - 1)
    ElseIf cset.Axes.Count = 1 Then
        ReDim x(j0, i0 + i1 - 1)
    Else
        ReDim x(1, 1)
    End If
    For i = 0 To UBound(x, 2)
        For j = 0 To UBound(x, 1)
            x(j, i) = ""
        Next
    Next
    ' Show caption:
    If WithCaption Then
        For i = 0 To i1 - 1
            For j = 0 To cset.Axes(0).Positions(i).Members.Count - 1
                x(j, i + i0) = cset.Axes(0).Positions(i).Members(j).Caption
            Next
        Next
        For j = 0 To j1 - 1
            For i = 0 To cset.Axes(1).Positions(j).Members.Count - 1
               x(j + j0, i) = cset.Axes(1).Positions(j).Members(i).Caption
            Next
        Next
    End If
    If cset.Axes.Count = 2 Then
        For i = 0 To i1 - 1
            For j = 0 To j1 - 1
                x(j + j0, i + i0) = nz(cset(i, j).Value, 0)
            Next
        Next
    ElseIf cset.Axes.Count = 1 Then
        For i = 0 To i1 - 1
            x(j0, i + i0) = nz(cset(i).Value, "")
        Next
    Else
        x(0, 0) = cset(0).Value
    End If
    MDGetMDX = x
    cset.Close
    conn.Close
    Exit Function
errorhandler:
    MDGetMDX = Err.Description
End Function

Function nz(x As Variant, other As Variant) As Variant
    If Not IsNull(x) Then
        nz = x
    Else
        nz = other
    End If
End Function