Search code examples
ssasmdxolap-cubessas-2008

Filtering MDX query with current year & month using Now()

How can I filter data in MDX with current Year & Month?

What I did so far is:

WHERE strtomember{ [Time Dim].[FSCL YEAR].&["+Format(now(), "yyyy")+"], [Time Dim].[FSCL MONTH].&["+Format(now(), "m")+"] }

but it is not working.

Solution

  • StrToMember is a function that takes one argument, which is a string. You seem to try to pass it a set, as you are using curly braces. The correct syntax would be:

    WHERE ( 
      strtomember("[Time Dim].[FSCL YEAR].&[" + Format(now(), "yyyy") + "]"),
      strtomember("[Time Dim].[FSCL MONTH].&[" + Format(now(), "m") + "]")
      )

    The argument to StrToMember in both cases ia a string concatenated from fix strings like "[Time Dim].[FSCL YEAR].&[" and "]", as well as the result of the Format function.