Cognos Prompt page - Passing different Parameter values to single parameter

[cognosun]

Hi Gurus,

I have 2 parameter names(ex:?p_a? and ?p_b?) linked to 2 prompts, and there source column is same, and here the user selects only one parameter/Prompt at any point of time, on the prompt page.

Now the problem is I need to pass the selected values, from either of the prompts (?p_a? or ?p_b?) to the third parameter( say......?p_c?).

I mean, finally I need to have values in my third parameter ?p_c? ONLY. 

You can ask me like why I'm not using ?p_c? itself, as parameter name to the above 2 prompts, but I have a challenge not to use like that :-)

Can anyone help me how shall I do this....

Many thanks in advance.

[cognosun]

Gurus :-)

[BigChris]

Your question is really too vague to answer. What are you trying to do?

The closest I can get to making sense of your post is that you might be looking for something like a cascading prompt whereby the user selects values for p_a or p_b

[hespora]

The way I understood the description was: We have two prompts, each feeding a different parameter, but both on the same query subject. Then, OP wants to combine values in both parameters into a third parameter, and then use that for filtering.

*Why* that is the goal, I think we fail to understand without some background. ;)

[tjohnson3050]

More background would definitely help us to help you.

I suspect the coalesce function might be of interest to you.  This returns the first non null value from a list of values.  Normally this is used with outer joins, however I have used it with prompts in the past.  Note use the full prompt macro syntax to give default values of 'NULL'.

coalesce(#prompt('p_a','token','NULL')#,#prompt('p_b','token','NULL')#)

This should return the value selected for a when it is selected, or b when a is not selected.  This assumes prompts objects set to 'no' for required and no default value assigned.

[cognosun]

Hi tJohnson,

You are close :-)

Actually i'm calling a PLSQL Function from Cognos FM, so, ideally i need to pass only one parameter say ?p_a? ( which is optional, and default value for it is N, which is set in the function).

But i'm facing some issues in my prompt page ( where JS is used) for "two" of the "select and search" prompt controls, whose source is same column from db ( say 'country name' prompt) and here I ended up using TWO DIFFERENT PARAMETER NAMES for the same country name column ( and these parameter names are ?p_a? and ?p_b?).

But as i told above i need to pass only one parameter to the function call, so how shall i make ?p_a? and ?p_b? as one parameter, which are optional parameters from Cognos ( i think this sentence formation is simple)

[tjohnson3050]

One way that might work is to have one query find out which prompt value has been selected, and pass that to a detail query that is using the parameter to pass to the PLSQL Procedure.  A master detail relationship between the two should handle it.

Master Query has a single data item that is a coalesce of the two prompt parameters ?p_b? and ?p_c?.
This Master Query can be set on the query property of a page set or a page.

Detail query will be your report query with a filter that has a data item that equals ?p_a?

Set a master detail relationship that points the coalesced data item in the master down to the parameter in the filter in the target report.

[cognosun]

coalesce(#prompt('p_a','token','N')#,#prompt('p_b','token','N')#

--------------------

Actually ur first solution is working for me...it's giving me output if I send value in p_a ( as first not null)

It's running forever if I send value in p_b (as second not null)

[tjohnson3050]

coalesce(#prompt('p_a','token','N')#,#prompt('p_b','token','N')#

so the 'N' as a default value should be 'NULL'. 

[SabihaN]

Hello,

I am building list report in Cognos 11.0.9. My requirement is to create text prompt  whose value will be search in 3 columns i.e col1, col2, col3. if value enter in prompt matches to any of  above mentioned columns, it should return records.

I am using select & search prompt and query expression   

col1 in (?test_param?)

or

col2 in (?test_param?)

or

col3 in (?test_param?)

However, it is not working correctly , only returing data based on col1 in (?test_param?).

Also, i have tried all the solution mentioned in this thread.


Any guidance will help.

Thank you,

Sabiha