Cognos and Me

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.


Friday, October 26, 2012

By-Design Defects

How often have you been told that something that you think is a defect is actually by design? Doesn't that mean that the design is flawed then?

One such by-design-defect as I would like to call them was brought to my notice when some of my colleagues were trying to create a Reset Button functionality using only drill through to avoid using Java Scripts (since IBM does not support them) in 10.1.1.

What happens is Cognos retains the parameter values between the drill through runs even if you do not set the target parameter values in the drill through options. And this happens only in Cognos Viewer not in Report Studio Viewer.

The developers were informed that this was by design and were asked to use Javascript as a work around to achieve the functionality.

Have you come across any more of such by-design defects?

Friday, October 12, 2012

Defect - Burst Report Views through Event Studio

With 10.1.1 developers no longer see the "Burst Report" checkbox when trying to burst report views through Event Studio.

Create Jobs to burst report views and then use the Job inside of Event Studio to work around this.

Monday, September 17, 2012

Cognos 10.2 - Prompt API announced

IBM has announced a fully documented and supported Prompt API with Cognos 10.2 allowing report authors to now safely use the supported JavaScript API in their reports to control prompts.

From the IBM website:

The prompt API enables report authors to write JavaScript applications to customize the standard prompt functionality available in IBM Cognos Report Studio to query and set user selections, validate typed-in values using patterns, and set default values using expressions or query data for faster report authoring and a better report viewing experience.

Here's the link to the article:

http://www-01.ibm.com/common/ssi/cgi-bin/ssialias?infotype=AN&subtype=CA&htmlfid=897/ENUS212-282&appname=USN#toc

Limitation - Descendants MDX Function against Sets/Hierarchy

Though it is documented on the IBM Support Site that Descendants against Set functions may result in incorrect outcome, wish there was a way we could work around this limitation.

Some reasons why we may need Descendants function against a Hierarchy name rather than a member is:
  • When we use TM1 as the source and have security set up such that a user has access to the lower level members of a dimension hierarchy but not to the root member of the hierarchy (which is perfectly valid in our business scenario).
  • And we do not have named levels set up since we deal with unbalanced hierarchies, again valid when dealing with Accounts dimension.
  • And we have multiple alternate hierarchies set up against a dimension.

So in this case we are limited to using the descendants function against the hierarchy name rather than the root member which does not result in the required outcome.

So one of the work-around in this case is to use other MDX expressions like Descendants with Except to build the set required.

Tuesday, July 31, 2012

Defect - Drill on MDX expression based members

Another one for the defect list and this in my opinion impacts a feature that is most important and that was working in earlier releases.

The defect relates to drill up functionality on MDX based lowest level members.

To re-create the issue:

Create an MDX based query calculation, say children of Cooking Gear.













Drill up on TrailChef Water Bag                                        










Drill up on Cooking Gear         
                                    







Drill down on Golf Equipment        
                                  







Drill down on Putters       
                                          






Drill down on Course Pro Putter   
                                    




Drill up on Course Pro Putter 109110           

                       











We see children of Cooking Gear (the list we started with) instead of Course Pro Putter.

Seems like the drill functionality in 10.1.1 has a lot of defects not just in Business Insight but in Cognos Viewer as well.

Thursday, July 5, 2012

Defect - Conditional Variables based on Members + Drill + Render Variable

There are times when you would want to use conditional variables to hide/display drill through links in a crosstab. If your condition is based on levels its easy to achieve this using levelNumber roleValue. If your condition is based on member names then there is a possibility that you may run into errors under the following scenario:

  1. Report has a conditional variable to drive the display of links and
  2. Report has a ParamDisplayValue layout calculation that has Render Variable set on a completely different conditional variable and
  3. You drill up/down on the member and then you select a value in a report page prompt and re-run the report you may run into the error: RSV-RND-0089 Failed to evaluate expression
This does not happen if you remove the render variable property on the layout calculation or you select prompt values and then carry out a drill activity

To avoid this, create a caption() member, set that as a property of the row member and modify the condition to be based on this member.


Thursday, June 14, 2012

Concurrent Query Execution

Even though Concurrent Querying is supported and can be defined in Report Studio, it isn't necessary that concurrent querying takes place behind the scenes. This depends on the number of processes set up and the values configured for the various concurrent query execution parameters.

By default Cognos executes queries sequentially unless the property is set in the report and also provided concurrent execution is enabled in the environment.

To enable this in the environment, at the minimum the below parameters must be added to the report and/or batch report services:

