reporting-servicesmdxssrs-2012ssrs-expressionquery-designer

Filter member properties in Query Designer using MDX

How can I filter two member properties using MDX?

enter image description here

Currently I have the following solution. It works but I'm sure an MDX query would be much more efficient.

The Dataset in Query Designer has the following MDX to include the member properties as fields:

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME, [Store].[Store].[Closed Date],[Store].[Store].[Opening Date]

I am using two filters in the Dataset Properties with the following expressions:

    =Cint(Fields!Opening_Date.Value) 
    < 
    =Cint(Format(Today(), "yyyyMMdd"))

and

    =Cint(Fields!Closed_Date.Value) 
    >
    =Cint(Format(Today(), "yyyyMMdd"))
Solution

  • I found that using a filter was the best way to solve this:

    filter(
  [Store].[Store].members, [Store].[Store].Properties( "Opening Date" ) < Format(Now(),'yyyyMMdd') 
  and [Store].[Store].Properties( "Closed Date" ) > Format(Now(),'yyyyMMdd'))
)