One of the most requested features in Cognos is expand/collapse feature. At the moment you can expand members in Workspace, but my users find the tool slow and generally difficult to use. There is a JS solution, but it only works in the rows and requires all of the data preloaded. If your data contains thousands of members, this can make the report run slow.

My solution works by passing the member unique name of the current row to a hidden prompt, appending that value to a set, and reordering the set to the natural order. Collapsing the row is a simple matter of removing the member from the prompt. As an added feature, this means you keep the third level visible while hiding the second level.

We’ll begin with the query. Each crosstab node refers to three data items. The set, the member unique name, and the count of children.

The set looks like:

#promptmany(
  'ProductsSet'
  ,'mun'
  ,'[sales_and_marketing].[Products].[Products].[Product line]'
  ,'hierarchize(union([sales_and_marketing].[Products].[Products].[Product line],descendants(set('
  ,'[sales_and_marketing].[Products].[Products]'
,'),1)))'
)#

If nothing is selected, it defaults to [sales_and_marketing].[Products].[Products].[Product line]. That can be any valid set expression. If a value is selected, it unions the descendants to the set, and reorders it to the natural order.

The count is:

count(1 within set children(currentMember([sales_and_marketing].[Products].[Products])))

This is to control when the expand button appears. It obviously doesn’t make sense to show the button if there are no children.

The member unique name is simply:

roleValue('_memberUniqueName',[Product line])

The count and mun are added to the properties of the node, so we can refer to them in an HTML expression.

case when [Query1].[PLChildren] = 0 then '' else 

case when ParamDisplayValue('ProductsSet') contains ([Query1].[PLMun])
then '<input type="button" onclick="
paulScripts.removeMunsFromPromptCompat(''Product line'','''+[Query1].[PLMun]+''');
oCR.sendRequest(cognos.Report.Action.FINISH);
"
value="-"
>'
else '<input type="button" onclick="
paulScripts.passMunsToPromptCompat(''Product line'','''+[Query1].[PLMun]+''');
oCR.sendRequest(cognos.Report.Action.FINISH);
"
value="+">'
end
end

If there are no children, then don’t render it. If the member already appears in the parameter, then show the collapse button. Otherwise show the expand button.

This method will also work in previous versions of Cognos if you adapt the JS to work with the old JS API.

expand-collapse.txt (3770 downloads)

There are many times when users want dynamic lists. They may want to be able to control which columns are visible at runtime. And while this is all functionality provided by Query Studio, the users may demand a more structured environment.

The most basic solution is to use token prompts. Have a list which contains as many columns as you have fields, an equal amount of checkbox prompts with the use as the field reference, an equal amount of render variables, and you have a report in which the user can check every field he wants to see.

For example:
The column: #promptmany(‘renderYear’,’token’,sq(‘Not Rendered’))#
Value Prompt: optional, multiselect, Static Choice use=[Business Layer].[Time].[Year], display=Year
Render Variable: paramValue(‘renderYear’) is not null

Personally I’m not a big fan of that solution. When using one prompt, column, and variable per field, building the report can be a nightmare. Dozens, if not hundreds, of fields can make the report simply too big to open. The user would also be unable to control the order of the fields.

Instead, let’s build a prompt in which the user can select the fields he wants to see, and choose their order.

This consists of three separate prompts. The prompt on the left contains the static choices for the columns. The right is an empty value prompt. There is also a hidden prompt that contains the currently select choices.

When the report first opens, the user can select the desired. Pressing the Add Fields button will move the option from one side to the other. The vertical move buttons behave as expected. When the finish button is selected, the JS will loop through the selected fields prompt, copying the values to the hidden prompt. This will allow us to automatically reselect the same fields after the page has been refreshed. The JS will then loop through again, appending the use value with the row number. So the first value selected might look like [Business Layer].[Time].[Year]|0|, and the second as [Business Layer].[Retailers].[Country]|1|.

By appending the Row ID, we are salting the fields with a value we can grep inside the query. Consider the following macro:
#
substitute('\|0\|'+sq('All'),'',
join('',
grep('\|0\|',
split(';',promptmany('SelectedFields_Attributes','token',sq('All')))
)
)+sq('All'))
#


This retrieves all of the selected fields, splitting them into an array. It will grep (or search) for any array elements containing “|0|”. Once it finds the value, it converts it back into a fragment with join. A render variable can be set on the column with the expression, ” ParamValue(‘SelectedFields_Attributes’) contains ‘|0|'”.

By doing this, we can arbitrarily limit the number of fields visible in the report, while still giving the flexibility to select whichever fields the user wants. The dynamic nature of the solution will ensure that the size of the report is kept to a minimum, and updating the report will be a simple matter of adding the desired field to the list.

The attached report is using 10.2.1. It’s actually not using the Prompt API, so this technique should work in any version since 8.4.

Selecting-Fields.txt (908 downloads)

First, the news:

