Friday, July 4, 2014

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>  





12 comments:

jake george said...

Cognos TM1 Online Training, ONLINE TRAINING – IT SUPPORT – CORPORATE TRAINING http://www.21cssindia.com/courses/cognos-tm1-online-training-91.html The 21st Century Software Solutions of India offers one of the Largest conglomerations of Software Training, IT Support, Corporate Training institute in India - +919000444287 - +917386622889 - Visakhapatnam,Hyderabad Cognos TM1 Online Training, Cognos TM1 Training, Cognos TM1, Cognos TM1 Online Training| Cognos TM1 Training| Cognos TM1| "Courses at 21st Century Software Solutions
Talend Online Training -Hyperion Online Training - IBM Unica Online Training - Siteminder Online Training - SharePoint Online Training - Informatica Online Training - SalesForce Online Training - Many more… | Call Us +917386622889 - +919000444287 - contact@21cssindia.com
Visit: http://www.21cssindia.com/courses.html"

celloindia said...



SAP HANA Online Training Hyderabad. Best online training institute for SAP Hana. Get your demo now for free of cost.

obiee training institutes in hyderabad said...

very useful really good information thanks for posting such a good information it will hepls the people a lot keep it up , Regards, obiee training in hyderabad

Viswa said...

Hi, very interesting information for me,I read the article with pleasure.Thank you admin of this site for interesting and useful information.

CognosPaul said...

Hey Zephyr,

If you've left a comment on my blog - leave another with your email?

Venkat M said...

Very Useful Information
Best Informatica Online Training INDIA, USA, CANADA, AUSTRALIA...
Visit Best Informatica Online Training

Mahesh Ram said...

Good explanation, step by step with images. anyone can understand very easily and clearly.Online COGNOS Training Classes

seoindiarank chennai said...

I cant wait to check out some of these blogs! I’ve really wanted to start learning more about cars and auto repairs lately and I think this will help a lot. I think it can save my family some money if we knew how to do some repairs at home.! Thanks again for all the options.
seo company in chennai

Monstermurtuza courseshussain said...

Great information is been provided, i thank the blogger for implementing these ideas and explaining it here in a much better way, i have learned alot on Cognos from here, when i was getting trained by monstercourses online , there experts teach you in a much excellent way, pls visit: http://www.monstercourses.com/courses/data-warehousing/ibm-cognos-online-training

oracleR12 said...

Hi,
Great information is been provided, i thank the blogger for implementing these ideas and explaining it here in a much better way. I am glad that you shared this information with us.
Thank you.
oracle fusion training

mounika mouni said...

we are offering best Biztalk online training with job support and high-quality training facilities and well expert faculty. To Register you free demo please visit.
Cognos TM1 Online Training

soumya Reddy said...

Very Useful you are shared on Cognos TM1 Online Training