Need guidance on writing an MDX Filter Expression in the SSAS Cube Browser

Question

Hi,

I've been away from SSAS 2005 for over a year now and I have forgotten so many things. Now, as I am trying to get back into it, I am having a hard time doing something that I thought was pretty simple. 

I would like to write an expression that filters my data based on the follwing: return only those where the StartDate is between 7/1/2008 and 9/1/2008 OR where the EndDate 7/1/2008 and 9/1/2008.  Another possibility with this is to enter where the StartDate is greater than 7/1/2008.

For now, I just want to enter that in an MDX Filter expression in the Cube browser.

Any suggestions on how I can write an expression to do this, would be greatly appreciated. I imagine once I see that, I can get my head back into this great stuff.

Thank you.

Answer 1

You can use either of two methods to add a filter:

·     Expand a dimension in the Metadata pane, and then drag a hierarchy to the Filter pane.
- or -

·     In the Dimension column of the Filter pane, click <Select dimension> and select a dimension from the list, then click <Select hierarchy> in the Hierarchy column and select a hierarchy from the list.

After you specify the hierarchy, specify the operator and filter expression. The following table describes the operators and filter expressions.

Operator	Filter Expression	Description
Equal	One or more members	Values must be equal to a specified member. (Provides multiple member selection for attribute hierarchies, other than parent-child hierarchies, and single member selection for other hierarchies.)
Not Equal	One or more members	Values must not equal a specified member. (Provides multiple member selection for attribute hierarchies, other than parent-child hierarchies, and single member selection for other hierarchies.)
In	One or more named sets	Values must be in a specified named set. (Supported for attribute hierarchies only.)
Not In	One or more named sets	Values must not be in a specified named set. (Supported for attribute hierarchies only.)
Range (Inclusive)	One or two delimiting members of a range	Values must be between or equal to the delimiting members. If the delimiting members are equal or only one member is specified, no range is imposed and all values are permitted. (Supported only for attribute hierarchies. The range must be on one level of a hierarchy. Unbounded ranges are not currently supported.)
Range (Exclusive)	One or two delimiting members of a range	Values must be between the delimiting members. If the delimiting members are the equal or only one member is specified, values must be either greater than or less than the delimiting member. (Supported only for attribute hierarchies. The range must be on one level of a hierarchy. Unbounded ranges are not currently supported.)
MDX	An MDX expression returning a member set	Values must be in the calculated member set. Selecting this option displays the Calculated Member Builder dialog box for creating MDX expressions.

For user-defined hierarchies, in which multiple members may be specified in the filter expression, all the specified members must be at the same level and share the same parent. This restriction does not apply for parent-child hierarchies.


For more go to http://msdn.microsoft.com/en-us/library/ms175675(SQL.90).aspx

---

Ashwani Roy

My Blog

Please click the Mark as Answer button if a post solves your problem!

Answer 2

Ashwani,

Thank you for the information. However, using the operators, I could not see how I would write an OR statement, only using the MDX filter.

Do you have an example of how I could write something like:
         Where StartDate is Between 7/1/2008 and 9/1/2008 OR EndDate Between 7/1/2008 and 9/1/2008 

I really appreciate the help.

Answer 3

MDX works on Set theory. Remember Venn diagrams in set theory...... if I have a SET A and SET B and I need to find out SET{A OR B} I need to do this

SET{A OR B} = SET{A} + SET{ B} - SET{A and B}.

Ok so now that I have a set theory formula I will try to model an MDX based on this...

WITH Member Measures.[A] As---SET A
([Ship Date].[Month of Year].&[1],[Measures].[Internet Order Count])

member Measures.[B] AS--SET B
([Delivery Date].[Month of Year].&[1],[Measures].[Internet Order Count])

member Measures.[C] AS --SUM(A,B)
Measures.[A]+Measures.[B] 

member Measures.[D] AS --SUM(A,B)-(TOTAL of A and B)
Measures.[C]
-
([Ship Date].[Month of Year].&[1],
[Delivery Date].[Month of Year].&[1],
[Measures].[Internet Order Count])

---Query to display data
Select non empty ({Measures.[A],Measures.[B],Measures.[C],Measures.[D],[Measures].[Internet Order Count]} )on columns, 
[Customer].[Customer Geography].[Country].allmembers on rows
from [Adventure Works]

But the only way you can do this by writing your own MDX as I did but it works

---

Ashwani Roy

My Blog

Please click the Mark as Answer button if a post solves your problem!

Answer 4

Ashwani,

Thank you for the detailed answer. It was exactly what I was looking for.

I really appreciate you taking the time to write that out for me, I would have taken me a long time to refresh myself with MDX if you hadn't provided this example.

-Gumbatman

Answer 5

Now that I finally got into this, I am confused again (no surprise there)...

If I am going to have a statement such as: Where StartDate is Between 7/1/2008 and 9/1/2008 OR EndDate Between 7/1/2008 and 9/1/2008, I first need to select the Dimension, then the Hierarchy and the Operator which this MDX statement is going to apply to.

When I select the Dimension, since I can only select a single dimension, how does this MDX statement which has an OR in it, apply back to the Dimension selected?

For example, I select the Dimension attribute StartDate, if my MDX statement contains a StartDate range OR an EndDate range, aren't the results only going to apply to StartDate - which is what was selected?

If I use Ashwani's example as the Where statement when writing out the entire MDX, that works fine, but how does it get applied in SSAS's Browser and Filter Expression?

Thank you.

Answer 6

MDX Builder Dialog Box (Analysis Services – Multidimensional Data)

Please help me in writing code in MDX builder

when ever I try some code like

(COUNT({[Measures].[weight_Avg],[Dim patient].[patient].children},excludeempty))>0

I get the following error

The Axis0 function expects a tuple set expression for the argument. A string or numeric expression was used. (Microsoft SQL Server 2008 R2 Analysis Services)