1. Developing Integrated Spreadsheets Using Oracle Visual Builder Add-in for Excel
  2. Configure Search Options for Download
  3. Merge Searches for Download

Merge Searches for Download

If you have configured more than one 'q'-type query for a layout, Oracle Visual Builder Add-in for Excel merges all 'q'-type queries configured for a layout into a single query for download.

Let's take a look how the add-in constructs the URL for the GET request. Suppose you want to download rows for employees in the Sales department who were hired after June 22, 2022 and have no manager assigned. In this case, you could configure a search in the Search Editor for the Sales department and hire date and then a download parameter using the Download Parameter Editor for the manager. Both the search you configure through the Search Editor as well as those configured through the Download Parameter Editor use 'q' parameters.

During download, the URL for the GET request includes a single 'q'-type query using the AND operator. like this (URL parameters are shown without encoding for clarity):

. . ./employees?q=HireDate > '2022-06-22' AND Department='Sales' AND Manager IS NULL&offset=0& ...

The add-in merges any download parameters where the Parameter Name value matches the 'q' parameter name using a case-sensitive comparison. In this case, this query returns rows for employees in Sales hired after June 22, 2022 who have no manager assigned.

For more information about search and download parameters, see Use the Search Editor to Find Required Data and Use Download Parameters to Limit Downloaded Data.

By default, workbooks created before 4.0 use separate 'q'-type filter queries instead. This may not provide the expected results since some services, such as ADF REST and VBBO services, ignore all but the first 'q' sent.

If the service ignores the second and third 'q' parameters, all rows with hire dates after June 22, 2022 are downloaded which is not the intended result.

To ensure all the queries are used to filter the results, configure your older workbooks to use the merge option:

  1. Click Manage Catalogs.
  2. In the Manage Business Object Catalogs window, select your existing catalog and click the Edit Business Object Catalog icon (Edit icon).
  3. In the Business Object Catalog Editor, click Advanced.

    Description of bo-catalog-advanced-merge.png follows
    Description of the illustration bo-catalog-advanced-merge.png

    The Merge Search for Download check box is selected by default for new catalogs.

  4. To merge searches on download, make sure Merge Search for Download is selected.

Note:

Workbooks configured to use this feature may no longer be compatible with add-in versions earlier than 4.0.