How to display only the levels or level names of hierarchy in a tree prompt

[xyz]

Hi Gurus,

I have Org_Hiearchy_Tree, which is a unbalanced and ragged hierarchy, Org_Hierarchy_Tree have levels like level1, level2, level3, level4, level5, level 6 and Org_Hierarchy. My requirement is I have to use the Org_Hierarchy_Tree hierarchy in the tree prompt and only have to display only the levels or level names not the members. Data source is SSAS cube.


Thanks & Regards,
xyz

[xyz]

Paul or Anyone,

Can you please reply on the same, your help will be much appreciated :)


Thanks & Regards,
xyz

[MFGF]

Good luck with that requirement. I think you're going to be rather disappointed. :)

MF.

[CognosPaul]

A tree prompt is designed to show individual members in a tree. Years - 2010 - Q1 2010 and so on. What your describing makes it sound like you want to replace the caption of each of the members with the level name. So you'd get:

Time
-Years
--Quarters
---Months
---Months
---Months
--Quarters
---Months
---Months
---Months


It's very possible that I'm misunderstanding of course.

Instead, you may want to use a different prompt control. You can set up a value prompt with static choices to allow the user to select a specific level.

For example a value prompt with parameter name LevelSelect:
Display  | Use
Years    | [sales_and_marketing].[Time].[Time].[Years]

Then you would use #prompt('LevelSelect','token')#

If that's not what you need, try drawing a mockup and maybe I'll understand more.

[xyz]

Thanks Paul and MF for the replies.

Actual requirement is report is having two prompts on the same hierarchy i.e. [Org_Hierarchy_Tree]. Where as [Org_Hierarchy_Tree] hierarchy have levels like Org level1, Org level2, Org level3, Org level4, Org level5, Org level 6 and Org_Hierarchy, overall seven levels in it. First prompt they are calling it as 'Org Unit', in which they want to display only levels from Org level 1 to Org level 6 and in second prompt, which they call it is as 'Org Level', it also should display levels below selected level in first prompt i.e. 'Org Unit'. Please find the attached screenshot, which explains, possible ways of selections.

Say suppose in 'Org Unit'

   Org Unit      Org Level
   Org Level 1      
X   Org Level 2      
   Org Level 3Org Level 3
   Org Level 4      Org Level 4
   Org Level 5      Org Level 5
   Org Level 6      Org Level 6
         Org Hierarchy

[xyz]

Sorry my mistake, before completing the below topic, it got posted.

Thanks Paul and MF for the replies.

Actual requirement is report is having two prompts on the same hierarchy i.e. [Org_Hierarchy_Tree]. Where as [Org_Hierarchy_Tree] hierarchy have levels like Org level1, Org level2, Org level3, Org level4, Org level5, Org level 6 and Org_Hierarchy, overall seven levels in it. First prompt they are calling it as 'Org Unit', in which they want to display only levels from Org level 1 to Org level 6 and in second prompt, which they call it is as 'Org Level', it also should display levels below selected level in first prompt i.e. 'Org Unit'. Please find the attached screenshot, which explains, possible ways of selections.

Say suppose in 'Org Unit' first prompt below, If I am choosing value 'Org Level 2' then 'Org Level' second prompt should start from 'Org Level 3' only. Subsequently he can choose Org Level 4 or Org Level 5 or Org Level 6 in 'Org Level' prompt.

    Org Unit        Org Level
   Org Level 1     
X Org Level 2     
   Org Level 3  XOrg Level 3
   Org Level 4    Org Level 4
   Org Level 5    Org Level 5
   Org Level 6    Org Level 6

When I had a call with the onsite counterpart to understand the requirement they explained me saying, 'Org Level' they don't want to see any members, they want only the levels display in 'Org Level' and said to me 'Org Hierarchy' which is a level in [Org_Hierarchy_Tree] hierarchy contains members info, which they don't want to see.

Please help me how to achieve the same above. I think I have explained, what I know, if still the requirement is not clear, please let me know, I will let you know, how I will explain much in detail.


