topic How to use filter in MDX expression in SAS Web Report Studio

How to use filter in MDX expression

Yu — Tue, 16 Aug 2011 06:04:53 GMT

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.

How to use filter in MDX expression

TSBruce — Tue, 16 Aug 2011 14:08:14 GMT

I would take a look at the documentation on filtering at http://support.sas.com/documentation/cdl/en/citug/61084/HTML/default/viewer.htm#p1508ri6xxyeu9n1b8w4zmffa54p.htm.

How to use filter in MDX expression

metalray — Thu, 18 Aug 2011 12:59:09 GMT

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