There may be cases when you need to manipulate the hierarchy unique name so you can pass it to drill-thorugh report which stored procedure base.
In main dataset of the report that use mdx, in the ON ROW section this code should be added DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME
SELECT NON EMPTY
{
} ON COLUMNS
, NON EMPTY
{ [Set]
} DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME
, [Sample].[Sample Hierarchy].[Sample Code]
ON ROWS
Hierarchy to manipulate: [Sample].[Sample Hierarchy].[Sample].&[14344]
This will be the expression in the drill-through report
=Code.returnId(Fields!Sample.UniqueName)
Add this in the Report Properties > Code section
VB Code
Public Function returnId(ByVal uniqueName As String) As String
Return Mid(uniqueName, InStr(uniqueName, "&[")+2, Len(uniqueName) - InStr(uniqueName, "&[") -2)
End Function
This will be the expression in the drill-through report
=Code.returnId(Fields!Product.UniqueName)
Result: 14344
In main dataset of the report that use mdx, in the ON ROW section this code should be added DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME
SELECT NON EMPTY
{
} ON COLUMNS
, NON EMPTY
{ [Set]
} DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME
, [Sample].[Sample Hierarchy].[Sample Code]
ON ROWS
Hierarchy to manipulate: [Sample].[Sample Hierarchy].[Sample].&[14344]
This will be the expression in the drill-through report
=Code.returnId(Fields!Sample.UniqueName)
Add this in the Report Properties > Code section
VB Code
Public Function returnId(ByVal uniqueName As String) As String
Return Mid(uniqueName, InStr(uniqueName, "&[")+2, Len(uniqueName) - InStr(uniqueName, "&[") -2)
End Function
This will be the expression in the drill-through report
=Code.returnId(Fields!Product.UniqueName)
Result: 14344