Help with MDX Query. Not aggregate parent levels for a certain given level.

[Former Member]

Hi Experts,

I am trying to make a MDX which returns hierarchized members for a certain level, but not the aggregation of the parent levels.

For example, I have got a hierarchy (H for example) like this (from high level to low):

Region - City - Person

With for example this data:

LEVEL00 LEVEL01 LEVEL02

REG1 CityA Person11A

REG1 CityA Person21A

REG1 CityB Person11B

REG2 CityC Person12A

... ... ...

Well, I want a MDX that returns the aggregate of LEVEL02, but with the information of the hierarchy.

A MDX query like next returns all aggregations.

SELECT [H].members ON rows,

[Measures].members on columns

FROM $CUBE

The MDX query for obtain the desired data would be the next:

SELECT [H].[LEVEL02].members ON rows,

[Measures].members on columns

FROM $CUBE

But with this query the hierarchy of the LEVEL02 members are not returned. However, executinig the sentence with SAPGUI and the option "Run query with flattening" returns the data in the way that I want.

I have been trying with "WITH MEMBER" clause, but I could not find the solution.

Could you help me with this issue?

Many thanks in advance.

Edited by: scruz84 on Jun 29, 2011 4:52 PM

[Former Member]

I found different uses of ".properties" function with the "WITH MEMBER " clause in this link,

http://www.databasejournal.com/features/mssql/article.php/10894_3696691_2/String-Functions-The-Prope...

Sentences like this, does not work in SAP?

WITH

MEMBER Measures.CustomerAddress

AS

\[Customer].\[Customer Geography].PROPERTIES( "Address" )

When I try sentences like previous in MDXTEST transaction, I get an error,

Invalid MDX command with properties

With long test as: Message no. BRAINOLAPAPI100

Any idea?