'*ALL' option in LOV of a prompt to select ALL values

[Former Member]

Hi Experts,

Please help on how to get '*ALL' option in LOV of a prompt to select ALL values in the LOV of the object at report level by editing the SQL of the report in DESKI XI R3.

In BO 6.5 FC the below syntax was working but in XI R3 its not.

(Table.Column IN @variable('1. Select Column:') or 'ALL' in @variable('1. Select Column:') )

Many Thanks.

[Former Member · Accepted solution]

Hi Sim,

Following solution might help you to resolve the issue.

The following example uses the Country and City fields from the Customer table in the Efashion database.

1. In Designer, go to the object properties of the City field then click the Definition tab.

2. Add the following WHERE clause:

@Select(Customer\Country) IN @Prompt('Select Country','A','Customer\Country',multi,free) OR 'ALL' IN @Prompt('Select Country','A','Customer\Country',multi,free)

3. Click Tools > Lists of Values > Edit a List of Values. Select Country, then click the Edit button.

4. In Query Panel, click the SQL button. Type the following SQL in SQL Viewer:

SELECT DISTINCT

Customer.Country

FROM

Customer

UNION SELECT 'ALL'

FROM

Customer

5. Select the Do not generate SQL before running option.

6. Save the universe and export it to the repository.

Regards,

Sarbhjeet Kaur

[Former Member · Accepted solution]

Hi Sarbjeet, Thanks for your reply and the solution. Your solution is really useful. I didn't reply back earlier on this as I implemented same solution the same day when I posted the question so didn't visit the forum after that.

Thanks again.

[Former Member]

Hi Sim,

You can create a new object in the universe with the following syntax

@Select(Class Name\Object Name) = @Prompt('Select State','A','Class Name\Object Name',mono,free)

or 'ALL' = @Prompt('Select State','A','Class Name\Object Name',mono,free)

You can then hide the orginal State object and repoting user will see only the new object

Whenever this object is used in the report for quering it will prompt for values to the end user.Now the result set depends on the values entered by the end user as follows:

--- If he selects A,B,C as values, then data for A,B,C will be returned

--- If he selects or types ALL, then the entire data set will be returned

Hope this helps

Sheeba

[Former Member]

Hi Sheeba,

Thanks for the solution. I didn't reply back earlier on this as I implemented same solution the same day when I posted the question so didn't visit the forum after that.

Thanks again

[Former Member]

Hi Sim,

As per my knowledge, we cannot have option ALL in the LOV prompts, instead u can use this code in the SQL

and while excueting the report, when the prompt appears, just type ALL.

(Table.Column = @variable('1. Select Column: or Enter All') or ('1. Select Column: or Enter All')='ALL')

Regards,

Aparna