Search code examples
ssasreporting-servicesmdxreportbuilder3.0

Report Builder: How to create a filter expression on a date hierarchy for all dates before last week?

Using Report Builder 3.0 connected to a TFS 2012 analysis database, need to create a filter on a date hierarchy, to match all days older than 14 days ago (specifically for the Created Date hierarchy of the Work Item dimension).

I'm guessing I need to set the operator to MDX and type in something a DATEADD expression somewhere, but I can't figure out how to get the syntax right.

I've tried: 

[Date].[Date].membervalue <= DATEADD("d",-14,[Date].[Calendar].CurrentMember)

in the Filter Expression box, but it complains that the CURRENTMEMBER function expects a hierarchy expression and a member expression was used.

Solution

  • Instead of trying to do the filter in the dataset, I got everything in the dataset and created the filter as part of the report (under Datasets, Dataset properties, Filters, and then I was able to use the DateAdd and Now functions to get what I needed.