BusinessObjects Board

"ALL" as Default Value in Prompt created in Univer

Hello,

I am having trouble select Specific value while I use “ALL” in Prompt.

We have 3 prompts in one report and 1 they need Specific value and another 2 prompts they need User to select / Type “ALL” in prompt or select Specific Value from LOV.

While I am typing ALL its giving me data but while I am using specific value it gives me No data fetch.

How can I resolve this problem ? Also I am unable to Insert ALL in LOV value. Can anybody explain step by step how to insert ALL in LOV as well as make DEFAULT “ALL” in LOV on prompt level.

ANY KIND OF HELP IS HIGHLY APPRECIATED.

Thanks for reading.


j_ratoz (BOB member since 2005-11-04)

Read this


haider :es: (BOB member since 2005-07-18)

Hi Haider,

I have used this query in Universe variable and try to run report. but when Type “ALL” it gives me data and when I select specific value it gives me No data to Fetch.

Still I can not getting “ALL” in my LOV list. Can you please explain me how can I add “ALL” in LOV in Universe level variable ?

Thanks for reply.


j_ratoz (BOB member since 2005-11-04)

You do this via a Union referring to either Dual, if on Oracle, or any small table in your DB.

Please do a search ‘adding ALL to LOV’ or something similar, this has been discussed a lot of times…


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

Hello Mark,

first of all thanks for reply.

I do insert

UNION
Select ‘ALL’ from Dual

In universe Variable but still i can not see ALL in my LOV list while access LOV in promp on report.

Do I am missing some thing ?? I have insert this, Also I have check box “Do not generate SQL” and also check box “Export with Universe” and this is my whole query for this variable.

SELECT PMX_SUPER.BATCH.IDENTIFIER FROM BATCH
UNION
SELECT ‘ALL’ FROM DUAL
WHERE
PMX_SUPER.BATCH.IDENTIFIER IN @Prompt(‘Select a Specific Batch / Type ALL :’,‘A’,‘Batch (BATCH)\IDENTIFIER’,multi,free) OR ‘ALL’ IN @Prompt(‘Select a Specific Batch / Type ALL :’,‘A’,‘Batch (BATCH)\IDENTIFIER’,multi,free)


j_ratoz (BOB member since 2005-11-04)

So, you have entered this union in the LOV SQL.

Sorry, I was a bit unsure when I read the query definition, below?


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

I have enter

UNION
SELECT ‘ALL’ from DUAL in LOV sql

query I have mentioned in Variable NAME_PROMPT

Select statement in SELECT
Where statement in WHERE


j_ratoz (BOB member since 2005-11-04)

I think you are missing the tablename.fieldname that you are unioning with ‘All’ in the LOV.

Check this post here, it may clarify things a little:-

https://bobj-board.org/t/87454


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

I have enter

UNION
SELECT ‘ALL’ from DUAL in LOV sql

query I have mentioned in Variable NAME_PROMPT

Select statement in SELECT
Where statement in WHERE


j_ratoz (BOB member since 2005-11-04)

Your LOV SQL should look like this:-

SELECT 
         PMX_SUPER.BATCH.IDENTIFIER
         FROM 
         Batch 
         UNION  
         SELECT 'ALL' FROM DUAL

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

I think you may be trying to modify the universe OBJECT’s definition. If so, that’s where you are going wrong.

Check out this Designer FAQ.

Also, since this topic is all about getting ALL into an object’s LOV, moved from “Building Reports” ==> BusinessObjects Classic to the “Semantic Layer” forum, which is where Designer topics are discussed.


Anita Craig :us: (BOB member since 2002-06-17)

This should be your code in the lov:
SELECT
table.field
FROM
table
UNION
SELECT ‘ALL’
FROM
table
ORDER BY 1

Check the ‘Do Not generate SQL’ box

This should be in the where clause of your object:
@Select(table\object name) IN @Prompt(‘Select a object’,‘A’,‘table\object name’,multi,free) OR ‘ALL’ IN @Prompt(‘Select a object’,‘A’,‘table\object name’,multi,free)

Under properties for the lov you should have automatice refresh and export with universe checked.

This should get you what you are after for an lov with multi or ‘All’ for your prompt. Although I have found that the sort order may or may not work according to your expectations. So far I have found that the sort is not a true alpha sort. Therefore All is down in the list of values.

If you are using Oracle you need to use the ‘DUAL’ in your code.

Thanks!


muffntuf :us: (BOB member since 2006-01-04)

Check this, I also feel you are modifying the objects’ definition itself rather than the LOV.
Suppose the LOV object is DEPTNAME which you will be referring to in conditions.
Go to the properties tab of Deptname object, click on Edit and change the SQL to

 select scott.deptname from dept
UNION
Select 'ALL' from dual

Parse it and tick on ‘Do not generate SQL before running’.
Press Ok twice. Click on Display button to ensure that ‘ALL’ is displayed along with other values.
The conditions object should refer to this deptname object which will show ‘ALL’ in the LOV when the prompt is used in report.

Cheers


haider :es: (BOB member since 2005-07-18)

Hi Haider,

As per your answer I can see ‘ALL’ with other values but when I put this Universe varible on report its not showing me ALL over there its only showing me values without ALL.

Any idea ? I am missing some thing ??

Thanks in advance


j_ratoz (BOB member since 2005-11-04)

When you are viewing the rows returned from the database, it’s not going to show you ALL because you don’t have that value in your data.

You’ve forced ALL to become part of the List Of Values as a mechanism for effectively “ignoring” the comdition. But you’re not going to see it in the data rows returned unless it’s in the data rows returned. :wink:


Anita Craig :us: (BOB member since 2002-06-17)

That’s correct Anita. The formulas only allow a person to choose ‘ALL’ to send with the query so it allows all values to come back.

You would not see ‘ALL’ in the data set.


muffntuf :us: (BOB member since 2006-01-04)