Drill

A Drill is a predefined route through different data dimensions and can be used to present the details behind KPI values, making it possible to drill down to underlying data and suborganizations to find more information about the figures calculations or comparisons are based on.

Two types of drills, Time drills and Actual-Budget drills, are predefined in the system in Shared web items. Other drills, including Custom drills, can also be created there.

Some drills follow the predefined hierarchy in the node structure (fixed structure). In other cases an AGG function creates the drill pattern (variable structure).

Drills may be added to any function. This is done in the Drill section of a function's configuration.

Some functions may be unsuitable or have a structure that is not logical to drill through.

E.g. if you assign a drill to a function that contains an IF-THEN- ELSE statement, the drill will contain the results of both functions, i.e. the "THEN" and the "ELSE".

Drill table links that refer to an incomplete set of dimensions, e.g. a function that is based on an expression, will produce an empty table.

Using Custom periods affects what is displayed in drills. When a Time drill is used with a Custom period, the normal date range functionality will be ignored and the Custom year will define the size of the Time drill period. In Actual-budget drills and custom drills, YTD will be calculated for the Custom year not the calendar year.

It is also possible to drill even further down in data that is stored in an external database, by basing the drill on a specially configured Query table in the Templates model. A Connector to the external database that the Query table is based on must already exist in Resources, and the Query table must be selected in Detail table in the drill configuration (see below).

Adding Drills to functions

You can add a Time drill, an Actual-Budget drill, and/or any Custom drill that has been created in your system to a function by clicking the icon to the right of the Drill field and marking one or more boxes under Drill.

Default Time drill shows the figures for the entire current year to date for all the nodes (with subnodes) and organizations that are specified in the function, per period
Default Actual-Budget drill shows a comparison of actual and budgeted values for the current period and year to date
A Custom drill allows you to create your own drill by modifying the function's period and node type

By clicking Include operator rows you can show rows containing the mathematical operators used in the function.

By selecting Include top row a row containing the calculator function(s) that are used in the function will be displayed.

If Hide NA and NaN rows is checked in the function, rows that don't contain values or where the values are invalid ("Not a Number", e.g. divided by 0) will not be displayed in the drill. If Hide 0 rows is checked, rows where all values are 0 will not be displayed in the drill.

When you select a drill on a function, the drill icon will appear in the function's Shortcut column on the KPI page on the web, indicating that there is at least one drill for that function. Then simply click on the icon to see the drill(s).

Using Custom periods affects what is displayed in drills. When a Time drill is used with Custom period, the normal date range functionality will be ignored and the Custom year will define the size of the Time drill period. In Actual-budget drills and Custom drills YTD will be calculated for the Custom year not the calendar year.

Renaming in Drills

You may sometimes want to change the names of nodes or functions in drills, in order to increase their readability, to differentiate between several references to the same node in a function etc. This can be done using 'AS' in the function.

Renaming nodes in a function

You can rename nodes in functions that will be used in drills, e.g. to differentiate between references to the same node with different node types.

Example:

A[100] as 'renamed actual' - B[100] as 'renamed budget'.

The texts "renamed actual" and "renamed budget" will be displayed instead of the node name.

Renaming functions

You can use 'AS' in any function that contains the "organization" reference, e.g. AGG, AGGAVG, IND, INDEX, as well as TOP functions.

Examples:

AGG('IND('[Just manager]+[Visible manager]') AS 'Good management'',100)

In the Drill, the IND function will be renamed "Good management"

AGG('IND('[Just manager]+[ Just manager]')',100) AS 'Good management'

In the Drill, the AGG function will be renamed "Good management"

If an organization based drill (e.g. AGG, INDEX, TQ, etc.) has a single organization reference and does not contain the 'AS' keyword after the AGG expression, the function itself will not be visible in the drill. This includes all functions that can contain an organization list.
Examples:

AGG('[100]',*this) - the AGG line will not be visible in the drill
AGG('[100]',*100,*102) - a line containing AGG will be visible in the drill
AGG('[100]',*this) as 'test' - a line containing 'test' will be visible in the drill
AGG('[100]',this) as 'test' - no AGG or 'test' line will be visible in the drill as there is only one organization level in the function