“Greater than” functions in MDX

Well its Sunday evening and i was about to start some work (I know its weird but this was nice work . .so . . .  ).
It’s just a feeling that mostly people say that MDX doesn’t have “Greater than” and “Smaller than” functions to use in WHERE clause. But I believe the main issue is that MDX doesn’t needs to have those. MDX is written on Cube where all the members have their unique identity (say address). “Greater than” functions works for values where you have to do a comparison. With measure values you can always use “>” operators. But if you want to do same but based on dimensional members (like date, age, may be ordered name of product etc.), you can only prepare a set of members which satisfy your need of members.
Anyways let’s talk about putting these functionality in MDX and its very simple, if you could just understand what I wrote on top.
To get “Greater than” functionality out of MDX we need to mention the set of members which falls under that range. So finally we are going to use Range function of MDX to get the requirement done.
Example here states, I want to get all the Subcategories with their Reseller Amounts after date 05/01/2008 (dates greater than 05/01/2008).

SELECT {[Measures].[Reseller Sales Amount]} ON COLUMNS, NON EMPTY [Product].[Subcategory].[Subcategory] ON ROWS FROM [Adventure Works] WHERE ( [Date].[Calendar].[Date].&[20080501] :[Date].[Calendar].[Date].&[20080501].LASTCHILD )

and if you want Smaller than version of it.

SELECT {[Measures].[Reseller Sales Amount]} ON COLUMNS, NON EMPTY [Product].[Subcategory].[Subcategory] ON ROWS FROM [Adventure Works] WHERE ( [Date].[Calendar].[Date].&[20080501].FIRSTCHILD :[Date].[Calendar].[Date].&[20080501])

See the result for yourself. The same result goes with all members of Cube. You can see the result for:

SELECT {[Measures].[Reseller Sales Amount]} ON COLUMNS, NON EMPTY [Product].[Subcategory].&[34].FIRSTCHILD :[Product].[Subcategory].&[34]ON ROWS FROM [Adventure Works]

To drill it more I can say it is always “Greater Than or Equals to” or “Smaller Than or Equals to”, and if you want to avoid “Equals to” part use EXCEPT function and remove it.
Mostly we have members ordered in Cube, just like DATE members and alphabetically for other dimensions. If you don’t have ordered members you will need GENERATE functions to bend those un-ordered members  to use. Best of luck and in case you have problems contact me. 🙂

Thanks a lot for reading and your thoughts, doubts, queries . . . are much appreciated in comment section.