Tree Prompt Issue

[Ammus1234]

Hello All,

I am facing an issue while expanding the tree prompt in cognos 10.2.1 FP2.

On expanding tree prompt it shows first 20 members, when I click more at the end it again displays the same 20 and goes on.
It never displays 21st member onwards.

I have already tried to change the package configuration setting 'Default member limit in a data tree level' to 0 and also tried to 10000
It is not helping.

I have 6000 members in the tree. It shows first 5000 members, when I click on more it shows the same 5000 members again. Not showing 5001 to 6000 members at all. Also very slow and IE hangs.

Is there any other setting I have to try. Could anyone help?

Thanks in advance.

[navissar]

For such a huge amount of categories I would strongly advise against using a tree prompt - it will cause slowness and the types of ugly behaviour you described, and also - there's no way a user will scroll through 5000 values to find what they need. I'd go with a search&select prompt.

[CognosPaul]

In addition to what Nimrod said, it may be worth considering the HTML a tree prompt generates. The following is a single member from a tree prompt:

Code Select Expand

<DIV id=CTree_N19DCC920x1B996CB0RS0.0 class=clsTreeLevel role=presentation treeRef="0.0" tree="CTree_N19DCC920x1B996CB0RS" dragRef="0.0" dragTree="CTree_N19DCC920x1B996CB0RS"><NOBR role=presentation treeRef="nobr" tree="CTree_N19DCC920x1B996CB0RS"><IMG class="clsTreeLineIcon clsTreeLineIconSpace" alt="" align=middle src="../skins/corporate/prompting/images/blank.gif" width=1 height=1 treeRef="img"><IMG id="CTree_N19DCC920x1B996CB0RS0.0toggle_root_Products_Camping Equipment" class="dijitTreeExpando dijitTreeExpandoClosed" title=Expand tabIndex=-1 alt=Expand align=middle src="../skins/corporate/prompting/images/blank.gif" width=1 height=1 treeRef="img"><SPAN class=dijitExpandoText role=presentation>+</SPAN><SPAN id=CTree_N19DCC920x1B996CB0RS0.0label title="Camping Equipment" role=presentation tabIndex=-1 treeRef="0.0" tree="CTree_N19DCC920x1B996CB0RS" dragRef="0.0" dragTree="CTree_N19DCC920x1B996CB0RS"><IMG id=CTree_N19DCC920x1B996CB0RS0.0icon class=clsTreeNodeIcon role=presentation alt="" align=middle src="../skins/corporate/prompting/images/blank.gif" width=1 height=1 treeRef="img" dragRef="0.0" dragTree="CTree_N19DCC920x1B996CB0RS"><SPAN onblur="this.classname='clsTreeNode_unselected clsTreeText';" style="FONT-FAMILY: ; textVariant: " aria-expanded=false id=CTree_N19DCC920x1B996CB0RS0.0labelText aria-checked=false class="clsTreeNode_unselected clsTreeText" onfocus="this.classname='clsTreeNode_hover clsTreeText';" role=treeitem treeRef="0.0" tree="CTree_N19DCC920x1B996CB0RS" dragRef="0.0" dragTree="CTree_N19DCC920x1B996CB0RS">Camping Equipment</SPAN></SPAN></NOBR></DIV>

That single member is 1,596 characters long. Taking only into account the HTML itself, ignoring the event handlers, this would take 1.5kb of ram. Multiply that by 5,000 and you've got an object taking 7.6 megs. Now add the cost of rendering the object, applying the handlers to each element, and it becomes very easy to understand why IE stalls.

[Ammus1234]

Thanks for your replies Nimrod and Paul.

I have tested against cognos 10.2.1 FP1. It is good there. So it seems to be a bug in FP2. May be will forward to support.

In this case which prompt do I use? Search and select? I have tried value prompt but it also takes time to fetch all the values as I need all the prompts on top of the report page itself.

Again the number of records are based on the user access rights. It could vary from 10 to 6000 values.
So I think a search prompt may not be good in all cases, when the user has to search from 10 records.

I am using SSAS cube as the Data source.

[CognosPaul]

You could have the prompt appear as a tree prompt if there are less than 100 entries, and as a search if more.

Simply create a singleton, and in the associated query use the expression:
memberCount: count(1 within set [hierarchy])

Check that in the properties of the singleton.
Drag a conditional block inside, and create a Boolean expression [Query1].[memberCount] > 100

In the yes block, drag the search prompt, in the no drag the tree prompt.

[Ammus1234]

Thanks Paul... I will try and update you  :)
It will be little more complex for me as I use javascript (prompt API) for tree prompts. The tree prompts appear inside a dropdown.
The users were demanding something similar to an excel search. The sns prompt in cognos is too big to display on top of the report.

