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.
Gurus :-)
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
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. ;)
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.
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)
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.
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)
coalesce(#prompt('p_a','token','N')#,#prompt('p_b','token','N')#
so the 'N' as a default value should be 'NULL'.
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