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

ssas mdx olap-cube ssas-2008
Aditya picture Aditya · Apr 25, 2014 · Viewed 10.8k times · Source

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.

Answer

FrankPl picture FrankPl · Apr 25, 2014

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.

Related questions

MDX Column Alias for use in SQL

I'm trying to query our SSAS cubes and return the values into a SQL view for use as parameters in cube based reports. I need to return 3 values for financial seasons based on the date hierarchy. This is the below …

Read More
Create member with description in MDX

This is a pretty straight forward question that may or may not be possible but, when you create a measure in a SSAS 2005 cube, there is a description property which can be set, which we've been using in the client …

Read More
SSAS MDX WHERE clause syntax - filtering multiple values from the same hierarchy

I'm quite new to data warehousing and MDX queries, and I realize that I'm probably not using the MDX syntax properly in the case below. I'm using SSAS 2005 on MS SQL Server 2005 and would like to query my cube and …

Read More