Datamining Tiers - Dynamic Columns Tier

Modified on Thu, 22 Jan at 10:44 AM

TABLE OF CONTENTS

Datamining Tiers – Dynamic Columns
Types of Dynamic Columns
Expression Columns
Expression Syntax
Web Button Columns
- Usage
- Button Configuration Options
Colour Rule Columns
Dynamic Columns – Canonical Rules
Dynamic Columns – Frequently Asked Questions
Common Support Scenarios

Datamining Tiers – Dynamic Columns

The Dynamic Columns tier allows you to enhance a Datamining Report with calculated values, interactive actions, and conditional styling.

Dynamic Columns add logic-based content to a report without modifying the underlying data. They are evaluated at report runtime and affect presentation and interaction only.

Types of Dynamic Columns

Dynamic Columns are available in three types:

Expression Columns
Web Button Columns
Colour Rule Columns

Expression Columns

Expression Columns allow you to create calculated fields using formulas that reference existing report columns.

This is similar to spreadsheet formulas and supports:

Calculated values
Conditional logic
Arithmetic operations
String manipulation
Date manipulation

Expressions are evaluated per row and produce a derived value that appears as a new column in the report.

How to Add Dynamic Columns

Click Add Dynamic Columns in the ribbon menu.
The Dynamic Columns tier is added to the report.

Only one Dynamic Columns tier can be added to a report. This single tier supports all required expression, button, and colour rule functionality.

The Dynamic Columns panel contains three buttons:

Add Expression Column
Add Web Button Column
Add Colour Rule Column

Any number of each Dynamic Column type can be added by clicking the relevant button and configuring the panel that appears.

Expression Processing Order

Expression Columns are processed top-to-bottom in the order they appear in the list, and are evaluated per row.

Expression Columns are evaluated before any column removal or reordering that occurs during final report rendering.

An Expression Column may reference the result of another Expression Column, provided the referenced expression appears earlier in the list.

Expression Configuration Options

Name

The display name of the Expression Column as shown in the report.

The name must be unique among all Expression Columns.

Expression Order Controls

Up Arrow – Move the expression up in the list
Down Arrow – Move the expression down in the list
X – Remove the expression

Data Type

Defines the expected data type of the expression result.

ManagementStudio attempts to convert the derived value to the selected data type. The selected data type affects sorting and filtering behaviour.

If a data type conversion fails, the resulting cell value is blank.

Add Column to Expression

Provides a dropdown of all available report columns and their internal names for easy insertion into expressions.

The report must be run at least once for this dropdown to be populated.

Expression

Defines the formula used to calculate the column value.

Example:

Contacts_FirstName + ' ' + Contacts_LastName

This produces a value such as John Smith.

Expressions must use internal column names, which typically follow the format:

ModuleName_ColumnName

Square brackets must not be used around column names.

Square brackets are reserved only for special tokens:

[Today]
[TimeStamp]

Expand Expression Editor

The Expand icon opens the expression editor in a larger window for easier editing.

Expression Syntax

Conditional and Arithmetic Operators

Operator	Description	Return Type	Nesting	Example
== , <>	Equals / Not Equals	Boolean	Allowed	Applications_AppVendor == 'Adobe'
&& , \|\|	Logical AND / OR	Boolean	Allowed	Applications_AppVendor == 'Adobe' \|\| Applications_AppVendor == 'Adobe Inc'
>= , > , <= , <	Greater / Less than	Boolean	Allowed	Applications_CustomInt1 >= 10
+ , - , * , /	Arithmetic operators	Numeric	Allowed	Applications_CustomInt1 * 2
LIKE	Case-insensitive match, supports * or % wildcards	Boolean	Allowed	Applications_AppVendor LIKE 'Adobe*'
IN	Checks value membership	Boolean	Allowed	UserMigrations_LastName IN ('Hynes', 'Cook')

IIF (Conditional Function)

IIF(condition, trueValue, falseValue)

Returns one of two values based on the evaluation of a logical condition.

IIF can be nested to create complex conditional logic.

Example:

IIF(UserMigrations_LastName LIKE 'Gordon*', 'Yes', 'No')

Nested Example – RAG Status

