All values in @prompt

[Former Member]

Hi Gurus,

I see a prompt in universe designed as below.

It is designed to get "All" in list of values.

I wanted to know how does it actually work.

( ( TITLE.TITLE ) IN @Prompt('Enter Product Title(s):  (* = ALL Titles)','a','Release Info\Active CTS Titles',multi,free) OR '*' IN @Prompt('Enter Product Title(s):  (* = ALL Titles)','a','Release Info\Active CTS Titles',multi,free)  )

After Query Excution and passing value as * the where clause becomes as below :

Where (     ( TITLE.TITLE)    IN     ( '*' )     OR    '*'  IN     ( '*' )       ).

[former_member199543]

Just use Optional prompts on reporting level and that's all OR if you really want to implement ALL values approach, then go to  http://www.forumtopics.com/busobj/viewtopic.php?p=99071#99071

[former_member207878]

Hi Hitesh,

When you select '*' for ALL , then your where condition become as  below as you said in your post:

Where (     ( TITLE.TITLE)    IN     ( '*' )     OR    '*'  IN     ( '*' )       )

It means, when you pass '*' through the prompt, then your second condition i.e. '*'  IN     ( '*' ) will get satisify and will return all the records.

On the other hand, if you select any specific value like '1' from LOV, then your where clause look like :

Where (     ( TITLE.TITLE)    IN     ( '1' )     OR    '*'  IN     ( '1' )       )

Here your first condition will get satisfy and return the records, which will be having Title = '1'

[Former Member]

Hi Hitesh,

Set your prompt as Optional & then if user doesn't Select any value it bring data for all .

[Former Member]

Thanks Nikhil for your Response, However I wanted to implement @universe level. I tried adding "Optional" in @prompt clause, however it throws java null point exception error in Webi.

I know what you mean. But that works when I select optional checkbox in query prompt properties at Report Level.

[Former Member]

Hi Hitesh,

When we use "All" in where clause it works as below

Empno   Salary

--------------------

1               1000

2               3000

3               5000

Suppose I have 3 values in the column say 1,2,3  as above. If user selects any of the number then I need only that particular value record,

SELECT Empno, Salary from Emp

where Empno in (1)

if user selects All then it should return all values i.e 1,2,3 without selecting individual values. In this case SQL query should form like this

SELECT Empno, Salary from Emp

where Empno in (1,2,3)

In these cases @prompt() need to use to get all the values in the column as above.

Hope this helps you in better understanding.

- Thanks.