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>  





Tuesday, June 10, 2014

10.2.1 Defects - Workspace

The below are against 10.2.1 DMR model using DQM mode.

For most of the defects the report has a crosstab with prompts on the report page to dynamically select rows and measures and a period tree prompt to select the period columns. The report has drills enabled.

Again, will update defect numbers as we get them. But that seems to be a time consuming process and am not the one involved.

Drill through lost on drill down

Drills not synched between chart and crosstab - Drill down on Product, Quarter combination in chart does not get reflected in crosstab

Drill on measures not synched between chart and crosstab - Drill down on fact cell intersection on chart does not reflect correctly in crosstab

Drill up and re-prompt doesn’t get reflected - Drill up on first run of the report and reset dimension. New dimension does not get reflected in objects

Resetting prompted widgets will re-prompt and then selecting different prompt values does not have any effect on the widgets.

Drill up/down and drill through doesn't pass all context values to the drill through report.

Charts - Drill and prompt on the same hierarchy does not reflect once drills are enacted - If a prompt dimension is used as series or categories then once a drill is acted upon the series/category re-prompting does not have any effect.

Drills get enabled and disabled intermittently on categories/rows and series/columns - Initially chart, crosstab do not have drills enabled on the time period series as set up in the report. Once drills are executed on the fact cells or on the chart intersections the drills become enabled on the time series.


Thursday, June 5, 2014

Cognos 10.2.1 DQM DMR Defects - Part 2

Drill and Re select Dimension with Render Variable - Behaves differently if measure is based on data item vs measure based on prompt macro.

If 2 members are defined - one with a token prompt macro and another with an MDX expression defined on top of the first data item. If member sets are defined on the 2 members and drill behvaiour set for the first member to replace expression and the second expression to preserve. Drill up/down on one dimension and then resetting dimension and then drilling up/down does not work.

Prompt Macro and MDX Expressions - topCount with token prompt macro

Not all MDX expressions work on prompt macro. topCount does not work on a data item that has #prompt(,'token')#.

Prompt Macro and MDX Expressions - roleValue with MUN prompt macro

Not all MDX expressions work on prompt macro. roleValue does not work on a data item that has #prompt(,'memberUniqueName')#.

Periods to Date does not work as expected with any expression in the 2nd argument in CQM mode.

It has to be a member for the right result to be returned. Even if the expression returns a member, PTD does not work correctly and returns only one period. This is only in CQM mode.

I will list out a couple of Workspace defects in the next article.


Monday, May 19, 2014

10.2.1 DQM DMR Defects - Part 1

This is going to be quite a long list so am listing out only a few important ones and most of them relate to prompt macros and drills. These are all against 10.2.1 DMR model using DQM mode. I am  not going to explain in detail with images unless someone wants me to since there are too many. I will update the article with Defect Numbers as we receive them.

For most of the defects the report has a crosstab with prompts on the report page to dynamically select rows and measures and a period tree prompt to select the columns. The report has drills enabled.

Level Label Calculation does not work as expected when using prompt macro and drills.

When we use a data item with a prompt macro for crosstab rows to dynamically select the dimension, have drill enabled and have a query item to get Level Label using the prompt macro data item the level label does not get captured consistently.

When we switch between dimensions in the prompt and then drill up/down the level label data item does not get reflected.

To disable member and data cache at report level for testing, query is set to use DB only and Use Local Cache is set to no. This causes charts to work incorrectly.

Report has a period tree prompt with chart and crosstab displaying periods selected in the prompt. With Use Local Cache set to No, selection of periods in the tree prompt returns "No Data Available" message.

Prompt Macros with Data Item names in Value Prompts do not work

If the value prompt Use Value is set to a Data Item name rather than a level then drilling up on one of the dimensions and then re-setting the dimension prompt to another dimension does not have any effect.

Prompt Macros with Filter Calculations in Value Prompts do not work

Same as above except if the use value is set to include a filter expression then re-setting dimension does not work.

Drill and Prompt on same hierarchy - Drill up returns to selections made rather than following dimensional hierarchy.

Select 2012, Q1 2012, Q1 2013 and run report. Drill down on 2012. Drill up on Q1 2012 takes you back to 2012, Q1 2012, Q1 2013 instead of showing list of years which is confusing to the users.

Long Time No See

Firstly a big thank you to all those who have been following this blog. And sorry for not being able to answer all your queries posted.

Its been a long time since I've posted anything on this blog. That's because I got pulled out to work on another BI tool. I am still working on the same but got pulled back into helping a few colleagues with a couple of Cognos activities. In the past 2 weeks that I have been working on Cognos, 10.2.1 in particular I have come across a  whole lot of defects and thought of at least listing out the same for the benefit of others out there. Most have to do with drills and token prompt macros. Workspace continues to disappoint me in terms of the number of defects related to drill.

 Hope I might be able to spend some time writing up a couple of new articles as well before I lose touch with Cognos.