CODEWITHSUNDEEP
mdxolap
Alejandro Carrillo
Alejandro Carrillo

Reputation: 25

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

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:

CUBE:

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:

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?

Upvotes: 0

Views: 4211

Answers (1)

FrankPl
FrankPl

Reputation: 591

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]

Upvotes: 1

Related Questions