For that you need to create a object with " All" as the select clause and attach it with the table for which you need to see the All values in the Prompt List of Values…
I didn’t get why you mentioned ‘Infoview’. I talked about exporting the universe to the repository to see the newly added ALL in the List of values of the prompt object while you refresh the report.
Hi,
The below Sql query I am using
SELECT DEPT_NUM FROM (SELECT
'ALL' DEPT_NUM
FROM
SYS.DUAL
UNION
SELECT DISTINCT
( MI_DEPT.DEPT_NUM )
FROM
MI_DEPT) MI_DEPT
WHERE
( ( MI_DEPT.DEPT_NUM ) in @Prompt('Select one Dept or ALL','A','MI Dept Details\Dept Num' ,multi,FREE) or ('ALL') in @Prompt('Select one Dept or ALL','A','MI Dept Details\Dept Num',multi,FREE) )
I didn’t created any object for ‘All’
Can you please give me the step by step procedure .
Press the SQL button in the query panel of the LOV
Add the phrase UNION SELECT ‘ALL’ FROM DUAL or similar dummy table for your particular database. For SQL Server (if you don’t care if the object parses or not) add SELECT xx From xx UNION SELECT ‘ALL’ (You don’t need a FROM table)
Click the “Do not generate SQL before running” check box.
[What you asked is already there in the suggested link and also in Vills’ post]
SELECT DISTINCT
MI_DEPT.DEPT_NUM
FROM
MI_DEPT
UNION
SELECT 'ALL' FROM DUAL
Select ‘do not generate SQL before running’ & press Ok
Do this in the predefined condition
MI_DEPT.DEPT_NUM in @Prompt('Select one Dept or ALL','A','MI Dept Details\Dept Num' ,multi,FREE)
or
'ALL' in @Prompt('Select one Dept or ALL','A','MI Dept Details\Dept Num',multi,FREE)
Then pull the dept_num object in the query along with the predefined condition
You can first test this in your local machine and then export universe to repository for others to see
Create an object with the below code in the select clause:
' All'
Click on the Associated Tables present at the right hand bottom side, and select the table name(any table from which you are fetching your column and you want to see “all” in that column)
Suppose you have an object “Name” and you want All with this and this “Name” is coming from “XYZ” Table Name. So in this case, in the second step you need to associate your All object with the XYZ table.
Go to the properties tab of this object and then click on Edit.
The query panel will appear. You will see there is a name object. So now click on Combined queries button and then add " All" object in that combined query. Perform Union Operation between Name and All object.
Check the SQL, you will see
Select XYZ.Names from XYZ
UNION
Select ' All' from XYZ
Now Save and Close and check the list of values for name object, you will see All in the LOVs at the top.
I saved the universe into my local system . I am n’t exporting universe into repository .
After saving the universe into local system , Creating new report(pointing to local univese) with the prompt value . while running the report in the prompt ‘ALL’ is not showing in the list of values.
Let me know is it mandatory to export the universe into Repository.
If you trying to develop a report in Deski, then it is not necessary. Your Local copy is sufficient.
Check whether you are taking the right universe means local copy for report development.