BI Data Analytics

Issue: Authoring a drill-through, selecting the ellipses beside"Select a report"shows Team Content instead of Target report path. In Cognos 10, when authoring a drill-through in Report Studio, after selecting a report, any subsequent request to select a report would bring the author to the target report directory (Public Folders > Folder1 > Folder2 > report). The same action in Cognos Analytics 11.0 brings the author to the root directory Team content. If the target report is several levels below Team content, the author has to navigate down the folder structure every time requiring many additional mouse clicks and consuming time. Having the dialog bring bring up the target report's directory would save considerable time in authoring drill-through definitions Solution: Raised RFE with IBM https://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=114618

1. When running report, we got error for oracle driver missing. It was restored by IBM to resolve the issue  2. Non-interactive Reports drill through to Excel doesn't work. An interim fix was applied to Cognos 11.0.8.  3. Excel output based reports were taking more than 5 minutes to run. Root cause identified was Image folder access/path was not enabled and causing report to stuck for 5 minutes. IBM resolved it via PMR.  4. When calling reports in Dashboard as web page url, drill through defined via image not working. It was working fine in 11.0.7. Issue is under investigation with IBM.  5. Data sets can not be scheduled with dependencies to each other thus limiting it's usage to sumarrize data over base data sets. They can't be added to Job or Event Studio to define auto refresh. As per IBM, This is planned for future release.  6. Reports name can't be changed dynamically based on parameters. There is RFE raised for this enhancement. 7. Audit database t...

In previous version of ICA Cloud Environments, there was error when try to browse image url.  Now, instead of it you can create an extension to upload custom images. Once this extension has been imported, images are available in all Reporting and Dashboarding interfaces.  The way to access them within reporting is to use an ‘image’ object and double click on the object to launch the image picker dialog.

Radio Button Style Issue When Report style is changed to  11.4 style, for radio Button Value Prompt ( option), additional text of "deselect" starts appearing in the report. We need to update property for deselect text to émpty string to remove this. Drill Through Parameters Navigation: When there are many drill through parameters, as you scroll down and select parameter values, it goes back to top of parameter box. it's kind of inconvenience and increase time to update drill throughs.

Below is the list of issues identified with Cognos Analytics 11.0.7 and hoping to get resolved in next release: Cognos Analytics 11.0.7 with Internet Explorer: In Cognos Analytics keyboard "Enter"  doesn't work and only happens with Internet Explorer and only if the ENTER key is used to enter the password for the deployment import. Using OK button works and using Chrome also works without issue. Cognos Analytics 11.0.7 Report drill through parameter window When you define drill through and there are many drill through parameters to pass, a scrollbar starts appearing to scroll down. When you click on bottom parameter to update, after each selection, it goes back to Top. This is quite inconvenient to update parameters.  

When we use Pie Chart property to show percentage instead of absolute value, there is no control over number of decimal places. If user wants to see % in decimal point, we can use conditional style to set format of values to percentage with decimal points. This way absolute value is converted in percentage. Percentage of percentage will still show values in percentage but also retain the decimal value. This way there is no change to underlying calculations in query.

Cognos Analytics 11.0.7 release Dashboard issues: Logo Image doesn’t work with relative URL ( this was raised in 11.0.6 as well). I have to provide full path:-      https:// /bi/images/test_Images/my_log.jpg Pdf download is not possible for Dashboard Drill through to Cognos reports and passing parameter is not possible from Dashboard Limit of 3000 records in dashboard Canvas per widget – should be customizable similar to Active Reports ( Use Cases for Exploratory Analysis - If we want to list down operations -overall and link to detail reports  using dashboard) Infographics icon are used only for summary Visualization. When they are dragged over other charts, they don't represent data values. There is no dynamic calculations available for changing image based on other filter ( say gender) to show different images.

I have faced below issues in working with Cognos Analytics 11.0.7 and causing delay in deployment of applications:- If report is set to non-interactive mode, any drill through defined on image gives firewall rejection error. This happens when "Open in new window "property is selected. IBM has raised defect and aiming to resolve it in next release. In Interactive mode, drill through defined in list gives same error though  image drill through works. In Interactive mode, excel icon drill through opens a new window ( Home Perspective) along with excel output when option to "open in new window " is selected. While clicking on drill through definitions, I don't see report path it's referring to. Instead it shows Team Content folder. This issue looks similar to 11.0.6 save as option where we raised defect. If we are using html items to embed reports ( containing iframe link) and to show as tabbed reports and if reports are having drill through in same win...

Cognos Analytics on Cloud 11.0.6 RFEs raised for features not available on cloud and/or defects identified: http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=108702 https://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=104089 https://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=108313 https://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=104089 http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=108701 http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=108899

