Hi,
I am trying to get all the values selected when a user types ‘All’ in a prompt given in a DATA PROVIDER. Can anyone help me out here ?
Please note that I am not talking about the ‘@Prompt function’ given in the universe. I know that if we are using the '@Prompt" we could write the function
‘All IN @Prompt(, , ,)’
Thanks for your help,
Sudha
SudhaP (BOB member since 2007-04-11)
prompts in data providers, that is where you choose to type in the prompt text in the query panel condition pane., use the @variable syntax in the BO SQL. it is not possible to combine a user supplied value of ALL without:
A/ Manually modifying the generated SQL and setting the “do not regenerate SQL” check box when you do. This has the disadvantage that you will lose the modification if you ever change any of the query panel options.
B/ Put the condition with the code to support the user typed value of ALL in the UNIVERSE. this has the advantage that you will not have to worry about losing the mdification to the SQL panel in reporter.
If you want BO to insert all the values in the List of values for the object being prompted into the SQL at run time. There is no option available to do this unless you employ the SDK to amend the SQL before refresh. and you will have to decide whether you are using hand coded lists of values (only available via @prompt) or universe supplied lists of values. each type of list i handled in a different way in the SDK.
G.E.Davies 
(BOB member since 2003-01-20)
Thank you very much for your help !
I do not have preveleige to modify the universe. So, I want to try the first option you gave which is changing the SQL in the sql tab.
Could you also please help me with embeding the user entered ‘all’ value into the @variable function ?
Thanks,
Sudha
SudhaP (BOB member since 2007-04-11)
THE PROMPT SHOULD BE SOMETHING LIKE THIS
( ‘ALL’ IN @Prompt(‘2. District Office # or ALL:’,‘A’,‘Custom prompts\cp50DO’,mono,constrained)
OR
@Select(Custom prompts\cp50DO) IN @Prompt(‘2. District Office # or ALL:’,‘A’,‘Custom prompts\cp50DO’,mono,constrained)
)
santosh4bo 
(BOB member since 2006-11-20)
Thanks for the answer. But as I mentioned before, we are not considering the @Prompt function for this problem.
Please suggest me anything else with out making any modifications to the universe.
Thanks,
SudhaP (BOB member since 2007-04-11)
You can manually code @variable for @prompt either will work, the syntax for @variable is simpler. Just drop all the extra @prompt stuff from the example “sushlsan” gave you.
G.E.Davies (BOB member since 2003-01-20)
Thats a great idea to manually code @prompt in @variable. But to get the proper syntax, I am writing the @Prompt function just like ‘Sulshan’ gave. My function in the object properties of the universe is like this:
‘ALL’ IN @Prompt(‘EnterYear or ALL’,‘A’,‘Time period\Year’,multi,free)
It is parsing ok and the query in the reporter is also working for the user input ‘all’. But when the user wants to select a value from the LOV, it is saying ‘No data to fetch’.
I don’t know where I am going wrong. Please help me again.
Thanks,
SudhaP (BOB member since 2007-04-11)
Hi Devis and sulshan,
I finally got it all worked out. Please ignore my previous post. Thanks for your help.
I am pasting the code below for other BO learners. Here’s my prompt-
(‘ALL’ IN @Prompt(‘EnterYear or ALL’,‘A’,‘Time period\Year’,multi,free)) or
(Calendar_year_lookup.Yr IN @Prompt(‘EnterYear or ALL’,‘A’,‘Time period\Year’,multi,free))
Thanks,
SudhaP (BOB member since 2007-04-11)
Forgive the newbie question, but why couldn’t you simply use a wildcard prompt? I’ve dealt with reports that have conditions such as:
<Emp ID> Matches pattern Prompt ('Enter employee ID, or '%' for All:')Would that not work for date values?
ctimmers (BOB member since 2007-03-27)
Hi Sudha,
Your formula works fine, But we need to type manualy “ALL” in prompt
('ALL' IN @Prompt('EnterYear or ALL','A','Time period\Year',multi,free)) or
(Calendar_year_lookup.Yr IN @Prompt('EnterYear or ALL','A','Time period\Year',multi,free)) will it be possible All should come one among the list of values ? 
If i select “ALL” it should display all the Lov’s
Thanks !!
simi (BOB member since 2007-10-09)
HI,
I tried the codes you guys posted here in this page. Everything is working fine(I’M ABLE TO GET ALL THE VALUES WHEN I TYPE ALL) but when I choose indiviual values from LOV it says “No data to fetch”. I donno whats wrong with my code. Could you please help me out.
Below is the code which I have tried:
(‘ALL’ IN @prompt(‘Select value(s):’,‘A’,‘Compressor\Model’,multi,free))
and I also tried:
(D_COMPRESSOR.MODEL IN @prompt(‘Select Value(s):’,‘A’,‘Compressor\Model’,multi,free))
when I use this I’m not able to get values when I type “ALL”, Instead here I’m able to choose individual values.
Thanks,
Sandy.
SPKumar (BOB member since 2012-02-02)
Please Ignore my previous post.
I have got it worked.
Thanks
SPKumar (BOB member since 2012-02-02)
(BOB member since 2008-05-12)