Creating a new value 'ALL' in an existing object

[Former Member]

Hi,

I have a dimension object which has only two values say 'yes' and 'no' in it.Since i need an another value 'ALL' in it,I added the following sql in the associate a list of values

SELECT DISTINCT

XXXXXXXXXXXXXX

FROM

XXXXXXXXXXXXX

union

select 'ALL'

My database is mysql.

The problem is when i pull this object in infoview as a prompt it contains all the three values 'yes','no' and 'all'.But when i select 'all' it throws no data to retrive but it works fine for other two prompt i,e 'yes','no'.

May i know where i m going wrong.

[Former Member · Accepted solution]

Hi.

You need create one prompt in designer with something like:

@prompt(....)=@select(.....) or @prompt(....)='all'

Becasuse, you only create the 'all' value in lov not in DataBase.

Sorry for my English

Edited by: Jaime Pestaña on Jul 13, 2009 10:37 AM

[Former Member · Accepted solution]

hi thanks for your valuable answer.I have created a condition

@Prompt('Select','a',<object name>,mono,free) = @Select(<object name>) or @Prompt('Select','a',<object name>,mono,free) = 'ALL'

This one works fine.

While running the report after pulling this condition in query filter it prompts for user input.All i need is to select the prompt from the list .Can you guide me how to get this?

[Former Member · Accepted solution]

Hi Jamie, & Rakul,

I am facing the same issue, How can we add 'None', where it should not bring any of them.

Thanks

Reddy

[Former Member]

Assume you have created promps and have LOV as ('YES','NO','ALL','NONE')

assume the output has 10 records

one column the value as yes or no

and out of 10 records there are 6 yes and 4 no

while running the report if you select YES it is returning 6 records

if you select NO it is returning 4 records

if you select YES it should select all 10 records

if you select NONE it should not select any records

for this you need to write 2 sub-queries in a case statement in the where clause of the object

eg.,

select a,b,c

from x

where column=select case

when(@prompt(,,mono,,'YES') then 'YES')

when (@prompt(,,,mono,,,'NO') then 'NO'

when (@prompt(,,,multi,,'ALL') then select (distinct(col_name) from table name)

when (@prompt(,,,mono,,'NONE') then select NULL

Let me know if you have any issues

Thanks,

-G