Create a custom RAG status based on Application Readiness Percent.

IIF(Applications_ReadyPercent >= 100, 'Green', IIF(Applications_ReadyPercent >= 90, 'Amber', 'Red'))

Text Search Functions

Function	Description	Return Type	Nesting	Example
StartsWith(x,y) EndsWith(x,y) Contains(x,y)	Text matching functions	Boolean	Not allowed	StartsWith(Contacts_FirstName, 'Richard')
IndexOf(x,y) LastIndexOf(x,y)	Find position of text	Integer	Not allowed	IndexOf('Quick Brown Fox', 'o') = 8
Len(x)	Text length	Integer	Allowed	Len(Contacts_FirstName)

Text Manipulation Functions

Function	Description	Return Type	Nesting	Example
ToUpper(x) ToLower(x)	Change text case	Text	Not allowed	ToUpper(UserMigrations_Domain)
Replace(x,y,z)	Replace text	Text	Not allowed	Replace('Global.local', '.local', '.com')
SubString(x,y,z)	Extract text	Text	Allowed	SubString(UserMigrations_Domain, 2, 3)
Format(x,y)	Format date or number as text	Text	Not allowed	Format(UserMigrations_DeployUnitSlotStart, 'MMM dd yyyy')

Date Functions

Function	Description	Return Type	Example
DateAdd(date, days)	Add days to a date	Date	DateAdd(UserMigrations_MigrationDate1, 10)
DateSub(date, days)	Subtract days from a date	Date	DateSub(UserMigrations_MigrationDate1, 10)
DateSub(date, date)	Days between two dates	Numeric	DateSub(UserMigrations_MigrationDate1, [Today])

Web Button Columns

Web Button Columns add clickable buttons to a Datamining Report when the report is viewed in a web browser.

Web Buttons are used to surface per-row actions at scale, such as linking users to Portal Forms, Self-Schedule pages, or executing bespoke automation via scripts.

In the WPF client, Web Buttons are displayed as non-interactive links.

When the report is published to the web UI, Web Buttons render as buttons and can be clicked by users.

Usage

To add a Web Button Column:

Add the Dynamic Columns tier to the report.
Click Add Web Button Column.
Configure the button using the panel that appears.

Multiple Web Button Columns can be added to the same report.

Button Configuration Options

Button Label

The text displayed on the button in the report.

Button Type

Defines the behavior of the button.

Portal Form
Links the button to a specific Portal Form, such as a migration survey or satisfaction survey.
Self-Schedule
Links the user to their personal Self-Schedule page where they can select a migration slot.
PS Script Button
Executes a registered script and reloads the report after completion. Common use cases include bespoke actions such as flagging an Application as retired.

Style

Web Buttons use Bootstrap CSS classes for styling.

Custom CSS classes can be provided, for example:

btn btn-secondary

Open In

New Tab
Opens the link in a new browser tab. The report remains open.
This Tab Result
Opens the link in the current tab. The report page is replaced.
This Tab Reload
Executes the action and reloads the report page on completion. Intended for Script Buttons.
Split Tab
Opens the link in an iframe below the report, creating a split view.

Form / Script Target

Defines the Portal Form or Script that the button targets.

Targets can be selected using a picker, or specified by:

Internal Name (resolved first)
Id (used if Internal Name is not found)

Self-Schedule buttons do not require a target, as only one schedule exists per item.

Script-Specific Options

The following options apply only to PS Script Buttons:

Are You Sure
Displays a confirmation dialog before executing the script.
Args 1–3
Values passed into the script. These may be static values or values taken from report columns.
Id Column
Defines which column value is passed as the Id to the script. By default, this is the Primary Tier’s Module Id.

PS Script Buttons execute as the system and receive parameters identifying the user who clicked the button.

Scripts must be registered in ManagementStudio and have the Portal Button flag enabled to be selectable and executable.

Colour Rule Columns

Colour Rule Columns apply conditional background colouring to one or more columns based on logical rules.

This works similarly to conditional formatting in Excel and is commonly used for visual indicators such as RAG (Red, Amber, Green) status.

Colour Rule Columns are supported in both the WPF UI and the Web UI for published reports.

Purpose

Colour Rule Columns add an additional visual dimension to report data.

