I have to create an object in Universe which should allow the users to see ALL in the web I report prompt, The user should be able to select either 1 or multiple values or by selecting ALL, user should be able to select all the values.
For example if the prompt displays all the states as values, the prompt should allow the user to select either 1 or multiple states(which can be done by creating a prompt) or displaying an ALL in the prompt so that when the user selects ALL he should be able to get all the states.
Help is very much appreciated as its very urgent. Thanks in advance.
Hameed
Create a SQL List of values, name it Plant_LOV in the Data foundation. usiing something like:
select Plant
from Table A
UNION
select ‘ALL’
(this syntax usually differs based on the DB. For Oracle, we use select ‘ALL’ from dual.
Create a parameter and associate the Plant_LOV to it as list of values.
Now on the Business layer, you can create a new Filter object and associate this paremeter to it.
Thanks but this is what I did below and its still throwing a error
Step1. Created an object in Universe like this below
SELECT Country. State FROM Country UNION SELECT ‘ALL’ FROM Country
Step2. Created a second object in Universe and made that as a condition object and wrote the below syntax in the select area of the object(Not sure if this has to be written as whole on select area or part of the syntax in where area too)
@Select(Country\State) IN @Prompt(‘Select State’,‘A’,‘Country\State’,multi,free) OR ‘ALL’ IN @Prompt(‘Select State’,‘A’,‘Country\State’,multi,free)
State is the Object and Country is the Class. I think the above syntax is correct and don’t know why its throwing an error. Its giving a syntax error.
You are supposed to create a list of value first. I see that you are trying to paste the sQL in object definition whereas it should be in the LOV.
Your overall approach is correct and i dont see anything wrong with the prompt syntax. What is the error message you are getting?
Lets assume prompt object as ‘X’ and edit the list values ( go for object properties lick edit then select sql) by default it was
select x from table A, u can make it has
select x from table A
union
select to_char(’–ALL–’) FROM DUAL;
‘–’ MAKE IT COME TO 1ST POSITION