QPR ProcessAnalyzer Table: Difference between revisions
No edit summary |
|||
(62 intermediate revisions by 3 users not shown) | |||
Line 1: | Line 1: | ||
Table | Table is a basic but versatile visualization for a flat dataset which is the data structure provided by the calculation. | ||
== | == Table functionalities == | ||
Table has the following functionalities: | |||
* | * Table shows rows in queried data as the table rows and dimensions/measures/columns as the table columns (in the order as they are defined and dimensions left of measures). | ||
* If there are dimensions/columns where corresponding filters can be created, clicking rows/cells creates the filter. | |||
* | * When there is maximum of one dimension/column defined, the table has row selection mode in use. When there are more than one dimension/column, cell selection mode is in use. | ||
* By default, there is the multi-selection in use (i.e. clicking keeps the previous selections). Single-selection can be applied with Ctrl key pressed. Range selection within a column can be used with Shift key. | |||
* | * Table rows can be sorted ascending or descending by columns. Multi-column sorting works with Ctrl key pressed. | ||
* | * Table columns can be reordered by dragging them from the header. | ||
* | * Table columns can be resized from the handle right side of the column header. The new widths are stored permanently to chart settings when the dashboard is saved. | ||
* | * Table can be filtered using the Excel-style column filtering, which is converted into model filter when the selection is confirmed. There are different filtering options available depending on the column type, e.g. for textual columns there are ''equals'', ''not equals'', ''starts with'', ''ends with'', ''contains'' and ''not contains'' filters, and for numerical columns there are ''equals'', ''not equals'', ''less than (or equals)'' and ''greater than (or equals)'' filters. In addition, greater and less than filters can be combined to have ''between'' filtering. | ||
* | * The table has an optional title above the table. | ||
* | * Table can be exported as Excel file and CSV file. Precise values of numbers and dates will go to the Excel file. | ||
* | * If there are more than 200 rows in the table, paging is enabled and footer for controlling paging is visible. The footer also has an option to change the number of rows per page. | ||
* | * Table supports the [[QPR_ProcessAnalyzer_Chart#Measure.2C_Dimension_and_Column_Settings|Custom Layout]] settings (options supported by Syncfusion) overriding the default settings set by the chart. | ||
* | * Columns can be hidden using the [[QPR_ProcessAnalyzer_Chart#Measure.2C_Dimension_and_Column_Settings|Hidden]] checkbox in the measure/dimension/column settings. | ||
* | * Text in a table cell can be set to be a [[QPR_ProcessAnalyzer_Chart#Measure.2C_Dimension_and_Column_Settings|web link]] to an external site (that opens to a new tab). The text will be the clickable link and clicking outside the text still makes model filter like without the web link. | ||
== Table actions == | |||
* fixed value, e.g. 5 | Clicking text items in the table can change dashboard variable values or open web links. These are configured using the table actions as follows: | ||
* | * '''Set dashboard variables''': Defines [[Dashboard_Variables|variables]] that are set, when clicking the text in the table cell. Similar tags as for the web link (more below), can be used both for the variable name and variable value fields. If there is either the sys:dashboard or sys:dashboardIdentifier variable among the changed variables (i.e. clicking will go to other dashboard), the variables are set to the session level - otherwise they are set to the dashboard level. If defining the ''Filter'' or ''Comparison'' variable with an empty value, the corresponding filter or comparison rule is created automatically (works only for dimensions). | ||
* | * '''Open web link''': When defined (works in tables only), the measure/dimension/column will be a clickable web link opening the target web site. Only the text in the table cell is clickable, so filtering is possible when clicking next to the text. To refer to the data in the same row, web links can contain tags (e.g. ''${dimension:1}''), which are replaced with the data. You can also refer to the clicked value with ''${this}''. If the web link only consists of a single tag, the tag value is not encoded (because the tag is assumed to contain a full url). If the web link contains static text in addition to the tags, the tag values are encoded using encodeUriComponent (because tags are assumed to be used as url parameters). If the link does not start with http:// or https://, the link is interpreted as a relative link which is added to the end of the QPR ProcessAnalyzer address to form the complete link. Examples: ''<nowiki>https://google.com/search/q=${measure:2}</nowiki>'', ''<nowiki>https://wiki.onqpr.com/pa/index.php?search=${this}</nowiki>'', ''<nowiki>qprpa/ui/#/dashboard?sys:dashboard=123&myVariable=${dimension:1}</nowiki>'' | ||
* | |||
There is also a checkbox '''Open in new window''' to determine whether to use same browser window or open to a new window or tab. When checked, the link will be opened or variables will be set in a new browser window or tab. When unchecked, the link will be opened or variables will be set in the same window. | |||
It's also possible to run scripts that are available in the [[Actions_to_Run_Script_in_Table|table context menu]]. | |||
==Conditional formatting== | |||
Table conditional formatting can be used to define table cell background colors, text colors, colored icons and databar, where all depend on the data in the table. Conditional formattings are defined in the measure/dimension/column settings as JSON with following allowed properties: | |||
* '''backgroundColor''' / '''textColor''' | |||
** '''rules''': Rules are checked in the defined order and the first matching rule determines the color. | |||
*** '''color''': Color to use when the rule matches. | |||
*** '''value''': Value to compare. Value can be numerical, textual, boolean or null. | |||
*** '''comparison''': Type of the comparison which is one of the following: <, >, <=, >=, =, !=. Less than and greater than comparisons can only be used for numbers. | |||
** '''scale''' | |||
*** '''color''': Color for the scale boundary. | |||
*** '''value''': Value for the scale boundary. Value needs to be numerical. | |||
** '''color''': Color that is used when there are no matching rules, or rules/scale is not defined. | |||
** '''value''': When defined, conditional formatting is based on this value. When not defined, the cell value is used by default. | |||
* '''databar''' | |||
** '''min''': Value that represents the databar minimum position (i.e. when databar is not visible). | |||
** '''max''': Value that represents the databar maximum position (i.e. databar fills the whole cell). | |||
** '''value''': Value where the databar width is based on. When not defined, the databar width is based on the data in the same cell. | |||
** '''color''': Color of the databar. Can be defined in similar way as the ''backgroundColor''. Default color is #4bc3ff. | |||
* '''icon''' | |||
** '''rules''': Similar syntax can be used as in the ''backgroundColor'' rules, except instead of "color" there is "icon" attribute to define the icon. All Google Material Icons are available: https://material.io/resources/icons/. | |||
** '''color''': Color of the icon. Can be defined in similar way as the ''backgroundColor''. Default color is #333333. | |||
=== Defining numeric properties === | |||
All values in the conditional formatting can be defined as follows: | |||
* fixed numerical value, e.g.,<syntaxhighlight lang="json" line> | |||
"value": 5 | |||
</syntaxhighlight> | |||
* fixed textual value, e.g.,<syntaxhighlight lang="json" line> | |||
"value": "Dallas" | |||
</syntaxhighlight> | |||
* fixed boolean value, e.g.,<syntaxhighlight lang="json" line> | |||
"value": true | |||
</syntaxhighlight> | |||
* aggregate of the same column data, e.g.,<syntaxhighlight lang="json" line> | |||
"value": { | |||
"aggregate": "max" | |||
} | |||
</syntaxhighlight> | |||
* refer to a cell value in other column in the same row, e.g.,<syntaxhighlight lang="json" line> | |||
"value": { | |||
"type": "dimension", | |||
"index": 1 | |||
} | |||
</syntaxhighlight> | |||
* aggregate of another column data, e.g.,<syntaxhighlight lang="json" line> | |||
"value": { | |||
"type": "measure", | |||
"index": 2, | |||
"aggregate": "max" | |||
} | |||
</syntaxhighlight> | |||
Available aggregations are: min, max, sum, average, median, first, last. | Available aggregations are: min, max, sum, average, median, first, last. | ||
== | === Defining color and icon properties === | ||
All color and icon properties in conditional formatting can be defined as follows: | |||
* fixed value, e.g.,<syntaxhighlight lang="json" line> | |||
"value": "#AABBCC" | |||
</syntaxhighlight> | |||
< | * refer to a cell value in other column in the same row, e.g.,<syntaxhighlight lang="json" line> | ||
"value": { | |||
"type": "dimension", | |||
"index": 1 | |||
} | |||
</syntaxhighlight> | |||
== Conditional formatting examples == | |||
Below are listed the examples used in dashboards with their respective use cases and Conditional formatting expressions. The recommended colors and their color codes are as follows: | |||
* Green: #68DD8F (Positively correlating KPIs) | |||
* Red: #EF5254 (Negatively correlating KPIs) | |||
* Blue: #4BC3FF (Case/Event counts) | |||
* Dark blue: #0F55A5 (Automation) | |||
* Orange: #FEA88A (Durations) | |||
* Yellow: #FFD851 (Value/Cost) | |||
===Traffic lights=== | |||
Use case: KPIs based on traffic light signaling | |||
[[File:Trafficlightconiditionalformatting.png|200px]] | |||
<syntaxhighlight lang="json" line> | |||
{ | |||
"backgroundColor": { | |||
"rules": [ | |||
{ | |||
"color": "#EF5254", | |||
"value": 10, | |||
"comparison": ">=" | |||
}, | |||
{ | |||
"color": "#FFD851", | |||
"value": 6.5, | |||
"comparison": ">=" | |||
}, | |||
{ | |||
"color": "#68DD8F" | |||
} | |||
] | |||
} | |||
} | |||
</syntaxhighlight> | |||
===Green databar=== | |||
Use case: KPIs where 100% is a desired value, eg. On-Time Delivery | |||
[[File:Greendatabar.png|200px]] | |||
<syntaxhighlight lang="json" line> | |||
{ | |||
"databar": { | |||
"min": 0, | |||
"max": 100, | |||
"color": { | |||
"scale": [ | |||
{ | |||
"value": 0, | |||
"color": "#FFFFFF" | |||
}, | |||
{ | |||
"value": { "aggregate": "max" }, | |||
"color": "#68DD8F" | |||
} | |||
] | |||
} | |||
} | |||
} | |||
</syntaxhighlight> | |||
===Red databar=== | |||
Use case: KPIs where 0% is the desired value and you want to highlight the worst performers of this metric, eg. Material shortages | |||
[[File:Reddatabar.png|200px]] | |||
<syntaxhighlight lang="json" line> | |||
{ | |||
"databar": { | |||
"min": 0, | |||
"max": { "aggregate": "max" }, | |||
"color": { | |||
"scale": [ | |||
{ | |||
"value": 0, | |||
"color": "#FFFFFF" | |||
}, | |||
{ | |||
"value": { "aggregate": "max" }, | |||
"color": "#EF5254" | |||
} | |||
] | |||
} | |||
} | |||
} | |||
</syntaxhighlight> | |||
===Blue databar=== | |||
Use cases: Case/event counts | |||
[[File:Bluedatabar.png|200px]] | |||
<syntaxhighlight lang="json" line> | |||
{ | |||
"databar": { | |||
"min": 0, | |||
"max": { "aggregate": "max" }, | |||
"color": { | |||
"scale": [ | |||
{ | |||
"value": 0, | |||
"color": "#FFFFFF" | |||
}, | |||
{ | |||
"value": { "aggregate": "max" }, | |||
"color": "#4BC3FF" | |||
} | |||
] | |||
} | |||
} | |||
} | |||
</syntaxhighlight> | |||
===Dark blue databar=== | |||
Use case: Automation rate and similar metrics that scale 0-100% | |||
[[File:Darkbluedatabar.png|200px]] | |||
<syntaxhighlight lang="json" line> | |||
{ | |||
"databar": { | |||
"min": 0, | |||
"max": 100, | |||
"color": { | |||
"scale": [ | |||
{ | |||
"value": 0, | |||
"color": "#FFFFFF" | |||
}, | |||
{ | |||
"value": { "aggregate": "max" }, | |||
"color": "#7588C4" | |||
} | |||
] | |||
} | |||
} | |||
} | |||
</syntaxhighlight> | |||
===Yellow databar=== | |||
Usecase: Monetary KPIs such as cost/value | |||
[[File:Yellowdatabar.png|200px]] | |||
<syntaxhighlight lang="json" line> | |||
{ | |||
"databar": { | |||
"min": 0, | |||
"max": { "aggregate": "max" }, | |||
"color": { | |||
"scale": [ | |||
{ | |||
"value": 0, | |||
"color": "#FFFFFF" | |||
}, | |||
{ | |||
"value": { "aggregate": "max" }, | |||
"color": "#FFD851" | |||
} | |||
] | |||
} | |||
} | |||
} | |||
</syntaxhighlight> | |||
===Orange scale=== | |||
Use case: Durations | |||
[[File:Orangescale.png|200px]] | |||
<syntaxhighlight lang="json" line> | |||
{ | |||
"backgroundColor": { | |||
"scale": [ | |||
{ | |||
"value": {"aggregate": "max" }, | |||
"color": "#fea88a" | |||
}, | |||
{ | |||
"value": {"aggregate": "min" }, | |||
"color": "#FBE8E6" | |||
} | |||
] | |||
} | |||
} | |||
</syntaxhighlight> | |||
=== Icons in table cells === | |||
Adds an icon left of the value (icon sentiment_satisfied_alt if value is over 100 and otherwise sentiment_very_dissatisfied). Also icons have different colors. | |||
<syntaxhighlight lang="json" line> | |||
{ | { | ||
" | "icon": { | ||
"rules": [ | "rules": [ | ||
{ | { | ||
" | "icon": "sentiment_satisfied_alt", | ||
"value": | "value": 100, | ||
"comparison": " | "comparison": ">" | ||
}, | }, | ||
{ | { | ||
" | "icon": "sentiment_very_dissatisfied" | ||
} | } | ||
] | ], | ||
"color": { | |||
"rules": [ | |||
{ | |||
"color": "#68DD8F", | |||
"value": 100, | |||
"comparison": ">" | |||
}, | |||
{ | |||
"color": "#EF5254" | |||
} | |||
] | |||
} | |||
} | } | ||
} | } | ||
</ | </syntaxhighlight> | ||
=== Textual equal rules for background color === | |||
If cell content is "Dallas", background color is #00FF00. If cell content is "New York", background color is #FF0000. Other cells have background color #FFFF00. | |||
<syntaxhighlight lang="json" line> | |||
{ | |||
"backgroundColor":{ | |||
"rules":[ | |||
{ | |||
"color":"#00FF00", | |||
"value":"Dallas", | |||
"comparison":"=" | |||
}, | |||
{ | |||
"color":"#FF0000", | |||
"value":"New York", | |||
"comparison":"=" | |||
}, | |||
{ | |||
"color":"#FFFF00" | |||
} | |||
] | |||
} | |||
} | |||
</syntaxhighlight> | |||
=== Numerical not equal rules for background color === | |||
If cell content is not 5, background color is #00FF00. Other cells have background color #FFFF00. | |||
<syntaxhighlight lang="json" line> | |||
{ | |||
"backgroundColor":{ | |||
"rules":[ | |||
{ | |||
"color":"#00FF00", | |||
"value":5, | |||
"comparison":"!=" | |||
}, | |||
{ | |||
"color":"#FFFF00" | |||
} | |||
] | |||
} | |||
} | |||
</syntaxhighlight> | |||
=== Boolean equal rules for background color === | |||
If cell content is "true", background color is #00FF00. If cell content is "false", background color is #FF0000. Other cells have background color #FFFF00. | |||
<syntaxhighlight lang="json" line> | |||
{ | |||
"backgroundColor":{ | |||
"rules":[ | |||
{ | |||
"color":"#00FF00", | |||
"value":true, | |||
"comparison":"=" | |||
}, | |||
{ | |||
"color":"#FF0000", | |||
"value":false, | |||
"comparison":"=" | |||
}, | |||
{ | |||
"color":"#FFFF00" | |||
} | |||
] | |||
} | |||
} | |||
</syntaxhighlight> | |||
Background color is based on a color scale where column minimum value gets color #FFFFFF and maximum value #FFD851. Color is scaled linearly between these colors. | === Equal rules getting reference value from other column === | ||
< | If cell content in same as in dimension index 2, background color is #00FF00, if cell content in same as in dimension index 3, background color is #FF0000, and other cells have background color #FFFF00. | ||
<syntaxhighlight lang="json" line> | |||
{ | |||
"backgroundColor":{ | |||
"rules":[ | |||
{ | |||
"color":"#00FF00", | |||
"value":{ | |||
"type":"dimension", | |||
"index":2 | |||
}, | |||
"comparison":"=" | |||
}, | |||
{ | |||
"color":"#FF0000", | |||
"value":{ | |||
"type":"dimension", | |||
"index":3 | |||
}, | |||
"comparison":"=" | |||
}, | |||
{ | |||
"color":"#FFFF00" | |||
} | |||
] | |||
} | |||
} | |||
</syntaxhighlight> | |||
=== Textual equal rules based on value in other column === | |||
If cell content in dimension index 1 is "Delivery", background color is #00FF00. Other cells have background color #FFFF00. Note that this conditional formatting results in same colors in all columns because the conditional formatting is not referring to the column itself. | |||
<syntaxhighlight lang="json" line> | |||
{ | |||
"backgroundColor":{ | |||
"value":{ | |||
"type":"dimension", | |||
"index":1 | |||
}, | |||
"rules":[ | |||
{ | |||
"color":"#00FF00", | |||
"value":"Delivery", | |||
"comparison":"=" | |||
}, | |||
{ | |||
"color":"#FFFF00" | |||
} | |||
] | |||
} | |||
} | |||
</syntaxhighlight> | |||
=== Textual equal rules for icons with colors === | |||
If cell content is "Delivery", icon is "arrow_upward" with color is #FF0000. Other cells icon is "arrow_downward" with color is #00FF00. | |||
<syntaxhighlight lang="json" line> | |||
{ | |||
"icon":{ | |||
"rules":[ | |||
{ | |||
"icon":"arrow_upward", | |||
"value":"Delivery", | |||
"comparison":"=" | |||
}, | |||
{ | |||
"icon":"arrow_downward" | |||
} | |||
], | |||
"color":{ | |||
"rules":[ | |||
{ | |||
"color":"#FF0000", | |||
"value":"Delivery", | |||
"comparison":"=" | |||
}, | |||
{ | |||
"color":"#00FF00" | |||
} | |||
] | |||
} | |||
} | |||
} | |||
</syntaxhighlight> | |||
===Other examples=== | |||
Background color is based on a color scale where column minimum value zero gets color #FFFFFF and maximum value #FFD851. Color is scaled linearly between these colors. | |||
<syntaxhighlight lang="json" line> | |||
{ | { | ||
"backgroundColor": { | "backgroundColor": { | ||
"scale": [ | "scale": [ | ||
{ | { | ||
"value": | "value": 0, | ||
"color": "#FFFFFF" | "color": "#FFFFFF" | ||
}, | }, | ||
Line 72: | Line 469: | ||
} | } | ||
} | } | ||
</ | </syntaxhighlight> | ||
Similar to previous example, except minimum and maximum values come from another column (measure number 1). | Similar to previous example, except minimum and maximum values come from another column (measure number 1). | ||
< | <syntaxhighlight lang="json" line> | ||
{ | { | ||
"backgroundColor": { | "backgroundColor": { | ||
Line 90: | Line 487: | ||
} | } | ||
} | } | ||
</ | </syntaxhighlight> | ||
The following setting defines a fixed background color for a column. | The following setting defines a fixed background color for a column. | ||
< | <syntaxhighlight lang="json" line> | ||
{ | { | ||
"backgroundColor": { | "backgroundColor": { | ||
Line 99: | Line 496: | ||
} | } | ||
} | } | ||
</ | </syntaxhighlight> | ||
Background color code is taken from column measure 1 from the same row. | Background color code is taken from column measure 1 from the same row. | ||
< | <syntaxhighlight lang="json" line> | ||
{ | { | ||
"backgroundColor": { | "backgroundColor": { | ||
"color": { "type":"measure", "index": 1 } | "color": { | ||
"type":"measure", | |||
"index": 1 | |||
} | |||
} | } | ||
} | } | ||
</ | </syntaxhighlight> | ||
Text color is #FF0000 if value is lower than 20, otherwise #00FF00. | Text color is #FF0000 if value is lower than 20, otherwise #00FF00. | ||
< | <syntaxhighlight lang="json" line> | ||
{ | { | ||
"textColor": { | "textColor": { | ||
Line 126: | Line 525: | ||
} | } | ||
} | } | ||
</ | </syntaxhighlight> | ||
Creates a red-white-green color scale where the minimum value is red, the nominal value (eg. a target value) of 50 is white and maximum value is green. | |||
< | <syntaxhighlight lang="json" line> | ||
{ | { | ||
"backgroundColor": { | |||
"scale": [ | |||
{ | |||
"value": { "aggregate": "min" }, | |||
"color": "#EF5254" | |||
}, | |||
{ | |||
"value": 50, | |||
"color": "#FFFFFF" | |||
}, | |||
{ | |||
"value": { "aggregate": "max" }, | |||
"color": "#68DD8F" | |||
} | |||
] | |||
} | |||
} | } | ||
</ | </syntaxhighlight> | ||
Background color uses a color scale where the colors are based on values in dimension index 2 (requires that the dimension contains numerical data). This conditional formatting can be applied to any column (also textual columns). | |||
< | <syntaxhighlight lang="json" line> | ||
{ | { | ||
"backgroundColor": { | |||
"value": { | |||
"type": "dimension", | |||
"index": 2 | |||
}, | |||
"scale": [ | |||
{ | |||
"value": 0, | |||
"color": "#FFFFFF" | |||
}, | |||
{ | |||
"value": { | |||
"type": "dimension", | |||
"index": 2, | |||
"aggregate": "max" | |||
}, | |||
"color": "#FFD851" | |||
} | |||
] | |||
} | |||
} | } | ||
</ | </syntaxhighlight> |
Latest revision as of 17:17, 4 August 2024
Table is a basic but versatile visualization for a flat dataset which is the data structure provided by the calculation.
Table functionalities
Table has the following functionalities:
- Table shows rows in queried data as the table rows and dimensions/measures/columns as the table columns (in the order as they are defined and dimensions left of measures).
- If there are dimensions/columns where corresponding filters can be created, clicking rows/cells creates the filter.
- When there is maximum of one dimension/column defined, the table has row selection mode in use. When there are more than one dimension/column, cell selection mode is in use.
- By default, there is the multi-selection in use (i.e. clicking keeps the previous selections). Single-selection can be applied with Ctrl key pressed. Range selection within a column can be used with Shift key.
- Table rows can be sorted ascending or descending by columns. Multi-column sorting works with Ctrl key pressed.
- Table columns can be reordered by dragging them from the header.
- Table columns can be resized from the handle right side of the column header. The new widths are stored permanently to chart settings when the dashboard is saved.
- Table can be filtered using the Excel-style column filtering, which is converted into model filter when the selection is confirmed. There are different filtering options available depending on the column type, e.g. for textual columns there are equals, not equals, starts with, ends with, contains and not contains filters, and for numerical columns there are equals, not equals, less than (or equals) and greater than (or equals) filters. In addition, greater and less than filters can be combined to have between filtering.
- The table has an optional title above the table.
- Table can be exported as Excel file and CSV file. Precise values of numbers and dates will go to the Excel file.
- If there are more than 200 rows in the table, paging is enabled and footer for controlling paging is visible. The footer also has an option to change the number of rows per page.
- Table supports the Custom Layout settings (options supported by Syncfusion) overriding the default settings set by the chart.
- Columns can be hidden using the Hidden checkbox in the measure/dimension/column settings.
- Text in a table cell can be set to be a web link to an external site (that opens to a new tab). The text will be the clickable link and clicking outside the text still makes model filter like without the web link.
Table actions
Clicking text items in the table can change dashboard variable values or open web links. These are configured using the table actions as follows:
- Set dashboard variables: Defines variables that are set, when clicking the text in the table cell. Similar tags as for the web link (more below), can be used both for the variable name and variable value fields. If there is either the sys:dashboard or sys:dashboardIdentifier variable among the changed variables (i.e. clicking will go to other dashboard), the variables are set to the session level - otherwise they are set to the dashboard level. If defining the Filter or Comparison variable with an empty value, the corresponding filter or comparison rule is created automatically (works only for dimensions).
- Open web link: When defined (works in tables only), the measure/dimension/column will be a clickable web link opening the target web site. Only the text in the table cell is clickable, so filtering is possible when clicking next to the text. To refer to the data in the same row, web links can contain tags (e.g. ${dimension:1}), which are replaced with the data. You can also refer to the clicked value with ${this}. If the web link only consists of a single tag, the tag value is not encoded (because the tag is assumed to contain a full url). If the web link contains static text in addition to the tags, the tag values are encoded using encodeUriComponent (because tags are assumed to be used as url parameters). If the link does not start with http:// or https://, the link is interpreted as a relative link which is added to the end of the QPR ProcessAnalyzer address to form the complete link. Examples: https://google.com/search/q=${measure:2}, https://wiki.onqpr.com/pa/index.php?search=${this}, qprpa/ui/#/dashboard?sys:dashboard=123&myVariable=${dimension:1}
There is also a checkbox Open in new window to determine whether to use same browser window or open to a new window or tab. When checked, the link will be opened or variables will be set in a new browser window or tab. When unchecked, the link will be opened or variables will be set in the same window.
It's also possible to run scripts that are available in the table context menu.
Conditional formatting
Table conditional formatting can be used to define table cell background colors, text colors, colored icons and databar, where all depend on the data in the table. Conditional formattings are defined in the measure/dimension/column settings as JSON with following allowed properties:
- backgroundColor / textColor
- rules: Rules are checked in the defined order and the first matching rule determines the color.
- color: Color to use when the rule matches.
- value: Value to compare. Value can be numerical, textual, boolean or null.
- comparison: Type of the comparison which is one of the following: <, >, <=, >=, =, !=. Less than and greater than comparisons can only be used for numbers.
- scale
- color: Color for the scale boundary.
- value: Value for the scale boundary. Value needs to be numerical.
- color: Color that is used when there are no matching rules, or rules/scale is not defined.
- value: When defined, conditional formatting is based on this value. When not defined, the cell value is used by default.
- rules: Rules are checked in the defined order and the first matching rule determines the color.
- databar
- min: Value that represents the databar minimum position (i.e. when databar is not visible).
- max: Value that represents the databar maximum position (i.e. databar fills the whole cell).
- value: Value where the databar width is based on. When not defined, the databar width is based on the data in the same cell.
- color: Color of the databar. Can be defined in similar way as the backgroundColor. Default color is #4bc3ff.
- icon
- rules: Similar syntax can be used as in the backgroundColor rules, except instead of "color" there is "icon" attribute to define the icon. All Google Material Icons are available: https://material.io/resources/icons/.
- color: Color of the icon. Can be defined in similar way as the backgroundColor. Default color is #333333.
Defining numeric properties
All values in the conditional formatting can be defined as follows:
- fixed numerical value, e.g.,
"value": 5
- fixed textual value, e.g.,
"value": "Dallas"
- fixed boolean value, e.g.,
"value": true
- aggregate of the same column data, e.g.,
"value": { "aggregate": "max" }
- refer to a cell value in other column in the same row, e.g.,
"value": { "type": "dimension", "index": 1 }
- aggregate of another column data, e.g.,
"value": { "type": "measure", "index": 2, "aggregate": "max" }
Available aggregations are: min, max, sum, average, median, first, last.
Defining color and icon properties
All color and icon properties in conditional formatting can be defined as follows:
- fixed value, e.g.,
"value": "#AABBCC"
- refer to a cell value in other column in the same row, e.g.,
"value": { "type": "dimension", "index": 1 }
Conditional formatting examples
Below are listed the examples used in dashboards with their respective use cases and Conditional formatting expressions. The recommended colors and their color codes are as follows:
- Green: #68DD8F (Positively correlating KPIs)
- Red: #EF5254 (Negatively correlating KPIs)
- Blue: #4BC3FF (Case/Event counts)
- Dark blue: #0F55A5 (Automation)
- Orange: #FEA88A (Durations)
- Yellow: #FFD851 (Value/Cost)
Traffic lights
Use case: KPIs based on traffic light signaling
{
"backgroundColor": {
"rules": [
{
"color": "#EF5254",
"value": 10,
"comparison": ">="
},
{
"color": "#FFD851",
"value": 6.5,
"comparison": ">="
},
{
"color": "#68DD8F"
}
]
}
}
Green databar
Use case: KPIs where 100% is a desired value, eg. On-Time Delivery
{
"databar": {
"min": 0,
"max": 100,
"color": {
"scale": [
{
"value": 0,
"color": "#FFFFFF"
},
{
"value": { "aggregate": "max" },
"color": "#68DD8F"
}
]
}
}
}
Red databar
Use case: KPIs where 0% is the desired value and you want to highlight the worst performers of this metric, eg. Material shortages
{
"databar": {
"min": 0,
"max": { "aggregate": "max" },
"color": {
"scale": [
{
"value": 0,
"color": "#FFFFFF"
},
{
"value": { "aggregate": "max" },
"color": "#EF5254"
}
]
}
}
}
Blue databar
Use cases: Case/event counts
{
"databar": {
"min": 0,
"max": { "aggregate": "max" },
"color": {
"scale": [
{
"value": 0,
"color": "#FFFFFF"
},
{
"value": { "aggregate": "max" },
"color": "#4BC3FF"
}
]
}
}
}
Dark blue databar
Use case: Automation rate and similar metrics that scale 0-100%
{
"databar": {
"min": 0,
"max": 100,
"color": {
"scale": [
{
"value": 0,
"color": "#FFFFFF"
},
{
"value": { "aggregate": "max" },
"color": "#7588C4"
}
]
}
}
}
Yellow databar
Usecase: Monetary KPIs such as cost/value
{
"databar": {
"min": 0,
"max": { "aggregate": "max" },
"color": {
"scale": [
{
"value": 0,
"color": "#FFFFFF"
},
{
"value": { "aggregate": "max" },
"color": "#FFD851"
}
]
}
}
}
Orange scale
Use case: Durations
{
"backgroundColor": {
"scale": [
{
"value": {"aggregate": "max" },
"color": "#fea88a"
},
{
"value": {"aggregate": "min" },
"color": "#FBE8E6"
}
]
}
}
Icons in table cells
Adds an icon left of the value (icon sentiment_satisfied_alt if value is over 100 and otherwise sentiment_very_dissatisfied). Also icons have different colors.
{
"icon": {
"rules": [
{
"icon": "sentiment_satisfied_alt",
"value": 100,
"comparison": ">"
},
{
"icon": "sentiment_very_dissatisfied"
}
],
"color": {
"rules": [
{
"color": "#68DD8F",
"value": 100,
"comparison": ">"
},
{
"color": "#EF5254"
}
]
}
}
}
Textual equal rules for background color
If cell content is "Dallas", background color is #00FF00. If cell content is "New York", background color is #FF0000. Other cells have background color #FFFF00.
{
"backgroundColor":{
"rules":[
{
"color":"#00FF00",
"value":"Dallas",
"comparison":"="
},
{
"color":"#FF0000",
"value":"New York",
"comparison":"="
},
{
"color":"#FFFF00"
}
]
}
}
Numerical not equal rules for background color
If cell content is not 5, background color is #00FF00. Other cells have background color #FFFF00.
{
"backgroundColor":{
"rules":[
{
"color":"#00FF00",
"value":5,
"comparison":"!="
},
{
"color":"#FFFF00"
}
]
}
}
Boolean equal rules for background color
If cell content is "true", background color is #00FF00. If cell content is "false", background color is #FF0000. Other cells have background color #FFFF00.
{
"backgroundColor":{
"rules":[
{
"color":"#00FF00",
"value":true,
"comparison":"="
},
{
"color":"#FF0000",
"value":false,
"comparison":"="
},
{
"color":"#FFFF00"
}
]
}
}
Equal rules getting reference value from other column
If cell content in same as in dimension index 2, background color is #00FF00, if cell content in same as in dimension index 3, background color is #FF0000, and other cells have background color #FFFF00.
{
"backgroundColor":{
"rules":[
{
"color":"#00FF00",
"value":{
"type":"dimension",
"index":2
},
"comparison":"="
},
{
"color":"#FF0000",
"value":{
"type":"dimension",
"index":3
},
"comparison":"="
},
{
"color":"#FFFF00"
}
]
}
}
Textual equal rules based on value in other column
If cell content in dimension index 1 is "Delivery", background color is #00FF00. Other cells have background color #FFFF00. Note that this conditional formatting results in same colors in all columns because the conditional formatting is not referring to the column itself.
{
"backgroundColor":{
"value":{
"type":"dimension",
"index":1
},
"rules":[
{
"color":"#00FF00",
"value":"Delivery",
"comparison":"="
},
{
"color":"#FFFF00"
}
]
}
}
Textual equal rules for icons with colors
If cell content is "Delivery", icon is "arrow_upward" with color is #FF0000. Other cells icon is "arrow_downward" with color is #00FF00.
{
"icon":{
"rules":[
{
"icon":"arrow_upward",
"value":"Delivery",
"comparison":"="
},
{
"icon":"arrow_downward"
}
],
"color":{
"rules":[
{
"color":"#FF0000",
"value":"Delivery",
"comparison":"="
},
{
"color":"#00FF00"
}
]
}
}
}
Other examples
Background color is based on a color scale where column minimum value zero gets color #FFFFFF and maximum value #FFD851. Color is scaled linearly between these colors.
{
"backgroundColor": {
"scale": [
{
"value": 0,
"color": "#FFFFFF"
},
{
"value": { "aggregate": "max" },
"color": "#FFD851"
}
]
}
}
Similar to previous example, except minimum and maximum values come from another column (measure number 1).
{
"backgroundColor": {
"scale": [
{
"value": { "type":"measure", "index": 1, "aggregate": "min" },
"color": "#FFFFFF"
},
{
"value": { "type":"measure", "index": 1, "aggregate": "max" },
"color": "#FFD851"
}
]
}
}
The following setting defines a fixed background color for a column.
{
"backgroundColor": {
"color": "#FFD851"
}
}
Background color code is taken from column measure 1 from the same row.
{
"backgroundColor": {
"color": {
"type":"measure",
"index": 1
}
}
}
Text color is #FF0000 if value is lower than 20, otherwise #00FF00.
{
"textColor": {
"rules": [
{
"color": "#FF0000",
"value": 20,
"comparison": "<"
},
{
"color": "#00FF00"
}
]
}
}
Creates a red-white-green color scale where the minimum value is red, the nominal value (eg. a target value) of 50 is white and maximum value is green.
{
"backgroundColor": {
"scale": [
{
"value": { "aggregate": "min" },
"color": "#EF5254"
},
{
"value": 50,
"color": "#FFFFFF"
},
{
"value": { "aggregate": "max" },
"color": "#68DD8F"
}
]
}
}
Background color uses a color scale where the colors are based on values in dimension index 2 (requires that the dimension contains numerical data). This conditional formatting can be applied to any column (also textual columns).
{
"backgroundColor": {
"value": {
"type": "dimension",
"index": 2
},
"scale": [
{
"value": 0,
"color": "#FFFFFF"
},
{
"value": {
"type": "dimension",
"index": 2,
"aggregate": "max"
},
"color": "#FFD851"
}
]
}
}