How to include a selection value 'ALL' in Prompt Values

system · February 26, 2009, 3:54am

Hi ,

Can any one suggest me how to include a selection value ‘ALL’ in prompt value.

For Example prompt values

Dept No
10
20
30

I need to include ALL in the prompt values

Dept No
ALL
10
20
30

Thanks & Regards,
Venki.

venkimca (BOB member since 2009-02-23)

system · February 26, 2009, 4:06am

Check this Designer FAQ.

Edit: Welcome to B B!!!

Jansi (BOB member since 2008-05-12)

system · March 3, 2009, 8:57am

Thanks for your reply.

I followed as per ur suggestion. But ‘ALL’ is not showing in the list of values.

But ‘ALL’ is showing in the LOV file .

Please advise me why ‘ALL’ is not showing the in the prompt list of values.

Regards.
Venki.

venkimca (BOB member since 2009-02-23)

system · March 3, 2009, 8:59am

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…

aniketp (BOB member since 2007-10-05)

system · March 3, 2009, 8:59am

Did you follow the suggested steps and save and then export it to the repository before executing the report?

Jansi (BOB member since 2008-05-12)

system · March 3, 2009, 9:05am

I am checking in the Deski , I didn’t check it out in Infoview.

Thanks .

venkimca (BOB member since 2009-02-23)

system · March 3, 2009, 9:07am

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.

Jansi (BOB member since 2008-05-12)

system · March 3, 2009, 9:08am

In Universe

for example you have query in edit LOV for Dept No, like
Select dept_no from table_name.

edit this LOV in custom sql,

Select dept_no from table_name
UNION
Select ‘-All-’ from table_name.

also check the checkbox “Do not generate SQL before running” at bottom size in SQL Viewer dialog box.

Vills (BOB member since 2007-10-24)

system · March 3, 2009, 9:14am

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 .

Thanks .

Regards,
Venki.

venkimca (BOB member since 2009-02-23)

system · March 3, 2009, 9:20am

Hi ,

I am checking in my local system . Instead of exporting to the repository .

I have opened the report in Deski , and refreshed the report . eventhough ‘ALL’ is not showing in the list of values.

Can you please advise me in what scenario ‘ALL’ is not showing in the list of values.

Thanks for your speed replies.

Regards,
Venki.

venkimca (BOB member since 2009-02-23)

system · March 3, 2009, 9:21am

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]

Save and close.
Export the universe to the repository.
Try running the report to see ‘ALL’ in LOV

Jansi (BOB member since 2008-05-12)

system · March 3, 2009, 9:24am

Do this in the LOV object properties of dept_num

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

.

haider (BOB member since 2005-07-18)

system · March 3, 2009, 9:27am

The steps I followed:

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.

Hope this can help you.

aniketp (BOB member since 2007-10-05)

system · March 3, 2009, 10:08am

Hi ,

Thanks for your information .

Now I can able to see ‘ALL’ in the Universe -> Object Properties-> Properties-> Display .

But the problem is when I create a new report with the prompt Object , In the prompt list of values ‘ALL’ is not showing .

I am not geting what is problem . Please advise me to resolve this issue.

Thanks for your support.

Regards,
Venki.

venkimca (BOB member since 2009-02-23)

system · March 3, 2009, 10:11am

Did you follow this steps…

aniketp (BOB member since 2007-10-05)

system · March 3, 2009, 10:51am

Hi,

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.

Thanks & Regards,
Venki.

venkimca (BOB member since 2009-02-23)

system · March 3, 2009, 11:19am

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.

aniketp (BOB member since 2007-10-05)

system · March 3, 2009, 2:28pm

Hi ,
Thanks for your help on this issue .

Its resolved as per ur steps.

Thanks.

Regards,
Venki.

venkimca (BOB member since 2009-02-23)

system · March 3, 2009, 2:34pm

Could you please let us know what you did to see ALL in your LOV at last?

Jansi (BOB member since 2008-05-12)