1. Developing Integrated Spreadsheets Using Oracle Visual Builder Add-in for Excel
  2. Configure Search Options for Download
  3. Use Search Parameters to Limit Downloaded Data

Use Search Parameters to Limit Downloaded Data

Create a search query that determines which data get returned from the REST service when a business user invokes a download. For example, if a business user only needs to access records for a specific location, you can create a query that includes a search parameter for that location.

Let's consider an example. To retrieve employee items where the managerId field is empty, you would create a search on the Employees business object with a search parameter of "managerId is null". The GET request would then include the following in the query string portion of the URL:

GET…/Employees?q=managerId+is+null

where:

  • q is a query parameter supported by the Employees service
  • managerId is the name of a field that supports the query parameter
  • null is the search value

Search parameters can use expressions to filter data. For example, you could use the Today() date function along with an integer ({ Today() - 90 }) to find employees with hire dates in the last 90 days. See About Expressions.

Expressions can reference workbook parameters embedded in the workbook. A search parameter for a 'q' query that references workbook parameters might look like this:

DepartmentId={ Workbook.Parameters['Dept'].Value } AND Salary >= { Workbook.Parameters['MinSal'].Value }

This search parameter returns employee rows where the department Id matches the value of the Dept workbook parameter and the salary is greater than or equal to the value of MinSal.

During download, Oracle Visual Builder Add-in for Excel constructs the GET URL by resolving the expressions in the Search Parameter value property. If the values of the Dept and MinSal parameters are "80" and "7000" respectively, then the resulting GET request URL would look like this:

.../Employees?q=DepartmentId=80 AND Salary >=7000

See Use Workbook Parameters for Download.

If the 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)))

You can configure search parameters for each layout in a set of dependent layouts. See Filter Data for a Set of Dependent Layouts.

Note:

The specific GET URL query string syntax is governed by the REST service. Consult with your service owner if required.

To add search parameters to your layout:

  1. In the Excel ribbon, click Designer.
  2. In the Layout Designer's Query tab, 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. Enter a name and value in the parameter fields.

    Note:

    Do not enter URL-encoded values in the parameter fields. The add-in applies URL encoding to the parameter value at download time. The search parameter name is not encoded.
  4. If you used an expression in the Parameter Value field, select Allow expressions in Parameter Value.

    Selecting this check box ensures the add-in evaluates the value as an expression.

  5. Click OK to close the editor.
  6. Test the search parameter by downloading data and viewing the results.

    Note:

    There is no validation in this editor or at download time. The add-in cannot determine which parameters are useful for search. Likewise, the add-in can't determine the proper syntax for the parameter values. If you enter invalid information, you may get a bad request error. Consult the API documentation for the service you are using to identify whether to use "q" for the parameter name and how to formulate the search expression properly.

When a search parameter is configured, the add-in appends the search query to the REST endpoint URL of the GET request when the business user clicks the Download Data button.

The Search Parameters property works in combination with the other two properties (where applicable). They are not mutually exclusive. If search or row finder settings have also been configured, the user is prompted to provide values for the configured parameters that will further filter the returned results.

WARNING:

If you configure multiple search options, make sure the service supports that combination. Some combinations may work where others may not.

Some services, such as ADF REST and VBBO services, do not support using more than one "q" parameter in a REST call. If you are using one of these services, configure the add-in to merge all configured "q"-type queries into a single query for download. See Merge Searches for Download.

Tip:

To troubleshoot a particular combination of search settings, open the Network Monitor and download data. Then, inspect request details in the Network Monitor's window. This information may help you refine search settings.