Saturday, September 21, 2013

How to avoid problems with parameter maps

Similar to prompt token, parameter maps will be used via the Macro form. The same behavior is that both prompt token and parameters map will specify value before sending to query engine. The difference is that the value for prompt token is from Prompt, while the value from parameter map is from specified key. There are two ways to setup parameter map:
1) Set up parameter maps based manual map
2) Setup parameter maps based on query.
The second way provides a very flexible solution to get parameter setup, as query can be derived from database table, or complicated query. In other word, we can use database to control parameters.

This document is intended to list two major issues with parameter maps based on query. These two issue can cause a lot of time to resolve.

The query used for parameter maps needs to be published
To setup parameter maps based on query, we need to publish that query subject along with package to be used. Otherwise, you can test it in framework manager without any problem; however, when used in report, you will get the default value.   We need to hide such query subject in package.
In addition, the query subject can be very complicated. Such as use complicated T-SQL  for SQL server, and use PL/SQL for oracle database.

Both parameter maps definition name and keys are CASE SENSITIVE
One of most used cases for parameter maps is to apply it in security, such as


In case when Cognos uses single sign on to pass user session for data security, $account.personalInfo.userName  is session variable from active directory.

ParameterMapsName is Cognos parameter map, which is actually based on a query. The definition name ParameterMapsName is case sensitive; and parametermap keys are case sensitive.

As parameter maps keys are from database, where the value from database table may not be case sensitive. However, when used to identify value from parameter maps, the keys have to make sure that the value from database and the value from active directory must be consistent.

Otherwise, we’ll be experience problem such as 1) screen froze; 2) An error occurred while performing operation 'sqlPrepareWithOptions' status='-56'; or 3) internal error.

No comments:

Post a Comment