Trying to Use a Stored Procedure Without Having to Use a Prompt

[Artby]

Hi,

We're trying to use a Stored Procedure in Cognos 8.  We have a Query Subject for it defined and working in Framework Manager. 

When we defined it in Framework Manager we used a prompt to value the input parameter that we pass to the Stored Procedure.   Something like this:
#prompt('p_IDnumber', 'int')#

When we reference the Query Subject for this Stored Procedure in Report Studio we don't want to have to manually enter a prompt value.  We want to have the value come from a query to another table. 

We want this: Query 1 returns an ID number.  We want this ID number passed to Query 2.  Query 2 uses the Query Subject from our Stored Procedure and returns the first and last name from the Stored Procedure to go with the ID number from Query 1. 

Does anyone know how to do this?  Everything I've seen requires the input to our Stored Procedure be entered manually via a prompt.  We want to avoid using a prompt.  We want to use a column from a table.

[CognosPaul]

Just saw this...

This is possible with a master detail relationship.

Create a list from Query 1. Each row returns an ID number. Unlock and drag another list inside that list. Drag in the output from the stored procedure into the sub list. Select the sublist and set the master detail relationship. Query1.ID --> p_IDnumber

The end result is the stored procedure running for each row in the master list.

Now in order to do this invisibly click on each list, remove the class and remove the header. On the master list add ID to the properties and remove the text item from inside the list column. On the detail list add the output to the properties and remove the text item. Now the sp will run for each record without either list appearing on the screen.