Can someone explain to me what a pass-through query is and how I would create one in Cognos Analytics 11?
Here's the scenario...I have an uploaded Excel file from which I built a data module on top of so I can write a report off of this data module. I need to restrict rows in another query to only show rows where the user running the report's user code is tied to a particular logistics group. For example, I login as "ssmith" and my userID is tied to a logistics group called "BA00". Only show me rows returned in a query if the logistics group value on each of the rows is equal to BA00.
How would I use a pass-through query to access the USR_T table that exists in another DB schema?
SELECT LGST_GRP_CD "Logistics Group"
FROM USR_T
WHERE UPPER(USR_CD) = #sq($account.personalInfo.userName)#
Any help or explanation would be appreciated. Thanks in advance.
Let's say you have 2 queries:
1st query [Query1] is for security and you have 2 columns
user_id | logistics_group
2nd query [Query2] is for data (that will be displayed in report) and has, as example
logistics_group | item_id | qty
You have two options from here:
1. Filter [Query1] with #sq($account.personalInfo.userName)# and create [Query3] with a join between [Query1] and [Query2]
[Query3] is that one you'll be showing in the report and will have only the data that is supposed to be visible for userName
2. In you object paramaters (crosstab, list, etc) create a Master Detail Relationship. If you list uses [Query2], set [Query1] as master and join them on logistics_group. That'll also do the job
EDIT
-------
Back to your original question... (really sorry about that!)
- You can add your USR_T table to your data module and join the excel file with your table
- Or you can create a new query in the report, then click the little hammer to add "Code SQL" and select a different schema so that you can have 2 different sources at the same time in your report
Have fun!
Thanks for the explanation. What's confusing me is that when I create a new query and click the toolbox hammer, I am only seeing the following:
Query Calculation
Data Item
Filter
Slicer Member Set
There is no SQL option as I've seen with other queries I've written.