Key Performance Indicators (KPI's)

A Key Performance Indicator (KPI) is a quantifiable measurement used to gauge a company’s performance. In Deem Insight, KPIs are displayed within widgets for the associated report.

KPI Method Overview

addKpi

The addKpi method is mandatory for creating a new KPI, as it initializes the widget in the associated report.

Syntax with parameter variables:

$olap.addKpi(
    "kpi_id",
    "title_label",
    "time_column",
    "time_interval",
    "target_value",
    "real_value",
    "yellow_budget_rate",
    "green_budget_rate",
    "trend_icon_mode",
    "data_filter"
    )

Parameter Variable Description

Normal parameter variables:

• kpi_id: A user-defined unique identifier for this KPI. The kpi_id specified here must be passed as an argument to subsequent methods (e.g., setKpiSource, setKpiSize) to further configure and customize the same KPI.
• title_label: The text label displayed as the title of this KPI.
• target_value: Defines the target or benchmark for the KPI. Typically used for comparison with the actual value. Set to 0 if no target is required.
• real_value: Defines the actual (realized) value of the KPI to be compared against the target. Typically contains the expression for the current performance metric.
• yellow_budget_rate: Defines the percentage of the budget at which the KPI color-status changes from red to yellow. Must be lower than green_budget_rate. Set to NULL if not required.
• green_budget_rate: Defines the percentage of the budget at which the KPI color-status changes from yellow to green. Must be higher than yellow_budget_rate. Set to NULL if not required.
• trend_icon_mode: Controls how trend indicators and icons are displayed for the KPI. Set to NULL if not required.
  • Valid arguments: 0 = Icon only, 1 = Icon and trend line, 2 = Trend line only, 3 = No icon or trend line
• data_filter: General filter expression to limit which data is included for the KPI. Set to NULL if not required.

Mutual dependent parameter variables (The column value used in time_column must match the period format specified in time_interval. For example when comparing different years between target_value and real_value, you need to select TPM or YTDM. Set both time_column and time_interval to NULL if not required):

• time_column: Specifies the column containing time-related data in order to delimit the time interval for the data used within the method.
• time_interval: Delimits the time interval for the data used in the method.
  • Valid arguments: YTD (default), NULL, TP, TPM, YTDM, 12M

Example with given arguments:

$olap.addKpi(
    "OR_YTDM",
    "Operating Revenue (Year-To-Day-Month)",
    "[time_month_no]",
    "YTDM",
    "SUM(CASE WHEN [time_year] = ?period.getYear(-1) AND [dim_1] BETWEEN '3000' AND '3999' THEN [accounted_amount_dcur] * -1 ELSE 0 END) * (1 + 0.1)",
    "SUM(CASE WHEN [time_year] = ?period.getYear() AND [dim_1] BETWEEN '3000' AND '3999' THEN [accounted_amount_dcur] * -1 ELSE 0 END",
    "0.8",
    "1.0",
    "1",
    "[dim_1_profit_or_loss_account] = 1"
    )

setKpiSource

Overrides the default data source for a KPI, allowing it to retrieve data from an alternative table or view. This is useful when KPI data is stored in a different dataset than the report’s main data source. Using filter and drill functionality works if the dimensions are equally named (same column names).

Syntax with parameter variables:

$olap.setKpiSource(
    "kpi_id",
    "alias_key",
    "matrix_name"
    )

Parameter Variable Description

Normal parameter variables:

• kpi_id: The unique identifier of the KPI to update.
• matrix_name: The name of the alternative data source table or view.

Optional argument:

• alias_key: The connection key for an external database if the data source is in a different database.

Examples with given arguments:

$olap.setKpiSource(
    "Operating Revenue (Year-To-Day-Month)",
    "fact_general_ledger"
    )

$olap.setKpiSource(
    "Operating Revenue (Year-To-Day-Month)",
    "mssql",
    "fact_general_ledger"
    )

setKpiSize

Defines the width of the KPI widget for different screen sizes, using bootstrap grid classes. This enables responsive layouts so tables look good on mobile, tablet, and desktop devices.

Syntax with parameter variables:

$olap.setKpiSize(
    "kpi_id",
    "xs_size",
    "sm_size",
    "md_size",
    "lg_size"
    )

Parameter Variable Description

• kpi_id: The unique identifier of the KPI to update.
• xs_size: Width of the KPI on extra small screens (mobile).
• Valid arguments: XS1, XS2, …, XS12
• sm_size: Width of the KPI on small screens (tablets).
• Valid arguments: SM1, SM2, …, SM12
• md_size: Width of the KPI on medium screens (desktops).
• Valid arguments: MD1, MD2, …, MD12
• lg_size: Width of the KPI on large screens (large desktops).
• Valid arguments: LG1, LG2, …, LG12

Example with given arguments:

$olap.setKpiSize(
    "Operating Revenue (Year-To-Day-Month)",
    "XS12",
    "SM3",
    "MD3",
    "LG3"
    )

setKpiFormat

Defines the display format for a KPI value (e.g., number of decimals, thousand-separators, percentage signs).

Syntax with parameter variables:

$olap.setKpiFormat(
    'kpi_id',
    'value_format'
    )

Parameter Variable Description

• kpi_id: The unique identifier of the KPI to update.
• value_format: The display format for the column value, using standard formatting syntax (e.g., number of decimals, thousand-separators, percentage signs).

Example with given arguments:

$olap.setKpiFormat(
    'Operating Revenue (Year-To-Day-Month)',
    '###,##0.00'
    )

Tip

For this method, you must use single quotes (”) around arguments instead of double quotes ("") due to syntax requirements. When applying a percentage format, do not scale ratios by multiplying by 100. The value should always remain in the range 0–1. The formatting logic will handle the conversion automatically.

setKpiColors

Sets custom colors for a KPI based on its status thresholds (e.g., red = poor, green = good).

Syntax with parameter variables:

$olap.setKpiColors(
    "kpi_id",
    "blue_color",
    "green_color",
    "orange_color",
    "red_color"
    )

Parameter Variable Description

• kpi_id: The unique identifier of the KPI to update.
• green_color: The color for KPI values above green_budget_rate (good performance).
• yellow_color: The color for KPI values between yellow_budget_rate and green_budget_rate (warning).
• red_color: The color for KPI values below yellow_budget_rate (poor performance).

Example with given arguments:

$olap.setKpiColors(
    "Operating Revenue (Year-To-Day-Month)",
    "blue",
    "green",
    "orange",
    "red"
    )

setKpiIcons

Sets the image for a KPI widget, changing dynamically based on whether the KPI is in red, yellow, or green color-status. Available images: fontawesome.

Syntax with parameter variables:

$olap.setKpiIcons(
    "kpi_id",
    "red_image",
    "yellow_image",
    "green_image"
    )

Parameter Variable Description

• kpi_id: The unique identifier of the KPI to update.
• red_image: Immage to display when KPI value is below yellow_budget_rate.
• yellow_image: Image to display when KPI value is between yellow_budget_rate and green_budget_rate.
• green_image: Image to display when KPI value is above green_budget_rate.

Example with given arguments:

$olap.setKpiIcons(
    "Operating Revenue (Year-To-Day-Month)",
    "frown_o",
    "meh_o",
    "smile_o"
    )

Tip

images with hyphens (-) in the name must be written with underscores (_). Example: user-circle-o becomes user_circle_o.

setKpiTargetRealName

Changes the default display names for the target and actual (real) values in a KPI widget. Useful for giving the KPI more user-friendly labels.

Syntax with parameter variables:

$olap.setKpiTargetRealName(
    "kpi_id",
    "target_label",
    "real_label"
    )

Parameter Variable Description

• kpi_id: The unique identifier of the KPI to update.
• target_label: The custom label to display for the target value.
• real_label: The custom label to display for the actual value.

Example with given arguments:

$olap.setKpiTargetRealName(
    "Operating Revenue (Year-To-Day-Month)",
    "Budget",
    "Actual"
    )

setKpiTargetRealDisplayField

Replaces the KPI’s numeric target and actual values with custom text labels. This is useful when the KPI should display a status message instead of numerical values.

Syntax with parameter variables:

$olap.setKpiTargetRealDisplayField(
    "kpi_id",
    "target_value",
    "real_value"
    )

Parameter Variable Description

• kpi_id: The unique identifier of the KPI to update.
• target_value: Text to show instead of the real target value.
• real_value: Text to show instead of the real actual value.

Example with given arguments:

$olap.setKpiTargetRealDisplayField(
    "Operating Revenue (Year-To-Day-Month)",
    "50 000 000",
    "~ 43 000 000"
    )

setKpiStyleSize

Sets the text style size for a KPI widget, adjusting how large the value appear.

Syntax with parameter variables:

$olap.setKpiStyleSize(
    "kpi_id",
    "style_size"
    )

Parameter Variable Description

• kpi_id: The unique identifier of the KPI to update.
• style_size: Defines the text size.
  • Valid arguments: huge (default), large, normal, samll, tiny.

Example with given arguments:

$olap.setKpiStyleSize(
    "Operating Revenue (Year-To-Day-Month)",
    "tiny"
    )

setKpiNegativePositiveLine

Enables a trend line in the KPI widget that uses dynamic colors: red for negative values and green for positive values, regardless of KPI thresholds.

Syntax with parameter variables:

$olap.setKpiNegativePositiveLine(
    "kpi_id"
    )

Parameter Variable Description

• kpi_id: The unique identifier of the KPI to update.

Example with given arguments:

$olap.setKpiNegativePositiveLine(
    "Operating Revenue (Year-To-Day-Month)"
    )

setKpiPlotLine

Adds a horizontal plot line at value = 0 in the KPI’s trend line chart to highlight the neutral threshold. This method depends on the trend-line being activated.

Syntax with parameter variables:

$olap.setKpiPlotLine(
    "kpi_id"
    )

Parameter Variable Description

• kpi_id: The unique identifier of the KPI to update.

Example with given arguments:

$olap.setKpiPlotLine(
    "Operating Revenue (Year-To-Day-Month)"
    )

hideKpiTargetInfo

Hides the target value information from the KPI widget, showing only the actual (realized) value.

Syntax with parameter variables:

$olap.hideKpiTargetInfo(
    "kpi_id"
    )

Parameter Variable Description

• kpi_id: The unique identifier of the KPI to update.

Example with given arguments:

$olap.hideKpiTargetInfo(
    "Operating Revenue (Year-To-Day-Month)"
    )

setKpiUrlLink

Adds a clickable link to the KPI widget that navigates to a report or another internal URL when the link is clicked.

Syntax with parameter variables:

$olap.setKpiUrlLink(
    "kpi_id",
    "internal_url"
    )

Parameter Variable Description

• kpi_id: The unique identifier of the KPI to update.
• internal_url: The clickable link. Part of or the full internal URL (e.g., report reference). The full internal URL in the example: http://localhost:18080/demo/ui#!x35_109

Example with given arguments:

$olap.setKpiUrlLink(
    "Operating Revenue (Year-To-Day-Month)",
    "x35_109"
    )

setKpiExternalUrlLink

Adds a clickable external URL to a KPI widget. When the user clicks the link, they are redirected to the specified web address.

Syntax with parameter variables:

$olap.setKpiExternalUrlLink(
    "kpi_id",
    "external_url"
    )

Parameter Variable Description

• kpi_id: The unique identifier of the KPI to update.
• external_url: The clickable link. The full external URL.

Example with given arguments:

$olap.setKpiExternalUrlLink(
    "Operating Revenue (Year-To-Day-Month)",
    "https://www.deem.no/en/"
    )

setKpiFilterCollection

Controls how filters and OLAP-actions (slice, dice, drill and pivot) affect a KPI. You can choose whether the KPI responds to all filter scope, only header filters, or remains static.

Syntax with parameter variables:

$olap.setKpiFilterCollection(
    "kpi_id",
    "olap_scope"
    )

Parameter Variable Description

• kpi_id: The unique identifier of the KPI to update.
• olap_scope: Defines which OLAP-actions affect the KPI.
  • Valid arguments: all = Respond to all actions, header = Respond only to header actions, report = Respond only to report-level actions.

Example with given arguments:

$olap.setKpiFilterCollection(
    "Operating Revenue (Year-To-Day-Month)",
    "header"
    )

setKpiHideIfNull

Hides the KPI widget from the dashboard if its value is NULL (empty or not calculated).

Syntax with parameter variables:

$olap.setKpiHideIfNull(
    "kpi_id",
    )

Parameter Variable Description

• kpi_id: The unique identifier of the KPI to update.

Example with given arguments:

$olap.setKpiHideIfNull(
    "Operating Revenue (Year-To-Day-Month)",
    )

setKpiHideIfZero

Hides the KPI widget from the dashboard if its calculated value is zero (0).

Syntax with parameter variables:

$olap.setKpiHideIfZero(
    "kpi_id",
    )

Parameter Variable Description

• kpi_id: The unique identifier of the KPI to update.

Example with given arguments:

$olap.setKpiHideIfZero(
    "Operating Revenue (Year-To-Day-Month)",
    )

KPI Wizard

The KPI Wizard makes it easier to create KPIs with minimal scripting. By using the wizard, users can follow a guided process to define and customize their KPIs more efficiently.

Settings

In the settings section of the wizard, the more technical aspects of the KPI are configured, such as real and target value, period and format.

Layout

This is where the more visual part of the kpi is configured, such as the layout type, icons and limits and the size of the kpi.

KPI Examples

$olap.addKpi("1", "Result", "[time_period]", "", "(SUM([budget_amount_dcur]) * -1) + 1000", "(SUM([accounted_amount_dcur]) * -1)", "0.8", "1.0", "1", "")
$olap.setKpiFormat('1', '###,###')
$olap.setKpiTargetRealName("1", "Budget", "Real")
$olap.setKpiSize("1", "XS12", "SM3", "MD3", "LG3")

$olap.addKpi("2", "Profit %", "[time_period]", "", "(SUM([budget_amount_dcur]) * -1) / (SUM(CASE WHEN [dim_1] BETWEEN '3000' AND '3999' THEN [budget_amount_dcur] ELSE 0 END) * -1)", "(SUM([accounted_amount_dcur]) * -1) / (SUM(CASE WHEN [dim_1] BETWEEN '3000' AND '3999' THEN [accounted_amount_dcur] ELSE 0 END) * -1)", "0.8", "1.0", "1", "")
$olap.setKpiFormat('2', '##0.00 %')
$olap.setKpiTargetRealName("2", "Budget", "Real")
$olap.setKpiSize("2", "XS12", "SM2", "MD2", "LG2")

$olap.addKpi("3", "COGS as % of turnover", "[time_period]", "", "(SUM(CASE WHEN [dim_1] BETWEEN 4000 AND 4999 THEN [budget_amount_dcur] ELSE 0 END)) / (SUM(CASE WHEN [dim_1] BETWEEN 3000 AND 3999 THEN [budget_amount_dcur] ELSE 0 END) * -1)", "(SUM(CASE WHEN [dim_1] BETWEEN 4000 AND 4999 THEN [accounted_amount_dcur] ELSE 0 END)) / (SUM(CASE WHEN [dim_1] BETWEEN 3000 AND 3999 THEN [accounted_amount_dcur] ELSE 0 END) * -1)", "1.2", "1.0", "1", "")
$olap.setKpiFormat('3', '##0.00 %')
$olap.setKpiTargetRealName("3", "Budget", "Real")
$olap.setKpiSize("3", "XS12", "SM2", "MD2", "LG2")
$olap.setKpiIcons("3", "align_justify", "align_justify", "align_justify")

$olap.addKpi("4", "Wages as % of turnover", "[time_period]", "", "(SUM(CASE WHEN [dim_1] BETWEEN 5000 AND 5999 THEN [budget_amount_dcur] ELSE 0 END)) / (SUM(CASE WHEN [dim_1] BETWEEN 3000 AND 3999 THEN [budget_amount_dcur] ELSE 0 END) * -1)", "(SUM(CASE WHEN [dim_1] BETWEEN 5000 AND 5999 THEN [accounted_amount_dcur] ELSE 0 END)) / (SUM(CASE WHEN [dim_1] BETWEEN 3000 AND 3999 THEN [accounted_amount_dcur] ELSE 0 END) * -1)", "1.2", "1.0", "1", "")
$olap.setKpiFormat('4', '##0.00 %')
$olap.setKpiTargetRealName("4", "Budget", "Real")
$olap.setKpiSize("4", "XS12", "SM2", "MD2", "LG2")
$olap.setKpiIcons("4", "users", "users", "users")

$olap.addKpi("5", "Operating expenses as % of turnover", "[time_period]", "", "ROUND((SUM(CASE WHEN [dim_1] BETWEEN 6000 AND 7999 THEN [budget_amount_dcur] ELSE 0 END)) / (SUM(CASE WHEN [dim_1] BETWEEN 3000 AND 3999 THEN [budget_amount_dcur] ELSE 0 END) * -1), 3)", "ROUND((SUM(CASE WHEN [dim_1] BETWEEN 6000 AND 7999 THEN [accounted_amount_dcur] ELSE 0 END)) / (SUM(CASE WHEN [dim_1] BETWEEN 3000 AND 3999 THEN [accounted_amount_dcur] ELSE 0 END) * -1), 3)", "1.2", "1.0", "1", "")
$olap.setKpiFormat('5', '##0.00 %')
$olap.setKpiTargetRealName("5", "Budget", "Real")
$olap.setKpiSize("5", "XS12", "SM3", "MD3", "LG3")

$olap.addKpi("3", "Female employees", "[time_period]", "", "0", "(SUM(CASE WHEN [sex] = 'Female' THEN [degree_of_occupation] ELSE 0.000001 END) * 100) / SUM(CASE WHEN [degree_of_occupation] > 0 THEN [degree_of_occupation] ELSE 0.0001 END)", "1.2", "1.0", "0", "")
$olap.setKpiFormat('3', '##0.00 %')
$olap.setKpiSize("3", "XS12", "SM2", "MD2", "LG2")
$olap.setKpiIcons("3", "female", "female", "female")
$olap.hideKpiTargetInfo("3")