Documentation
Demos
Support
Blog
Schedule Free Demo
Search results

Configuring Expression Columns in Bold BI Dashboard Designer

An expression column is used to create an expression which is a combination of data columns, operators and built-in functions. This expression column will act as a calculated measure that can be configured to widget like other normal numeric columns as a quantitative measure.

Adding an expression column

An expression field can be added by clicking Expression menu in the tool bar of the data design view.

Expression icon

Click Add in the Query Expressions window to add a new expression column.

Expression wizard

Enter a suitable name for the expression in the Name text area. By default, it will be Expression1.

Enter the expression that you like to define in the Expression text area.

Add expression

The syntax for defining a simple expression is,

{function name(}[columnname]{operator[columnname]…}

Where, content within curly braces is optional.

Some expressions for reference:

  1. YEAR([Order Date]) – To compute year of order date.

  2. COUNTD([EmployeeID]) – To compute distinct count of employees.

  3. [Freight]+100 – To compute the total with 100 added to Freight.

Following built-in functions are supported in Expression Designer.

Category Functions Syntax & Descriptions
Numbers ABS
    Syntax:
    ABS(numeric_expression)
    Description:
    Returns the absolute value of the given expression.
Numbers ACOS
    Syntax:
    ACOS(numeric_expression)
    Description:
    Returns the inverse cosine (also known as arccosine) of the given numeric expression.
Numbers ASIN
    Syntax:
    ASIN(numeric_expression)
    Description:
    Returns the inverse sine (also known as arcsine) of the given numeric expression.
Numbers ATAN
    Syntax:
    ATAN(numeric_expression)
    Description:
    Returns the inverse tangent (also known as arctangent) of the given numeric expression.
Numbers CEIL
    Syntax:
    CEIL(numeric_expression)
    Description:
    Returns the smallest integer value that is greater than or equal to the given numeric expression.
Numbers COS
    Syntax:
    COS(numeric_expression)
    Description:
    Returns the cosine of the angle specified in radians, in the given expression.
Numbers COT
    Syntax:
    COT(numeric_expression)
    Description:
    Returns the cotangent of the angle specified in radians, in the given expression.
Numbers DEGREES
    Syntax:
    DEGREES(numeric_expression)
    Description:
    Returns the angle in degrees for the one specified in radians, in the given numeric expression.
Numbers EXP
    Syntax:
    EXP(numeric_expression)
    Description:
    Returns the exponential value of the given expression.
Numbers FLOOR
    Syntax:
    FLOOR(numeric_expression)
    Description:
    Returns the largest integer value that is less than or equal to the given numeric expression.
Numbers MOD
    Syntax:
    MOD(numeric_expression, numeric_expression)
    Description:
    Returns the reminder of the first numeric expression divided by second numeric expression.
Numbers LOG
    Syntax:
    LOG(numeric_expression)
    Description:
    Returns the logarithm of the given expression to the specified base.
Numbers PI
    Syntax:
    PI()
    Description:
    Returns the constant value of PI.
Numbers POWER
    Syntax:
    POWER(expression1, expression2)
    Description:
    Returns the value of the given expression (expression1) to the specified power (expression2).
Numbers RADIANS
    Syntax:
    ROUND(numeric_expression)
    Description:
    Returns a rounded value.
Numbers SIGN
    Syntax:
    SIGN(numeric_expression)
    Description:
    Returns a value representing the positive (+1), zero (0), or negative (-1) sign of the given numeric expression.
Numbers SIN
    Syntax:
    SIN(numeric_expression)
    Description:
    Returns the sine of the angle specified in radians, in the given expression.
Numbers SQRT
    Syntax:
    SQRT(numeric_expression)
    Description:
    Returns the square root of the given numeric expression.
Numbers TAN
    Syntax:
    TAN(numeric_expression)
    Description:
    Returns the tangent of the given numeric expression.
Aggregation AVG
    Syntax:
    AVG(numeric_expression)
    Description:
    Returns the average of the values in the given expression.
Aggregation COUNT
    Syntax:
    COUNT(numeric_expression)
    Description:
    Returns the number of items in the given expression.
Aggregation COUNTD
    Syntax:
    COUNTD(numeric_expression)
    Description:
    Returns the distinct number of items in the given expression.
Aggregation MAX
    Syntax:
    MAX(numeric_expression)
    Description:
    Returns the maximum value in the given expression.
Aggregation MIN
    Syntax:
    MIN(numeric_expression)
    Description:
    Returns the minimum value in the given expression.
Aggregation STDEV
    Syntax:
    STDEV(numeric_expression)
    Description:
    Returns the standard deviation of values in the given expression.
Aggregation SUM
    Syntax:
    SUM(numeric_expression)
    Description:
    Returns the sum of values in the given expression.
Aggregation VAR
    Syntax:
    VAR(numeric_expression)
    Description:
    Returns the variance of values in the given expression.
Conditional COALESCE
    Syntax:
    COALESCE(exp1,exp2,...exp(n))
    Description:
    Returns the first non-NULL expression among its arguments, or NULL if all its arguments are NULL.
Conditional IF
    Syntax:
    IF(expression, true_part, false_part)
    Description:
    Returns either true part or false part, depending on the evaluation of the expression.
Conditional ISNULL
    Syntax:
    ISNULL(expression)
    Description:
    Returns true if the given expression evaluates to null.
Logical AND
    Syntax:
    (expression1) AND (expression2)
    Description:
    Returns true if both the expressions evaluates to true.
Logical NOT
    Syntax:
    NOT(expression)
    Description:
    Returns the reversed logical value of the expression being evaluated.
Logical OR
    Syntax:
    (expression1) OR (expression2)
    Description:
    Returns true if any of the expressions evaluates to true.
Date CURRENTMONTH
    Syntax:
    CURRENTMONTH()
    Description:
    Returns the current month.
Date CURRENTYEAR
    Syntax:
    CURRENTYEAR()
    Description:
    Returns the current year.
Date DATEADD
    Syntax:
    DATEADD(numeric_expression, date_expression)
    Description:
    Adds the number of days to the specified date.
Date DATESUB
    Syntax:
    DATESUB(numeric_expression, date_expression)
    Description:
    Returns the date subtracted from the specified date.
Date DAY
    Syntax:
    DAY(date_expression)
    Description:
    Returns a numeric value representing the day part of the specified date.
Date DAYDIFF
    Syntax:
    DAYDIFF(start_date_expression, end_date_expression)
    Description:
    Returns a numeric value representing the difference between two specified dates.
Date HOUR
    Syntax:
    HOUR(date_expression)
    Description:
    Returns the hour of the given date as an integer.
Date MINUTE
    Syntax:
    MINUTE(date_expression)
    Description:
    Returns a numeric value representing the minute part of the date resulted from specified date expression.
Date MONTH
    Syntax:
    MONTH(date_expression)
    Description:
    Returns a numeric value representing the month part of the date resulted from specified date expression.
Date NOW
    Syntax:
    NOW()
    Description:
    Returns the current date and time.
Date PREVIOUSMONTH
    Syntax:
    PREVIOUSMONTH()
    Description:
    Returns the previous month.
Date PREVIOUSYEAR
    Syntax:
    PREVIOUSYEAR()
    Description:
    Returns the previous year.
Date TODAY
    Syntax:
    TODAY()
    Description:
    Returns the current date.
Date YESTERDAY
    Syntax:
    YESTERDAY()
    Description:
    Returns the yesterday date.
Date YEAR
    Syntax:
    YEAR(date_expression)
    Description:
    Returns a numeric value representing the year part of the date resulting from the specified date expression.
Date DATENAME
    Syntax:
    DATENAME(date_part, date_expression)
    Description:
    Returns a string representing the specified date_part of the given date expression.
Date DATEPART
    Syntax:
    DATEPART(date_part, date_expression)
    Description:
    Returns an integer value representing the specified date_part of the given date expression.
Date MAX
    Syntax:
    MAX(expression)
    Description:
    Returns the maximum value in the given expression.
Date MIN
    Syntax:
    MIN(expression)
    Description:
    Returns the minimum value in the given expression.
String LEN
    Syntax:
    LEN(string_expression)
    Description:
    Returns the number of characters in the given string expression.
String CHAR
    Syntax:
    CHAR(integer_expression)
    Description:
    Converts the given integer ASCII code into a character.
String CONCAT
    Syntax:
    CONCAT(string_expression1, string_expression2,…, string_expressionN)
    Description:
    Returns a string value resulting from the concatenation of two or more string values.
String CONTAINS
    Syntax:
    CONTAINS(string_expression, substring_expression)
    Description:
    Returns true if the given string expression contains the specified substring expression.
String ENDSWITH
    Syntax:
    ENDSWITH(string_expression substring_expression)
    Description:
    Returns true if the given string expression ends with the specified substring expression.
String LEFT
    Syntax:
    LEFT(string_expression, numeric_expression)
    Description:
    Returns the specified number of characters from start of the given string expression.
String LOWER
    Syntax:
    LOWER(string_expression)
    Description:
    Returns a lower case converted string value from a given string expression.
String LTRIM
    Syntax:
    LTRIM(string_expression)
    Description:
    Returns the string value with any leading blanks removed from string expression.
String MAX
    Syntax:
    MAX(expression)
    Description:
    Returns the maximum value in the given expression.
String MIN
    Syntax:
    MIN(expression)
    Description:
    Returns the minimum value in the given expression.
String REPLACE
    Syntax:
    REPLACE(string_expression, substring, new_substring)
    Description:
    Replaces all occurrences of the substring within the given string expression, with the new_substring.
String RIGHT
    Syntax:
    RIGHT(string_expression, numeric_expression)
    Description:
    Returns the specified number of characters from end of the given string expression.
String RTRIM
    Syntax:
    RTRIM(string_expression)
    Description:
    Returns the string value with any trailing blanks removed from string expression.
String STARTSWITH
    Syntax:
    STARTSWITH(string_expression, substring_expression)
    Description:
    Returns true if the given string expression starts with the specified substring expression.
String SUBSTR
    Syntax:
    SUBSTR(string_expression, starting_index, length_of_the_string)
    Description:
    Returns a specific length of string starting from specific index from the given string expression.
String TRIM
    Syntax:
    TRIM(string_expression)
    Description:
    Removes the leading and trailing spaces from the string expression.
String UPPER
    Syntax:
    UPPER(string_expression)
    Description:
    Returns an upper case converted string value from a given string expression.

Once framing an expression, click Save in Query Expression window.

Deleting an expression column

Select an expression column in left pane.

Click Delete icon to remove the selected expression column.

Delete icon

Updating an expression column

Select an expression column in left pane that you need to update.

Edit the Name and Expression text areas, if required.

Click Save in Query Expression window to save the modifications handled.

Configuring expression column in widgets

Saved measure expression will be shown in Measure Columns section of ASSIGN DATA tab like below.

Expression columns

Saved dimension expression will be shown in Dimension Columns section of ASSIGN DATA tab like below.

Expression columns

You can also drag and drop expression column into widgets from measure or dimension fields or both.

Aggregation function expression

You can also apply filters for expression column which is used in widget. For numeric expressions, you can apply filter just like a measure filter. For string and date expressions, you can apply filter just like a dimension filter.

Blog Post - https://www.boldbi.com/blog/using-calculated-fields-in-your-dashboard

Previous
Next