1

The 1st 9 calculated members in my script below define start and end date strings (e.g. 'StartDateStr' and 'EndDateStr'), that I then parse into a StrToMember() function within my SELECT command, but it throws the following error:

  • Query (27, 22) The Dimension '[StartDateStr]' was not found in the cube when the string, [StartDateStr], was parsed.

These calculated members resolve as follows:

  • StartDateStr = [Date].[Day].[Day].&[2020-07-22T00:00:00]
  • EndDateStr = [Date].[Day].[Day].&[2021-07-20T00:00:00]

If I place these strings directly in my SELECT command (which compromises the point of them being dynamic parameters), the query works fine.

I've tried parsing the calculated members into my SELECT command without the StrToMember() function also, but it throws the same error. I've also tried renaming my calculated members (e.g. '[Date].[Day].[Day].StartDateStr') and only parsing the variable (member) portion of the dimension, hierarchy, and level reference.

Can I not use the StrToMember() function this way, or parse calculated members like this, or does my script have some other syntactic issue I'm missing? I've hit a bit of a dead-end after an extensive Google search, so would greatly appreciate any help.

Thanks

WITH
    MEMBER TodayStr AS FORMAT(NOW(),'yyyy-MM-dd')
    MEMBER TodayDate AS CDATE(CSTR(LEFT(TodayStr,4)+'-'+LEFT(RIGHT(TodayStr,5),2)+'-'+RIGHT(TodayStr,2)))
    MEMBER No1 AS WEEKDAY(TodayDate,1) + 3
    MEMBER No2 AS 7
    MEMBER Mod AS No1 - INT(No1 / No2) * No2
    MEMBER EndDate AS IIF(WEEKDAY(TodayDate) = 4,TodayDate - Mod - 8,TodayDate - Mod - 1)
    MEMBER EndDateStr AS '[Date].[Day].[Day].&['+RIGHT(LEFT(EndDate,10),4)+'-'+RIGHT(LEFT(EndDate,5),2)+'-'+LEFT(EndDate,2)+'T00:00:00]'
    MEMBER StartDate AS EndDate - (7 * 52) + 1
    MEMBER StartDateStr AS '[Date].[Day].[Day].&['+RIGHT(LEFT(StartDate,10),4)+'-'+RIGHT(LEFT(StartDate,5),2)+'-'+LEFT(StartDate,2)+'T00:00:00]'
    MEMBER [Measures].[FilteredHrs] AS
        IIF([Measures].[Roster Actual Sum Hours Nett] = 24, [Measures].[Roster Actual Sum Hours Nett], NULL)
SELECT 
    NON EMPTY 
        {[Measures].[FilteredHrs]} ON COLUMNS,
    NON EMPTY 
        {
                [Staff].[StaffNumber].[StaffNumber].ALLMEMBERS*
                [Date].[Roster Week].[Roster Week].ALLMEMBERS*
                [Date].[Day].[Day].ALLMEMBERS*
                [Pay Type].[Pay Type].[Pay Type].ALLMEMBERS
        }
     ON ROWS
FROM 
(
    SELECT
        {STRTOMEMBER(StartDateStr):STRTOMEMBER(EndDateStr)} ON COLUMNS
    FROM [Model]
)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.