Filter member properties in Query Designer using MDX

Question

How can I filter two member properties using MDX?

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"))

What is the issue you are facing? At a glance I see you want to get the rows where `Closed_Date` is greater than today date, is it right for your logic? — alejandro zuleta, Dec 11 '15 at 16:01
@alejandro zuleta: Exaclty, it is running quite slow and I would like to (if possible) add this filter into an MDX query instead. — krynil, Dec 12 '15 at 20:37
@whytheq: I have tried using the filter function, however it did not return any rows. I will get back to you with some code. — krynil, Dec 12 '15 at 20:41

score 1 · Answer 1 · answered Dec 11 '15 at 17:34

1

I doubt performance of the below would be satisfactory, but you can give it a shot.

SELECT SomeDim.SomeHIerarchy.MEMBER 
HAVING 
Val(SomeDim.SomeHIerarchy.CURRENTMEMBER.Properties("Opening Date")) <  Format(now(), "dd-MM-yyyy")
Val(SomeDim.SomeHIerarchy.CURRENTMEMBER.Properties("Closing Date")) > Format(now(), "dd-MM-yyyy")
ON 1,
Measures.Foo on 0
FROM [Your Cube]

answered Dec 11 '15 at 17:34

SouravA

5,147
2
24
49

Thank you! I will try this out :) – krynil Dec 12 '15 at 20:41
I managed to solve the issue by using a filter in MDX. Your code definitely helped, so thank you! – krynil Dec 16 '15 at 15:59

score 1 · Accepted Answer · answered Dec 16 '15 at 15:58

1

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'))
)

answered Dec 16 '15 at 15:58

krynil

233
2
5
18

Filter member properties in Query Designer using MDX

2 Answers2