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 !
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.