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

Use Download Parameters to Limit Downloaded Data

You can configure additional parameters to add to the query string at the time of download. These download parameters help limit the data that gets 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 parameter that filters for that location.

You can configure 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.

How Download Parameters Work

Let's consider a couple of examples. Let's suppose you want to retrieve employee rows where the managerId field is empty. In this case, you would create a search on the Employees business object with a 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

Download 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.

An expression can reference a workbook parameter embedded in the workbook. A 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 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 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)))

To add download parameters to your layout:

  1. In the Excel ribbon, click Designer.
  2. In the Layout Designer's Download tab, click the Add or Edit Download Parameter icon next to the Download Parameters property to open the Download Parameter Editor where you add or edit download 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 properties.

    Note:

    Do not enter URL-encoded values in the parameter properties. The add-in applies URL encoding to the parameter value at download time. The parameter name is not encoded.
  4. If you used an expression in the Parameter Value property, 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 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 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 Download 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.