1. User’s Guide for Oracle Analytics Desktop
  2. Filter Your Data
  3. Apply Different Filter Types

Apply Different Filter Types

Different filter types enable you to focus in on the data that you're interested in.

Topics:

Apply Range Filters

You use Range filters for data elements that are numeric data types and have an aggregation rule set to something other than none.

Range filters are applied only to measure columns and limits data to a range of contiguous values, such as revenue of $100,000 to $500,000. Alternatively, you can create a range filter that excludes (as opposed to includes) a contiguous range of values. Such exclusive filters limit data to two noncontiguous ranges (for example, revenue of less than $100,000 or greater than $500,000).
If your workbook doesn't have a visualization, create one. See Begin to Build a Workbook and Create Visualizations.
  1. On the Home page, select a workbook, click the Actions menu, and then select Open.
  2. In the Visualize canvas, click the filter, then click Range.
    Description of filter-menu.png follows
    Description of the illustration filter-menu.png

  3. Click By to view the selected list of Attributes, then configure the filter:
    • Click a member to remove or add it to the selected list.
    • Click the Plus (+) icon to add a new member to the selected list.
    • Set the range that you want to filter on by moving the Min and Max sliders in the histogram.
  4. Click outside of the filter to close the filter panel.

Apply Top Bottom N Filters

Use the Top Bottom N filter to filter a measure or attribute and display its highest or lowest values.

  1. To apply the Top Bottom N filter to the canvas and all visualizations in the workbook:
    1. On the Home page, select a workbook, click the Actions menu, and then select Open.
    2. In the Data panel of the visualization, select the attribute or measure that you want to filter on and drag and drop it to the filter bar.
    3. In the filter bar, click the filter, then click Top Bottom N. You can only convert a range filter to Top Bottom N filter.
  2. To apply the Top Bottom N filter to a specific visualization in the workbook:
    1. In the canvas, select the visualization that you want to filter.
    2. In the Data Panel, locate the attribute or measure that you want to filter on and drag and drop it to the Filter drop target in the Grammar Panel.
    3. In the filter bar, click the filter, then click Top Bottom N.
  3. To apply the Top Bottom N filter to an on-canvas filter:
    1. With the canvas selected, go to the Data Panel and click Visualizations, then click the List Box filter.
    2. In the Data Panel, locate the attribute or measure that you want to filter on, and drag and drop it to the List Box visualization that you just created.
  4. To configure a Top Bottom N filter, click the filter, then:
    • To change between top to bottom, click Method value and click Top or Bottom.
    • To specify the number of rows that are displayed, click the Count field and enter the number of rows.
    • To change which attribute or measure column to limit by, click the By field and select an attribute or measure or custom calculation included on the canvas. Or click Plus (+) to locate and select the attribute or measure or metric that you want to limit by
  5. Click outside of the filter to close the filter panel.

Apply List Filters

You apply list filters to text, non-aggregatable numbers, and dates, and you can choose which members to include or exclude from the filter.

If your workbook doesn't have a visualization, create one. See Begin to Build a Workbook and Create Visualizations.
  1. On the Home page, select a workbook, click the Actions menu, and then select Open.
  2. In the Visualize canvas, select a filter in the filter bar or select an on-canvas filter, then click List.
  3. Locate the member you want to include and click it to add it to the Selections list. Alternatively, use the Search field and the Search Options Menu to find a member you want to add to the filter. Use the wildcards * and ? for searching.
  4. Optional: Perform the following actions related to the Selections list:

    • Click a member to remove it from the list.

    • Click the eye icon next to a member to filter it out, but not remove it from the list.

    • Click Menu at the top, and select Exclude Selections to exclude members from the list.

    • Click Null to include members with null values in the list.

    • Click Add to add all members to the list.

    • Click Clear to remove all members from the list.

  5. Click outside of the filter to close the filter panel.

Apply Date Range Filters

