Drillthrough

The Drillthrough feature is enabled by default for all visualizations, except for tables and gauges.

NOTE: For more information on the Drillthrough feature for table visualizations, please refer to the articleon Tables (Part 1: Plotting Fields) .

Drillthrough is enabled when a Group or a Series is marked as a drillthrough filter, as shown in the screenshot of the chart visualization above, or the visualization has a drillthrough target (to a dashboard or a specific visual on a dashboard), as shown on the screenshot of the gauge visualization below.

In target dashboards, the drillthrough filter name entered in the Filter Name property is used to build context variables and substitution value expressions to reference this filter's selected value(s) as drillthrough filter value(s). Refer to Advanced Drillthrough Features for BI Dashboard Developers and Designers section for more information on using this property.

When Drillthrough is enabled, the chart becomes clickable.  When the chart is clicked, the user is taken to the drillthrough target defined for the visualization.  When the visualization does not have a drillthrough target defined, the current dashboard is the default target.  Each Group value or Series value marked as a drillthrough filter is sent to the target as a parameter filter. If page filters exist, all page filter values are also sent to the target dashboard.

The Parameter Filter pop-up window, launched from the Parameter Filter icon (), displays a history of viewed drillthrough visualizations.  It lists all parameter filters for the entire dashboard. A parameter filter is a field-value list pair in this pop-up window. All parameter filters on this pop-up window are active and the operator between these parameter filters is “and”. Click on the arrow icon to open a target previously drilled into.  An underlined parameter is from a page filter. A page filter may contain multiple values, separated by a semi-colon (;).  The operator between a page filter value is “or”.

To set what visualizations get filtered by a parameter filter, add the folder of the parameter filter's field to all visualizations that are to be filtered by it. Note, the filter's field does not need to be added to the visualizations, only its folder, even if the folder is not used by the visualization. 

During data retrieval for the visualizations, the system looks for visualizations with the same folder, and only ones with it are filtered.

The user can go back to any target that was drilled into. The Back icon () allows the user to see the last drillthrough dashboard that was viewed. Clicking on the Back icon discards the last set of parameter filters and takes the user “back” to the state prior to the creation of the last set of parameter filters.  The last Drillthrough visualization will be framed in green if it is drilling into the same dashboard.

When drilling up, the state (excluding data) of the dashboard at the time of drilling down is re-displayed to the user instead of the latest version from the database.  This allows the user to see the same parent dashboard when drilling back up.  In addition, if the user made changes to the current dashboard and forgot to save before drill-down, the changes to the current dashboard are not lost and the user can simply drill-up to retrieve them.

NOTE: The Parameter Filter icon and the Back icon appear on the upper right-hand corner of the dashboard, as well as on the currently selected visualization.

Advanced Drillthrough Features for BI Dashboard Developers and Designers

Two advanced drillthrough features are available for dashboard developers and designers:

  • Context Variable Name

  • Drillthrough Filter Name

The Context Variable Name is for the current dashboard (dashboard context variable) whereas the Drillthrough Filter Name is for the target dashboard. These two features are discussed in more detail in the subsections that follow.

Context Variable Name

The Context Variable property is used to enter a context variable name for the current dashboard. For example, when a dashboard filter is added to a dashboard, a context variable name can be assigned to the dashboard filter’s selected value(s) by entering it in the dashboard filter’s Format tab, as shown in the screenshot above. This context variable name makes the selected value(s) available to the database views for performance tuning via database context variables. 

NOTE: Refer to Supported Context Variables for the names of context variables a folder is supporting in BI Dashboard Builder using the Fields pane.

When multiple dashboard filters have the same context variable name, the value of the last dashboard filter (in flow layout) will be used.  When there is a drillthrough filter context variable with the same name as a dashboard filter context variable, the value of the dashboard filter context variable is used. A dashboard filter’s context variable name is automatically converted to uppercase.

Clicking on the Help icon () beside the Context Variable property provides additional details about the context variable name. SQL view developers can use these details during construction of SQL views to optimize performance.

Example

The following is an example of how a SQL view developer can consume the context variable in the database.

Given a context variable PARTNER_CODE with values ABC|AERO|ACTION|FEDEX|WALMART|GARMIN|MAGNET, we can use the variable to filter bpartners by bp_code in SQL as follows:

Copy 
with bpartner_code_ctx_var as
(select regexp_substr(sys_context('CMIC_BI', 'PARTNER_CODE'),'[^|]+',1,level) as bp_code
from dual
connect by regexp_substr(sys_context('CMIC_BI', 'PARTNER_CODE'),'[^|]+',1,level) is not null)
select bp.bp_code, bp.bp_name
from da.bpartners bp
inner join bpartner_code_ctx_var bp_ctx_var on bp.bp_code = bp_ctx_var.bp_code

Result:

Drillthrough Filter Name

In the target dashboards, drillthough filter value(s) can be made available to the database views for performance tuning using database context variables.

The Drillthrough property is used to enter the drillthrough filter name for the target dashboard. In the target dashboard, the drillthrough filter name turns into one context variable (or two if the data type is DATE) and a substitution value expression (e.g. #{^post_date}).

Pop-up window launched from Context Information option in the Dashboard drop-down menu

Since there may be name conflicts, the Context Information pop-up shows the consolidated names and values for the current page. It contains the following information:

  • Context variables and values for the SQL view developers to build/debug the SQL views.  Some variables are not from the parameters but from the current page's dashboard filters.

  • Parameter filter value expressions and values for dashboard designers to build/debug dashboards.

When multiple drillthough filters have the same context variable name, the last drillthrough value is used.

Clicking on the Help icon () beside the Drillthrough Name property provides additional details about the drill-down filter name. The selected value(s) can be made available to the database views for performance tuning using database context variables, as shown in the screenshots below.

When multiple dashboard filters have the same drillthrough filter substitution value expression, the value of the last dashboard filter (in flow layout) will be used. When there is a drillthrough filter context variable with the same name as a dashboard filter context variable, the value of the dashboard filter context variable is used.

 

 

Drillthrough filter value(s) can be made available to visualization filters using a substitution value expression. By using a substitution value expression, the target visualization can eliminate the join to the filter value’s folder. This may significantly improve performance for complex queries.

All dashboard filter values are passed to target dashboards during drillthrough. When a dashboard filter contains multiple values, its drillthrough substitution value expression will be resolved to multiple values. In this case, a filter criterion using the drillthrough substitution value expression will be expanded.

For example, if the expression #{^amount} is resolved to 100, 200, 300, the criterion:

budget is #{^amount}

is resolved to:

budget is 100 or budget is 200 or budget is 300

 

A substitution variable visual can be passed as a parameter filter to target dashboards.  In the target dashboard, its value can be referenced by using a value expression (e.g. #{^post_date} ), as shown in the screenshot above.