fetching the first sibling of every level in the hierarchy

[lalitha.nov20]

Hi,

I have a requirement where i need to fetch the first sibling/member of the every member in the hierarchy using MDX

for example:
heirarchy
- all
---country
------India
------US
------China
---State
------Delhi
------x
------y

i need to display first member of level country say India and fist member of level state sya Delhi.

output should be
India
Delhi


Datasource is a microsoft analysis server cube
Can anyone help me out.

Thank you,
Lalitha

[MFGF]

Hi,

Can you clarify - in your example is Delhi necessarily a child of India or is it just the first member on the City level regardless of whether the first member of the Country level is its parent?

How many levels do you have?

Here's one example:

union(item([Your first desired level],0), firstChild(item([Your first desired level],0)))

MF.

[lalitha.nov20]

Hi,

Thanks for the reply.

Delhi is child a first level.

Here is whole issue

i ahve report where i need to provide a input org unit which provides me the detail data of the immediatel level . say for example i provide the input india for org unit prompt, it provides me the details of all the states.

In addition to it, report has the capability to view the report at any lower level the user wants view the data.
so, there is another input prompt which gives me level details, so country is at Org Level1, level prompt would display all the level labes below org level 1.

Once the user select the levelin org level input prompt, the repoort should come up.

All the issue lies in getting the level label values in the prompt. this should be dynamic , when the org unit is at org level 2, all thye below level should be displayed in org level prompt.

Thank you,
lalitha

[CognosPaul]

Code Select Expand

generate(
    [Cube].[Dim].[Hier]
  , item(
    level(
      currentMember(
        [Cube].[Dim].[Hier]
      )
    )
  ,0
  )
)

That will get you the first member of each level. Unfortunately there doesn't seem to be a decent way of getting the level label. You can get the level unique name with:
roleValue('_levelUniqueName',currentMember([Cube].[Dim].[Hier]))

If you're using SSAS, you could set the level name as an attribute of each member, and call it the same way.

[MFGF]

Nice! :)

[lalitha.nov20]

Hi Paul,

Thank you for the reply!

The code given below is return me all the value in the level, not sure if i am missing something.

Thank you,
lalitha

[CognosPaul]

Are you referencing a level or the hierarchy? Can you post your expression?

[lalitha.nov20]

Hi,

Please find the expression I used, but it returned all the level value

generate(
   descendants([Labor].[Org Hierarchy].[Hierarchy - Org Hierarchy Tree]->?p_Org?,1,self)
  , item(
    level(
      currentMember([Labor].[Org Hierarchy].[Hierarchy - Org Hierarchy Tree]            )
    )
  ,0
  )
)

[CognosPaul]

When you say all the level value, you mean it's returning every member in the level?

In your code, you're prompting for a value, finding it's children. For each of those members, it's identifying the level and returning the first item in the set. The "All" flag isn't set, so it should be returning a single member.

What happens when you run this:

generate(
   [Labor].[Org Hierarchy].[Hierarchy - Org Hierarchy Tree]
  , item(
    level(
      currentMember([Labor].[Org Hierarchy].[Hierarchy - Org Hierarchy Tree]            )
    )
  ,0
  )
)

[lalitha.nov20]

Its still return all the members and all the levels.. Please find the attached snapshot. it includes the actual dimension and the output window as well

[CognosPaul]

What type of cube is this?

If you're using TM1, are level names enabled?

If you take a random member and do level([Member]) does it return all of the members of that level, or just itself?

If it returns all of the members of that level, what does item(level([Member]),0) return?