BusinessObjects Board

Prompt -ALL

building semantic layer
#1

hi Everyone,

I would like to select ALL the values in the prompt. To achieve this, I did the following steps. but somewhere I mised out…

In the universe,Object properties, Edited the SQL and written the following statement with the existing one.

I used Union, select ‘ALL’ from dual

and enbaled the do not overritten the SQL check mark.
Finally, My SQL look like,

select distinct ‘deptname’ from department
union
select ‘ALL’ from dual

Then, I have created the condition in the condition pane of the Universe, I have enetered the following query.

(@Select(Department\deptname) IN @Prompt(‘Enter the Department Name’,‘A’,‘Department\deptname’,mono,free)) OR
(‘ALL’ IN @Prompt(‘Enter the Department Name’,‘A’,‘Department\deptname’,mono,free))

Parsing is Ok.

This is what I have done in the designer.

In the reporter, I have created the propmt for the deptname, while selecting the values, I have seen ALL, and choosed the same.

When I am running the report. I am getting “No Data Fect Message” (My Department table has rows)

Where I have missed out? Is there anyting wrong in the query?

Is it the way to get all the values or any other way is there?

Please help me…

Thanka and Regards,

N.Selva

selvaips (BOB member since 2005-05-03)

Setting 'ALL' in the prompt
LOV and prompt
#2

Try changing your predefined condition to (replacing mono with multi):

   @Select(Department\deptname) 
   IN @Prompt('Enter the Department Name', 'A', 'Department\deptname', multi, free) 
OR 
   'ALL' IN @Prompt('Enter the Department Name', 'A', 'Department\deptname', multi, free)

Keep in mind that most DBMS are case-sensitive when it comes to text (UPPER vs. lower case).

Andreas :de: (BOB member since 2002-06-20)

#3

Sorry for the delay in response and thank you very much for the prompt response.

Still the problem persists even i changed to Multi from Mono and I have checked the Case sensitive also. No issue on this.

Regards,
N.Selva

selvaips (BOB member since 2005-05-03)

#4

Try to use the keyword ANY as follows:

@Select(Department\deptname)
IN @Prompt(‘Enter the Department Name’, ‘A’, ‘Department\deptname’, multi, free)
OR
‘ALL’ = ANY @Prompt(‘Enter the Department Name’, ‘A’, ‘Department\deptname’, multi, free)

ohuibers :netherlands: (BOB member since 2004-03-05)

#5

Check the generated sql in the report if there were other conditions out of your prompt.

PeterC :hungary: (BOB member since 2005-02-14)

#6

Do you get any data back if you select any other valid dept name rather than “ALL”?

reemagupta (BOB member since 2002-09-18)

#7

This works for me in SQL server, although two differences are, my query does not use a union and the dual table (Oracle Specific) and my selected object is not a select distinct.

(@Select(Promo\Promo Decrip) IN @Prompt('Select Promo Descrip or Enter * for All Promo Descrip','a','Promo\Promo Decrip,multi,free)  OR '*' IN @Prompt('Select Promo Type or Enter * for All Promo Descrip','a','Promo\Promo Descrip',multi,free))

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