About Download Parameters

Download parameters can help you retrieve just those rows from the service you want to work with. During download, the add-in appends the parameter to the GET request it sends to the service. The service returns only those rows matching the parameter.

Suppose you want to retrieve employee rows where the managerId field is empty. In this case, you would create a download parameter for the Employees layout with a parameter name of "q" and a parameter value 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 include expressions that reference fields, variables, and workbook parameters. These expressions can also include functions.

Here's an example of a parameter for a 'q' query that references workbook parameters, Dept and MinSal:

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.

You can also use a function, like a date function, in a parameter. For example, to find employees with hire dates in the last 90 days, you would use the Today() date function along with an integer on the HireDate date field, like this:

HireDate > '{ Today() - 90 }'

See About Expressions.

If the service supports more complex searches, you can create a number of conditions and link them together using the AND and OR operators, like this:

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

In this example, the service returns rows where the first name includes "es" or where the hire date and department are "January 13th, 2001" and "10", respectively.

Note:

Each service defines which query parameters it supports as well as the expected syntax for searching. Consult the REST service documentation for details.