Sunday, March 10, 2013

How to avoid a few "common errors" that are difficult to be identified in Cognos report authoring

Three errors are identified as common errors, but they are very difficult to be identified when authoring a complicated report. For the sake of easy explanation, three simple reports are created, please download detail report specification for your review when needed
  1. Order misplaced when using union ( version 10.1.1)
  2. Not Fact related Query ( version 10.1.1)
  3. Not database related Query ( version 10.1.1)

Order misplaced when using union

When there are multiple queries unions, such as different period’s union together for crosstab, YTD, HTD, current Month, last month and etc. the sequence of query items can be misplaced. Cognos doesn’t check the query item name, as long as total number of items and type of correspondent item match.

Please see screenshot below
Obviously, query item region and product line are misplaced.

Query 1

Query 2

Query 3

Not Fact related Query

Sometimes, for whatever reasons, all query items in a query don’t relate with a fact table. Even there are some measures, but these measures are defined as fixed value.  Please see sample below

This design could cause many not projectable issues, dependant on framework manager.  The solution is to have a clear join path defined by adding filters.

Not database related Query

Please see query below, it doesn’t work
Error: UDA-SQL-0219

The reason is that the query doesn’t associate with database; it can only work when the function is from Cognos, which uses local processing

The solution is to either use local processing function, or add database related filter

No comments:

Post a Comment