Tag: prompts

Macros

Quickie: How to use the new macro function queryValue

| cognospaul

A very pleasant surprise when building reports. There’s a new function called “queryValue”.

On the surface it looks fairly innocuous, but the result of this is a lot of effort saved when building reports. There are many times where I want a way of pulling a default value from a database in a filter. For example, my report might need to let the user select a year, but last date with data is unknown. Normally we can do something like:

[Sales (query)].[Time].[Year] = #prompt('Year','integer',
'maximum([Sales (query)].[Time].[Year] for report)'
)#

WITH 
"TQ0_q_SelectedMonth1" AS 
    (
    SELECT
        "GO_TIME_DIM"."CURRENT_YEAR" AS "Year0", 
        MAX("GO_TIME_DIM"."CURRENT_YEAR")
            OVER(
            ) AS "Max1"
    FROM
        "GOSALESDW"."gosalesdw"."GO_TIME_DIM" "GO_TIME_DIM"
    )
SELECT
    "TQ0_q_SelectedMonth1"."Year0" AS "Year0"
FROM
    "TQ0_q_SelectedMonth1" 
WHERE 
    "TQ0_q_SelectedMonth1"."Year0" = "TQ0_q_SelectedMonth1"."Max1" 
GROUP BY 
    "TQ0_q_SelectedMonth1"."Year0"

It works, but I don’t like it. It’s creating a window function, and actually hits every row in that table. One way around would be to create a parameter map in the framework model. Create a model query that has the value you want, add a data item with a static 1, and create a parameter map based on that. You could then do:
[Sales (query)].[Time].[Year] = #prompt('Year','integer',
sq($lastYearWithData{1})
)#

Then the SQL will look like: 

SELECT
    "GO_TIME_DIM"."CURRENT_YEAR" AS "Year0"
FROM
    "GOSALESDW"."gosalesdw"."GO_TIME_DIM" "GO_TIME_DIM" 
WHERE 
    "GO_TIME_DIM"."CURRENT_YEAR" = 2013 
GROUP BY 
    "GO_TIME_DIM"."CURRENT_YEAR"

This is much better but still way too clunky. It requires access to the framework model, and a new parameter map for each and every scenario that we need. What if we want the last date of data for a specific product line? Or product?

Instead, let’s take a look at queryValue()

[Fact Business].[Dates].[Year] = #prompt('Year','integer'
, queryValue('maximum([Sales (query)].[Time].[Year])')
)#

This behaves exactly like the parameter map, and the SQL is identical. Cognos is actually making a query that runs maximum([Year]), and returns that value to the macro – exactly like a parameter map would. We can add filters to the query like this:
[Sales (query)].[Time].[Year] = #prompt('Year','integer',
queryValue('maximum([Sales (query)].[Time].[Year])','[Sales (query)].[Sales].[Quantity]>0 and [Sales (query)].[Products].[Product]=''Trail Star''')
)#

Notice using a double quote to escape the single quote. Great Outdoors stopped selling Trail Star in 2011, and 2011 is now the default value in the prompt.

This is also especially useful in Data Sets and Data Modules, where parameter maps simply don’t exist.

Uncategorized

Advanced Select and Search Prompts

| cognospaul

This solution was built on 10.2.1.1, but should work on all versions after 10.2. Versions prior to 10.2 will need some rewriting as this is based on the Prompt API.

One of the biggest complaints I get about search prompts is the amount of real estate they take from reports. The multiselect prompts are huge, and users hate them for it. So I was asked to find a way to shrink it down.
SnS hogging the page

Obviously the solution was to everything but the search input and button. The keyword and instruction text can be controlled by the properties, simply setting it to “Specified Text” is sufficient to hide them:
Hiding Keywords

That leaves the Options link and the search results. Personally I don’t believe the results list need to appear unless there are actually results. The options should be at the mercy of the report author, so my solution includes a way to choose a default value and to hide the options.

First a look at the JS

/* function paulScripts.advancedSnS - Paul Mendelson - 2013-09-16
 * Will convert a standard search and select. It will add a button to expand/collapse the 
 * are selection area. The button will display how many matches have been found, and how 
 * many selected. 
 * 
 * The Option param will control the default search method. 
 * 1 = Starts with any of these keywords **DEFAULT**
 * 2 = Starts with the first keyword and contains all of the remaining keywords
 * 3 = Contains any of these keywords
 * 4 = Contains all of these keywords
 *
 * Hide the options link by setting 1 in the hideOption param.
 */ 
