Prompt from FM to be used in RS

[cognostechie]

Hi All -

I have a situation where I am unable to associate a Prompt in FM inside RS.

I have a Query Subject in FM with 'Native' SQL as under:

Select

Package.get_effective_date(# prompt('Enter Date in the Format 01-JAN-2010','Date''01-JAN-2010')#) Effective_Date,
peo.person-id

From

XXCUS_HR_EMPLOYEE peo

This will prompt the user to enter a date before this SQL gets executed in the DB. 'Package.get_effective_date' is a function in the DB which will accept the Date from the user.

It is working perfectly and the data is perfect in Report Studio.

Problem is it creates a prompt of it's own . I created a Prompt Page, created a Prompt and associated it with the existing Parameter that was created because of the Prompt Macro in the Query Subject. That way, it prompts the user using the Prompt that I created in the Prompt Page.

Now the problem - I add another prompt to the Prompt Page and after that it prompts the user for the Date first and then goes to the Prompt Page to use the 2nd Prompt !! So it creates TWO Prompt Pages, one by itself and another created by me. If I delete the 2nd prompt, it again uses the Date Prompt from the Prompt Page and does NOT create iut's own Prompt Page.

Is there a way to make it work, that is, all the Prompts should be in One Page.

Thanks !

[CognosPaul]

Your post is a little confusing to my sleep deprived brain, so let's try mapping it out. I'm changing a few details to make it easier (for me) to read on page.

Query Subject in FM:

Code Select Expand

select whatever.fn(#prompt('repDate','date','trunc(sysdate,'+sq('year')+')')#) from whatever

In RS Prompt page:
date prompt with parameter set to repDate.

Functions perfectly.

Add a new prompt. I'm assuming the values for this prompt also come from the database? If so, that's the problem.

Cognos is attempting to run the SQL in order to retrieve the values for the prompt list. Because it's running before the date parameter is being set from the date prompt, it forces you to select a date in a generated prompt page.

If not, can you tell me more about the second prompt?

[cognostechie]

I think you have a point there.

Anyway, I found the fix. I inserted 'current_date' as the default date in the Prompt Macro and now it puts all the Prompts in the Prompt Page.

You are right..it might have been prompting me for the Date to run the SQL to populate other Prompts.