While IBM is promising searchable tree prompts in Cognos 11.1.7 (hopefully), many cognos instances won’t upgrade to that soon. Also, it’s likely that prompt will only work in interactive mode, so a non-interactive mode prompt would be very useful.

The technique I’ve come up with doesn’t use any JavaScript, and works just as well for non-interactive mode. The only downside is that it involves refreshing the page, but the only query that changes should be query feeding the tree prompt, so it shouldn’t be too bad.

For the purposes of this post, I’ll build it in interactive mode.

To start, let’s build the structure of the search. Create a table with 2 rows, 2 columns, and merge the bottom two cells.

Top left: Drag in a text box prompt, parameter name: “ProductSearch”
Top right: Drag in a prompt button
Bottom: Drag in a tree prompt, parameter name: “Product”. Don’t select anything to feed the prompt, we’ll do that later.

When you’re done, it should look like this:

Now we have to define the data item feeding the tree prompt. In this case this is the code we want:

#prompt(
   'ProductSearch'
  , 'string'
  ,'[Sales (analysis)].[Products].[Products].[Product line]'
  ,'union(filter(descendants([Sales (analysis)].[Products].[Products].[Products],1,self beforewithmember after),lower(caption(currentMember([Sales (analysis)].[Products].[Products]))) contains lower('
  , ''
  , ')),'+promptmany('Product','mun','emptySet([Sales (analysis)].[Products].[Products])','set(','',')')+')'

)#

It seems complicated, so let’s go over step by step.

First, look at the ProductSearch parameter. If it is null, then take the Product line level from the hierarchy. This is the way tree prompts behave normally.

Next, if there is a value in ProductSearch, let’s filter the ENTIRE hierarchy looling for the specific string. If a user types in, “polar” the expression would look like:

filter(descendants([Sales (analysis)].[Products].[Products].[Products],1,self beforewithmember after),lower(caption(currentMember([Sales (analysis)].[Products].[Products]))) contains lower(‘polar’))

And finally, we union the already selected Products, if any, onto that set. On the first search it might look like:

union(filter(descendants([Sales (analysis)].[Products].[Products].[Products],1,self beforewithmember after),lower(caption(currentMember([Sales (analysis)].[Products].[Products]))) contains lower(‘polar’)),emptySet([Sales (analysis)].[Products].[Products]))

Assign that data item to the tree prompt, and let’s see it in action.

It’s working, but it doesn’t look great. You can drag a text item into the prompt button, and resize things to make it a little more inline.

Report XML below
search-tree-report.xml (1103 downloads)