Author Topic: check if level exists when using descendants  (Read 175 times)

Offline oscarca

  • Community Leader
  • *****
  • Join Date: Jul 2018
  • Posts: 159
  • Forum Citizenship: +0/-0
check if level exists when using descendants
« on: 11 Jun 2019 10:02:17 am »
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

Offline CognosPaul

  • Global Moderator
  • Statesman
  • *****
  • Join Date: Jan 2009
  • Posts: 1,809
  • Forum Citizenship: +261/-1
    • Paul's Cognos Blog
Re: check if level exists when using descendants
« Reply #1 on: 11 Jun 2019 07:07:11 pm »
you can certainly count the children of an set

Code: [Select]
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]
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.

Offline oscarca

  • Community Leader
  • *****
  • Join Date: Jul 2018
  • Posts: 159
  • Forum Citizenship: +0/-0
Re: check if level exists when using descendants
« Reply #2 on: 12 Jun 2019 03:24:23 am »
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
« Last Edit: 12 Jun 2019 04:07:28 am by oscarca »

Offline CognosPaul

  • Global Moderator
  • Statesman
  • *****
  • Join Date: Jan 2009
  • Posts: 1,809
  • Forum Citizenship: +261/-1
    • Paul's Cognos Blog
Re: check if level exists when using descendants
« Reply #3 on: 12 Jun 2019 11:31:59 am »
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.

Offline oscarca

  • Community Leader
  • *****
  • Join Date: Jul 2018
  • Posts: 159
  • Forum Citizenship: +0/-0
Re: check if level exists when using descendants
« Reply #4 on: 12 Jun 2019 02:18:50 pm »
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

Offline CognosPaul

  • Global Moderator
  • Statesman
  • *****
  • Join Date: Jan 2009
  • Posts: 1,809
  • Forum Citizenship: +261/-1
    • Paul's Cognos Blog
Re: check if level exists when using descendants
« Reply #5 on: 13 Jun 2019 04:31:14 pm »
So the descendants(<SET>,<LEVEL>) should work fine for you.

Offline oscarca

  • Community Leader
  • *****
  • Join Date: Jul 2018
  • Posts: 159
  • Forum Citizenship: +0/-0
Re: check if level exists when using descendants
« Reply #6 on: 14 Jun 2019 06:29:59 am »
You are a legend Paul, Many thumbs up (Y)

 



       
Twittear