Condition object with 'ALL' in IDT 4.0

[Former Member]

Hello all,
In 3.1 universe we can write a custom sql in the object properties tab by editing and modifying the sql to implement 'ALL' in the LOV's and then use this 'ALL' in the condition object.
Is there a way to implement same thing in IDT 4.0? if so can someone please point me in the right direction. I have oracle as my DB.

Thanks.

Santhosh.

[Former Member]

Hi Santhosh,

If your requirement is to show "ALL" in the list of values then this is the right way as you have done(by editing the sql of the object and write a union query).

Otherwise take the object in the filter pane in WEBI as a prompt and make it as optional.

[Former Member]

Thanks for your reply Sachin,

As I said I'm working in BO4 IDT, when I click on advanced tab of the object I cannot append the sql to write a union, do I need to have privileges to modify the sql?

Thanks.

Santhosh.

[Former Member]

Hi Santosh,

In IDT, you can create list of value of City object and uppend your union query of ALL in edit sql over there. Then create a parameter for City object and associte the already created City list of value object to the parameter and use that City parameter in your report. You can see ALL value uppended to the city list of values on report while selecting the value from City Parameter.

[mhmohammed]

Hi Santosh,

Steps to achieve what you are trying to do:

1. Go to Parameters and Lists of Values
2. Click on the new icon on top left and select Create a List of values based on business layer objects
3. Give the List of Value a name, say City (including All), double click on City object from the Business Layer to bring it to Result Objects
4. On the top left, click on the icon View Script, and check Custom Query and update the Query that Unions ' All' (Add space ' ' before All, so it shows up on top, also add this Query before the original query)
5. Validate & Save the Query
6. Create a Prompt in the Business Layer and in the 3rd parameter of @Prompt, instead of the Object Name, please specify the name of the List of Value you created in step 3.  If you double click on the List of Value name from far right hand column in the window, it adds @Execution() to the list of value name, just delete the @Execution part.
   
7. Create the prompt logic as you want
8. Save and Publish Universe
9. Create a test report with this prompt

Hope this helps.

Mahboob Mohammed