Using either or condition in WEBI

system · July 29, 2008, 11:35am

Hi All

I have arequirement where I should provide the option for user to enter ID and once he enters the ID the report should fetch the other values based on ID.

I have done this using promt. Now One more criteria they were asking for is
There should be a prompt on Name and if user enters either of them (ID , Name), based on that the report shd fetch values but not on both.

For ex: if he enters ID as 10 then it should not ask for entering Name, and the report shd fetch values for ID. If he dont know the ID , then user shd enter Name, so that based on name, the report shd run. but in any case it shd not ask user to enter both values.

Any Luck?

Thanks in Advance
Anu

Asmitha (BOB member since 2008-06-06)

system · July 29, 2008, 11:38am

There is no direct way to get the desired effect.
You can however make use of the ‘All’ keyword option for both the prompts.
If a specific ID or IDs are entered then ‘All’ should be passed for name prompt or vice versa
Read on syntax for ALL

.

haider (BOB member since 2005-07-18)

system · July 29, 2008, 12:17pm

Hi,

Which version of BusinessObject Web Intelligence are you using ?
because what you can do is that you create 2 optional prompt on your report, one with the ID and the other one with name.
In that way the user can fill the field he wants and judging by that you will get what you want on your report.

I hope this will help you.

Best Regards,
Cédric Paumard

DeskI & WebI Developer
BI Toolkit - Offices in UK and Holland
http://www.bitoolkit.com

[bitk]Cedric (BOB member since 2008-03-10)

system · July 30, 2008, 9:28am

Hi

Thanks for reply. I am using BOXIR2.

Optional prompt is not in XIR@ i suppose. If so How can i do that?

Pls suggest me

Thanks
Anu

Asmitha (BOB member since 2008-06-06)

system · July 30, 2008, 9:41am

Nope only in 3.0. If you search for optional prompts on here you will find plenty to help you… .

Mak 1 (BOB member since 2005-01-06)

system · July 30, 2008, 10:56am

Okey Anu,

Do it this way, keep default value for both the prompts as ‘*’ that means everything will be selected for both the prompts.

There will be an And condition between both the prompts. So if a user selects one of the values, It will fetch only data common to both the prompts

You can do it either in custom SQL or at universe level

(Table.Name IN @Prompt('Select Name or 
enter * for all Names','a','Class\Object',multi,free,Not_Persistent,{'*'},User:0,) ) OR 
('*' in @Prompt('Select Name or 
enter * for all Names','a','Class\Object',multi,free,Not_Persistent,{'*'},User:0,))

and 


(Table.UseID IN @Prompt('Select UserID or 
enter * for all Users','a','Class\Object',multi,free,Not_Persistent,{'*'},User:1,) ) OR 
('*' in @Prompt('Select UserID or 
enter * for all Users','a','Class\Object',multi,free,Not_Persistent,{'*'},User:1,))

Done.

suruworks (BOB member since 2007-05-21)

system · July 30, 2008, 4:08pm

Hi

Thanks

I have used this and Lov query I changed and to riginal query i added union select ‘*’ from table name

But In lov’s i could see only * and some garbage values.

Could you pls help me on this?

Thanks
Anu

Asmitha (BOB member since 2008-06-06)

system · July 31, 2008, 5:20am

Why are you using LOV for Name and User ID?

There is no meaning of using LOV for it,

I think you better remove it and let only ‘*’ be there in the selection area.

Name and UserID are supposed to be added manually.

Still if you want to put it then send a snapshot so that I can uderstand your problem properly.

suruworks (BOB member since 2007-05-21)

system · July 31, 2008, 9:43am

Hi

Thanks for help!

I have one question:

Could you pls tell me what
"Not_Persistent,{’*’},User:0, --> these things describes in given code?

So u mean to say that No need to change LOV query rt?

Thanks
Anu

Asmitha (BOB member since 2008-06-06)

system · July 31, 2008, 9:47am

Not_Persistent is given, so the prompt value chosen for the previous run will not be stored for the successive runs.

Jansi (BOB member since 2008-05-12)

system · July 31, 2008, 9:50am

Not_Persistent - Clears the last selected values while running the quries previously

{’*’} - puts default values as *

User:0 - defines position of the prompt

No need to have LOV for large sets like UserID or Names

suruworks (BOB member since 2007-05-21)

system · July 31, 2008, 11:57am

Hi suru

Thanks for reply!

I have used the following query:

(view.ID IN @Prompt(‘Select ID or enter * for all Ids ‘,‘A’,‘ALL Option data\ID’,multi,free,Not_Persistent,{’’},User:0,) ) OR
( '’ in @Prompt(‘Select ID or
enter * for all Ids ‘,‘A’,‘ALL Option data\ID’,multi,free,Not_Persistent,{’*’},User:0,) )

I did not do anything with LOV query. it is same and generated by BO.

and when i tried running query at report level with this condition in filter pane, i have got 2 prompts: below

Select ID or &#xD&#xAenter * for all ids *
2.Select ID or enter * for all Ids *

default value as *

I am trying to attach a screen shot, not sure, it is giving some error, but those are the exact prompts firing when i hit on run query

Pls suggest me on this

Thanks
Anu

Asmitha (BOB member since 2008-06-06)

system · July 31, 2008, 12:18pm

okey

try using this

(view.ID IN @Prompt('Select ID or enter * for all Ids ','A',,multi,free,Not_Persistent,{'*'},User:0,) ) OR 
( '*' in @Prompt('Select ID or 
enter * for all Ids ','A',,multi,free,Not_Persistent,{'*'},User:0,) )

I hope it works

n ya my name’s Saurabh…dnt u see the signature blow???

suruworks (BOB member since 2007-05-21)

system · July 31, 2008, 12:54pm

Hi All

Thanks for everyone who helped me on this

I got it running finally. The mistake I have done is i did not put the same prompt message (difference in spaces)

I replaced it and it is working fine now.

Thanks
Anu

Asmitha (BOB member since 2008-06-06)

system · July 31, 2008, 12:57pm

huh…

I am more than Happy…

suruworks (BOB member since 2007-05-21)