For the past 4 years I’ve been working as a freelancer. Fixing issues at client sites, finding novel solutions to unusual problems, and generally having a ball. The BI market where I live is fairly saturated, I can think of a dozen BI companies covering a country smaller than New Jersey. My high price (I just can’t bring myself to undersell my expertise) tends to put me out of the running for many clients. So, at this point, I’m looking for an American company to adopt me. I’m not focusing on any specific location, although my wife would prefer to avoid Alaska.

What can I offer your company? All of the solutions I’ve published here, and quite a few that I haven’t, are at your disposal. I’ve been involved in almost all phases of BI projects, from planning and architecture at the beginning, to the hand-off training at the end. My main selling point has been my talent for finding solutions to those impossible insane fascinating requests clients have had. I am willing to travel on occasion, and I am willing to teach everything I know to your company employees.

Want to hear more? Drop me a line at cognospaul@gmail.com.

Now for the fun bit: Saving Prompts to a Local File

Every now and then clients request a report with many prompts. I’ve seen one report with 60 of them. Usually these reports show row level data, and the people viewing the reports are only interested in a small subset. In cases like these it’s best to train the users on how to create a report view, and to save the prompt settings in the properties of the report.

But what happens if your users can’t create a report view? This might be the case in OEM implementations, or in cases where users simply aren’t trained in Cognos. In these cases I’ve found it better to simply give the users a way to save their prompt selections. I actually have three solutions for this.

The first (this one) has a button to download a text file containing the prompt selections. Another button selects the text file and loops through the selections.

The second is based on the first, but instead of a button, it automatically saves the prompt selections into a table with a stored procedure. I’ll write about that in the future.

Finally we have a solution that Rick Blackwell wrote that saves the prompts into a cookie. This solution can be found in the Prompt API samples. I dislike this solution as browsers have a finite number of cookies that can be saved per domain. If you use this solution extensively, it will cause problems with Cognos sessions. Cookies also have a finite max size of (roughly) 4k, and are sent in all browser requests, eating a lot of bandwith. Lots of big cookies will give a server indigestion.

Rick’s solution is a great starting point though. He wrote a nice script that loops through the prompts on the page and saves them as JSON. We’ll simply copy that, with some minor modifications, for use in our own script.

/*
 * function paulScripts.JSONEncode - Rick Blackwell - ?
 * Modified by Paul Mendelson - 2012-11-20
 * Why reinvent the wheel? Rick wrote this to populate a cookie (as opposed to a file) with the prompt values. 
 * Modified to escape the strings.
 */
paulScripts.JSONEncode = function(promptControl) {
  // Create an empty variable to contain the JSON encoded prompt values
  var JSONData = " ";

  // Create an array to hold the selected values for the prompt control passed to this function (promptControl)
  aPromptValues = promptControl.getValues();

  // If there are no selected values, then the array length is zero
  if (aPromptValues.length == 0) {
    JSONData = "[]";
  } else {
    JSONData = "[";

      // Loop through the prompt values
      for (var j=0; j< aPromptValues.length; j++) {
        var promptValue =  aPromptValues[j];
        if (promptValue.use) {
          // Non Range value 
          if (j == 0) {
            JSONData = JSONData + " {'use' : '" + escape(promptValue.use) + "'" +", 'display' : '" + escape(promptValue.display) + "' }";
          } else {
            JSONData = JSONData + ",  {'use' : '" + escape(promptValue.use) + "'" +", 'display' : '" + escape(promptValue.display) + "' }";
          }
        } else {
          // Range value
          var rangeStart = promptValue.start;
          var rangeEnd = promptValue.end;
          if (rangeStart && rangeEnd) { 
            if (j == 0) {
              JSONData = JSONData + " {'start' : {'use' : '" + escape(rangeStart.use) + "'";
            } else {
              JSONData = JSONData + ", {'start' : {'use' : '" + escape(rangeStart.use) + "'";
            }
            if (rangeStart.display) {
              JSONData = JSONData +  ", 'display' : '" + escape(rangeStart.display) + "'}"
            } else {
              JSONData = JSONData +  "}"
            }
            JSONData = JSONData + ", 'end' : {'use' : '" + escape(rangeEnd.use) + "'";
            if (rangeEnd.display) {
              JSONData = JSONData +  ", 'display' : '" + escape(rangeEnd.display) + "'}  }"
            } else {
              JSONData = JSONData +  "}  }"
            }
          } else if (rangeStart && !rangeEnd) {
            if (j == 0) {
              JSONData = JSONData + " {'start' : {'use' : '" + escape(rangeStart.use) + "'";
            } else {
              JSONData = JSONData + ", {'start' : {'use' : '" + escape(rangeStart.use) + "'";
            }
            if (rangeStart.display) {
              JSONData = JSONData +  ", 'display' : '" + escape(rangeStart.display) + "'} }"
            } else {
              JSONData = JSONData +  "} }"
            }
          } else if (!rangeStart && rangeEnd) {
            if (j == 0) {
              JSONData = JSONData + " {'end' : {'use' : '" + escape(rangeEnd.use) + "'";
            } else {
              JSONData = JSONData + ", {'end' : {'use' : '" + escape(rangeEnd.use) + "'";
            }
            if (rangeEnd.display) {
              JSONData = JSONData +  ", 'display' : '" + escape(rangeEnd.display) + "'} }"
            } else {
              JSONData = JSONData +  "} }"
            }
          } else {
            alert ("Range not set.");
          } // end if
        } // end if
      } // end for 
    JSONData = JSONData + " ]";
  } //end if

  return JSONData;
};  // end JSONEncode function

