MDX Query in Saiku Analytics (date string to date)

Question

When I try to access the date variable in my saiku analytics in CDE Pentaho (which uses MDX query), I'm unable to use it as a date param. I'm able to access it only as a date string.

For eg:

select
    { [Measures].[CumUserCount]} ON COLUMNS,
      FILTER(
      [UserCreationDate].[UserCreationDate].[1970-01-01 00:00:00.0] :
       [UserCreationDate].[UserCreationDate].[2015-12-31 00:00:00.0], [Measures].[CumUserCount] > 100
      )ON ROWS
from [totalUsersAgg]

Instead of

select
    { [Measures].[CumUserCount]} ON COLUMNS,
      FILTER(
      [UserCreationDate].[UserCreationDate].&[19700101] :
       [UserCreationDate].[UserCreationDate].&[20151231], [Measures].[CumUserCount] > 100
      )ON ROWS
from [totalUsersAgg]

When I imported the data source, the data type of UserCreationDate is a date field (yyyy-mm-dd) and I also made it a time dimension. Yet nothing seems to work and it keeps using UserCreationDate as though its a string. I need to be able to use date drill downs in my dashboard for which date strings cannot be used.

How to change date string [Date].[2015-12-31 00:00:00.0] to date format [Date].&[20151231]?

Answer 1

[Date].[2015-12-31 00:00:00.0] and [Date].&[20151231] are two different things. When you don't use an ampersand operator &, you are calling a member by it's name property. When you do use an &, you are calling a member by it's ID(key). There might be two different attributes used for designing the key and name member or the same attribute using two different formats.

IMO, you should check out both these properties of the date members and see if the format at both places is what you want.