Use a Workbook Parameter Value for a Row Finder Variable

You can use an expression that references a workbook parameter to determine the default value for a row finder variable. This way, you can use a value stored in a workbook to control which rows are returned by a row finder during data download.

Let's say the service has a row finder, "FindByBusinessUnit", with a variable, "BusinessUnit". Instead of setting the variable's default value to a static value—like "Manufacturing"—you may want to write the user's business unit to a workbook parameter and use an expression that references this parameter.

When a business user downloads data, they are prompted to provide the business unit for the row finder, which then returns expense reports matching this value.

If you set the default value dynamically based on where the workbook is retrieved from, you would need to embed the value as a workbook parameter while it is being retrieved from the web application. Workbook parameters are name-value pairs that are embedded in your workbook programmatically, typically by a web developer. For help writing values to workbook parameters, see Embedding Workbook Parameters in a Workbook.

To use a workbook parameter value as the default value, enter an expression like this: { Workbook.Parameters['<para_name>'].Value } in the Default Value field of the Row Finder Variable Editor. See Workbook Parameters in Expressions.

In this example, the expression references a workbook parameter, BusUnit which stores the name of the business unit.

Description of the illustration expression-row-finder-variable.png

Oracle Visual Builder Add-in for Excel does not validate expressions in the Row Finder Variable Editor. You should test your expression before distributing the workbook.

Keep in mind the following when testing:

• If the expression result cannot be successfully converted to the appropriate data type for the row finder variable, the row finder variable value will be blank/empty. There is no error message.
• Workbook parameters do not have a data type associated with them. Expressions that use them treat them as string data values.

  There are no conversions for different cultures within the expression.

• If you change the default value in the editor after downloading data, be sure to clear the layout to test the new default value.
• The expected format of the workbook parameter values is "invariant":
  • Dates: ISO 8601 "1992-06-15"
  • Numbers: decimal point; "3.14"
  • Date-time: ISO 8601 UTC: "2017-04-19T12:33:19Z"
  • Boolean: "True" / "False" (not localized)

After you complete your testing and distribute the workbook, your business users are presented with a prompt showing the value stored in the BusUnit workbook parameter when downloading data.