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

Parameter Map Syntax

Started by Steve, 11 Sep 2023 08:36:34 PM

Previous topic - Next topic

Steve

IBM's documentation doesn't provide any examples..

I have a Query Subject with the data like:

City Code      Zip Code   
-------------------------

  1000            90638
  2000            91765
  3000            91786
  4000            91909

The table in the DB that this Query Subject uses doesn't have Territory Code but the users have that in an EXCEL file.

EXCEL File:

Zip-Code     Territory Code
------------------------------

91909          2
91765          1
90638          2
91786          3

Now the objective is to display Zip Code, City Code and Territory Code in the report along with other columns.

I create a Parameter Map with the Zip Code as the Key and Territory Code as the Value.

I though I could create a Query Item in the Query Subject that would give me the Territory Code

Normally the syntax is #sq($Parameter_Map_Name{'ABC'})# but in this case I have to replace the hard coded value ABC with
the Query Item for Zip Code. So if the Query Item is [Database Layer].[table Name].[Zip_Code] then what would be the correct syntax in the Business Layer for this?

Thanks !



   

MFGF

Quote from: Steve on 11 Sep 2023 08:36:34 PM
IBM's documentation doesn't provide any examples..

I have a Query Subject with the data like:

City Code      Zip Code   
-------------------------

  1000            90638
  2000            91765
  3000            91786
  4000            91909

The table in the DB that this Query Subject uses doesn't have Territory Code but the users have that in an EXCEL file.

EXCEL File:

Zip-Code     Territory Code
------------------------------

91909          2
91765          1
90638          2
91786          3

Now the objective is to display Zip Code, City Code and Territory Code in the report along with other columns.

I create a Parameter Map with the Zip Code as the Key and Territory Code as the Value.

I though I could create a Query Item in the Query Subject that would give me the Territory Code

Normally the syntax is #sq($Parameter_Map_Name{'ABC'})# but in this case I have to replace the hard coded value ABC with
the Query Item for Zip Code. So if the Query Item is [Database Layer].[table Name].[Zip_Code] then what would be the correct syntax in the Business Layer for this?

Thanks !

Hi,

I don't think this is a workable approach in this case. Parameter maps are addressed via macro code, which gets evaluated and run *before* regular query execution happens. This essentially means you can't use result values from your query (such as the contents of a data item) to feed into your macro - the timing is wrong.

I would suggest you upload the Excel file containing the Territory info, create a Data Module, add the package and the uploaded Excel file into this, then join them in the data module. You can then base your reports on the data module and have access to all the required data.

Cheers!

MF.
Meep!