check if level exists when using descendants

[oscarca]

Hello Cognoise,

I am trying to create a tree prompt that shows the descendants of the selection in a crosstab e.g. choose level "Shoes" in the tree prompt and the crosstab will display the children of shoes.

I have created a data item containing this prompt macro: descendants(set(#promptmany('Item';'MUN')#) that works create until you choose the lowest level, then it displays NO DATA. I understand that this is purely logical since the last level doesnt have any children. Has anyone solved this ? with some kind of level check if statement.

Best regards,
Oscar

[CognosPaul]

you can certainly count the children of an set

Code Select Expand

count(1 within set descendants(set(#promptmany('Item';'MUN')#),1)

Are you trying to suppress the members from the tree prompt?

Add a detail filter to the tree prompt query (this is the one time where I'd say it's okay):

Code Select Expand

count(1 within set descendants([Products],1))>0

When a member is expanded Cognos will append the filter to the query being sent to the cube.

[oscarca]

Hey Paul,

The suppression option in the prompt Query is a nice solution, but it restrict the user from selecting a specific member of the last level.

Will it be possible to display the member of the lowest level? i.e. if I choose member from level 1, it will display level 2 members (descendants from level 1), but when you have reached the lowest level, instead of displaying decendants it should display the specific member. Otherwise you will never be able to choose a date (2019-01-01) for exempel in the tree prompt, since its the lowest level.

I did try one of your solutions from an older post i.e. creating a second data item and placing it after the first data item in the columns of the crosstab. Ideally I would like to accomplish this without having two data items. Worst case senario I could try to non block the first data item column to hide it.

union(
   children(currentMember([C_SalesBudge].[D_Item].[Type-Family-Group]))
   ,head(
currentMember([C_SalesBudge].[D_Item].[Type-Family-Group])
   ,if(Count(1 within set Children(currentMember([C_SalesBudge].[D_Item].[Type-Family-Group])))=0)
then (1)
else (0)
)
)

Best regards,
Oscar

[CognosPaul]

So if the user selects level 1, it shows the descendants on level 2. If they select level 2, it shows itself.

Is this a ragged hierarchy?

If it's not, you could just use the descendants function referencing the level instead of a number:

descendants(set([2017],[2019-04-01]),[Cube].[Time Dim].[Time Hier].[Date]) will return all of the dates in 2017 and 2019-04-01.

[oscarca]

Exactly, so The crosstab row will always display the descendents of the member you choose in the tree prompt level until you reach the lowest level then it should display itself. So if you choose 2019 it should display all the months of 2019, if I choose 2019/Jan it should show all the dates of 2019/Jan, if I choose 2019-01-01, it should display 2019-01-01 since its in the lowest level.

The hierarchy is not ragged

[CognosPaul]

So the descendants(<SET>,<LEVEL>) should work fine for you.

[oscarca]

You are a legend Paul, Many thumbs up (Y)