Before you Begin
Learn how to limit the list of values in a filter using parameter binding in Oracle Analytics.
Background
In this tutorial, you create a parameter to use to limit the number of values of a list filter.
Binding a parameter to a list filter enables the parameter to listen for any value from the list filter and accept or reject the value based on the parameter definition. This tutorial uses the City data element as the example for binding a parameter to a list filter. When you constrain the list filter values, the user can only select the values defined in the parameter definition. The visualizations that you create in the workbook are updated when the user selects a City value.
What Do You Need?
- Access to Oracle Analytics
- Download sample_order_lines.xlsx to your computer
Create a Dataset
In this section, you add the sample_order_lines.xlsx file to Oracle Analytics to create a dataset table.
- Sign in to Oracle Analytics.
- On the Home page, click Create, and then click Dataset.
- In Create Dataset, click Drop data file here or click to browse, select the
sample_order_lines.xlsx
file, and then click Open. - In Create Dataset Table from sample_order_lines.xlsx, click OK. Click Save
.
- In Save Dataset As, enter
sample_order_lines
, and then click OK.Description of the illustration sample_order_lines.png
Create Visualizations
In this section, you create the visualizations to use with the constrained list filter.
- Click Create Workbook.
- In the Data pane, select Sales and drag it to the canvas.
Oracle Analytics creates a tile visualization of total sales in the dataset.
- In the Data pane, select Quantity Ordered, drag it to the canvas, and then release it next to the Sales tile visualization.
- In the Data pane, hold down the Ctrl key, click Sales, and then click Product Sub Category. Right-click, select Pick Visualization and select Horizontal Bar
.
Description of the illustration visualizations.png - Click Add Filter
. In the sample_order_lines list, click Product Sub Category, and then click Top Bottom N.
- In Top Bottom N, click Select Measure and click Sales. Keep Top as the Method, and 10 as the Count. Click outside the filter dialog.
Description of the illustration prod_subcat_top_10.png - In the Sales by Product Sub Category visualization, click Menu
, select Sort By, select Sales, and then select High to Low.
Description of the illustration prod_subcat_hilo.png
Create a Parameter
In this section, you define a parameter using the City data element in the sample_order_lines dataset.
- Click Parameters
. Click Add Parameter
.
- In Create Parameter, enter
City
in Name. - Keep Text as the Data Type.
- Click Allow Multi Select
to disable the ability to select more than one city.
If the Multi Select toggle contains a white side and gray side
then the ability to select multiple values is disabled.
- From the Available Values list, select Value.
- Click Add Value four times to create five fields. Enter the following values one in each value field:
Seattle
Los Angeles
Denver
Boise
Salt Lake City
- Leave the Initial Value field empty and click OK.
Description of the illustration create_param.png
Create a Dashboard Filter Control
In this section, you add a dashboard filter control, add the City data element to the list filter, and then bind the City parameter to the list filter.
- Click Visualizations
.
- In Visualizations under Dashboard Controls, select and drag Dashboard Filters
to the canvas.
- Click Data
.
- In the Data pane, select City and drag it to the Dashboard Filter control.
Description of the illustration city_filter.png - Click All to open the City Filter dialog.
There are 133 cities in the dataset. Too many to search through the list for the cities your users want to view.
Description of the illustration all_cities.png - In Filter Controls, click the X to delete the City data element.
- From the Data pane, select and drag City to the Filter Bar.
- In the Filter dialog, click Bind to Parameter
.
The parameter icon turns green
in the filter dialog when the filter is bound to the parameter.
- Click Parameters
. Select and drag the City parameter to the dashboard filter control.
- Click the dashboard filter control list and select a city.
The City dashboard filter list doesn't contain an initial value. The values defined in the parameter display when you click the list.
Description of the illustration city_param_dashboard_filter.png - Click Save
. In Save Workbook, enter a Name, and click Save.
Review the Constrained Filter
In this section, you select from list of values in the filter to see the changes in the visualizations.
- Click Preview
.
- Click City and select Denver.
Description of the illustration selected_city.png - Click Edit
.
Enable Multiple Values in the Parameter
In this section, you edit the City parameter to use multiple values from the list filter.
- In the Visualize page, click Parameters
.
- Right-click the City parameter and select Edit Parameter.
- In Edit Parameter, click Allow Multi Select
and click OK.
The Allow Multi Select icon should have a green square on the right side
.
- Click Yes to overwrite the current value.
- Click Save
.
- In the dashboard filter, click City, select Seattle and Salt Lake City, and then click outside of the dialog.
Oracle Analytics updates the visualizations with the values from the selected cities.
Description of the illustration multiple_cities.png
Learn More
Constrain Filter Values in Oracle Analytics
F84251-01
September 2023
Copyright © 2023, Oracle and/or its affiliates.
Learn how to limit the list of values in a filter using parameter binding in Oracle Analytics.
This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.
If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, then the following notice is applicable:
U.S. GOVERNMENT END USERS: Oracle programs (including any operating system, integrated software, any programs embedded, installed or activated on delivered hardware, and modifications of such programs) and Oracle computer documentation or other Oracle data delivered to or accessed by U.S. Government end users are "commercial computer software" or "commercial computer software documentation" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, the use, reproduction, duplication, release, display, disclosure, modification, preparation of derivative works, and/or adaptation of i) Oracle programs (including any operating system, integrated software, any programs embedded, installed or activated on delivered hardware, and modifications of such programs), ii) Oracle computer documentation and/or iii) other Oracle data, is subject to the rights and limitations specified in the license contained in the applicable contract. The terms governing the U.S. Government's use of Oracle cloud services are defined by the applicable contract for such services. No other rights are granted to the U.S. Government.
This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.
Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
Intel and Intel Inside are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Epyc, and the AMD logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.
This software or hardware and documentation may provide access to or information about content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services unless otherwise set forth in an applicable agreement between you and Oracle. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services, except as set forth in an applicable agreement between you and Oracle.