Member was not found in the cube when the string was parsed

0
votes

I'm working in an application where I have a database table with member values but, it could be that there aren't the same members in the database that in the cube. For example:

DATABASE:

  • AverageSpeed
  • AverageTime
  • AverageDistance

CUBE:

  • AverageSpeed
  • AverageTime

My MDX is something like this:

WITH   MEMBER [IDENTIFIER] AS [Measures].[AverageDistance]
MEMBER [DateYear] AS 
'ANCESTOR( [DimDate].[All DimDate].CurrentMember,[DimDate].[All DimDate].CurrentMember.Level.Ordinal -1).Member_Key'
MEMBER [DateMonth] AS 
'ANCESTOR( [DimDate].[All DimDate].CurrentMember,[DimDate].[All DimDate].CurrentMember.Level.Ordinal -2).Member_Key'
MEMBER [DateDay] AS 
'ANCESTOR( [DimDate].[All DimDate].CurrentMember,[DimDate].[All DimFecha].CurrentMember.Level.Ordinal -3).Member_Key' 
SET [COLUMNS] as ' {[DateYear],[DateMonth],[DateDay],[DateDay]} '         
SET [IDENTIFIERS] as ' {[IDENTIFIER]} '                  
SELECT { [DimDate].[All DimDate].[All] } ON ROWS,                
{ [COLUMNS] + [IDENTIFIERS] } ON COLUMNS  
FROM ( select {[DimDate].[All DimDate].[Day].&[20100401]:[DimDate].[All DimDate].[Day].&[20180628] }      
on 0 from  [CUBE])         
WHERE ( [DimPlace].[Place].&[000001] )

When I execute the query it gives me the next error:

  • The '[AverageDistance]' member was not found in the cube when the string, [Measures].[AverageDistance], was parsed.

I want to return null if the member doesn't exist.

I know I can use ISERROR(), but I just want the null value if the member doesn't exist, if there is other error I have to show it to the user.

Is there a way to achieve this from the MDX query?

1
mdxolap

1 Answers

1
votes

If you are restricted to one statement, then IsError is probably the best you can achieve. However, if you can run another statement and then generate your final MDX statement, then you could first check which measures exist, and then generate your MDX appropriately.

A simple statement to see all measures would be

SELECT {} on COLUMNS,
       Measures.AllMembers on ROWS
FROM [CUBE]