[CognosPaul]

I've also had a lot of complaints about the size of the sns prompts.
Take a look at my solution: http://cognospaul.com/2013/12/16/advanced-select-search-prompts/

[Ammus1234]

Thanks Paul. I will check the link.

I also find another issue with multi select tree prompt. Please see the screenshot.

For example, when I select 'Products' and if I select 'Camping Equipment', 'Products' at the top level is not greyed out.
The data is also not getting filtered for Camping Equipment.

If we uncheck 'Products' and select 'Camping Equipment' then 'Products' is  greyed out.

Is there a solution?

[CognosPaul]

Hi Ammus,

What you're seeing is actually expected behaviour. There are times when you want the user to be able to select from multiple levels, for example someone might want to select the metropolitan area of Mumbai and the state entire state of Haryana. Consider if you're selecting two cities, in SQL the filter would be table.cities in ('Mumbai', 'Lucknow'). An alternative way of writing that might be: table.cities = 'Mumbai' or table.cities = 'Lucknow'.  By selecting Products and Camping Equipment, the comparable filter would be table.products = 'All' or table.producttype='Camping Equipment'.

There may be a way to detect if the member selected is a descendant of an already selected node, but that would require a bit of thought and programming. The easiest solution is to educate the users, the report is giving them exactly what they're asking for.

[Ammus1234]

Thanks Paul.

I understand that.
But normally if a member is selected from inside the level, the result is expected only for that member, isn't it?
I think it is difficult to make a selection in this case, especially when there are many levels.

I was looking at how this is handled in excel. Please see the screenshot. When I uncheck 'tyty' the upper levels are automatically greyed out. I was expecting a similar behaviour here. :(
But in excel everything is checked beforehand. In cognos, only the default selection is checked.

[Ammus1234]

Hi Paul,

Do you think this will be possible with prompt API?
I want to uncheck (grey out) the upper level when a lower member is selected in tree prompt.

[CognosPaul]

The Prompt API alone won't be able to handle this. Any script that does this would have to loop upwards, unchecking all the parent checkboxes. And what happens when you select a parent member? Do you want to deselect the lower? If you refresh the page, the lower level element won't be rendered on the page.

The biggest issue is that the checked values will be returned as a regular JSON array. It is impossible, from that array, to determine the hierarchical position of any given member. For example, you might have an array like this [memberA, memberB]. Is memberB a descendant or ancestor of memberA? There is simply no way of telling.

[Ammus1234]

Hi Paul,

When we select a parent member, all the children should be selected. When we select any child, the upper levels of it must be unchecked/grey out.
As you said, in case we can't find out the selected member is a parent or child then I think it will be impossible to do it.
Have you tried the excel plug in for SSAS, the users are asking for a similar behaviour of the tree ??? and that too with a search inside would be heavenly. :)

[McAndrew]

I experienced the original issue with one of our reports and a tree prompt. >:(  It would continuously display the first 20 entries over and over.  I did some research and found an older article discussing the number of items to display in a tree prompt.  I changed the settings suggested and it removed the need to click on the More link.

Here's the link:
https://www-304.ibm.com/support/docview.wss?uid=swg21339700

It's definitely a bug, but there is a workaround.

~Andrew

[Ammus1234]

Thanks Andrew for confirming it.
Yes, I did the same work around. But the problem is it takes long time to load the page when there is large number of values.

[CognosPaul]

I've been digging through the internals in my free time trying to figure out a way to piggyback on tree prompts. The way Cognos handles tree prompts is pretty close to magic. It's a closed system that's almost impossible to penetrate.

From what I've been able to piece together from going through the JS files, it generates a URL containing the member and data source. The generated URL calls:
..\c10_64\templates\ps\prompting\promptTree.xts

From there it looks like it's doing a dispatcher request through buslogic.xslt, retrieving the children, and transforming the results for the prompt object. Everything is so interwoven, and poorly commented (although to be fair, it might be commented on the development side), that it would be next to impossible to hack together a search option on top of the tree prompt.

[Ammus1234]

Thank you Paul. Really surprised to know that you have a look into it  :)

I told my client that it is impossible to change the way it works.  :) 
But sometimes I get some wrong results (may be the mdx query formation issue), when I select the top node and some levels below it (for testing). If I understand correctly the result must always be for the top node selected, regardless of the levels below it.

PS: Sometimes I am doing a ctrl+F in browser to find an item in treeprompt after expanding it  ;)

[Ammus1234]

Issue with repeating members in treeprompt after the first 20 is solved in 10.2.1 FP4 fixpack.