We need to somehow allow users to type a ‘*’ in the prompts in WebI, to allow them to indicate ‘All’. We find that the WebI prompts don’t allow
modification…Anyway this can be achieved?
You need to implement 1-many-all logic. This requires that you build pre-built conditions.
Here is a sample
[list]This is a sample of the SQL that can be used in a pre-defined condition
to accomplish “1, many or all” functionality. You don’t need to do this
if it’s 1 or all. You can simply use the “matches pattern” operator.
Unfortunately, matches pattern doesn’t work if the user wants to enter
several items.
(PER_JOBS.ATTRIBUTE3 IN @Prompt(‘Enter Employee Survey Code(s) (“ALL” for
all)’,‘A’,‘Job Information\Add. Job Details Survey Code’,multi,free) OR
‘ALL’ IN @Prompt(‘Enter Employee Survey Code(s) (“ALL” for all)’,‘A’,‘Job
Information\Add. Job Details Survey Code’,multi,free) OR
‘All’ IN @Prompt(‘Enter Employee Survey Code(s) (“ALL” for all)’,‘A’,‘Job
Information\Add. Job Details Survey Code’,multi,free) OR
‘all’ IN @Prompt(‘Enter Employee Survey Code(s) (“ALL” for all)’,‘A’,‘Job
Information\Add. Job Details Survey Code’,multi,free) OR
‘*’ IN @Prompt(‘Enter Employee Survey Code(s) (“ALL” for all)’,‘A’,‘Job
Information\Add. Job Details Survey Code’,multi,free) OR
‘%’ IN @Prompt(‘Enter Employee Survey Code(s) (“ALL” for all)’,‘A’,‘Job
Information\Add. Job Details Survey Code’,multi,free))
I got your syntax for accepting a * for a ALL seach.
Just to clarify my question again…
WebI is not allowing us to actually type a * in the prompts. It just displays a list of values from which a value can be ‘selected’ but not ‘typed’. How do I make the prompts allow a ‘*’ to be typed in them?
Another suggestion:
Create a Universe or UDO object whose syntax (Select) is simply "".
In the BO Reporter module or WebI query panel create a condition as follows:
Object eqaul to (or inlist) Prompt 'Select a value or '’
or
"" equal to (or inlist) Prompt 'Select a value or '’ (here “*” stems from the universe object created in step 1).
If the user enters “" as prompt value then the second part of the 'OR’ed condition will be to true and evaluate to "” = “*” thus effectivly removing the condition i.e. there isno condition, all values will be returned.
The advantage of this method is that it can be re-used for any (dimension) object in the universe !!
Another suggestion:
Create a Universe or UDO object whose syntax (Select) is simply "".
In the BO Reporter module or WebI query panel create a condition as follows:
Object eqaul to (or inlist) Prompt 'Select a value or '’
or
"" equal to (or inlist) Prompt 'Select a value or '’ (here “*” stems from the universe object created in step 1).
If the user enters “" as prompt value then the second part of the 'OR’ed condition will be to true and evaluate to "” = “*” thus effectivly removing the condition i.e. there isno condition, all values will be returned.
The advantage of this method is that it can be re-used for any (dimension) object in the universe !!
Two more things that may need clarification:
1.the second condition ("" equal to prompt…) is being created by re-using the first prompt (Select from a list of prompts)
2. the syntax for the "" object in Oracle is ‘*’ i.e. using single quotes. A parse will fail because it doesn’t reference a table, but you can disregard this. However in the advanced properties you should specify that this object can only be used as condition.
An example of how a generated SQL with this method looks like is:
SELECT
OBJ_M_DOC_pend.M_DOC_C_NAME
FROM
OBJ_M_DOCUMENTS OBJ_M_DOC_pend
WHERE
(
OBJ_M_DOC_pend.M_DOC_C_NAME = @variable(‘Which Docu’)
OR ‘ALL’ = @variable(‘Which Docu’)
)
I think I may not have explained my problem clearly…I’ll try again…
I have got a condition object set up with the syntax suggested by Steve & Wolfgang. Now when I pull this object into the ‘Conditions’ pane of WebI and click on the ‘Run Query’ button, the subsequent prompt that appears does not allow me to TYPE * in the prompt. The prompt is only a list box from which I can select but not TYPE anything. This is the problem on which I want help.
Hi, I realize this is an old topic. I wonder if you ever got this question answered or resolved your issue. I am running into the same situation. I have a predefined condition set to free. It will allow me to either select from values or manuallly enter a value in full client, but it will only allow me to select from values in Webi 2.x. If you can help I would appreciate it!
Not sure if you found what you were looking for yet…If not, check out the following link under Designer:
You need to modify the list of values presented to the user in WEBi by creating a custom LOV that also allows the option of ALL. This LOV - together with the where clause of an ascociated prompt - will provide you with a solution.