0845 643 64 63

Convert MDX fields to SQL

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

Lets say that you have a report using an MDX dataset, if you want to call a drillthrough report based on SQL Server you will need to pass at least one attribute through as a parameter to filter the second report. If you add the code above to the custom code section, you can set the parameter value of the second report to

    =Code.MDXParamToSQL(Fields!MyField.Value,”%”)

The second report will then just receive the member name, not the full MDX unique identifier.

2 Responses to Convert MDX fields to SQL

  • Thanks, this is cool. I’m new to SSAS and SSRS. Would you have version for passing multi-parms, an array?

  • How to convert Mdx for Sql server

    Preço Médio Venda

    Iif(
    IsEmpty([Measures].[Qtd Faturada]) Or [Measures].[Qtd Faturada] <= 0,
    null,
    ([Measures].[CA Net] – [Measures].[Nota Crédito] – [Measures].[Devolução]) / [Measures].[Qtd Faturada]
    )

    —————————————————————————————————————————

    Preco Médio Net

    Iif(
    IsEmpty([Measures].[Qtd Faturada]) Or [Measures].[Qtd Faturada] <= 0,
    null,
    [Measures].[CA Net Líquido] / [Measures].[Qtd Faturada]
    )

Leave a Reply

Your email address will not be published. Required fields are marked *

HTML tags are not allowed.

366,326 Spambots Blocked by Simple Comments

Power BI Sentinel
The Frog Blog

Team Purple Frog specialise in designing and implementing Microsoft Data Analytics solutions, including Data Warehouses, Cubes, SQL Server, SSIS, ADF, SSAS, Power BI, MDX, DAX, Machine Learning and more.

This is a collection of thoughts, ramblings and ideas that we think would be useful to share.

Authors:

Alex Whittles
(MVP)
Reiss McSporran
Jeet Kainth
Jon Fletcher
Nick Edwards

Data Platform MVP

Power BI Sentinel
Frog Blog Out
twitter
rssicon