RSVP.CONCURRENTQUERY.NUMHELPERSPERPROCESS,
RSVP.CONCURRENTQUERY.MAXNUMHELPERSPERREPORT

More details on the parameters can be found in the Administration and Security guide.

The NUMHELPERSPERPROCESS sets the number of query execution helpers. It needs to be kept in mind that each query execution helper spawns a new data source connection.

The MAXNUMHELPERSPERREPORT sets the maximum query execution helpers available per report.

In an environment that has:

4 processes (2 high and 2 low affinity)
NUMHELPERSPERPROCESS set to 2
MAXNUMHELPERSPERREPORT set to 2

The NUMHELPERSPERPROCESS allows for a maximum of (2 + 2 + 2) * 4 = 16

If a report has 4 queries set to concurrent execution then this report will consume all query execution helpers with 2 queries being executed concurrently even though 4 have been set to execute concurrently.

Thus it isnt' necessary that a query marked for concurrent execution will execute concurrently.

Wednesday, May 23, 2012

Defect - Duplicate Queries generated by Cognos

I seem to be on a ride running into numerous defects over the last couple of months. The latest one being similar duplicate queries generated by Cognos in a particular scenario.

As can be seen from the screenshot, Cognos generates 2 queries with no differences and both having the same name.




This happens in the below scenario:

When you have a singleton object before all other Objects in the report.
And when you have Prompt Macro as the first filter in the first object of the report.

And if you have value prompts after the singleton but before the first chart/list/crosstab object, then these queries get duplicated as shown below:


When each query counts towards performance, this isn't good.

So the work-around is to have a Required prompt without macros as the first prompt in a list/crosstab/chart object. If one doesn't exist create a dummy required filter to get around-this.


Using Text Wrap property instead of setting Widths

A lot of beginners in Cognos try to set list column widths to fit data to avoid text wrapping rather than using the White Space property. This is probably because the property name is misleading.

So to all those beginners who didn't know, set the White Space property of a list column to No Wrap instead of trying to set fixed widths if the intention is to avoid the data displayed in the column from wrapping.

Wednesday, May 2, 2012

Defect - Advanced Drill Link with Higher level Default prompt selections?

We have report with a value prompt on the report page and its default value is controlled by a prompt macro. The report also has a Pie chart and a Crosstab both based on different queries whose data items are linked for drills.The report is based on a cube.

The observation is that when the default value for the prompt is set to a higher level of the dimension say the year level in a Time dimension and when a user selects any value from the drop down and does a drill on the pie chart categories member the drill links don't work in the Crosstab only the pie chart gets updated.

This happens only the first time a selection is made. Selecting any other value and executing a drill on the pie chart categories and then selecting the same value and then executing a similar drill does not re-produce the above error.

This also does not happen if you execute the drill without making any selections. Any selection later doesn't re-produce the error.

So basically it seems like this happens for the  first selection made and when you have a higher level member set as the default through a prompt macro. Once a drill is executed and then any number of drill executions later doesn't re-produce the error.

This has been observed in a Pie - Crosstab combination and not in a Stacked Chart - Crosstab combination.

Some screenshots against Go Sales Cube to demonstrate the observation against a simmplified requirement:

First run of the report:


On selection of January 2004 from the drop down and then executing a drill up on Pie chart for January 2004:


The drill does not get reflected in the crosstab.

On selection of Q1 2004 and then executing a drill up on the Pie chart:


The drill is reflected in the crosstab.

When January 2004 is set as the default, first run of the report:


On selection of February 2004 and then executing a drill up on the pie chart:

The drill is reflected in the crosstab.
 

Thursday, April 12, 2012

Cognos Insight

To add on to what has been mentioned in the previous article, this new component from IBM allows users to pull in data from a number of sources and not just TM1 as I had wrongly assumed.

From a licensing perspective this requires Advanced Business Author role or above for BI and TM1 contributor role or above for TM1. The tool integrates with Cognos 10.1.1 and TM1 10.1.

Here's what else I could find on IBM site:

You have all the capabilities of the Insight Standard Edition plus:
• Import data from existing Cognos reports
• Share files using the Cognos Connection portal
• Publish and distribute content to the server for managed deployment
• Extend content created with Insight to other Cognos tools and interfaces like mobile and Web
• Use Insight as the interface to contribute to a managed planning process


Since this is a stand-alone desktop tool, I am curious as to how it fits architecturally with other Cognos components and how requests are executed. Since it supports in-memory analysis is this in-memory analysis handled on BI server or on the client machine? And if it uses a TM1 engine behind the scenes, how do requests flow when reports are used as sources?

