I did do some searching and browsing through a number of pages on this board, but didn’t see anything that answers my question, which is surprising, because I’d think it’s something that many would wish to do.
I have a list of values, lets call the object Site, with NYC and Boston. I’m prompting the user to enter a value based on this object. I wish to add an item ‘All’ so users do not have to multi-select everything to get everything. (most prompts I’d actually implement this for would have many more than 2 values)
If ‘All’ is selected, then in a table based only on Site, I’d want ‘All’ to be displayed, as opposed to all Sites.
Is there a way to force a default of All to populate the query prompt (or a default of NYC or Boston, etc.)
Wow, if I can get all three of these questions answered, I’ll be one happy camper! And this board rocks, so looking forward to seeing what I get back. Feel more than free to provide links as well as advice. Take care everyone!
Did you search for the text ‘WildCard’ and ‘All’ in the list? I am sure there were some threads on these topics. However, below are the answers to all the 3 queries –
Include the table SYS.DUAL in your universe. Create an object in the universe and Call it ‘All Sites’. In the object definition sat ‘ALL’ and select the SYS.DUAL table from tables list.
Now, edit the LOV for the site object. Edit the query and do a union to select the object ‘All Sites’.
This way you would see the text ‘ALL’ in the LOV.
You can mask the objects in the report table by creating a variable that would be based on the UserResponse function. You can say, IF UserResponse =‘ALL’ THEN ‘ALL’ Else
There is no straightforward way. what you can do is run the report for ‘ALL’ and save it. Then publish it to the repository. This way the last run prompt would be shown as default.
Not sure I fully understand your answer to question one, but let me try implementing it first before I ask for more help on that.
Regarding your answer to number 2, my only question would be what syntax to use in order to help BO understand that the userresponse function corresponds to a specific prompt? (ie if there are more than one prompt in the same query)
I am not sure if I would add the Sys.DUAL table to the universe: You might run into more problems then it is worth (think of cartesian product warnings, or maintaining joins/contexts).
Universe objects using the Sys.DUAL table in their definition will not parse in BO Designer, but will work fine in BO Reporter when used with other universe objects, which are based on “real” tables.
Also, Sys.DUAL is used in Oracle, I am not sure which table you would use for DB2, Terradata, MS SQL, etc.
Regarding Prompts and ALL or wildcard: Please, take a look at the FAQ section of BOB.
Alright, here’s where i stand with this. Found some great threads to help me keep on working through my issues.
I’ve got a Site variable, a Site LOV variable (that successfully add’s ‘All’ to the LOV using a union in the sql), and a Site Test variable I’ve been trying to work with in order to have it act like Site, but accept All as a valid choice.
right now, I have a select statement for Site Test with the followiing in it:
SITE in @Prompt(‘enter field’,‘A’,‘Study Summary\Site\Site LOV’,MULTI,FREE) or ‘ALL’ in @Prompt(‘enter field’,‘A’,‘Study Summary\Site\Site LOV’,MULTI,FREE)
think if I hit edit for the variable and check out the sql, it has site.site selected.
This doesn’t seem to be working at all. Any idea what I might be doing wrong?
The problem is probably in the ‘Study Summary\Site\Site LOV’ syntax. There should only be one . The part in front is the class name, which is unique within the whole universe so it doesn’t need to be “fully qualified” at all. I bet if you change it to ‘Site\Site LOV’ it will work.
A friendly user on another forum replied this this advice…
" Hi the first thing u have to do is the make sure the SQL for the LOV is right
it should be
select distinct site from table
union
select 'All from dual
After this make an object in the universe with the prompt definition. eg name PROMPTSITE @Prompt(‘select Site or All’,‘A’,)
then make a predefined condition which you will use in the report condition
@Prompt(‘enter Site or All’,‘A’,‘Site\SiteLOV’,MULTI,FREE)
is the select statement for promptsite right now - exactly, no equal signs or anything else. I have tested SiteLOV and that works fine. However, I should mention that when parsing the promptsite select statement, i get the following:
Parse Failed: ORA-00907: Missing Right Parenthesis
god - think i’m throwing in the towel. thanks for all your suggestions, but i cannot get this thing to work! unless I could get a working example, which in BO would certainly be like pulling teeth i’m sure.
B
got it to a working point, satisfactory point at least Found it easiest just to make the users use * as a wildcard, and put a select statement in the where clause of a custom condition. It’s easy and it works. Sorry for getting frustrated all, and thanks for the help!
(BOB member since 2002-06-20)
(BOB member since 2002-09-19)
so sorry I’m throwing out one disjointed suggestion after another. I’ll bookmark this to review this evening…
Found it easiest just to make the users use * as a wildcard, and put a select statement in the where clause of a custom condition. It’s easy and it works. Sorry for getting frustrated all, and thanks for the help!