Use Workbook Parameters for Download

You can use workbook parameters stored in your workbook to control which rows are retrieved during download. Workbook parameters are name-value pairs that are embedded in your workbook programmatically, typically by a web developer.

An organization may choose to embed different values in an integrated workbook—for example, when it is downloaded—based on the context. Let's consider an example. Suppose your organization has a web app that includes an Employees page and that this page includes a button for downloading an integrated workbook. The web developer could use a server-side mechanism to write the same filter values to the workbook used by the business user in the web app.

For example, a business user may filter the employees list in the web app to show only employees in the Sales department with a salary greater than or equal to $7000. If this user then clicks the download button on the page, the web server embeds the filter values— Dept=Sales and MinSal=7000—in the integrated workbook before sending it to the user.

If you configure a search query, a row finder, or a search parameter that references these workbook parameters, then the user sees the same data as in the web page when they open the downloaded workbook and click Download Data.

How these workbook parameters are written to the workbook is up to you. However, if your organization plans to take advantage of this technology, your web developers will need to know where to write these workbook parameters and in what format. You can point them to Embedding Workbook Parameters in a Workbook for these details.

Create a Search Using Embedded Values

If your workbook includes workbook parameters, you can create a search query based on the values stored in these parameters.

For example, an Employees workbook may include a couple of workbook parameters for storing department and salary values: Dept=Sales and MinSal=7000. If these workbook parameters are included, you can create a search query to return items based on these values.

To reference a workbook parameter, use this syntax: Workbook.Parameters['name'].Value where name is the name of a workbook parameter.

To use a "q" query that returns sales employees with salaries greater than or equal to $7000, configure the search parameter like this:

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

Your parameter must be within curly braces ({ and }). To use the curly brace characters as literals in the property value, escape them using a backslash (\). For more information, see About Expressions.

To use a workbook parameter in a search parameter:

In the Excel ribbon, click Designer.
In the Layout Designer's Query tab, click the Add Search Parameter icon () next to the Search Parameters property to open the Search Parameter Editor.
Enter a parameter name and parameter value for your search parameter.

In this image, the search parameter uses a q search parameter with a search value that references two workbook parameters, Dept and MinSal.

Description of the illustration workbook-parameter-config.png
Select Allow expressions in Parameter Value to ensure the add-in evaluates the value as an expression.

WARNING:
If this check box is not selected, the parameter value is used as a verbatim string when the add-in creates the GET URL. In this example, this would result in a 400 Bad Request error.
Click OK to close the editor.
Test the search parameter before distributing the workbook. See Test a Download with Workbook Parameters.

During download for the layout, Oracle Visual Builder Add-in for Excel resolves the expressions in the Search Parameter value property and builds the GET URL.

Test a Download with Workbook Parameters

Before you distribute the workbook, it is recommended that you test the search behavior of a configured search query, row finder, or search parameter that references a workbook parameter.

To test the search behavior:

Open the Network Monitor from the Oracle Visual Builder ribbon, then click the Download Data button.
The Network Monitor shows the URLs that the add-in constructs using the search or row finder settings. See Network Monitor.
If you experience issues, try modifying the value of the workbook parameter and then clicking Download Data again. The next download will reflect any changes made to these parameters.

Note:
If a workbook parameter is missing from the workbook or the syntax is incorrect, the add-in can't evaluate the expression. This will likely result in a 400 Bad Request error.
To view or modify workbook parameters for testing purposes, open the Edit Workbook Parameters dialog from the Advanced menu.

The Edit Workbook Parameters menu command is only available when the designer tools are enabled.

Description of the illustration edit-workbook-parameters.png

To change a value, select the workbook parameter from the list and click the Edit icon ().

Note:
The Workbook Parameters dialog should only be used for testing purposes. Any parameters added or edited using this dialog are removed when the workbook is published.

Embedding Workbook Parameters in a Workbook

Write workbook parameters to a workbook using a custom solution such as a J2EE servlet or browser-based JavaScript. Workbook parameters are name-value pairs that are stored in a hidden worksheet of the workbook using a packed string-value format. Workbook developers can create search queries that reference these parameters.

Workbook parameters might be name-value pairs that correspond to URL arguments passed when the workbook is downloaded from a web app. They may also represent some state from a web app page. Or they might be arbitrary named values.

Oracle Visual Builder Add-in for Excel does not include functionality for embedding workbook parameters programmatically. An organization that wants to use this functionality must provide their own custom solution for embedding these parameters in a given workbook. This topic is intended for the programmer or web developer tasked with coding this solution.

Workbook parameters must be written to a specific location and in a specific format. Before you proceed, review the parameter requirements carefully to make sure you embed your workbook parameters correctly.

Sample Methods

Here are some possible methods to consider when planning a solution:

A server-side mechanism using a J2EE servlet or filter to examine and update the workbook during download
A client-side/browser-based (JavaScript) mechanism that can examine and update the contents of the workbook after it is retrieved from the server and before it is written to the local computer file system

Location

All workbook parameters are stored as a string on a hidden worksheet named _VBCS_WorkbookInfo at cell address $B$15.

Format

The format for the packed string is:

<paramName1>=<paramValue1>/<paramName2>=<paramValue2>/...

with these conditions:

Each paramName and paramValue must be separately URL-encoded.
Each name must be separated from its value using the equals character (=).
When there are multiple name and value pairs in the string, each name value pair is separated from others by the forward slash (/) character.
The equals (=) and forward slash (/) characters must not be encoded.
The name portion must not be empty and must match the name used in the search parameter expressions. See Use Workbook Parameters for Download.

Notes

The total size for the packed string format for the set of embedded parameters is limited to 32,759 characters.
The specific worksheet cell (_VBCS_WorkbookInfo!$B$15) may:
- Be empty (no parameters)
- Contain a packed string of 1 or more parameter name and value pairs
- Contain the special placeholder string, $$VbafeWorkbookParameters$$
  This value is set by default when a workbook is first integrated using the add-in, and also when the workbook is published.
  
  The add-in treats this value, if found, the same as an empty string (no parameters available)
Workbook parameters created using the Edit Workbook Parameters dialog are removed when the workbook is published.