IBM COGNOS down grading a report studio report from 8.4 to 8.3

This is a unique situation where we down grade a report from 8.4 to 8.3. Strange isn’t. There are some users who just don’t want to update themselves. On one side we are with the COGNOS 10 beta customers and on the other side we have these handfuls of Clients who are on 8.3 but want all the new reports that were built with 8.4.  We have manually downgrading these set of new reports and give them out through a package separately created on a 8.3 environment.

Anyways, here is a simple example with screen shots on how to do it.

1.        Open the report in a report studio (8.4)
2.        Go to tools and copy to clip board
3.        Open a notepad and paste
4.        On 8.4 reports we will see something similar to what is shown in the first screen shot.
5.        Delete everything after the language specification up to the ‘layouts’

Your report XML should be something like screen shot 1 initially and end like screen shot 2, now copy the data from the notepad and open it in 8.3 report studio.

Your report should working on 8.3 environments now J

IBM COGNOS 8 Report Prompt Page taking too long to load

Before going in to the solution we need to know few things:

1.      Prompts Reconciliation ---- COGNOS ensures the parameter definition matches the parameter usage. Parameters are defined in filters and calculations.

2.       The resulting parameters are used in prompts. A parameter definition contains several key items

a.       Cardinality – The number of input values that can be supplied to the parameter

b.       Discreteness – Whether an input value defines a single value or a value range

c.        Optionality – Whether a parameter is required or optional in the context of the filter or calculation.

d.       Data type – The required data type that is expected in the context of the filter or calculation to match other referenced data items or constants. These types can be one of Numeric, Date, Time, Date

e.      Time, Interval, String, or Member Unique Name (MUN).

Here is an example I found in the IBM COGNOS documentation ----- In order to perform prompt reconciliation, IBM Cognos 8 examines the queries to determine which parameters exist and their characteristics. The larger and more complex the queries, the longer this process can take.  A customer report in IBM Cognos 8.1 with over 200 queries has been noted to require over 40 seconds to show the first prompt page. Most of this time was spent on prompt reconciliation. 

(Content taken from IBM COGNOS knowledge forums)

I had similar issue, what I saw was that the query subjects (this is particular report) were very complex and had about 20 tables joined under it.  But the main culprit for this problem was due to a join that was created on the report with 2 such (complex) query subjects that are totally different with a dummy value and this new query had couple of filters. Crazy mistake and yes, the prompt was taking 40 seconds to load for us J

Solution --- > We created new metadata / query subject that was a combination of the 2 query subjects that we were joining i.e. basically we moved the join from the report to the framework and all the filters that we used on the report were made optional on the framework. (They were generic filters and so we did not have any issues). Now the prompt page comes up in a flash. :)

IBM COGNOS Error CM-REQ-4313 / CM-SYS-5043 (CMOBJECTS cannot be locked)

This usually occurs when the deployment folder does not contain the destination package for the exporting object.

Let me put an example here, while creating a package we put in a ‘PROD Export’ as the destination file so that we can modify it before the release(depending on the kind of release). If the deployment folder does not contain this ‘PROD Export’ zip or was not created during the export object creation; we get this error. As the error explains ---‘Content Manager failed to create an object’. (This was because I set up things in a way that I can blindly build the package every time but due to maintenance we had to remove all the files in the deployment folder and I completely forgot about it)

Anyways, the screen actually talks you about the exact error.

Solution, redo the process with the export object name so the IBM COGNOS can build a new package as we don’t have an existing file here.

Finding the issue before the error occurs --- In the configuration > Content Administration > go to the properties of the export object > click on ‘export’ property and check if there is a Deployment Archive folder specified.

IBM COGNOS Cube error ---- The supported limit of categories in the model has been exceeded.

Recently I was asked about a situation where a cube would run out of space. There are scenarios / wrong design where I think the cube would usually go out memory other than excess data:
1. Too many date columns J
2. Unwanted drill downs ( should always be user centric)
3. Improper usage of pivot property for the columns that are relative
4. Creating a cube from a framework / report that is build on a wrong data schema

A good example I can relate to here, is the default date column that the transformer creates with date, year, quarter, month. In my case, the end users don't really use quarters in their reporting. When I removed quarters for couple of date columns, the cube size drastically changed and even the performance improved.

In the first image here, the error was due to large amount of data and excess date columns on the cube. The design was just ridiculous. Once I removed the quarter it did solve the issue, temporarily. Sometimes it is better to educate the end user and make them use a canned report based on the cube rather than letting them use the analysis studio or query studio on the cubes. Just a thought.

Best practices ---- it is always good to have a session bi-weekly or monthly with a group of end users and design  / update the cubes according to the changing needs. (with the business modules being so aggressive now a days, I am confident the requirements change from day to day). I understand people are reluctant to change but that is where proper education comes to place. I have seen the impact of proper education to a business process and approach. Business Intelligence tool like IBM COGNOS is not only designed for reporting needs but also for decision making.

IBM COGNOS 'sqlPrepareWithOptions' staus='-69' Error --- explaining the issue right!!!

I was working on a report where we needed some SQL based query subject that had to be joined in with metadata based query subject. Usually, I write the SQL queries on an editor; check it once and then put it on the report studio but this was one of those cases where I am trying to finish up things.

In the query I wrote, I had used   'max (case …..) 'Function; basic syntax on SQL query is that when we use an aggregate function we need to have a 'group by' function at the end. There is nothing new in this, the only thing that surprised me was that COGNOS error -69 details, explained the issue perfectly. I was so impressed, don't know why but made me happy. J

IBM COGNOS Report Studio List / Block with a scroll bar without Java Script

COGNOS Report Studio has a million options to create a report; but we only explore a few depending on our needs. 

In most of my reports (8.3 / 8.4 / 10 versions) I try to use some java scripting to the lists or graphs in order to make them look better. The problem that I face is, when the list moves to the next page, the scripting does not work as efficiently as it should. Check the image 1 & 2.

So the solution to this is;

1. Put the list  / graph / crosstab into to a block

2. Go to the properties pane and select the 'Size and Overflow'

3. Choose 'Use scrollbars only when necessary' 

It is as simple as that.

 

F Y I ---- Screen shot based on junk data.

IBM COGNOS Java script for auto refresh of a report in a COGNOS viewer / bringing the concept of live feed to COGNOS report studio.

I came across this script in the IBM COGNOS documents that lets you automatically refresh the report data depending on the time interval that we have selected.  This script would especially be useful for someone who works on transactional data or someone is trying to work on alerts on a report. (Alerts on a report can be achieved through Metric Studio but this script here helps us achieve the same in a report studio.)

Let me put in a story line so that we have a concept that we can sellJ.  A good example for this would be to calculate the check-in and check-out times. Here our goal is to minimize the avg wait time the guest has to wait. So we can build a report with guess no check-in, check-in time, checkout-time, time taken to process, expected process and delta (difference between check-in and check-out).  Just like the RMV or customer care call where they tell us the avg time.

The screen shot above explains a different scenario where we are counting no of guests attended by each employee. A team lead or a manager has a target for the day (for each team member) and he can keep track of their work with this live feeder report; no need to run the report every now and then. We can set the auto refresh time (which is 2 minutes in this case) and project the report on a TV in the roomJ. Taking things positively, the manager can help out the employee who might be stuck with one guest for a long time.

Implementing the report; copy the java script image ‘Script for COGNOS Report Studio Auto Refresh ‘ and add it to the report studio in a HTML tag from the toolbox.(I am not able to upload the script file will do it ASAP)  Now create your regular report.

 TIP --- Add footer to the report with timestamp; this way it will let the user know when the report was last run.