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.
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]
)