Tag Archives: strtomember

Member Does Not Exist in MDX Calculations

Granted if an MDX calculation involves a specific named member, then this member should ideally exist in the datamart and be processed in the cube.  Nonetheless, there may be the occasion where this isn’t the case and when browsing the calculated measure in your OLAP client will get a #Value! error.

The following code snippet provides an example for how to handle missing member. The approach uses a SET expression to look for the missing member followed by a simple IIF expression in the CREATE MEMBER to determine if this member is missing and how to handle it.

Noted, there are numerous more performance savvy and sometimes cumbersome ways to solve this problem in Analysis Services, however the simple approach is taken here to get you up and running.  This will buy you some time to come up with a  more sophisticated approach.

/* PreExempt Missing Member */
Create Set CurrentCube.[MissingMemberSet] As
iif(IsError(StrToMember("[Dimension].[Hierarchy].&[MEMBER]")),
{}, {[Dimension].[Hierarchy].&[MEMBER]});

Create Member CurrentCube.Measures.[Calculation on Missing Member]
AS
IIF ([MissingMemberSet].Count > 0,
([Dimension].[Hierarchy].&[MEMBER],Measures.[SomeMeasure]),
0
)
,
FORMAT_STRING = "Currency",
LANGUAGE = 1033,
NON_EMPTY_BEHAVIOR = { [SomeMeasure] },
VISIBLE = 1 ,  DISPLAY_FOLDER = 'Display Folder'  ;

Thanks to Dr. Nick and various posts by Deepak Puri for the inspiration.