Professional reports in Cognos 10.2 followed by the rules of success

Today, I want to present a pretty cool cognos report based on the rules of success (http://www.hichert.com/de/success).

The data in this example is based on a Adventureworks SQL Server 2008 R2 Cube.

As you can see the rules of success are a great way to optimize the report output for readiness and understandability.

Succes report in Cognos 10.2

Optionally you can combine this with the BICommentor which gives you the opportunity to post back any textual comments (utf-8 - including line breaks) and to re-display these context specific comments back in any report.

In this example the procedure works as follows.

You click on edit.

illustrates a writeback functionality within Cognos

Now you can enter a comment. (as above)

Click then on "save".

Don´t worry. The status says updated but currently we do not see the output yet.

So, lets re-run the report and see what happens.

Great! We see our comment. By the way. The same comment could be seen in different reports. The level of context granularity is self defined.

But happens if I run the same report in pdf? Will the comment go away? Let´s have a closer look...

Great thing!

Also interested?!

Just let me know. 

If Ansi outer join SQL leads to different results on Informix…

During the migration of a reporting platform with native Informix SQL Syntax (especially the informix outer extension syntax) to an ansi compliant system (for example Cognos) you probably may encounter problems. There is no 1:1 equivalence for the outer extension syntax in informix to ansi sql. Therefore we have to workaround those cases to elimante different results.

But what is exactly the difference? A short example should help you to understand the issue with outer joins.

Our data (2 Tables):

Simple Ansi left outer:

SELECT x.c1, x.c2, y.c1, y.c2
FROM x LEFT JOIN y ON x.c1 = y.c1

Result:

Now the same example with a filter applied on the outer table leads us to the issue.

Informix syntax (what we want to re-write in ansi):

SELECT x.c1, x.c2, y.c1 AS yc1, y.c2 AS yc2
FROM x OUTER y
where x.c1=y.c1 AND y.c2='a'

Not (!) the same as (ansi left outer) :

SELECT x.c1, x.c2, y.c1, y.c2
FROM x LEFT JOIN y ON x.c1 = y.c1
WHERE y.c2='a';

Result (Ansi):

Correctly translated ansi syntax:

SELECT x.c1, x.c2, derived_y.c1 AS yc1, derived_y.c2 AS yc2 FROM x
LEFT JOIN (SELECT c1, c2 FROM y WHERE c2='a') AS derived_y ON x.c1 = derived_y.c1;

Result (Informix):

Here we are. As you can see we make use of the derived table syntax to workaround this issue. Unfortunately Informix 9.x does not support the derived sql syntax. In addition: I always recommend to make use of the latest current informix server version where possible because many ansi sql issues (mostly performance) were adressed in the version 11.x and above

Sorting in Business Objects vs. IBM Cognos

By migrating Reports from Business Objects to Cognos you should keep in mind that the sort algorithm differ a bit. Especially string fields with non breaking spaces will be sorted different in Cognos. Upper and lower letters do net get considered. This also applies for local processed group functions (min, max) etc.

\Data\cqe\RTModels is growing very large

For most customer the Cognos 8 runtime model directory which is only a temporary buffer pool grows very large (tons of gigabytes) after a while. Unfortunately there is no way to prevent the directory from growing. Usually this directory is designed to clean itself but depending on the usuage this cleanup does not work in most cases.

So, can this directory be cleared manually?

Yes, you can flush the directory, but the Cognos 8 Dispatcher and Content Manager Service has to be shut down at this time. Make sure that you delete only the contents (including subdirs), not the directory itself.

Cognos 8 Report Studio report based drill through

Unfortunately, it is currently not possible to decide on a per report base which user is able to do an drill through and which not. So, if you want to limit the access to the drill through functionality per report AND by user or by row we need to do a trick. The drill through report uses usually parameters which can be (if you know the syntax) also feeded by url.  By the way, drill through by direct url is even faster than the internal function of cognos 8, since Cognos 8 has to validate the request before executing the drill through report. So, if you need to save some seconds during drill through this is also an option. Details on how to open a report by url and feed the required prompt parameters can be read in the Cognos 8 BI Report Studio Guide. Before using such an feature, make sure that a misuse of the parameters isn´t possible (data security)…

Report picklists and data security

Many Customers are using report picklists (with static or dynamic values) to limit the access of data. By doing this please keep in mind that the only way to secure your data access is to insert a filter in the FM Model and not in the report.

Do you know why? ;-)

Multiple CSV Output in Cognos 8

On common problem in the daily business e.g. during the migration from Business Objects to Cognos is the export of multiple csv files from one single report. In full clients this is easily possible, heavily used and well accepted (for example by Business Objects Users) 

In Web applications which are based on a HTTP GET Request it is unfortunately impossible to accomplish such a feature since it is only possible to support one single file transfer through one single HTTP request. Therefore IBM Cognos 8 does by design return only the CSV File for the first query (alphabetic order). All other Queries will be ignored.

So, what is the recommend way to solve the users problem?

One possible way to do this would be to create a forward page which is able to decide based on the requested output format if the user should be forwarded to the normal report layout or a CSV helper page. The helper page itself will ask for the meaningful name or description of the query which should be exported.

After answering the query question the user will be forwarded to a simple csv report and is able to save the output. For using this functionality the requesting IBM Cognos Consumer has to have the right to execute reports with embedded HTML containers, otherwise the user will get an error message saying that his user rights are to low to execute such kind of report.

Overall this solution is  an acceptable workaround for reports which can´t be fully exported to CSV in IBM Cognos yet. Nevertheless if the output is needed for an daily progress it could be an option to save the csv output with an IBM Cognos SDK program on a central fileshare in the network rather than exporting the data manually. For sure also the use of the xml output format instead would be an good workaround.