Below IBM document explains behavior of copy/move or overwriting impact of objects:- https://www.ibm.com/support/knowledgecenter/en/SSEP7J_11.0.0/com.ibm.swg.ba.cognos.wig_cr.doc/c_gtstd_copy_move_items.html If you copy and overwrite an existing report, the copied report ID and links replace those of the existing report. In this case, you might need to update links, such as links to job schedules. If you move and overwrite an existing report, the existing report ID and links are maintained. In this case, references to the moved entry are broken. However, this doesn't work in all cases and we see drill through links broken in some cases. Best approach in such case is to use cognos deploy method where you update reports in separate folder in same environment and import it to destination location. You can create single export and import tasks to use for all such updates.

Environment: Cognos 11.0.6 on Cloud Issues/Observations: Some time when you right click on Package to see it's properties, it is missing. On Laptop, sometime report output is cutting some content on header or footer end. On zooming out, problem resolves.

In IBM Cognos Cloud, we don’t have access to Extension and Views creation. We can use Theme customization for a role to get similar effects of having Portal Tabs. Create Role and provide require Capabilities. Add groups and members. –> Manage/Accounts Area In  Team Content, create separate dummy folder with no content in it. Purpose is to restrict folder navigation for users in designated role to dummy empty folder. Create Wrapper Report with one html item per page. Use Report properties to show page as Tab. Embed Main Portal Report link ( copying from Property menu – Embed). Modify frame property to have no border and width- Height to be 100% Update Theme to have logo and color of Navigation and app bar to make them transparent . You can download default theme spec.json and update navigation and application bar's color, logo path to return blank, Name of Theme.  In Manage – Select Role Properties – Customization Add Dummy Folder be the folder User can nav...

Below are the list of issues faced during upgrade to ICA 11.0.6. Got error when trying to rename report Navigate to the report and click the 'More' icon beside its name. From the menu, select 'Properties. From here there are two methods to change the report's name: a) Click on 'Advanced' to see more properties. b) Click 'Set...' beside 'Languages' Error : Firewall Security Rejection  2. When running multiple reports, get  error Data source adapter error: java.sql.SQLRecoverableException: No more data to read from socket and on clicking Ok, the screen doesn't go away. It needs to be closed and go to actual report 3. Save As option Save As Option takes to main Team Content Folder instead of folder where the report was residing. 4. List within List is causing caching issue and On report prompt page doesn't reflect changes to outputs. 5. On reports using multiple pages, page down causes Query Service Internal E...

Problem: When we try to right click on query to see Tabular data, there is pop up comes for requesting "Allow Clipboard Access". The option is already set  in Report Studio Property to allow access to clipboard. Solution: 1. Go to Tools -> Internet Options.  2. Go to Security tab.  3. Select “Internet” zone, then click on “Custom level…” button.  4. Scroll down to “Scripting” section (at the bottom few).  5. Under “Allow Programmatic clipboard access” option, check or select (tick) Enable. Default action for this option is Prompt, which cause the pop-up message whenever there is any attempt to access the Clipboard.  6. Click Ok twice to exit from Internet Options.

Below are the properties to set to keep column size and width with text wrapping: Column Merge issue  http://www-01.ibm.com/support/docview.wss?uid=swg21674298 Set the advanced server property RSVP.EXCEL.XLS2007_COLUMN_WIDTH_CONTROL to TRUE for both BatchReportService and ReportService services.        Row Merge issue http://www-01.ibm.com/support/docview.wss?uid=swg21440556 RSVP.EXCEL.XLS2007_ALLOW_WRAPPING_SINGLE_CELL also to be set to TRUE  for both BatchReportService and ReportService services.   Once done, you can set any column width to be fixed and see same result in Excel as well as HTML. Please note that above properties will not be honored if you have unchecked Grouping repeating cells Report property in Report Properties menu. 

If anyone is using IBM Cognos Analytics Version 11.0.0.0 to 11.0.5.0, there is security bulletin released by IBM to notify vulnerability where sensitive information can be revealed in its logs files:- http://www-01.ibm.com/support/docview.wss?uid=swg22000095 The recommended solution is to apply version 11.0.6.0 as soon as viable.

Below are some of the changes in LCM for IBM Cognos Analytics and should be used to identify reports to assess efforts ( Planning Phase) may be needed for upgrade process from IBM Cognos 10.2.2:- There is no reporting or exporting of results to capture differences and failures, progress. Prompts fill out using  'Capture Prompt Values' not working in IBM Cognos Analytics version. Reports having images as references to templates and using Layout component reference need to be validated for any issue. Validate impact where Java Scripts are used  such as multi select auto cascading implementation

