Parameter Maps are not available within A Data Module. How can we get parameter map like functionality using a Data Module source?

A parameter map is accessed via the Cognos Macro language, essentially a look up table – converting one value to another.

There are 3 types of parameter map

• Manually entered.
• Imported from a file.
• Populated from a query.

Regardless of how the parameter map is built the result is the same, a table that looks like:

The syntax to access the parameter map is the same:

$ParamMapName{'key}

For the example shown:

#$FiscalPeriodToEndDate{'20/21 AP11'}#

will return 2021-02-28.

Parameter Map functionality, looking up a value to find another value, can be replicated using the QueryValue macro function.  QueryValue is only available against a DQM datasource - but as the purpose of the post is to replicate Parameter Maps within Data Modules we should be ok! The QueryValue is an extremely powerful function with many parameters, consult Cognos Help for the full feature set of QueryValue. 

queryValue ( value_expression [ , filter_expression [ , options ] ] )

To replicate parameter maps we only need the basic functionality of QueryValue. If we think of it in terms of a Parameter Map then we will use it like so:

#QueryValue( ReturnExpression, KeyFilter )#

Looping back to the Framework Manager Parameter Map shown at the beginning if this post. This was a parameter map created from a query called Calendar, with the column FISC_MONTH_NAME used as the key and end date used as the value.

The data within the CALENDAR query looks like:

The QueryValue function works against a query. We can use QueryValue against this same CALENDAR query.

To reproduce the parameter map function of #$FiscalPeriodToEndDate{'20/21 AP11'}# we could use QueryValue:

#queryValue(

   "[Data Layer].[CALENDAR].[END_DATE]", 
   " [Data Layer].[CALENDAR].[FISC_MONTH_NAME] = '20/21 AP11' "

)#

We want the macro to return the END_DATE where the FISC_MONTH_NAME = 20/21 AP11. This also returns 2021-02-28. In this example I have used a mixture of double and single quotes as I need my filter to contain a string. You could do 'quote the quote', but I find mixing double and single quotes makes the syntax more understandable.

The example shown is for Framework Manager like item names, which are [NAMESPACE].[QUERY].[ITEM]. Within a Data Module items follow the format of QUERY.ITEM. If this were in a data module, or a report based on a data module, the equivalent QueryValue syntax would be:

#queryValue(

   "CALENDAR.END_DATE", 
   " CALENDAR.FISC_MONTH_NAME = '20/21 AP11' "

)#

In the majority of my Cognos solutions, parameter maps are query based. With QueryValue I now find myself no longer having to create parameter maps within Framework Manager and just using QueryValue instead. QueryValue removes the step of populating a parameter map with a query just to look up a value. If the underlying query changes then I can simply edit the QueryValue syntax. With a parameter map there is no way to edit it if I need to point it to a different query sometimes you find yourself having to recreate them.

Just like parameter maps can be based on the results of other parameter maps, QueryValues can also be nested within each other. That is the result of a QueryValue can be used to populate either the Key or Filter parameters.

What about manual Paremeter Maps?

This is all good for parameter maps based on a query, but what about Parameter Maps which are manually created, or built from a file?  This can be achieved using Cognos Analytics Upload file functionality. Admittedly for manual Parameter Maps there is an extra step to create a file containing the values. Upload the file to Cognos Analytics  and import that uploaded file as a data source into your data module. You can then use queryValue on the file.

What about Default Values?

Parameter Maps can have a default value, if the key is not found then the default value is returned. In the Parameter Map shown the default value is 1900-01-01.

For the example shown: #$FiscalPeriodToEndDate{‘Marc'}# will return 1900-01-01.

QueryValue returns the literal null when the result is empty. We can substitute this Null with a default value. To get a default value the syntax is:

There are other ways to achieve the same thing, such as the macro substitute function, but it’s nice to show the new macro let command in action!

Parameter Map Multiple Values

In the help guides IBM states that parameter map keys must be unique to consistently retrieve the correct value. However, the IBM DQM red book ( DQM Red book) shows a method of a parameter map returning multiple values using the @ symbol:

The macro queryValues function can be used to return multiple values.