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.         

To report this post you need to login first.

19 Comments

You must be Logged on to comment or reply to a post.

  1. Amais Qureshi

    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

    (0) 
      1. Amais Qureshi

        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

        (0) 
        1. 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

          (0) 
            1. 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.

              (0) 
                1. 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.

                  (0) 
                  1. 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. 😕

                    (0) 
  2. 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. 😕

    (0) 

Leave a Reply