Date range filters use calendar controls to adjust time or date selections. You can select a single contiguous range of dates, or use a date range filter to exclude dates within the specified range.

If your workbook doesn't have a visualization, create one. See Begin to Build a Workbook and Create Visualizations.
  1. On the Home page, select a workbook, click the Actions menu, and then select Open.
  2. In the Visualize canvas, select a filter in the filter bar or on-canvas filter, and click Date Range.
  3. Click the drop-down list, and select the type of date range you want to apply (for example, Range, Start at, End at, Equal).
  4. Use the date pickers to configure the range.
  5. Click outside of the filter to close the filter panel.

Apply Relative Time Filters

Use the relative time filter on a Date or Date/Time column to display data for a specified time period based upon the current date and time.

You can specify a relative time period as either an explicit number of past or future time units (for example 2 years), or you can specify a previous period. For example, Year To Date which includes data from 1-January this year to the current date, and Month To Date which includes data from the beginning of the month to the current date.

You can apply a Relative Time filter only to date columns that already exist in the data source, and not to derived columns such as Year, or Quarter. The Relative Time filter type supports Date (with no time of day portion) and DateTime (that is, TIMESTAMP with both date and time of day) column types.

The current date and time used in queries is the Oracle Analytics server host's date and time in the server's timezone (not the browser host's time or timezone).

If your workbook doesn't have a visualization, create one. See Begin to Build a Workbook and Create Visualizations.
  1. On the Home page, select a workbook, click the Actions menu, and then select Open.
  2. In the Visualize canvas, click the filter in the filter bar or canvas, then click Relative Time.
  3. Select a Type that defines the range that you want to filter.
    • Last - You specify a Period to apply to the selected Time Level (Years, Quarters, Months, Weeks, Days, and includes Hours, Minutes, and Seconds if the column time is TIMESTAMP) relative to today's date, to display records for the date values for that period.

      Last filters that are based on a DateTime column and which have a grain of Day or longer (for example, Year, Quarter, Month, Week, Day), retrieve data from the same time of day on the starting day. For example, if the server date/time is currently Thursday 3:15pm , a Last 2 Days filter on a DateTime column retrieves data with timestamps between Tuesday 3:15pm and Thursday 3:15pm in the server's timezone. Filter queries that are based on a DATE column type (which by definition have no time of day associated) only depend on the server host's date, not the time of day.

    • Next - You specify a future Period number to apply to the selected Time Level (Years, Quarters, Months, Weeks, Days, also Hours, Minutes, and Seconds if the column time is TIMESTAMP) relative to today's date, to display records for the date values for that period.
    • To Date - You specify a past Time Level (Year, Quarter, Month, Week, Day, also includes Hour, and Minute if the column time is TIMESTAMP) relative to today's date, that you want to apply to the date values in the visualization.

      A To Date filter retrieves data from the beginning of the chosen period, for example, Month to Date retrieves data from midnight of the first day of this month up until the current date and time (that is, Today or Now).

  4. Click outside of the filter to close the filter panel.

Filter Data Using an Expression Filter

Using expression filters, you can define more complex filters using SQL expressions. Expression filters can reference zero or more data elements.

For example, you can create the expression filter "Sample Sales"."Base Facts"."Revenue" < "Sample Sales"."Base Facts"."Target Revenue". After you apply the filter, you see the items that didn’t achieve their target revenue.

You create expressions using the Expression Filter panel. You can drag and drop data elements to the Expression Filter panel and then choose functions to apply. Expressions are validated for you before you apply them.

If your workbook doesn't have a visualization, create one. See Begin to Build a Workbook and Create Visualizations.
  1. On the Home page, select a workbook, click the Actions menu, and then select Open.
  2. In the Visualize canvas, go to the filter bar and click Add Filter and then select Add Expression Filter.
  3. In the Expression Filter panel, compose an expression in the Expression field. For example, enter "Sample Sales"."Base Facts"."Revenue" < "Sample Sales"."Base Facts"."Target Revenue" to focus on items that didn’t achieve their target revenue.
  4. In the Label field, give the expression a name.
  5. Optional: Enter a description.

    The description displays in the tooltip when you hover over a calculation.

  6. Click Validate to check if the syntax is correct.
  7. When the expression filter is valid, then click Apply. The expression applies to the visualizations on the canvas.

Use Role-Based Filters

This topic describes what you need to know to apply role-based filters to workbooks and visualizations. Filters based on application roles, known as role-based filters, enable business analysts and users to access just the data they need.

Topics:

About Role-Based Filters

Filters based on application roles, known as role-based filters, enable Oracle Analytics business analysts and users to access just the data they need. For example, suppose you want some users to see North American sales data when they open a shared global sales workbook. To accomplish this, you create a custom application role called North American Analyst and then use it to filter the workbook data.

Role-based filters overview
  • Dataset owners apply filters based on Oracle Analytics application roles that allow users to see data that's applicable to their application roles.
  • You apply role-based filters to datasets.
  • You can apply multiple role-based filters to a dataset at the same time.
  • When you add role-based filters in the Dataset editor, the preview data shown isn't filtered, but the dataset is filtered when workbook consumers access the dataset.
  • You can use existing application roles or create your own application roles. For example, to filter a global sales dataset for analysts in Europe and North America you might create application roles EuropeanSales and NorthAmericaSales.


    Description of rbf-example-application-roles.png follows
    Description of the illustration rbf-example-application-roles.png

  • You use expressions to specify role-based filters. For example, on a North America Sales application role you might filter on the COUNTRY_SUBREGION column of a COUNTRIES table using the expression COUNTRY_SUBREGION = "Northern America".
    Description of rbf-example-3-config.png follows
    Description of the illustration rbf-example-3-config.png

  • Filter expressions can reference Oracle Analytics system variables. For example, on a Sales Rep application role you might filter on the SALESREP_ID column using the expression SALESREP_ID = USER(), where USER () is a system variable that provides the ID of the logged-in user.
    Description of rbf_example_simple2.png follows
    Description of the illustration rbf_example_simple2.png

  • In the Dataset editor, you display the role-based filter panel using the Hide or display the role-based filter bar option on the toolbar.
    Description of rbf_toggle.png follows
    Description of the illustration rbf_toggle.png

Tips on Using Role-based Filters
  • To filter a dataset for all users and roles, add a role-based filter to the Authenticated User role. For example, if you want users to only access data for the EMEA region, add the filter expression REGION = "EMEA" to the Authenticated User role.
    Description of rbf-global-filter.png follows
    Description of the illustration rbf-global-filter.png

  • When you place a role-based filter on a dataset, any user who doesn't have the role(s) specified won't be able to see any data, including the dataset owner. If a dataset owner needs to see the data, add the dataset owner to one of the specified roles. In addition, if users with a super role such as an Admin or the OrgVP need to see all data, create a dummy filter. For example, if you would like the OrgVP to see all data in a region, create an additional filter for the application role of OrgVP and create a filter with the expression 1=1. When a user with OrgVP logs in and creates a workbook, they will be able to see all of the data.

Filter a Dataset Based on Application Role

You use filters based on application roles to enable business analysts and users to access just the data they need. For example, you might want North American business users to only see North American data when they open a shared workbook.

When you've applied role-based filters to a dataset, add it to a workbook and share the workbook with other business analysts and users. When other users log in to Oracle Analytics and open the workbook, they'll only see data that you've shared with them using role-based filters.
  1. From the Home page, select a dataset, click the Actions menu, and then select Open.
  2. In the dataset editor, click Join Diagram.
  3. Click the Hide or display the role-based filter bar option on the toolbar.
    Description of rbf_toggle.png follows
    Description of the illustration rbf_toggle.png

  4. Hover over the role-based filter bar, and click Add Role (Add Role icon on role-based filter bar) to display a list of application roles.
    Description of rbf_add_role.png follows
    Description of the illustration rbf_add_role.png

  5. In the drop down list, select the application role that you'd like to use to filter the dataset.
  6. Right-click the application role and select Create Filter to display the Expression Filter editor.
  7. Optional: If required, use the Label field to change the filter name to something more meaningful.
  8. In the Expression box, enter the logical expression that filters the data. using single quotes around data values.
    For example, if your dataset has a column COUNTRY_SUBREGION that identifies geographical region, you might enter COUNTRY_SUBREGION = 'Northern America'.
  9. Click Validate, then click Apply.

Example One - Apply a Simple Role-Based Filter in a Workbook

This example shows how to filter a dataset so that North American sales representatives can access only North American sales data when they open a shared global sales workbook.

Example report

When North American sales representatives log into Oracle Analytics and open the shared global sales workbook, they only want to see sales data for North America. In this example, sales representative dvauthoruser can see sales for North American countries Canada and United States of America.


Description of rbf-example-3-rep.png follows
Description of the illustration rbf-example-3-rep.png

Example data

The example dataset contains sales data, with the geographical location stored in the COUNTRY_SUBREGION column in the COUNTRIES table.


Description of rbf-example-3-data.png follows
Description of the illustration rbf-example-3-data.png

Example users and application roles

  • User dvauthoruser is assigned the application role North America Sales.

Example dataset access configuration

In the Inspect dialog for the dataset, under Access, then Roles, authenticated users are given Read-Only access.
Description of rbf-example-3-share.png follows
Description of the illustration rbf-example-3-share.png

Example role-based filter

In the dataset editor, the workbook author applies a role-based filter to the North America Sales application role with the expression COUNTRY_SUBREGION = "Northern America".


Description of rbf-example-3-config.png follows
Description of the illustration rbf-example-3-config.png

Example Two - Apply Multiple Role-Based Filters in a Workbook

This example shows how sales vice presidents and sales representatives use the same shared workbook to analyze sales data. Vice presidents can see the sales data for every sales representative in their team. Sales representatives can only see their own sales data.

Example report

Sales vice presidents or sales representatives can open the same shared workbook and see data appropriate to their application role and user ID.

  • When a sales vice president logs into Oracle Analytics and opens the shared sales workbook, they see the sales data for each sales representative in their team. In this example, sales vice president dvauthoruser can see a sales summary for sales representatives in their team (bitechtest, bitechtest2, and bitechtest3).
    Description of rbf_example_complex_start.png follows
    Description of the illustration rbf_example_complex_start.png

  • When a sales representative logs into Oracle Analytics and opens the shared sales workbook, they only see their own sales data. In this example, sales representative bitechtest2 can see his or her sales $33,692.11.
    Description of rbf_example_simple_start.png follows
    Description of the illustration rbf_example_simple_start.png

Example data

The example dataset contains sales data, with the sales representative ID in the SALESREP_ID column.


Description of rbf_example_simple1.png follows
Description of the illustration rbf_example_simple1.png

The sales vice president to sales representative hierarchy is implemented using a RepHierarchy table.


Description of rbf_example_complex2.png follows
Description of the illustration rbf_example_complex2.png

The RepHierarchy table relates sales vice presidents to sales representatives in their team:


Description of rbf_example_complex1.png follows
Description of the illustration rbf_example_complex1.png

Example users and application roles

  • Users dvauthoruser and dvauthoruser2 are assigned the application role Sales VP.
  • Users bitechtest to bitechtest7 are assigned the application role Sales Rep.

Example dataset access configuration

In the Inspect dialog for the dataset, under Access, then Roles, authenticated users are given Read-Only access.
Description of rbf-example-3-share.png follows
Description of the illustration rbf-example-3-share.png

Example role-based filters

The workbook author applies two role-based filters to the dataset used by the workbook, one for sales vice presidents and one for sales representatives.