If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Substitute values using parameter map

Started by saumil287, 02 Feb 2012 12:10:12 AM

Previous topic - Next topic

saumil287

hi All,
I am having a query subject containing ocrcode and ocrname and other columns.
I created a parameter map containing ocrcode as key and ocrname as value.
I created a new querysubject with new column for displaying ocrname using parameter map.
But I am not able to achieve the same.
I tried adding exp #sq($New_Parameter_Map{'[TestB].[OOCR].[OcrCode]'})#
where new_parameter_map is parameter name and [TestB].[OOCR].[OcrCode]
I also tried other possible exp but unsolved

Your guidance will be appreciated

thanks in advance

blom0344


saumil287

Hi
I am having say qs A which contains code and nm and qs B containing code.
since qs B is an order table which is used miltiple times and many alias have been created so in order to display name every time qs B is connected to qs A.
So in order that directly substitute a code with name I am trying for parameter map.

The first stmt I wrote was a mistake mentioning only 1 qs

thanks in advance

cognostechie

I think a parameter map would work best when you have small set of values to deal with. In your case, it might be better to join to the QS A because QS A would use the index of the table (presuming the table has an index on the Code). A parameter map can read data from a Query Subject but it will probably not use the index to find the value of the Code to return the Description.  My 2 cents !

saumil287

hi
Thanks for your reply

Can u guide me in syntax for substitution of values.
I tried using the above syn in diff formats and also the macro func Substitution but didnt work.

Thanks again

Greg

Unfortunately it is not possible to lookup a value in a parameter map using a query item as the key such as you are trying to do. This is because macros, including parameter map lookups, are evaluated first before the query engine executes queries.

The best suggestion I have for you is to create a database table to store the lookup key and name, import it as a query subject and create a join to the oocr query subject.

If there is some reason that you can't do the above and really need to manage the lookup values in FM then you could look into creating a virtual table using Cognos SQL inside a data source query subject. Another alternative, only if oocr is the only query subject that needs to lookup these values, would be to create another query item in oocr for ocrname that uses a case statement.

saumil287