paulScripts.advancedSnS = function(promptName,option,hideOption){
  var prompt = paulScripts.getControl(promptName)
    , elm = document.getElementsByName(prompt.getParameterName())[0].parentNode
    , selectTable = elm.getElementsByTagName('table')[4]
    , selectDiv = document.createElement('div')
    , selectOptions = cognos.Report.getReport("_THIS_").prompt.getControlByName(promptName).getSelectOptions()
    , selectChoices = cognos.Report.getReport("_THIS_").prompt.getControlByName(promptName).getSelectChoices()
    , tr = elm.getElementsByTagName('button')[0].parentNode.parentNode.parentNode.appendChild(document.createElement('tr'))
    , td= tr.insertCell(0)
    , slideDiv = document.createElement('div')
    , btn = document.createElement('input');


    if(option) elm.getElementsByTagName('input')[option+2].checked=true;
    if(hideOption) tr.parentNode.parentNode.parentNode.parentNode.nextSibling.style.display='none';
    td.setAttribute('colSpan',2);

//Hacky – Building a new dispatcher request to save the state of the SnS. Do any IBMers have any comments? 
  if(typeof oReq ==='undefined') oReq = new ViewerDispatcherEntry(window['oCV'+'_THIS_'].getRV().getCV());
  if(!oReq.getFormFields().m_aValues[promptName]) oReq.addFormField(promptName,'hidden')

  btn.type='button';
  btn.value='';

  if(!selectOptions&&!selectChoices) btn.value='click to open';
  if(selectOptions)  btn.value = selectOptions.length + ' found. ';
  if(selectChoices)  btn.value += selectChoices.c.length + ' selected.';

  selectTable.parentNode.insertBefore(selectDiv,selectTable);
  selectDiv.appendChild(selectTable);
  
  //the right side of the table is a bit ugly; not enough padding.
  selectDiv.style.paddingRight='5px';

  // Sets the table of the select options to position absolute. This will prevent the page from expanding vertically
  // when the table is visible;
  selectDiv.style.position='absolute';
  selectDiv.style.overflow='hidden';
  selectDiv.style.backgroundColor='white';

  btn.onclick = function() {
    if(selectDiv.style.visibility=='hidden')
      {slidedown(selectDiv);
oReq.addFormField(promptName,'visible');}
    else {slideup(selectDiv);oReq.addFormField(promptName,'hidden');}
      return true;

  }

  selectDiv.style.visibility=oReq.getFormFields().m_aValues[promptName];
  td.appendChild(btn);
  return true;
}

The JS itself is fairly straight forward. The variables define the DOM element, a JSON array of the found options, another JSON array of the selected objects, and a couple of generated elements.

If a default search option is selected, it will set that. 

if(option) elm.getElementsByTagName('input')[option+2].checked=true;

If hideOption is set to true, it will hide the options link. 

 if(hideOption) tr.parentNode.parentNode.parentNode.parentNode.nextSibling.style.display='none';

Then it will insert a button, and finally the “results” lists are moved into a generated div which can then be slid open or shut. 

  td.appendChild(btn);

Whenever I create a solution that changes the appearance of the page, showing or hiding an element, the users demand that the change persist through a page reset. The easiest way would be to create a new parameter to store the display state. Possibly a hidden text box with box type set to none. But if we always did things the easy way, we’d never learn anything. 

 //Hacky – Building a new dispatcher request to save the state of the SnS. Do any IBMers have any comments? 
  if(typeof oReq ==='undefined') oReq = new ViewerDispatcherEntry(window['oCV'+'_THIS_'].getRV().getCV());
  if(!oReq.getFormFields().m_aValues[promptName]) oReq.addFormField(promptName,'hidden')

I haven’t tested this on a wide scale, so I don’t know of any performance impact. To be honest, while I love figuring out little hacks like these, without a more in-depth understanding of what it’s doing, it may not be wise to push this to all reports. The attached report contains two functions, the first using the hacky dispatcher request, and the second using a secondary text box to retain the state. The disadvantage of using the text box is that you’ll need to remember to add that in.

Once the JS is in place, we can call the functions with a simple:

paulScripts.advancedSnS ('Retailers', 3,1);

It will look for the prompt named Retailers, set the option to “Contains any of these keywords”, and hide the options link.

And the final product:
Searchng and Selecting

Report XML:
Advanced SnS (4749 downloads)

Javascript, Prompts

Checking an “All” option in a checkbox prompt

| cognospaul

