1. Using the Oracle Visual Builder Add-in for Excel in Oracle Integration
  2. Configure Search Options for Download

6 Configure Search Options for Download

Configure the search options for a given layout. The configured search options are used when the user clicks Download in the Oracle Visual Builder tab.

The add-in provides search options that you configure for each layout. The properties that you can configure depend on the type of service that you integrate your Excel workbook with. For example, all properties are available to use when your workbook integrates with an Oracle RAMP REST service while you can only configure values for the Search and Search Parameters properties when you use Oracle REST Data Services.

Configure values for the Search property when you want to provide users with the option to enter search terms to filter the data that they download from the Oracle RAMP REST service or Oracle REST Data Services. Consider, for example, a data table that downloads data about employees. In this scenario, you can add the Department name business object field as a search field to the Search property, so that users can enter search criteria to download the employee records of those employees who belong to the department that matches the search criteria. You can enter multiple fields to the Search property so that users can enter multiple terms in their search.

Configure a value for the REST Finder property if the Oracle RAMP REST service you connect to supports finders. If you select a value for the REST Finder property, the add-in uses it during download. A finder that a Oracle RAMP REST service supports may have parameters. If the finder that you choose for the REST Finder property specifies parameters, the add-in prompts users to provide parameter values during download.

Add values for Search Parameters if the REST service that you download data from supports the addition of various parameters in the query portion string.

Depending on the service you use, you can configure one or more of these properties. If you configure values for all properties, the add-in prompts the user to enter the value(s) supported by the REST Finder property before it prompts the user to enter value(s) for the Search property. Once the user enters the requested input values, the add-in downloads data based on the values that the user input.

Use Search to Limit Downloaded Data

For workbooks that integrate with Oracle RAMP REST service and Oracle REST Data Services, you can configure the workbook to enable a user to specify search values to limit the data that the add-in downloads to the workbook.

  1. In the Excel ribbon, click Designer.

  2. In the Query tab of the Layout Designer, click the Edit icon next to the Search property to open the Available Business Object Fields window.

  3. Select the business object field that you want to enable users to enter search terms for. For example, select Department Name and click OK if you want to enable users to search on employees by department, as shown by the following image.

    Description of vb-search-fields.png follows
    Description of the illustration vb-search-fields.png

  4. Click Add Field to add additional search fields.

  5. Click OK.

Use REST Finders to Limit Downloaded Data

For workbooks that integrate with Oracle RAMP REST services, you may select one of the pre-defined REST Finders if any are associated with the layout's business object.

  1. In the Excel ribbon, click Designer.

  2. In the Query tab of the Layout Designer, click the Edit icon next to the REST Finder property, as shown in the following image.

    The REST service owner must configure the service to expose the options that appear in the Available Finders window.

    Description of finder-table-property.png follows
    Description of the illustration finder-table-property.png

  3. Click OK to close the open windows.

Refer to the following resources for more information about configuring the Oracle RAMP REST service that supports finders:

Use Search Parameters to Limit Downloaded Data

You add search parameters to the workbook to include in the request to download data from the REST service.

Many REST services support the addition of various parameters in the query string portion of the URL, such as the following example.

GET…/orderReleaseLines?q=ID=1234

In this example:

  • "q" is an optional parameter supported by the orderReleaseLines service
  • "ID" is the name of a field that supports query
  • 1234 is the search value

Each REST service defines which parameters can be used for search. Likewise, each REST service defines the required and supported syntax for the expression that appears on the right-hand side of the assignment operator (=).

The add-in does not know which parameters are useful for search. Likewise, the add-in does not know the proper syntax for the parameter values. So, you will need to consult the API documentation for the REST service you are using to identify whether to use "q" for the parameter name and how to formulate the search expression properly.

There is no validation in this editor or at download time. If you enter invalid information, you may get a bad request error.

When the user clicks the Download Data button, the search parameters are added to the appropriate URL along with the other search options (if applicable).

The add-in applies URL encoding to the parameter value at download time. So, you should not enter URL-encoded values. The search parameter name is not encoded.

If the REST service supports complex searches, you can create complex searches in the layout, as shown in the following example

q=((firstName LIKE '*es*') or ((hireDate< "2001-01-13") and (department = 10)))

The Search Parameters property does not support "dynamic" search values. Using the example above, the value, 1234, is specific in the Layout Designer as a constant.

The Search Parameters property works in combination with the other two properties (where applicable). They are not mutually exclusive. However, some combinations may work where others may not. If you choose to configure multiple search options, you must ensure that the REST service supports that combination.

To add search parameters to your layout:

  1. In the Excel ribbon, click Designer.
  2. In the Query tab of the Layout Designer, click the add or edit search parameter icon next to the Search Parameters property to open the Search Parameter Editor where you add or edit search parameters. Description of excel-search-parameters.png follows
    Description of the illustration excel-search-parameters.png
  3. Click OK to close the open windows.

Download Behavior in Layouts that Use Search and REST Finders

If you configured a value for the REST Finder property, the behavior of the add-in depends on the configuration of the finder exposed by the REST service:

  • If there are no parameters, there is no prompt.

  • If there is exactly one parameter, a prompt appears that allows the user to specify a value for the parameter.

  • If the finder has more than one parameter, a prompt appears that allows the user to specify values for each parameter.

Note:

Only Oracle RAMP REST services support REST Finders.
Description of finder-table-runtime.png follows
Description of the illustration finder-table-runtime.png

If you added search fields to the Search property, the add-in prompts users who download employee data using the Download Data button to enter values in the search field, as illustrated in the following image.

Description of excel-filter-choicelist.png follows
Description of the illustration excel-filter-choicelist.png