Report Studio : Tree Prompts Explained

Report Studio : Tree Prompts Explained

A Tree Prompt provides the user with ability to navigate levels in a hierarchy easily and select members across Levels or even select multiple levels within a hierarchy.

I have covered below a few points on Tree Prompts

• To set up a tree prompt filter, in your report embed a prompt function inside a data item
  
  Example: To display only a selected Product Hierarchy members / levels and associated revenue:
  
  [Sales and Marketing].[Products].[Products]->?Products?





• To create a multiselect tree prompt, change the expression to -
  
  set([Sales and Marketing].[Products].[Products]->?Products?)





• To display members of levels starting from a particular level -
  
  Example: Display a Tree Prompt that allows users to select members or levels starting from Product Type.
  
  In the query associated with your tree prompt, drag the level from which you wish to be able to display members and descendants and set this as the use value for your tree prompt.
  
  


• Default values can only be hard-coded selections.


• To use a Tree Prompt in a Macro -
  
  set(#promptmany('Products','memberuniquename','[Sales and Marketing].[Products].[Products].[Product type]->:[PC].[@MEMBER].[951]')#)


• Tree prompts are based on Dimensional model, but you can still create Tree Prompts using a Relational Model which I shall explain in a later article.

Report Studio : Tree Prompt on Relational Models using Prompt API

There is already an article published on using single select tree prompt against relational models that makes use of prompt macros. This article focuses on using the Prompt API against the Tree prompt making it easier to use the Tree prompt against a relational model.

This method works for multi-select tree prompts as well. The solution can be extended to get prompt selected data for the various levels and using them individually in the reports for various other solutions.

Using this method we can do away with writing complex macros to massage the prompt data to suit the relational model.

The solution is simple:

Assuming the MUNs have Year, Q, Month included in them to help identify the levels, if not you will have to modify the sections referring to val.indexOf("Year"), val.indexOf("Q"), val.indexOf("Month") to match your MUN sections to identify Year, Quarter and Month: 

1. Have a couple of hidden text box prompts in the report depending on the number of levels you have in the tree prompt. 
2. Use the prompt API to get the list of selected prompt values.
3. Identify the level of the selected prompt value.
4. Transform the prompt value to a relational value.
5. Append the prompt value in the respective text box prompt.
6. Create filters in the query passing in the text box prompt values.

Step 1: Create the tree prompt and a list report to get the required data.

Step 2: Name the tree prompt as "treePrompt".

Step 3: Create 3 hidden text box prompts named YearPrompt, QrtrPrompt, MonthPrompt.

Step 4: Create filters in the list query to accept the parameter values from YearPrompt, QuarterPrompt, MonthPrompt as mentioned below:
      (#csv(split(';',promptMany('pYear','token')))#)

Step 5: Insert an HTML item with the below prompt API code:

 <script>  
 function Prompts() {  
   var oCR = cognos.Report.getReport("_THIS_");  
   var treePrompt = oCR.prompt.getControlByName("treePrompt");  
   var YearPrompt = oCR.prompt.getControlByName("YearPrompt");  
   var QrtrPrompt = oCR.prompt.getControlByName("QrtrPrompt");  
   var MonthPrompt = oCR.prompt.getControlByName("MonthPrompt");  
   var vYear = ';';  
   var vQrtr = ';';  
   var vMonth = ';';  
   vPreString = "[";  
   vPostString = "]";  
   var vTree = treePrompt.getValues();  
   var valStart, valEnd;       
   for (i = 0; i < vTree.length; i++) {  
     var val = vTree[i].use;  
     if (val.indexOf("Year") > 0) {  
       valStart = val.lastIndexOf(vPreString);  
       valEnd = val.lastIndexOf(vPostString);  
       vYear = vYear.concat(val.substring(valStart + 1, valEnd));  
       vYear = vYear.concat(";");  
     }  
     if (val.indexOf("Q") > 0) {  
       valStart = val.lastIndexOf(vPreString);  
       valEnd = val.lastIndexOf(vPostString);  
       vQrtr = vQrtr.concat(val.substring(valStart + 1, valEnd));  
       vQrtr = vQrtr.concat(";");  
     }  
     if (val.indexOf("Month") > 0) {  
       valStart = val.lastIndexOf(vPreString);  
       valEnd = val.lastIndexOf(vPostString);  
       vMonth = vMonth.concat(val.substring(valStart + 1, valEnd));  
       vMonth = vMonth.concat(";");  
     }  
   }  
   var vYearArr = new Array();  
   var clmns = {  
     use: vYear  
   };  
   vYearArr[0] = clmns;    
   YearPrompt.setValues(vYearArr);  
   var vQrtrArr = new Array();  
   var clmnsQrtr = {  
     use: vQrtr  
   };  
   vQrtrArr[0] = clmnsQrtr;    
   QrtrPrompt.setValues(vQrtrArr);  
   var vMonthArr = new Array();  
   var clmnsMonth = {  
     use: vMonth  
   };  
   vMonthArr[0] = clmnsMonth;    
   MonthPrompt.setValues(vMonthArr);  
   oCR.sendRequest(cognos.Report.Action.FINISH);  
 }  
 </script>