Substitute values using parameter map

[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]

What is your goal?

[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]

Hi Greg,
Thanks for your reply.