Before You Begin

This tutorial shows you how to create a Top Bottom N parameter to use as a filter in Oracle Analytics.

Background

The Top Bottom N parameter enables selecting a measure to show the highest (top) values to the lowest (bottom) values, for example, the top N (number) products sold, or top N cities by sales. You apply the parameter as a dashboard filter in a visualization.

What Do You Need?

Create a Dataset

In this section, you add the sample_order_lines.xlsx file to Oracle Analytics to create a dataset table.

  1. Sign in to Oracle Analytics.
  2. On the Home page, click Create, and then click Dataset.
  3. In Create Dataset, click Drop data file here or click to browse, select the sample_order_lines.xlsx file, and then click Open.
  4. In Create Dataset Table from sample_order_lines.xlsx, click OK. Click Save Save icon.
  5. In Save Dataset As, enter sample_order_lines, and then click OK.


    Description of sample_order_lines.png follows
    Description of the illustration sample_order_lines.png

Create a Top N Parameter

In this section, you create a Top Bottom N parameter in a workbook created from the sample_order_lines dataset.

  1. Click Create Workbook.
  2. Click Parameters Parameters icon.
  3. In the Parameters panel, click Add Parameter Add icon.
  4. In Create Parameter, enter Top N in Name.
  5. In the Data Type row, click Text, and select Integer as the value.
  6. In the Available Values list, select Any. In Initial Value list, select Value, enter 10 in the value field, and then click OK.


    This example uses integer as the data type and 10 as the initial value.

    Description of topn_parameter.png follows
    Description of the illustration topn_parameter.png

Create a Visualization

In this section, you create a visualization to use the Top Bottom N parameter as a filter.

  1. Click Data Data panel.
  2. In the Data panel, hold down the CTRL key, select Sales and City. Right-click, select Pick Visualization, and then select Horizontal Stacked Horizontal Stacked visualization icon.


    Description of horizontal_stacked.png follows
    Description of the illustration horizontal_stacked.png
  3. In the Sales by City visualization, click Menu Menu icon, select Sort By, select Sales, and then select High to Low.


    Description of sorted_viz.png follows
    Description of the illustration sorted_viz.png
  4. In the Data panel, select and drag City to Color.


    Description of sales_by_city_color.png follows
    Description of the illustration sales_by_city_color.png

Connect the Parameter as a Filter

In this section, you create an expression filter with the Top N parameter.

  1. In the Grammar pane, hover over Filters click Drop Target Options, select Create Expression Filter.


    Description of grammar_pane_filter.png follows
    Description of the illustration grammar_pane_filter.png
  2. In Expression Filter, enter Top N in Label.
  3. In the Expression, enter Ran, select Rank. In the parentheses, click column and select Sales. Place your cursor outside of the closing parenthesis.
  4. Click Show Available Functions Show Available Functions icon, expand the Operator list, double-click less than or equal to (≤).
  5. Click Parameters Parameters icon. Drag the Top N parameter to the expression field. In default value, enter 10.
  6. Click Validate. Click Apply.


    Description of topn_exp.png follows
    Description of the illustration topn_exp.png


    Oracle Analytics updates the visualization using the Top N expression filter.

    Description of topn_viz.png follows
    Description of the illustration topn_viz.png

Expose the Top N Filter

In this section, you use the parameter in a dashboard filter.

  1. Click Visualizations Visualizations panel icon. In the Visualization panel, select and drag Dashboard Filters Dashboard Filter Control to the canvas, and then release above the Sales by City visualization.
  2. Click Parameters Parameters icon.
  3. Select and drag the Top N parameter to Filter Controls.


    Description of topn_applied.png follows
    Description of the illustration topn_applied.png
  4. Click Save. In Save Workbook, enter a Name, and then click Save.

Learn More