One of my readers sent me an interesting problem. They need a checkbox prompt in which the top option is “All” and checking on any other option would automatically uncheck the “All” choice. Similarly, checking “All” should uncheck the other choices. Taking it to the conclusion, when checking or unchecking all of the options, the “All” should be checked.

Since they are still on 10.1, I have not used the Prompt API, meaning this should work on all versions since 8.4. To begin, the prompt itself.
check this out

In this case, the prompt is based on the Retailers hierarchy in the Sales and Marketing cube. Other times you might want to add a static value.

The JavaScript itself is not that difficult. The JS will loop through the prompt each time an option is clicked. If the first option is clicked, it will check it and uncheck the other options. If any other option is click it will loop through the prompt, counting the number of checked options and act accordingly. If 0 or all of the options are checked, it will check the first option, otherwise it will simply uncheck it.

When working with Checkbox prompts in JavaScript, the thing to remember is that, for whatever reason, the checkboxes that we see are actually images. To show the prompt as checked, the input needs to have the class “dijitCheckBoxChecked”.

Now the JS:

<script>
/* 
  * Function: addEvent
  * Author: Dan Fruendel
  * Attachs an event or adds an event listener depending on the browser.
  */
var addEvent = function(element, event, func){
    if(element.addEventListener){
      addEvent = function(element, event, func) {
        element.addEventListener(event, func, false);
        return true;
      };
    }
    else if(element.attachEvent) {
      addEvent = function(element, event, func) {
        return element.attachEvent("on" + event, func);
      };
    }
    else {
      addEvent = function(element, event, func) {
        var oldEventHandler = element['on' + event];
        element['on' + event] = function() {
         //using .apply to pass on anything this function gets.
          if(typeof(oldEventHandler) === "function") {
            oldEventHandler.apply(element, arguments);
          }
          func.apply(element, arguments);
        }
        return true;
      };
    }
    addEvent(element, event, func);
  }


// Cognos form and namespace identifier. Don't touch.
var fW = (typeof getFormWarpRequest == "function" ? getFormWarpRequest() : document.forms["formWarpRequest"]); 
if ( !fW || fW == undefined)   {
       fW = ( formWarpRequest_THIS_ ? formWarpRequest_THIS_ : formWarpRequest_NS_ );
   } 
 var preFix = "";
 if (fW.elements["cv.id"]) {     preFix = fW.elements["cv.id"].value;   }
     var nameSpace = "oCV" + preFix;
 
function attacher(elm,prompt,clicked){
  var func = function() {
    //if all is selected, zero out everything else.
    if(clicked==0) {prompt[0].selected=true;prompt[0].checked=true;prompt[0].parentNode.className="dijitInline dijitCheckBox dijitCheckBoxChecked";for(var i=1;i<prompt.length;++i){prompt[i].selected=false;prompt[i].checked=false;prompt[i].parentNode.className="dijitInline dijitCheckBox"}}

    //if individual, count number of selected
    if(clicked>0) {var c=0;
      for(var i=1;i<prompt.length;++i){
        if(prompt[i].checked){++c}
      }
      //if the count of checked is 0, then set the all to checked
      if(c==0) {setter=false;prompt[0].selected=true;prompt[0].checked=true;prompt[0].parentNode.className="dijitInline dijitCheckBox dijitCheckBoxChecked"}
      //if the count of checked is equal to the length of the prompt, then set the all to checked and uncheck everything else
      else if(c==prompt.length-1) {setter=false;prompt[0].selected=true;prompt[0].checked=true;prompt[0].parentNode.className="dijitInline dijitCheckBox dijitCheckBoxChecked";        for(var i=1;i<prompt.length;++i){prompt[i].selected=false;prompt[i].checked=false;prompt[i].parentNode.className="dijitInline dijitCheckBox"}}
      //if the count is one and less than the length of the prompt then just set all to unchecked;
      else if(c>0&&c<prompt.length-1){prompt[0].checked=false;prompt[0].selected=false;prompt[0].parentNode.className="dijitInline dijitCheckBox"}
    }

    canSubmitPrompt();
    }
  addEvent(elm,'click',func)

}

var prompt=fW._oLstChoicesCountries;
for (var i=0;i<prompt.length;++i){
  attacher(prompt[i],prompt,i);
}

</script>

EDIT: An eagle-eyed reader noticed that the appearance of the checks are actually slightly different than they are when first rendered. The solution was to put a parentNode after prompt[i] when calling the className. Thanks Sue!

Checkbox Prompt - All option (1831 downloads)