Expressions

12 Jul 20188 minutes to read

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 report item like other normal numeric columns as a quantitative measure.

Expression Dialog can be launched through the Expression icon in the query designer toolbar highlighted below:

Now the expression dialog will be launched.

Note: The expression icon in the tools pane will be in disabled state, if there is no table found dropped in table design view.

Adding an Expression Column

  • Click Add button in the Expression Dialog to add a new expression column.

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

  • Define an expression in an Expression text area. 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. [Freight]+100 – To compute the total with 100 added to Freight.

    Following built-in functions are supported in Expression Designer.

    </tr> </tr>
    Category Functions Syntax & Descriptions
    Numbers ABS
      Syntax:</br> ABS(numeric_expression)</br> Description:</br> Returns the absolute value of the given expression.</br>
    Numbers ACOS
      Syntax:</br> ACOS(numeric_expression)</br> Description:</br> Returns the inverse cosine (also known as arccosine) of the given numeric expression.</br>
    Numbers ASIN
      Syntax:</br> ASIN(numeric_expression)</br> Description:</br> Returns the inverse sine (also known as arcsine) of the given numeric expression.</br>
    Numbers ATAN
      Syntax:</br> ATAN(numeric_expression)</br> Description:</br> Returns the inverse tangent (also known as arctangent) of the given numeric expression.</br>
    Numbers COS
      Syntax:</br> COS(numeric_expression)</br> Description:</br> Returns the cosine of the angle specified in radians, in the given expression.</br>
    Numbers DEGREES
      Syntax:</br> DEGREES(numeric_expression)</br> Description:</br> Returns the angle in degrees for the one specified in radians, in the given numeric expression. </br>
    Numbers EXP
      Syntax:</br> EXP(numeric_expression)</br> Description:</br> Returns the exponential value of the given expression. </br>
    Numbers LOG
      Syntax:</br> LOG(numeric_expression)</br> Description:</br> Returns the logarithm of the given expression to the specified base. </br>
    Numbers PI
      Syntax:</br> PI()</br> Description:</br> Returns the constant value of PI.</br>
    Numbers POWER
      Syntax:</br> POWER(numeric_expression, numeric_expression)</br> Description:</br> Returns the value of the given expression (expression1) to the specified power (expression2).</br>
    Numbers ROUND
      Syntax:</br> ROUND(numeric_expression)</br> Description:</br> Returns a rounded value.</br>
    Numbers RADIANS
      Syntax:</br> RADIANS(numeric_expression)</br> Description:</br> Returns the angle in radians for the one specified in degrees in the given numeric expression.</br>
    Numbers SIGN
      Syntax:</br> SIGN(numeric_expression)</br> Description:</br> Returns a value representing the positive (+1), zero (0), or negative (-1) sign of the given numeric expression.</br>
    Numbers SIN
      Syntax:</br> SIN(numeric_expression)</br> Description:</br> Returns the sine of the angle specified in radians, in the given expression.</br>
    Numbers SQRT
      Syntax:</br> SQRT(numeric_expression)</br> Description:</br> Returns the square root of the given numeric expression.</br>
    Numbers TAN
      Syntax:</br> TAN(numeric_expression)</br> Description:</br> Returns the tangent of the given numeric expression.</br>
    Conditional IF
      Syntax:</br> IF(expression, true_part, false_part)</br> Description:</br> Returns either true part or false part, depending on the evaluation of the expression.</br>
    Conditional IFNULL
      Syntax:</br> IFNULL(expression1,expression2)</br> Description:</br> If the expression is numeric/string/date, returns the first expression. If the first expression is NULL, returns the second expression.</br>
    Conditional ISNOTNULL
      Syntax:</br> ISNOTNULL(expression)</br> Description:</br> If the expression is numeric/string/date is NULL, returns a string representing false, otherwise returns true.</br>
    Conditional ISNULL
      Syntax:</br> ISNULL(expression)</br> Description:</br> Returns true if the given expression evaluates to null.</br>
    Logical AND
      Syntax:</br> (expression1) AND (expression2)</br> Description:</br> Returns true if both the expressions evaluates to true.</br>
    Logical NOT
      Syntax:</br> NOT(expression)</br> Description:</br> Returns the reversed logical value of the expression being evaluated.</br>
    Logical OR
      Syntax:</br> (expression1) OR (expression2)</br> Description:</br> Returns true if any of the expressions evaluates to true.</br>
    Date DATEADD
      Syntax:</br> DATEADD(numeric_expression, date_expression)</br> Description:</br> Adds the number of days to the specified date.</br>
    Date DATENAME
      Syntax:</br> DATENAME(date_part, date_expression)</br> Description:</br> Returns a string representing the specified date_part of the given date expression.</br>
    Date DATEPART
      Syntax:</br> DATEPART(date_part, date_expression)</br> Description:</br> Returns an integer value representing the specified date_part of the given date expression.</br>
    Date DATESUB
      Syntax:</br> DATESUB(numeric_expression, date_expression)</br> Description:</br> Returns the date subtracted from the specified date.</br>
    Date DAY
      Syntax:</br> DAY(date_expression)</br> Description:</br> Returns a numeric value representing the day part of the specified date.</br>
    Date DAYDIFF
      Syntax:</br> DAYDIFF(start_date_expression, end_date_expression)</br> Description:</br> Returns a numeric value representing the difference between two specified dates.</br>
    Date HOUR
      Syntax:</br> HOUR(date_expression)</br> Description:</br> Returns the hour of the given date as an integer.</br>
    Date MINUTE
      Syntax:</br> MINUTE(date_expression)</br> Description:</br> Returns a numeric value representing the minute part of the date resulted from specified date expression.</br>
    Date MONTH
      Syntax:</br> MONTH(date_expression)</br> Description:</br> Returns a numeric value representing the month part of the date resulted from specified date expression.</br>
    Date NOW
      Syntax:</br> NOW()</br> Description:</br> Returns the current date and time.</br>
    Date TODAY
      Syntax:</br> TODAY()</br> Description:</br> Returns the current date.</br>
    Date YEAR
      Syntax:</br> YEAR(date_expression)</br> Description:</br> Returns a numeric value representing the year part of the date resulting from the specified date expression.</br>
    Date MAX
      Syntax:</br> MAX(expression)</br> Description:</br> Returns the maximum value in the given expression.</br>
    Date MIN
      Syntax:</br> MIN(expression)</br> Description:</br> Returns the minimum value in the given expression.</br>
    String CHAR
      Syntax:</br> CHAR(integer_expression)</br> Description:</br> Converts the given integer ASCII code into a character.</br>
    String CONCAT
      Syntax:</br> CONCAT(string_expression1, string_expression2,…, string_expressionN)</br> Description:</br> Returns a string value resulting from the concatenation of two or more string values.</br>
    String CONTAINS
      Syntax:</br> CONTAINS(string_expression, substring_expression)</br> Description:</br> Returns true if the given string expression contains the specified substring expression.</br>
    String ENDSWITH
      Syntax:</br> ENDSWITH(string_expression substring_expression)</br> Description:</br> Returns true if the given string expression ends with the specified substring expression.</br>
    String LEFT
      Syntax:</br> LEFT(string_expression, numeric_expression)</br> Description:</br> Returns the specified number of characters from start of the given string expression.</br>
    String LEN
      Syntax:</br> LEN(string_expression)</br> Description:</br> Returns the number of characters in the given string expression.</br>
    String LOWER
      Syntax:</br> LOWER(string_expression)</br> Description:</br> Returns a lower case converted string value from a given string expression.</br>
    String LTRIM
      Syntax:</br> LTRIM(string_expression)</br> Description:</br> Returns the string value with any leading blanks removed from string expression.</br>
    String MAX
      Syntax:</br> MAX(expression)</br> Description:</br> Returns the maximum value in the given expression. </br>
    String MIN
      Syntax:</br> MIN(expression)</br> Description:</br> Returns the minimum value in the given expression. </br>
    String RIGHT
      Syntax:</br> RIGHT(string_expression, numeric_expression)</br> Description:</br> Returns the specified number of characters from end of the given string expression.</br>
    String RTRIM
      Syntax:</br> RTRIM(string_expression)</br> Description:</br> Returns the string value with any trailing blanks removed from string expression.</br>
    String STARTSWITH
      Syntax:</br> STARTSWITH(string_expression, substring_expression)</br> Description:</br> Returns true if the given string expression starts with the specified substring expression.</br>
    String SUBSTR
      Syntax:</br> SUBSTR(string_expression, starting_index, length_of_the_string)</br> Description:</br> Returns a specific length of string starting from specific index from the given string expression. </br>
    String UPPER
      Syntax:</br> UPPER(string_expression)</br> Description:</br> Returns an upper case converted string value from a given string expression.</br>

  • You can use the functions list and column fields list to include the function names and the column names in the Expression text area, by double-clicking the specific name in respective lists.

  • Finally, click Save in Expression Dialog to add the expression in the list. The saved expression list will be listed in the left pane like below.

Deleting an Expression Column

  • Select an expression column in left pane.

  • Click Close icon to remove the selected expression column.

Edit an Expression Column

  • Select an expression column in left pane that need to be updated.

  • Edit the Name and Expression text areas, if required.

  • Click Save button in Expression Dialog to save the modifications handled.