CODEWITHSUNDEEP
ssasmdxssas-tabularssas-2012mdx-query
TAMILARASAN R
TAMILARASAN R

Reputation: 225

Type mismatch error occurs while casting string values to date type in MDX query with some blank rows

I have a string-type dimension in my SSAS cube having the date values along with some blank values and please find the screenshot for your reference.

enter image description here

I am trying to convert these values into Date using the CDate function in the MDX query. I have used the following MDX query which will return null when the value is either empty or null and returns the date value for valid values.

WITH
MEMBER [Measures].[Company].[OrderDate] AS 'CASE WHEN ISEMPTY([Company].[OrderDate].CurrentMember.MemberValue) OR [Company].[OrderDate].CurrentMember.MemberValue = null THEN null Else CDate([Company].[OrderDate].CurrentMember.MemberValue) END' SELECT{
[Measures].[Company].[OrderDate]} ON COLUMNS ,Subset(NONEMPTY((
(Order((([Company].[OrderDate].[OrderDate].ALLMEMBERS)), CDate([Company].[OrderDate].CurrentMember.Member_Caption),ASC))),{[Measures].DefaultMember}),0,100) ON ROWS FROM [Model] CELL PROPERTIES VALUE, FORMATTED_VALUE, FORMAT_STRING

However, When am trying to convert these string values into Date I am getting the Type mismatch error as shown in the following screenshot:

enter image description here

Can anyone please suggest how to convert the string dimension values to date type when blank values are present in the dimension?

Upvotes: 4

Views: 346

Answers (0)

Related Questions