Apply Different Filter Types
Different filter types enable you to focus in on the data that you're interested in.
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.
- On the Home page, hover over a workbook, click Actions, then select Open.
- In the Visualize canvas, click the filter, then click Range.
- 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.
- 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.
- To apply the Top Bottom N filter to the canvas and all visualizations in the
workbook:
- On the Home page, hover over a workbook, click Actions, and then select Open.
- 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.
- In the filter bar, click the filter, then click Top Bottom N. You can only convert a range filter to Top Bottom N filter.
- To apply the Top Bottom N filter to a specific visualization in the
workbook:
- In the canvas, select the visualization that you want to filter.
- 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.
- In the filter bar, click the filter, then click Top Bottom N.
- To apply the Top Bottom N filter to an on-canvas filter:
- With the canvas selected, go to the Data Panel and click Visualizations, then click the List Box filter.
- 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.
- 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
- 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.
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.
- On the Home page, hover over a workbook, click Actions, then select Open.
- In the Visualize canvas, select a filter in the filter bar or on-canvas filter, and click Date Range.
- Click the drop-down list, and select the type of date range you want to apply (for example, Range, Start at, End at, Equal).
- Use the date pickers to configure the range.
- 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 or end of the last period.
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).
- On the Home page, hover over a workbook, click Actions, then select Open.
- In the Visualize canvas, click the filter in the filter bar or canvas, then click Relative Time.
- 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).
- 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.
- If for type you selected Last or Next, then in Relative To, choose Today if you want to filter on all data or choose End of Last Period to filter on data through the end of the last period.
- 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.
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.
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.
- 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 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"
. - 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. - 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.
- 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.
- 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 an 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.
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.
Example data
The example dataset contains sales data, with the geographical location stored in the COUNTRY_SUBREGION
column in the COUNTRIES table.
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 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"
.
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).
- 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
.
Example data
The example dataset contains sales data, with the sales representative ID in the SALESREP_ID
column.
Description of the illustration rbf_example_simple1.png
The sales vice president to sales representative hierarchy is implemented using a RepHierarchy table.
Description of the illustration rbf_example_complex2.png
The RepHierarchy table relates sales vice presidents to sales representatives in their team:
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 the illustration rbf-example-3-share.png
Example role-based filters
- The workbook author applies a role-based filter to the sales vice president 'Sales VP' application role with the expression
SALESVP_ID = USER()
. The argument USER() is a system variable in Oracle Analytics that provides the ID of the logged-in user. - The workbook author also applies a role-based filter to the 'Sales Rep' application role with the expression
SALESREP_ID = USER()
.