Build test cases to detect below common Reporting issues Any blank column on the report for all selection Rows repeating for same dimension and attributes Double addition for amounts for same dimension for different selections Report filtering data for all selections ( default, individual, multi select) Download options working for all selections Query properties and naming conventions followed  Output getting distorted in pdf/excel or parameters, standard logos are not displayed

1. Create text Box Prompt for parameter and set default value to 1 ( we are going to use it for default selected).  Name it PromptOpt. 2. Make it's property visible to No. 3. Add HTML item before it with below script and check box . < script text= "javascript"> function handleClick(cb) { var fW = (typeof getFormWarpRequest == "function" ? getFormWarpRequest() : document.forms["formWarpRequest"]); if ( !fW || fW == undefined) { fW = ( formWarpRequest_THIS_ ? formWarpRequest_THIS_ : formWarpRequest_NS_ ); } var f = getFormWarpRequest(); var list1 = f._textEditBoxPromptOpt; list1.value = 1; canSubmitPrompt(); SetPromptControl('reprompt') ; } < / script> < TABLE class=tb style="WIDTH: 150px; BORDER-COLLAPSE: collapse" cellPadding=0> < TBODY> < TR class=tableRow> < TD class=tableCell onclick="handleClick(this)"> < DIV class=block>< input type="...

Below new features I found interesting from IBM Cognos Analytics 11.0.6:- Users of Report content can view Data Lineage without going into edit mode. This features enable consumer of report to see source of data and also helpful for report author to see the source while testing the report. Now you can create Report views for Active reports In interactive mode reports, users have ability to freeze or pin column headers so that they stay in view when scrolling. It is available for List object only. It can be enabled by setting the 'scrollable area height' property.  Formatting applied on the List are also maintained. When creating a report, to add a scroll bar to a list prevent the list to accompany multiple pages use 'scrollable area height' List property to enable it. Now we are able to modify queries built in report studio using data modules. In the new version of Cognos Analytics (Cognos 11), sorting is an out of the box feature. Authoring is no longer requ...

In Cognos 10.x and 8.x , this was the simple to handle on Cognos Connection or via Repot Studio interface.  In Cognos 11.0.6, You  need to load classic view (Cognos Connection) to go back to this interface to change the package. In Cognos Cloud environment, we have single environment for Development and UAT. In such cases, when we move content from Dev folder to UAT area, we need to switch to classic mode to change the package.

There is nice feature of Data Player added in IBM Cognod Analytics. We can use data player feature in a Dashboard to show trends transition over time filter. This will auto play the filter to next selection and refresh charts on dashboard. This works fine with fast data retrieval queries. However, if the query for chart is slow compared to filter query, it can result in chart data not changing while data player changes to the next selection. There is need to control the time the data player transitions to have the consistency between Data Player filter and output.

Add html item after the radio button. Add below:- < STYLE > Div.clsCheckBoxList{ border-style:none; } <  / STYLE >

Many times it's necessary to create multiple connections within Data Sources; for example, DB migration, multiple changes to keep in different DBs but same schema for parallel project. However, in IBM Cloud we have one single environment for UAT and Development so as soon as we enable it, it's not transparent to users and it adds additional step to select Data source and test. To resolve this, we can create separate group for each UAT users and change permission to specific Data source connection to deny for Read/Execute/Traverse. With this, they won't be prompted for Data source connections while running a report.

Environment: Cognos 10.2.2 Database: Oracle Issue: When report is run in html or excel 2007 format, dates appear to be fine whereas when run in Excel 2007 data or csv format, date appears to be in time stamp format. Cause & Resolution: Oracle dates are interpreted as Time Stamp in Cognos. You need to cast them at model or report level to date. On layout, date formatting was applied hence Date format appeared fine on Excel 2007 and HTML ouptuts.

Issue Symptoms: After installing Life Cycle Manager, when clicking on start window, we get error that path not specified. Also, clicking on URI and trying to create project gives HTTP Not found error  Solution Create  "projects" folder under  C:\apps\ibm\cognos\LifecycleManager\data Make sure have full access on  C:\apps\ibm\cognos\LifecycleManager Restart  IBM Cognos Lifecycle Manager Startup and you should see below for successful start Using CATALINA_BASE:   "..\tomcat" Using CATALINA_HOME:   "..\tomcat" Using CATALINA_TMPDIR: "..\temp" Using JRE_HOME:        "..\bin\jre\7.0" Using CLASSPATH:       "..\tomcat\bin\bootstrap.jar" How to start and stop LCM Windows -> Start menu - navigate to the Lifecycle Manager entry and select " IBM Cognos Lifecycle Manager Startup" option.   Default: http://localhost:4797/LifecycleManager Lifecycle Manager s...

