multidimensional expressions (MDX) calculation error in SSAS Cube

Question

I just got a project handover regarding SSAS cubes and multidimensional expressions. There is a calculation written in Visual Studio:

(StrToMember(
"[DIM Time_V3].[Year -  Week -  Date].[Month].&["+
MID(MemberToStr([DIM Time_V3].[Year - Month - Day].CurrentMember)
,44,11)+"T00:00:00]"),[Plan Revenue])

This formula is not working with unknown reasons (The excel pivot table showing #VALUE! under this formula).

I converted the above formula into MDX Query in SSMS:

SELECT 
[Measures].[Plan Revenue] ON COLUMNS,
StrToMember(
              "[DIM Time_V3].[Year -  Week -  Date].[Month].&[" 
              + MID(MemberToStr([DIM Time_V3].[Year - Month - Day].CurrentMember),44,11) 
              + "T00:00:00]"
            ) ON ROWS
FROM [FR_ACCOUNTING 3]

There is an error says "The 'T00:00:00' string cannot be converted to the date type". I then tried following two query scripts:

SELECT  [Measures].[Plan Revenue] ON COLUMNS,
        [DIM Time_V3].[Year -  Week -  Date].[Month] ON ROWS
FROM [FR_ACCOUNTING 3]

Here is a random member of above query result:

Caption Manufacturing Period 1, 2023
Name    [DIM TIME_V3].[Year -  Week -  Date].[Month].&[2023-01-03T00:00:00]
UniqueName  [DIM TIME_V3].[Year -  Week -  Date].[Month].&[2023-01-03T00:00:00]
Description 
LevelName   [DIM TIME_V3].[Year -  Week -  Date].[Month]
LevelDepth  3
DrilledDown False
ParentSameAsPrevious    False
ChildCount  4

SELECT  [Measures].[Plan Revenue] ON COLUMNS,
        [DIM Time_V3].[Year - Month - Day].[Manufacturing Month] ON ROWS
FROM [FR_ACCOUNTING 3]

And here is a random member of above query result:

Caption Manufacturing Period 1, 2023
Name    [DIM TIME_V3].[Year - Month - Day].[Manufacturing Month].&[2023-01-03T00:00:00]
UniqueName  [DIM TIME_V3].[Year - Month - Day].[Manufacturing Month].&[2023-01-03T00:00:00]
Description 
LevelName   [DIM TIME_V3].[Year - Month - Day].[Manufacturing Month]
LevelDepth  2
DrilledDown False
ParentSameAsPrevious    False
ChildCount  28

I also tried following code to see if hardcoding instead of MID function can return something:

SELECT 
[Measures].[Plan Revenue] ON COLUMNS,
StrToMember(
              "[DIM Time_V3].[Year -  Week -  Date].[Month].&["
              + "2023-01-03"
              + "T00:00:00]"
            ) ON ROWS
FROM [FR_ACCOUNTING 3]

The query successfully returned a value.

Finally I tried following code:

--Check if MID function works for manufacturing calendar
SELECT 
[Measures].[Plan Revenue] ON COLUMNS,
StrToMember(
              "[DIM Time_V3].[Year -  Week -  Date].[Month].&[" 
              + MID(MemberToStr([DIM Time_V3].[Year - Month - Day].CurrentMember),44,11) 
              + "]"
            ) ON ROWS
FROM [FR_ACCOUNTING 3]

The result says "The '' string cannot be converted to the date type".

In conclusion, the reason why error occurs is that the MID function did not extract the correct date format (yyyy-mm-dd) from a member, but I do not know how to solve this issue.

I am so curious how does MID function work and how do we extract a substring of a member. So much appreciated for solving my problem.

Answer 1

This is one horrific way of finding the month member in the YWD hierarchy corresponding to the current position along the YMD hierarchy. Such operations should utilize attribute relationships and EXISTS.