Thanks & Regards,
xyz

[CognosPaul]

It sounds like two value prompts may be the way to go. Can you describe how they want to use them? An example of the final output?

[xyz]

Hi Paul,

Whatever level we select in 'Org Level' the members of that, we have to display in the list report. It's not mandatory to use tree prompt, we can use value prompt as well for the prompts.

Example: Say suppose in 'Org Level' prompt Level 2 is selected, consider Level 2 is for Region information, then in the list report, I have to show all the 'Regions'.

Thanks & Regards,
xyz

[xyz]

Paul or Anyone,

Can you please reply on the same, your help will be much appreciated, I am eagerly waiting for reply.


Thanks & Regards,
xyz

[CognosPaul]

xyz, you can't expect me to answer immediately. Unless I'm on call, a lag of a day is not unreasonable to expect an answer from me.

You can use variables to show/hide static choices in the prompt. You may want to play around with that for the second prompt.

I'm still not 100% certain the purpose of the two prompts. The users want to see all members between the two selected levels?

Set up two value prompts (with params FromLevel and ToLevel) as I suggested earlier:

Display  | Use
Level1   | [Cube].[Time].[Time].[Level 1]
Level2   | [Cube].[Time].[Time].[Level 2]
Level3   | [Cube].[Time].[Time].[Level 3]
Level4   | [Cube].[Time].[Time].[Level 4]

In your query you can do the following:

descendants(
    #prompt('FromLevel','token')#
  , #prompt('ToLevel','token')#
  , self beforewithmember
)

[xyz]

Thank you very much for the reply, Paul.

But my requirement is not to display the members between two prompts.

India  - Level 1 ( Country)
-Karnataka - Level 2 (State)
--Bangalore - Level 3 (City)
--Mangalore - Level 3 (City)
-AP - Level 2 (State)
--Hyderabad - Level 3 (City)
--Vijayawada - Level 3 (City)

See above for the sample data, say suppose, in first prompt If, I choose 'India', which is Country category, then in the second prompt, I should get the 'State' and 'City' only once, it should not repeat as there are two member under Level 2 for 'State' and four members under Level 3 for 'City'.

So, the prompt selection should look like below

   Org Unit           Org Level
X India - Level 1     
                           State - Level 2
                           City - Level 3

In 'Org Level' prompt, If I choose 'State' then in the report, I will get 'Karnataka' and 'AP'.

Hope, you can understand the requirement better now.

Thanks & Regards,
Abdul
                 

[CognosPaul]

I think I get it.

So there are two prompts, a tree and a value. The user selects a value from the tree and the value prompt allows him to select any level below it.

The descendants function will do that:

descendants(
    #prompt('TreePrompt','mun')#
  , #prompt('ToLevel','token')#
)

The only problem is limiting the value prompt to only levels below what was selected. I'll need to think a bit

[xyz]

Thank you very much for the help Paul.

I tried like the first prompt as a tree prompt and the second prompt as value prompt ,
Second prompt I tried to use it as a cascaded prompt for the first prompt and in between I am using reprompt button to refresh the second prompt. By using ordinal function I am finding the level selected in the first prompt and then restricting the second prompt query to show only the levels below first prompt, but the problem is if I use descendants function to get all the descendants of first prompt, then I can't use roleValue function to get the level name information of the descendant members in second prompt. If I get level information also then for each member, level names will be repeated, which I want only unique level names.

So, basically if I use descendants function, how will I get the level names information using roleValue.

Example: roleValue('levelLabel', [Org_hierarchy_tree])

The above example give level name information for each member in the hierarchy, which will have duplicates, how will I get the unique level names.

Can you please help me, how to code roleValue function with combination of descendants to get decendants of first prompt with unique level names.

Thanks & Regards,
xyz

[xyz]

Can Paul or any one please reply on the same. It is urgent and high priority one.

Say suppose we have data like below

