Prompts

Searchable Tree Prompts

| cognospaul

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:
Structure

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.

search tree working

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 (879 downloads)

Uncategorized

Livestream: Bag o’ Tricks and Bag o’ Controls – UPDATED!

| cognospaul

On February 13, at 2PM EST, I’ll be guest presenting a live stream with my good friend Ryan Dolley. Ryan will be doing a deep dive on changes (documented and not) in Cognos 11.1.5. I’ll be going over my Bag o’ Tricks extension pack. What it does, why it’s important, and how to use it.

More details here: https://pmsquare.com/events/2020/1/28/livestream-reporting-in-cognos-analytics-1115

Sign up and drop me a line if there’s something specific in the extensions you want me to cover.

UPDATE – It’s gonna happen for real this time! I promise. It’s a YouTube livestream, so it will be saved forever and ever.

Javascript

Quickie: The user is already authenticated in all available namespaces

| cognospaul

Have you ever stepped away from your computer for just a few minutes, and come back to Cognos proudly stating that you’re already logged in? That very useful message preventing you from saving the report that you’ve worked for hours and hours on.

already authenticated

Can you guess what happened to me this morning?

Well, after a few moments of panicking I realized that the reports are still there in the browser. I’m not sure which reports are saved (all of them, right? because I always save when I should), but at least the XML is there waiting for me.

Fortunately, the application JavaScript we need is not obfuscated. So this will work:

for(var i=0;i<frames.length;++i){
if(!frames[i].Application.GlassView) continue;
var gv = frames[i].Application.GlassView
  , saved = gv.isDirty()?'Not Saved':'Saved'
  , name = gv.getTitle()
  , cmProp = gv.cmProperties;

console.log(name + ' is ' + saved)
if(gv.isDirty()&&cmProp){
console.log('In order to save ' + name + ' , type in frames['+i+'].Application.Save() or you can get the report XML by typing frames['+i+'].Application.Document.GetReportXML()')
}
else if(gv.isDirty()&&!cmProp){
console.log('This is an unsaved report so a simple save action will not work. You can retrieve the report xml by typing frames['+i+'].Application.Document.GetReportXML()')
}
}

Run that from the top frame in the console and follow the instructions for each report.

Now a colleague did mention that sometimes pressing the back button will resolve it, but it doesn’t always work for me and I’m super paranoid about losing work.