Custom SQL List of Values
In addition to assigning an Oracle Value Set to the Bind Variable or Filter in your Report Definition, you can now create a custom SQL based List of Values. This can be done when defining your Bind Variables or Filters in the Create Report Definition Wizard.
Write the SQL you want to use for your List of Values in a tool like TOAD or SQL Developer.
Example SQL |
---|
SELECT segment1 “Supplier Number”, vendor_name “Supplier Name” FROM po_vendors |
The SQL can be no longer than 2000 bytes and must return only two columns
At the Bind Variable or Filter Stage of the Create Report Definition Wizard
- Select the Bind Variable or Filter Name
- Select the LOV Check Box.
- Select the XXE4ACUSTOMSQL value set.
- Paste the SQL in the field for Default Value.
The List of Values will then be accessible from the Parameter List and the Create List of Values Dialog.
The SQL will only be validated at the end of the process once the Finish button has been clicked. It will report any SQL errors and return you to either the Bind Variable or Filter Stage to rectify the problem.