India - Level 1
- Karnataka  - Level 2
-- Bangalore - Level 3
--- X -  Level 4
--- Y  -  Level 4
--- Z - Level 4
-- Mangalore - Level 3
--- G - Level 4
--- E - Level 4
--- F - Level 4
- AP - Level 2
-- Hyderabad - Level 3
--- A - Level 4
--- B - Level 4
-- Vijayawada - Level 3
-- D - Level 4

Say suppose , if I select 'India' in first prompt i.e. (prompt1) then in the second prompt I.e. (prompt2),
I have to get Level 2, Level 3 and Level 4 level names in the second prompt.

Using descendants(#prompt('prompt1', 'mum'), 1, self after)
Function I will get all the members of 'India' in the second prompt.

But whereas I have to get the level names instead of members information in second prompt.

To get the level names, by using roleValue function, we can get level names but it needs hierarchy

roleValue('_levelLabel', [Org_Hierarchy_Tree])

The above function will give level name for each member of the hierarchy, with duplicate level names.
How will I get unique level names?

So basically using descendants and roleValue function combination, how will I write the piece of code to get the desired output in the second prompt I.e. 'Org Level' prompt.

Org Unit          Org Level
India
                      Level 2
                      Level 3
                      Level 4

If I select 'Level 2' from the second prompt i.e. 'Org Level' then in the list report, I will see 'Karnataka' and 'AP', similarly if 'Level 3' is selected in the second prompt then in the list output I will see
'Bangalore', 'Mangalore', 'Hyderabad' and 'Vijayawada'. And similarly if 'Level4' is selected in the second prompt then the list report output will be 'X', 'Y', 'Z', 'G', 'E', 'F', 'A', 'B' and 'D'.

Thanks & Regards,
xyz

[xyz]

Hi All,

I found the solution for displaying unique levels in second prompt, where the levels in second prompt should always be lesser than first prompt, I have created a data item called children with the below expression first, so that it will give me all the members below selected member from first prompt.

descendants(#prompt('prompt1', 'mun')#, 1, self after)

and then on top of it, I created one more dataitem LvlName, in which I have used the expression like below.

roleValue('_levelLabel', [children])

Above expression gave me all the level names of all the members, here I was facing the issue like level names were repeating as there are mulitple members in the same level, but whereas I have to display only unique level name.

To get the unique level name, I created one more dataitme called unqlvlname, where I used case statement like below

Case
When [LvlName] = 'Level 2'
Then 'Level 3'
When [LvlName] = 'Level 3'
Then 'Level 3'
When [LvlName] = 'Level 4'
Then 'Level 4'
End
.
.
etc

Then I am using [unqlvlname] dataitem in the second prompt as use value and display value. So that it will give always unique levels below selected level in first prompt.

Please find the sample data below.

India - Level 1
- Karnataka  - Level 2
-- Bangalore - Level 3
--- X -  Level 4
--- Y  -  Level 4
--- Z - Level 4
-- Mangalore - Level 3
--- G - Level 4
--- E - Level 4
--- F - Level 4
- AP - Level 2
-- Hyderabad - Level 3
--- A - Level 4
--- B - Level 4
-- Vijayawada - Level 3
-- D - Level 4

'Org Unit'          'Org Level'
India
                      Level 2
                      Level 3
                      Level 4

Now, I am facing the issue like, How will I display selected level members in the list report.

Basically, If I select 'Level 2' from 'Org Level' prompt then in the report, I have to display the members of Level 2 like 'Karnataka' and 'AP'. Similarly if 'Level 3' is selected in the second prompt then in the list output I will see 'Bangalore', 'Mangalore', 'Hyderabad' and 'Vijayawada'. And similarly if 'Level4' is selected in the second prompt then the list report output will be 'X', 'Y', 'Z', 'G', 'E', 'F', 'A', 'B' and 'D'.

Using normal case statement, nested case statement with descendants combination, I am unable to achieve the same.

Please see my sample code below, which I have used.

Case
When [unqlvlname] = 'Level 2'
Then descendants(#prompt('prompt1', 'mun')#, 1, self)
When [unqlvlname] = 'Level 3'
Then descendants(#prompt('prompt1', 'mun')#, 2, self)
When [unqlvlname] = 'Level 4'
Then descendants(#prompt('prompt1', 'mun')#, 3, self)
End

Please help me on the same.


Thanks & Regards,
xyz

[xyz]

Some one or Paul, can you please give me the reply on the below email. I am eagerly waiting for the same.

Creating a data item with below expression output is not displaying as expected, but the problem is 'TreePrompt' has a default selection and 'ToLevel' prompt has a first value selected by default (using Java script, we selected the first value in 'ToLevel' prompt by default)

descendants(
    #prompt('TreePrompt','mun')#
  , #prompt('ToLevel','token')#
)

Example:

India - Level 1
- Karnataka  - Level 2
-- Bangalore - Level 3
--- X -  Level 4
--- Y  -  Level 4
--- Z - Level 4
-- Mangalore - Level 3
--- G - Level 4
--- E - Level 4
--- F - Level 4
- AP - Level 2
-- Hyderabad - Level 3
--- A - Level 4
--- B - Level 4
-- Vijayawada - Level 3
-- D - Level 4

'Org Unit'          'Org Level'
xIndia
                     xLevel 2
                      Level 3
                      Level 4

For 'Org Level' prompt 'display value' I am displaying level names and 'use value', I am using the numbers. If display value is 'Level 2' then use value is '2'. Default selection 'India' for 'Org Unit' tree promt and 'Level 2' for 'Org Level' prompt.

Using the above expression with above data, result should be 'Karnataka' and 'AP' in the list report. But that is not displaying, if I choose 'Level 3'  and 'Level 4', those level members are getting displayed in list report.

Your help would be much appreciated.

Thanks & Regards,
xyz

[CognosPaul]

In what way isn't it working? Are you getting an error?

In my tests, this worked perfectly.
Open the following in RS and see if this works.

Code Select Expand

<report xmlns="http://developer.cognos.com/schemas/report/8.0/" useStyleVersion="10" expressionLocale="en">
<modelPath>/content/folder[@name='Samples']/package[@name='sales and marketing']/model[@name='2013-05-02T10:57:02.761Z']</modelPath>

<queries>

<query name="Query2"><source><model/></source><selection><dataItem name="Products"><expression>descendants(
  #prompt('Products','mun','[sales and marketing].[Products].[Products].[Products]-&gt;:[PC].[@MEMBER].[Products]')#
  , #prompt('Level','token','[sales and marketing].[Products].[Products].[Product line]')#
)</expression></dataItem></selection></query><query name="Products"><source><model/></source><selection><dataItem name="Products" aggregate="none"><expression>rootMembers([sales and marketing].[Products].[Products])</expression></dataItem></selection></query></queries>
<layouts>
<layout>
<reportPages>
<page name="Page1"><style><defaultStyles><defaultStyle refStyle="pg"/></defaultStyles></style>
<pageBody><style><defaultStyles><defaultStyle refStyle="pb"/></defaultStyles></style>
<contents>

<selectWithTree parameter="Products" refQuery="Products"><selectWithTreeItem refDataItem="Products"/></selectWithTree><selectValue parameter="Level"><selectOptions><selectOption useValue="[sales and marketing].[Products].[Products].[Product line]"><displayValue>Product Line</displayValue></selectOption><selectOption useValue="[sales and marketing].[Products].[Products].[Product type]"><displayValue>Product Type</displayValue></selectOption><selectOption useValue="[sales and marketing].[Products].[Products].[Product]"><displayValue>Product</displayValue></selectOption></selectOptions><defaultSelections><defaultSimpleSelection>[sales and marketing].[Products].[Products].[Product line]</defaultSimpleSelection></defaultSelections></selectValue><promptButton type="reprompt">
<contents/>
<style>
<defaultStyles>
<defaultStyle refStyle="bp"/>
</defaultStyles>
</style>
</promptButton><crosstab name="Crosstab1" refQuery="Query2" repeatEveryPage="true" pageBreakText="false">
<crosstabCorner>
<contents/>
<style>
<defaultStyles>
<defaultStyle refStyle="xm"/>
</defaultStyles>
</style>
</crosstabCorner>


<noDataHandler>
<contents>
<block>
<contents>
<textItem>
<dataSource>
<staticValue>No Data Available</staticValue>
</dataSource>
<style>
<CSS value="padding:10px 18px;"/>
</style>
</textItem>
</contents>
</block>
</contents>
</noDataHandler>
<style>
<CSS value="border-collapse:collapse"/>
<defaultStyles>
<defaultStyle refStyle="xt"/>
</defaultStyles>
</style>
<crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell><crosstabRows><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Products" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows></crosstab></contents>
</pageBody>


</page>
</reportPages>
</layout>
</layouts>
<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="true" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/><XMLAttribute name="RS_modelModificationTime" value="2013-05-02T10:57:02.807Z" output="no"/></XMLAttributes><drillBehavior drillUpDown="true" modelBasedDrillThru="true"/></report>

[xyz]

Hi Paul,

Thanks for the reply, your help is really appreciated, it saved me lot of time.

Actually the issue was, when I was using the below expression in dataitem, only to get the selected level members in the list report, the use value, I am passing from 'Org Level', was 3, 4, 5 etc. But the below expression descendants, expects whatever value selected for 'TreePrompt' in the run time, it considers the level to start from 0 and then subsequent childrens with 1, 2 3 etc. So, finally I made the 'Org Level' prompt to dynamcially pass the values starts with 1 and then 2,3 etc.

descendants(
    #prompt('TreePrompt','mun')#
  , #prompt('ToLevel','token')#
)

So, finally it worked and working as expected.

Paul you are cool man.


Thanks & Regards,
xyz

[misap]

Hi,
The issue is with selecting the default values dynamically with the user login. For instance, a user has access to only 'Camping Equipment', when he logs in – he is able to view only 'Camping Equipement' and it's related children. I am looking for a solution to select 'Camping Equipment' and it's children as default values. Similarly, when the 'Mountaineering Equipment' user logs in – he should be able to see the 'Mountaineering Equipment' and it's related children to be selected as default values.

Further, my model is dimensional though.

I know this is possible via javascript and have tried few but no success.
Any help will be truly appreciated.



Best Regards
Manish

[CognosPaul]

The normal way of accomplishing that type of dynamic default is by creating an invisible prompt page, and setting it there.

First create the query that will return only the members that should be selected. Easiest way is to use rootMember([Cube].[Dimension].[Secured Hierarchy]). By using the rootMember function, you avoid possible issues that may occur if security is applied on different levels.

The next step is to use that query as the source of a prompt (value prompt is easiest).  After that you simply use JavaScript to select the entries in the prompt, and send the "next" navigation command to the page. Which version of Cognos are you using?

[misap]

Hi Paul,
Thanks a lot for your response. I really appreciate it. The version am using is 10.2.1. I can't switch to value Prompt, the model is dimensional and is a business requirement.Also,honestly am not a javascript expert. I am just doing trial and error method with the javascript from the existing prompt api samples from the 10.2.1 version. could you share your thoughts on creating the hidden promot page, but feeding it to a tree prompt? Also, would you be able to share the script to set the default value for tree prompt? your help is truly appreciated, Paul.

------below is the value prompt javascript that I am trying to make it work with tree prompt------
<script type= text/javascript>

//<!–

function getPromptValue ( pName )

{

//get report

var oCR = cognos.Report.getReport("_THIS_");

//get prompt control

var oc = oCR.prompt.getControlByName(pName);

var promptValues = oc.getValues(true);

var v=new Array();

var j=0;

//loop through all prompt value, select those meet criteria

for (var i = 0; i < promptValues.length; i++)

{//check whether country code value is less than 3000

if ( promptValues.use <3000)

{

v [j]= { 'use': promptValues.use , 'display': promptValues.display }

j=j+1

}

}

oc.setValues(v);

}

getPromptValue( "countries");

//–>

</script>

[CognosPaul]

The JavaScript would go onto a hidden prompt page, meaning the business requirement for a tree prompt is meaningless.
Basically:

Code Select Expand


<script>
var paulScripts = {},  oCR = cognos.Report.getReport( "_THIS_" );

paulScripts.getControl = function (promptName) {
  return oCR.prompt.getControlByName(promptName);
}

paulScripts.getControl('myPrompt').setValues(paulScripts.getControl('myPrompt').getValues(true));
</script>


See xml:

Code Select Expand

<report xmlns="http://developer.cognos.com/schemas/report/11.0/" useStyleVersion="10" expressionLocale="en-us">
<modelPath>/content/folder[@name=&apos;Samples&apos;]/folder[@name=&apos;Cubes&apos;]/package[@name=&apos;Sales and Marketing (cube)&apos;]/model[@name=&apos;2008-07-25T15:28:38.072Z&apos;]</modelPath>
<drillBehavior/>
<layouts>
<layout>
<reportPages>

<page name="Page2">
<pageBody>
<contents><textItem><dataSource><reportExpression>ParamDisplayValue(&apos;Parameter1&apos;)</reportExpression></dataSource></textItem></contents>
<style>
<defaultStyles>
<defaultStyle refStyle="pb"/>
</defaultStyles>
</style>
</pageBody>
<style>
<defaultStyles>
<defaultStyle refStyle="pg"/>
</defaultStyles>
</style>
</page></reportPages>
<promptPages><page name="Prompt Page1">

<pageBody>
<contents><selectValue parameter="Parameter1" refQuery="Time" multiSelect="true" selectValueUI="listBox" name="myPrompt"><useItem refDataItem="Time"/><style><CSS value="visibility:hidden"/></style></selectValue><HTMLItem>
<dataSource>
<staticValue>&lt;script&gt;
var paulScripts = {},  oCR = cognos.Report.getReport( &quot;_THIS_&quot; );

paulScripts.getControl = function (promptName) {
  return oCR.prompt.getControlByName(promptName);
}


paulScripts.getControl(&apos;myPrompt&apos;).setValues(paulScripts.getControl(&apos;myPrompt&apos;).getValues(true));
setTimeout(function() {oCR.sendRequest(&apos;cognos.Report.Action.NEXT&apos;)},500);
&lt;/script&gt;</staticValue>
</dataSource>
</HTMLItem></contents>
<style>
<defaultStyles>
<defaultStyle refStyle="py"/>
</defaultStyles>
</style>
</pageBody>

<style>
<defaultStyles>
<defaultStyle refStyle="pp"/>
</defaultStyles>
</style>
</page></promptPages></layout>
</layouts>
<XMLAttributes><XMLAttribute output="no" name="RS_CreateExtendedDataItems" value="true"/><XMLAttribute output="no" name="listSeparator" value=","/><XMLAttribute output="no" name="RS_modelModificationTime" value="2008-07-25T15:28:38.133Z"/></XMLAttributes><queries><query name="Time"><source><model/></source><selection><dataItem aggregate="none" name="Time"><expression>[sales_and_marketing].[Time].[Time]</expression></dataItem></selection></query></queries></report>

[misap]

Hi Paul,
for some reason my response to your suggestion is not posted. I had immediately tried your script after receiving it but came across couple of issues. Am gonna re-post it with the xml specs. Once again, sorry if you thought that I didn't care to respond. Thanks for your support so far.


Cheers
Manish

[misap]

Hi Paul,
I am still testing the script which you had provided....it seems to work partially......since it depends on the dimension security......am gonna test it further and get back to you....we had some migration going on so couldn't fully test it......I thank your time and support so far.....very grateful to you.


Cheers

[CognosPaul]

Keep at it. Stuff like this is hard to get your head around at first, but once you really understand it, everything becomes easy.