MDX | The Frog-Blog

 

Convert MDX fields to SQL

Saturday, December 15th, 2007

A number of our customers have reporting systems that use both MDX and SQL, retrieving data from both OLAP and SQL Server databases. This generates the problem of converting an MDX field ([Dimension].[Hierarchy].&[Attribute]) into SQL Server field value (Attribute). The following code is a Reporting Services custom code section that will rip off the MDX and leave you with the value.

    Public Function MDXParamToSQL(Parameter As String, All As String) As String

        Dim Val As String
        Val = Parameter

        If Val.Contains(”[”) Then
            If Val.ToLower().Contains(”].[all]”) Then
                Return All
            Else
                Val = Val.Substring(1, Val.LastIndexOf(”]”) - 1)
                Val = Val.Substring(Val.LastIndexOf(”[”) + 1)
                Return Val
            End If
        Else
            Return Val
        End If

    End Function

MDX | The Frog-Blog