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>  

Cognos 10 Prompts - New Features

As has been pointed out by Ziegenbart in his comments, with Cognos 10, you needn't use any Javascript to modify the value prompt title line. Cognos 10 provides properties through which you can control the first 2 lines of the prompts.

User Contribution [Ziegenbart]:

I've been using the properties of the value prompt itself to either customize or remove the header text. Super simple and no need to use javascript with the disclaimer that there is no guarantee it will work or won't break. (I'm using Cognos 10 btw).

The default selection for the "Header Text" under "Prompt Text" for the value prompt is "Automatic".

Click on the ellipsis to the right of "Automatic" in the "Header Text" property. Change the selection to "Specified text" and leave the text field empty.

The first two lines of the Value Prompt are now removed.

You can also use this to customize what the first line says to meet business needs by entering some text (e.g. "Please make selection")

You can manipulate many of the presentation elements of a value prompt using the properties menu.

Optional Prompt Macros

A common misconception that I have noticed people have with respect to optional prompt macros is with regards to Preceding / Trailing text in a prompt macro.

An optional prompt macro always has a default value specified, so if no prompt value is selected the default value is applied in the query but this will not cause the preceding / trailing text in the macro to get applied. The preceding / trailing text get applied only if a prompt value is selected.

A simple example to demonstrate this:

We have ProdID and Sales displayed in a report.


The report has an optional prompt macro for ProdId defined as:

[Presentation Layer].[REVENUE_FACT].[PRODUCTID]= #prompt('Prod Id', 'Integer' ,1, '', '', 'and [Presentation Layer].[REVENUE_FACT].[SALES] > 5000' )#

Notice that the prompt macro has a default ProdID value of 1 and a trailing text that will add an additional filter of Sales > 5000.

When no value is selected for the ProdId prompt, the output displays data for Prod Id 1. Notice that the additional Sales filter doesn't get applied.



And here is the output when a ProdId selection of 1 is made. The additional Sales filter gets applied and displays the sum total of Sales for ProdId where Sales > 5000.

Caching Prompts

Started the new year with a Cognos job failure for refreshing the prompt cache due to server issues. I am now trying to think of an effective failure/re-start strategy.

Caching Prompt values makes sense when you have huge dimensions in your warehouse that change slowly over a period of time. In my case we have dimensions that have SCD Type 2 implemented in them requiring us to cache prompts.

But one needs to keep in mind that the data you see in your prompts are not live data and are not security oriented. One also needs to monitor the prompt cache refresh jobs to ensure they run successfully else users may end up seeing old data in the reports.

To cache report prompts you need to create a Cognos job. Select the report whose prompt values need to be cached.Under report options, choose "Refresh the report cache" option for "Run the Report to" property. The clear cache option is available under the report properties section. You can also set properties to clear the cache in x days.