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.
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 ?
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)
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.
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.
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.
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.
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.