Skip to Content
0
Former Member
Jun 29, 2011 at 02:52 PM

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

93 Views

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