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