If anyone has any inputs, questions, concerns as always please let us know.


Wednesday, April 11, 2012

All New IBM Cognos Insight

IBM recently released a new component IBM Cognos Insight bundled with TM1 10. This is not related to Business Insight and is a separate Dashboarding component. I am yet to read up and evaluate this so expect more on this from me in later articles. The demos look cool though.

The tool seems to be more inclined to supporting TM1 10 and has an in-memory analytic engine with write-back capabilities. Visually too this looks so much better than our Business Insight.

But as always with any new release there are bound to be disappointments so the wish list here too is huge.

More to follow.

Tuesday, April 3, 2012

Multi Tab Portlets cause CPS PAG Error

When using Multi Tabs portlet as the first tab in Cognos Connection, users might run into CPS-PAG-4801 error. The error is not consistent and refreshing the page renders the multi-tab portlet without error. THis does not happen when the multi tab portlet is placed after any of the other tabs.

This has been logged as a defect and is due to authentication information not being passed on to the portlet when used as the first tab.

The only work around is to use a HTML Viewer and have the URL for the Multi Tab portlet included in the HTML Viewer.

Monday, March 5, 2012

Optimized Master Detail relationships with DQM, not really

Here's an extract from IBM site on optimized master detail relationship using DQM (http://publib.boulder.ibm.com/infocenter/cbi/v10r1m0/index.jsp?topic=%2Fcom.ibm.swg.ba.cognos.dyn_query.10.1.0.1.doc%2Fc_master-detail.html):

Optimized master detail relationships in reports

Master detail relationships allow you to deliver information that would otherwise require two or more reports.

You specify a relationship between the query in the master data container and the query in the detail data container. For example, you can combine a master list with a detail chart. The list can contain product lines and the chart can show details for each product line.

With the dynamic query mode, master detail relationships are optimized. IBM® Cognos® pushes the master query as a separate edge to the detail query. Therefore, instead of sending one detail query to the data source for each master value, only one query is sent for HTML, PDF, and Microsoft Excel spreadsheet software output formats.
That is not the case though when you check the profile tree logs. Master detail relationships, still execute the old fashioned way with DQM. I had this confirmed by IBM as well that this is as per design.
I am posting this so developers don't get misleaded by the article. Wish IBM removes the misleading article.

 

Wednesday, February 8, 2012

Business Insight - Limitations

A lot has been said and written about Business Insight. While you will come across a lot of articles highlighting the good, jazzy and the positive features of Business Insight, I wanted to focus on some limitations in the use of Business Insight.

Business Insight being a new technology/component from IBM, it has a lot of bugs that need to be fixed before we can benefit from the use of Insight. Most of these bugs that I have come across have to do with drill ups/downs. I consider these to be really important issues to be addressed as the primary shift in datasources is from relational to dimensional. The issues have been listed out in the article:

Another big drawback that I find is with respect to prompts. Though Slider and Check-box List prompts are available, Insight lacks the availabilty of value prompts, date prompts. These need to be created inside of reports and users need to drag them into the Insight reports to make use of them. Value prompts when dragged into Insight are not jazzy enough to blend with the other components on the screen.

Non Auto-Submit Value prompts when dragged into Insight cause individual Apply/Cancel buttons to appear for each such prompt.

The available Slider and List Box prompt has another disadvantage. When these prompts are set inside Insight, users need to select a list of values that need to appear in the prompts. Though this makes sense when you think of Insight as an ad-hoc tool, how often do you come across users who are willing to spend a few minutes to create an ad-hoc dashboard. More often than not, the requirement is to have pre-authored dashboards and in such cases this limitation is huge. New values don't show up unless the user goes in and modifies the prompts each time.

Another disappointment is you cannot convert the whole dashboard into different formats. You can only convert individual widget into various formats. So much for being able to take a snapshot of your dashboard.

Even though Insight was meant to be an ad-hoc dashboard environment but gone are the days when dashboards were static. Users require ability to slice and dice their key metrics data. With the line between Dashboards and Reports are getting blurred at some places it would be great to have more prompting capability in Insight.

Insight is a CPU intensive tool. Heavy dashboards can take a while to render.

Having dished out about Insight limitations, I need to say, I really liked the slider feature. Wish we had such components available in RS. It would be great to have some of the jazzy looking Active Report Prompt components also available in RS.

Your thoughts on Insight?