Below are issues/observations noted for reports upgrade: Repeater behavior changes In Cognos 10.2.1, it was working similar to singleton and was able to show single value ( if there is only one value for all rows). This was specifically used for Language based drill through link. After upgrade to Cognos 10.2.2 FP7, the report kept running forever in interactive mode. Upon running in batch mode, it was revealed that it repeated values for each row. To solve this, either convert data item to report expression item or use singleton (to get first value). Extract function data type In 10.2.1, it is being treated as string whereas in 10.2.2 FP7 it's treated as number. Due to this additional function used such as TO_Number resulted in warning in higher version. The argument at position 1 of the function 'TO_NUMBER' is invalid in filter/slicer expression ' TO_NUMBER(extract (YEAR,current_date))' of query 'Prompts', expected one of the following t...

Error: An unexpected exception occurred: com.cognos.xqe.data.values.VarcharValue incompatible with com.cognos.xqe.data.values.ArrayValue Cause: We have condition to have default selection "ALL". ([Fund Code] in (?pFund?) )or (?pFund? = ('ALL')) After removing it, error was rectified: [Fund Code] in (?pFund?) and make filter optional.

Problem: After upgrade to FP7, noticed error generated from a crosstab having crosstab space nested within cross tab nodes:- Found an internal error: '!mapSuccess - reportName=test_FP7 - edgeName=11 - edgeOrdinal=1 - tuple=([TBdimension_4].[TBhierarchy_4].[all].[test data].[1].[1].[0], [TBdimension_5].[TBhierarchy_5].[all].[data value for test data within data).'. Solution: It seems that FP7 version was looking for hierarchy information for crosstab spaces as there were no measures assigned to that row. Just added one of values as nested crosstab node above the crosstab with default measure and hide it. This worked to stop generating error and report started working again without loosing display format needed.

IBM has provided version numbers at below link:- http://www-01.ibm.com/support/docview.wss?uid=swg21343355 By looking at FM - About menu, it is not apparent what's fix pack applied. It just shows 10.2.2 You can check cmpls.txt file on FM install path and open in text editor. locate below line:- C8BISRVR_UPDATE_version=C8BISRVR-AW32-ML-RTM-10.2.6107.28-0 As per above link: 10.2.6107.28 - 10.2.2 FP7 You can also check instlog files to see this version number in file name itself. just after update.

Request: The rich text boxes in Source system allow for the inclusion of tables, but they are not exporting as tables when you read as text item. Other requirement is to have it in pdf format as well. Environment: Cognos 10.2.2 Solution: We can use Rich Text item available in Cognos Report Studio Drag list in page Unlock report and drag rich text item in list column change source of text item to data item value select text field from DB Run report in html/pdf and validate

Issue: While moving to Cognos cloud, report fails to pass Date parameter. XQE-DAT-0001 Data source adapter error: java.sql.SQLDataException: ORA-01830: date format picture ends before converting entire input string Version: Cognos 10.2.2 FP3/FP7 Database: Oracle 11g Cause: Cognos is treating Oracle parameters as Timestamp and cast/to_date function at both end doesn't work. Solution: Create data item: (extract(year, ?As Of?) * 100 + extract(month, ?As Of?) ) * 100 + extract(day, ?As Of?) 2. Pass data item value instead of Parameter value 3. In target report, data item should have below ( to use for filtering) and/or displaying: case when (length (?Date?)=8) Then to_date(substring(?Date?, 1,4)||'-'||substring(?Date?, 5,2)||'-'||substring(?Date?, 7,2),'YYYY-MM-DD') Else to_date(?Date?,'YYYY-MM-DD') end

Issue: Native SQL based report against Oracle DB giving error: XQE-DAT-0001     Data source adapter error: java.sql.SQLSyntaxErrorException: ORA-00907: missing right parenthesis Symptoms: Reports have multiple native sqls against Oracle DB. It is using Master details, Page set as well Cause: Cognos 10.2.2 FP7 upgrade from FP3 Analysis and Solution: Run query in Tabular data and see error coming from specific queries On analysis, it was found that there is comment statement in the end of query and it's not able to handle in FP7. It should have omitted it for evaluation. After removing the commented line, error disappeared. Comments: If we use standard comments /* */  or we move last commented line to in between query, it is able to parse sql correctly and send to Oracle as well. This is possible issue in supporting Oracle comments for this scenario in FP7.

Issue:  For Value Prompts Radio Button/List Boxes shows side scroll bar. Cause: Report was using 8.x styles Solution: Change Report properties to use 10.x styles