0

I'm really stumped here...trying to create some year-to-date measures for a Cube, with NOW() specifying the current month and day (minus a two-day lag), with year being specified by the current member of the Year value on the hierarchy - and using STRTOMEMBER to paste together these bits of the year into a reference to a specific member of the dimension that can be cross-joined with measures to get annual value to-date.

This code works just fine when using STRTOMEMBER to specify the range...it lists every day between the first and the tenth, and the revenue for each day:

SELECT [Measures].[Revenue USD] ON COLUMNS,
{
STRTOMEMBER('[Time Dimension].[Year-Qtr-Month-Date].[Date].&[2017-01-01T00:00:00]') : 
STRTOMEMBER('[Time Dimension].[Year-Qtr-Month-Date].[Date].&[2017-01-10T00:00:00]') 
}  ON ROWS
FROM [CurrentCube]
WHERE [Time Dimension].[Year].&[2017]

But then the following does not work, it gives the error:

"The dimension '[Time Dimenson]' was not found in the cube when the string, [Time Dimenson].[Year-Qtr-Month-Date].[Date].&[2017-01-07T00:00:00], was parsed."

I realize the date doesn't quite match up with the same range, but otherwise an identical string is being constructed. It makes no sense to me that the former would work and the latter wouldn't. Tried putting single quotes around it, too, which then complains that STRTOMEMBER expects a member expression but that a string or numeric expression was used. Makes no sense to me that the first option would work but the second one doesn't. Can't find much online to explain this error message, either.

SELECT [Measures].[Revenue USD] ON COLUMNS,
{
STRTOMEMBER(FORMAT(CSTR("[Time Dimenson].[Year-Qtr-Month-Date].[Date].&[" + [Time Dimension].[Year].CURRENTMEMBER.MEMBER_CAPTION + "-" + RIGHT("0" + FORMAT(CSTR(MONTH(NOW()-5))),2) + "-"  + RIGHT("0" + FORMAT(CSTR(DAY(NOW()-5))),2) + "T00:00:00]"))) : 
STRTOMEMBER(FORMAT(CSTR("[Time Dimenson].[Year-Qtr-Month-Date].[Date].&[" + [Time Dimension].[Year].CURRENTMEMBER.MEMBER_CAPTION + "-" + RIGHT("0" + FORMAT(CSTR(MONTH(NOW()-2))),2) + "-"  + RIGHT("0" + FORMAT(CSTR(DAY(NOW()-2))),2) + "T00:00:00]"))) 
}  ON ROWS
FROM [DataInsight]
WHERE [Time Dimension].[Year].&[2017]
0

Spotted the error - it was a typo..."Time Dimenson" has the word "dimension" misspelled. The error in this case was between the keyboard and the chair.

Not the answer you're looking for? Browse other questions tagged or ask your own question.