This will convert the selected prompt values into a string, which we can then store to reuse later.

How do we store that string? Initially I tried using the Data URI method, but unfortunately Internet Explorer does not support Data URIs correctly. So I had to shop around for another solution. The best solution I found was to use a flash widget called “OpenSave”. You can download that here: OpenSave.

The Save button will run a function to pull the prompt values:

/*
 * Function createPrompts. Paul Mendelson 2012-11-12
 * This will pass a string containing all of the prompts and currently selected values to the OpenSave save button.
 */ 
paulScripts.createPrompts = function()
{
//find all of the controls and stick them in aPromptControls. 
  var aPromptControls = oCR.prompt.getControls( );
  var names = [];
  var choices = names

//loop through the controls - finding the name and values and sticking them into the names array
  for(i=0;i<aPromptControls.length;i++)
  {
    var ctrl = aPromptControls[i];
    var values = ctrl.getValues();
    names[i] = ctrl.getName();

//if the prompt doesn't have any selected values, then skip it and move on.
    if(values.length == 0) continue;

//append the JSON encoded values to the prompt name.
    names[i] = ctrl.getName() + '||' + paulScripts.JSONEncode(ctrl);

  }
names.unshift('Cognos Prompts');
return  names.join('\r\n');
}

On the flip side, when we pull the file we use the following function to populate the prompts.

/* 
 * function paulScripts.getFile - Paul Mendelson 2012-11-20
 * This function will load the file and populate the prompts accordingly. If the prompt file has unmatched prompt
 * names, it will dump those into an array and alert the users at the end.
 */ 
paulScripts.getFile = function(base)
{
  var base = opensave.Base64_decode(base.data64);
  if(!base||base.length===0) {alert('File is empty');return false};
  var params=base.split('\r\n');
  var notFound = new Array();

  //is this a prompts file? If not, STOP EVERYTHING.
  if(params[0] != 'Cognos Prompts') {alert('File is not formatted correctly.');return false;};
  for (i=1;i<params.length;i++)
  {
    if(params[i].length===0) continue;
    //any params with values will have '||' separating the param name from the value
    //on second thought, if the parameter exists on the page, but has no value selected, it should simply clear the parameter. 
    //if(params[i].indexOf('||')<0) continue;
    var sepPos = params[i].indexOf('||');	

    //if the sep isn't found, then the promptname is the entire string
    var promptName = sepPos<0?params[i]:params[i].substring(0,sepPos);

    var promptObject = paulScripts.getControl(promptName);
    //sanity check - does the prompt exit? If not, push it into the notFound array 
    if(!promptObject) {notFound.push(promptName);continue}

    //clear the prompt before applying the new values
    promptObject.clearValues();

    //now set the value, if there is a value to set
    if(sepPos>0) promptObject.setValues(eval(unescape(params[i].substring(sepPos+2))));
    promptObject.checkData();
  }

  //Are there any unmatched prompt names? Oh no! Quick, tell the user!
  if(notFound.length>0) alert('Could not find the following prompts: ' + notFound.join(', '));
}

The save button should be an HTML item set to report expression. This way we can automatically have it pull the report name:

'<div id="saveTextFieldButton"> Save Text Field Button Target Div</div>

<script>

function myOpenSaveHandler(buttonID){
   var bi = opensave.getButtonInfo(buttonID);
   var retObj = new Object();
     retObj.filename = "' +
case when  ReportName () ='<%ReportName()%>' then 'Unsaved_Report' else URLEncode (ReportName ()) end
+'.prompts";
      retObj.data = acme.createPrompts();
   return retObj;
}

opensave.make({
  width: 105,
  height: 24,
  buttonDiv: "saveTextFieldButton",
  handler:  myOpenSaveHandler}
);
</script>'

On the other side, the button to open the prompts file can be a simple HTML item:

<div id="openTextFieldButton"> Open Text Field Button Target Div</div>

<script>

opensave.make({ 
  kind: "open", 
  filename: "report.prompts", 
  width: 105,
  height: 24,
  buttonDiv: "openTextFieldButton",
  handler: paulScripts.getFile
}
);
rsa1
</script>

Finally, if everything works, it should look at bit like the following:

Because we’re using the Prompt API, this will only work in versions 10.2+, it could be adapted to previous versions with a bit of work. I’ve chosen to put the OpenSave folder under webcontents/JS/OpenSave. If you don’t save it there, make sure to change the pointer in the HTML item at the top of the page. It’s also worth mentioning that these make good candidates for custom toolbox items; add these to the toolbox controls and report developers can simply drag them into their reports.

openSave-report.txt (726 downloads)