Common use cases include:

Highlighting readiness or SLA breaches
Identifying items in specific Blueprints
Distinguishing Deployment Units or phases

Usage

To add Colour Rule Columns:

Add the Dynamic Columns tier to the report.
Click Add Colour Rule Column.
Configure the colour rules.

Multiple Colour Rule Columns can be added to the same report.

Configuration Options

Columns

Select one or more columns to apply background colouring to.

The report must be run at least once for this dropdown to be populated.

Colour rules do not need to reference the same columns that are being coloured.

Add Colour Condition

Each colour condition defines a rule that applies a specific colour when its expression evaluates to true.

You must add one rule per colour.

Select a colour using the picker.
Enter a logical expression that returns true or false.

Example:

Expression_RAGStatus == 'Green'

Colour Rule Evaluation

Colour rules are evaluated in the order they are defined.

If multiple rules match, the first matching rule is applied.

Only logical operators are supported in Colour Rule expressions.

Colour rules may reference:

Expression Columns
Columns not visible in the report output

Colour Rule Columns apply background colour only. The system automatically adjusts foreground text colour for contrast and readability.

Dynamic Columns – Canonical Rules

Dynamic Columns do not modify underlying data.
Dynamic Columns are evaluated at report runtime only.
Only one Dynamic Columns tier can be added to a report.
Expression Columns are evaluated top-to-bottom, per row.
Expression Columns can reference earlier Expression Columns, but not later ones.
Expression Columns are evaluated before column removal or reordering during final rendering.
Expression Columns must use internal column names without square brackets.
Square brackets are reserved only for special tokens such as [Today] and [TimeStamp].
Failed data type conversion in an Expression Column results in a blank cell.
Web Button Columns are interactive only in published web reports.
PS Script Buttons execute as the system and receive parameters identifying the clicking user.
Colour Rule Columns apply background colour only and do not modify data values.
If multiple Colour Rules match, the first matching rule is applied.

Dynamic Columns – Frequently Asked Questions

Why does my Expression Column show blank values?

This occurs when the expression cannot be converted to the selected data type, or when the expression references an invalid column or value.

Why can’t my Expression Column reference another Expression Column?

Expression Columns can only reference expressions that appear earlier in the list. Expressions are evaluated top-to-bottom.

Why does my expression fail when I add square brackets around a column?

Square brackets are not valid for column references. They are reserved only for special tokens such as [Today] and [TimeStamp].

Why can’t I nest certain functions inside IIF?

Some functions cannot be nested and must be the only function in the expression. These functions cannot appear inside an IIF statement.

If needed, resolve the value in one Expression Column and reuse it in a later Expression Column.

Why can’t I click a Web Button in the WPF client?

Web Buttons are only interactive when the report is published to the web UI. In the WPF client, they are displayed as non-interactive links.

Why does my Script Button work for some users but not others?

Script Buttons are permissioned by the script configuration in ManagementStudio. The script must be registered and have the Portal Button flag enabled.

Why did my report reload after clicking a button?

This occurs when the button is configured with the This Tab Reload option. This behavior is intended for Script Buttons.

Why is my Colour Rule not applying?

Colour Rules only support logical operators and must evaluate to true or false. Ensure the expression returns a Boolean value.

Why did the wrong colour apply when multiple rules match?

Colour Rules are evaluated in order. The first matching rule is applied and subsequent rules are ignored.

Can Colour Rules reference Expression Columns?

Yes. Colour Rules can reference Expression Columns as well as columns that are not visible in the report output.

Do Dynamic Columns affect Readiness or Time In Process?

No. Dynamic Columns are presentation-only and do not affect Readiness calculations, Time In Process, or any underlying data.

Common Support Scenarios

Scenario: Creating a RAG status column

Use an Expression Column with nested IIF logic to derive Green, Amber, and Red values, then apply a Colour Rule Column to visually highlight the result.

Scenario: Adding per-user action buttons

Use Web Button Columns in a published web report to surface Portal Forms or Self-Schedule links for each row.

Scenario: Highlighting SLA breaches

Use Expression Columns to calculate SLA thresholds and Colour Rule Columns to highlight rows that exceed them.