Skip to Content
Author's profile photo Former Member

Steps to insert “ALL” as a value in Object LOV to select all values in WebI prompt

Here in this document I am trying to show how we can insert “ALL” value in the LOV of the particular object so that we can use it as prompt and user can select only one value as “ALL” to select all the available LOVs in one click and user need not select all the values from the list.

Below are the steps.

1. Edit the lists of value of an object through object properties where you want to insert “ALL” value. Write a custom query to append UNION query with ALL             value.

By default your object gives below query which gives distinct list of values.

select OBJECT from TABLE //Here OBJECT means the object on which you want “ALL” value

Write a custom sql as below

select OBJECT from TABLE
UNION
SELECT ‘ALL’ from dual //if underlying database is oracle. Check the union query for other DB and write the same.

NOTE:

          1. If you are using UDT then the EDIT LOV option is present in the Object properties.
          2. If you are using IDT, then you need to create List of value object in business layer then create a parameter object and associate newly created list of value object to the parameter and use the parameter in below steps

2. After writing the above code in LOV in object Properties, you can see the “ALL” value inserted in the LOVs of the object with other available values.

3. Create a condition object at universe level with the below code

@(Class\OBJECT) = @Prompt(‘Select OBJECT:’,’A’,’Class\OBJECT’,mono/Multi,free/constrained,Persistent/Not_Persistent…)

or

“ALL” = @Prompt(‘Select OBJECT:’,’A’,’Class\OBJECT’,mono/Multi,free/constrained,Persistent/Not_Persistent…)

NOTE:  If you are using IDT then the parameter object needs to be taken instead of the OBJECT.

4. Take the above created condition object in the query filter on the BO report.

5. If user selects any value from LOV other than “ALL” then first code will execute(Before OR) and it will not go to the second line code(ALL)

6. If user selects “ALL” value from LOV then the second line code will execute (After OR) and not the first one.

NOTE : This method is only applicable when you want to see “ALL” as a value in the LOV of the object to select all values in one click

Otherwise you can make the prompt as OPTIONAL and write a proper prompt text so that user can understand not to select any value to select all values by default.

       Developer can take any value instead of “ALL” as per requirement from the end user.         

Assigned Tags

      20 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Hey Sachin nice article.

      Thanks for sharing.

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thanks Chetan

      Author's profile photo Former Member
      Former Member

      Well Explained . Keep Sharing !!

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thanks Yogeshwara

      Author's profile photo Former Member
      Former Member

      Gud Document Sachin 🙂

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thanks Tulika....

      Author's profile photo Former Member
      Former Member

      I am searching  for this since many days.

      Thanks for sharing good info.

      Regards,

      Chandrakanth

      Author's profile photo Former Member
      Former Member

      Excellent article.

      Just wanted to know that how can we use it as OPTIONAL prompt as I have to apply this on three objects and use it in the same report.

      It would be great if you reply soon.

      Regards,

      Amais

      Author's profile photo Sahil Khurana
      Sahil Khurana

      Hi Amais,

      To make it as Optional, you have to uncheck below property while creating filter:

      /wp-content/uploads/2014/02/webi_383451.jpg

      Author's profile photo Former Member
      Former Member

      THanks for the reply Sahil. I am using bo 3.1. So I dont think we have the above option in it. and when I run the report with "ALL"in the prompt it gives me null values as well. As i mentioned earlier I have 3 objects on which I am applying ALL And making all 3 objects as optional prompts.

      Regards,

      Amais

      Author's profile photo Sahil Khurana
      Sahil Khurana

      Amais,

      Above screenshot is taken from BOXIR3. with the help of this you can make 3 prompts and by unchecking "Use filter as mandtaory in query" option; You can make all the prompts as optional.

      Please let us know, if you face any issues

      Author's profile photo Former Member
      Former Member

      I couldnt find this option. can you guide me on this.

      And can you look into below link.
      http://scn.sap.com/thread/3497049

      Regards,

      Amais

      Author's profile photo Former Member
      Former Member

      Sahil,

      Found the option. Tried it but it is making the prompt mandatory not optional.

      Author's profile photo Sahil Khurana
      Sahil Khurana

      Amais,

      You have to uncheck check box that is : "Use filter as mandatory in query". After that save the universe and export it to repository. By this this prompt will become optional in query.

      Author's profile photo Former Member
      Former Member

      Still not working.

      Author's profile photo Sahil Khurana
      Sahil Khurana

      Amais,

      You can try below work around, which is not permanent solution,but to some extent it works fine:

      ' ' = @Prompt('Displayed text ','A','Store\City',Mono,Constrained,Persistent,{' '})

      In this, I just compare space with default value; which is also space.

      Author's profile photo Harry Shenoy
      Harry Shenoy

      I came across a couple of issues while working on this. We have UDT

      1. I have opened the UDT-> opened the object prop for the obj I want ALL in LOV -> edited LOV -> viewed SQL and tried to give the sql query mentioned in the doc above. I tried ot save it but it just doesn't get saved and the object remains the same. I am not sure what I am missing here. I am still not able to see the ALL in the LOV.

      My query is like, "SELECT DISTINCT object name FROM table name" and to this I just added "UNIONSELECT 'ALL' from dual" to it and then tried to save it. and then nothing happens.

      Please advice. 😕

      Author's profile photo Former Member
      Former Member

      Nice Information. Very Usefull

      Author's profile photo Harry Shenoy
      Harry Shenoy

      Hey Guys,

      I came across a couple of issues while working on this. We have UDT

      1. I have opened the UDT-> opened the object prop for the obj I want ALL in LOV -> edited LOV -> viewed SQL and tried to give the sql query mentioned in the doc above. I tried ot save it but it just doesn't get saved and the object remains the same. I am not sure what I am missing here. I am still not able to see the ALL in the LOV.

      My query is like, "SELECT DISTINCT object name FROM table name" and to this I just added "UNION SELECT 'ALL' from dual" to it and then tried to save it. and then nothing happens.

      Please advice. 😕

      Author's profile photo Former Member
      Former Member

      Do we have a video for this on youtube ?