Traditional web-based reporting with SAS BI tools

How to use filter in MDX expression

Reply
Occasional Contributor Yu
Occasional Contributor
Posts: 5

How to use filter in MDX expression

Hi guys,

I have a simple question,  I am building an information map based on a cube.

In the cube, there is a dimension named "District Level".  In the expression tab, it's showing "<<District Level.District Level>>".  I want to apply some filter on it (with MDX).

for example, the value in the data are

0000

1000

2000

3000

4000

5000

I want to select all records between 1000 to 4000 (exclude those < 1000 and those > 4000 ie. 0000, 5000 in this case).

How should I write the MDX?  I tried lots of things but can't make it works.

Any idea is very helpful to me.  Thx in advance.

SAS Employee
Posts: 37

How to use filter in MDX expression

Regular Contributor
Posts: 207

How to use filter in MDX expression

Hi Yu,

there is not much out there on MDX in SAS so you can start looking or posting in the Microsoft boards.


Using the range function works for me but I dont know if this is what you need.

MDX Range:

SELECT
{
[DIM_PERIODE].[Hierarchy1].[All Hierarchy1].[2005] :  [DIM_PERIODE].[Hierarchy1].[All Hierarchy1].[2009]
} ON 0,
[DIM_JPI].[All DIM_JPI].Children ON 1
FROM mycue

--------------------------------


If you need a bit more complex logic like <= then I would use a filter:

http://msdn.microsoft.com/en-us/library/ms145502.aspx

Regarding the example, I did not get it to work apart from filtering with measures (SAS OLAP).
I dont even know if it works with normal dimension members.

@TSBruce, the documentation has nothing to do with MDX. Why not filter in MDX instead of loading lots of unused data into dataset

Ask a Question
Discussion stats
  • 2 replies
  • 905 views
  • 0 likes
  • 3 in conversation