Report Studio

Operators specify what happens to the values on either side of the operator. Operators are similar to functions, in that they manipulate data items and return a result.

• (

  Inserts an open parenthesis in your expression.

  Syntax

  (expression)

• )

  Inserts a closed parenthesis in your expression.

  Syntax

  (expression)

• *

  Multiplies two numeric values.

  Syntax

  value1 * value2

• ,

  Separates expression components.

  Syntax

  expression(param1, param2)

• /

  Divides two numeric values.

  Syntax

  value1 / value2

   

• ||

  Concatenates strings.

  Syntax

  string1 || string2

• +

  Adds two values.

  Syntax

  value1 + value2

• -

  Subtracts two numeric values or negates a numeric value.

  Syntax

  value1 - value2 or - value

• <

  Compares values against a defined value and retrieves the values that are less than the defined value.

  Syntax

  value1 < value2

• <=

  Compares values against a defined value and retrieves the values that are less than or equal to the defined value.

  Syntax

  value1 <= value2

   

• <>

  Compares values against a defined value and retrieves the values that are not equal to the defined value.

  Syntax

  value1 <> value2

   

• =

  Compares values against a defined value and retrieves the values that are equal to the defined value.

  Syntax

  value1 = value2

• >

  Compares values against a defined value and retrieves the values that are greater than the defined value.

  Syntax

  value1 > value2

   

• ->

  Used as a separator in a literal member expression.

  Syntax

  [namespace].[dimension].[hierarchy].[level]->[L1]

   

• >=

  Compares values against a defined value and retrieves the values that are greater than or equal to the defined value.

  Syntax

  value1 >= value2

• and

  Returns true if the conditions on both sides of the expression are true.

  Syntax

  arg1 AND arg2

• auto

  Use with summary expressions to define the scope to be adjusted based on the grouping columns in the query. The scope is context dependent.

  Syntax

  aggregate_function ( expression AUTO )

• between

  Determines if a value falls in a given range.

  Syntax

  name BETWEEN value1 and value2

   

  Example

  [Revenue] between 200 and 300

  Result

  Returns the number of results with revenues between 200 and 300.

  Result data

  Revenue	Between
  $332.06	false
  $230.55	true
  $107.94	false

   

• case

  Use with When, Then, Else, and End.

  Syntax

  CASE expression { WHEN expression THEN expression } [ ELSE expression ] END

• contains

  Determines if a string contains another string.

  Syntax

  string1 CONTAINS string2

• currentMeasure

  Keyword that can be used as the first argument of member summary functions.

  Syntax

  aggregate_function( currentMeasure within set expression )

   

• default

  Use with LOOKUP construct.

  Syntax

  lookup(....) in (....) DEFAULT(....)

   

• distinct

  A keyword used in an aggregate expression, to include only distinct occurrences of values. See also the function unique.

  Syntax

  DISTINCT dataItem

  Example

  count ( DISTINCT [OrderDetailQuantity] )

  Result: 1704

• else

  Use with If or Case constructs.

  Syntax

  IF (condition) THEN .... ELSE (expression) , or CASE .... ELSE expression END

• end

  Use with Case When construct.

  Syntax

  CASE .... END

• ends with

  Determines if a string ends with a given string.

  Syntax

  string1 ENDS WITH string2

• for

  Use with summary expressions to define the scope of the aggregation in the query.

  Syntax

  aggregate_function ( expression FOR expression { , expression } )

• for all

  Use with summary expressions to define the scope to be all the specified grouping columns in the query. See also FOR clause.

  Syntax

  aggregate_function ( expression FOR ALL expression { , expression } )

• for any

  Use with summary expressions to define the scope to be adjusted based on a subset of the grouping columns in the query. Equivalent to FOR clause.

  Syntax

  aggregate_function ( expression FOR ANY expression { , expression } )

• for report

  Use with summary expressions to define the scope to be the whole query. See also FOR clause.

  Syntax

  aggregate_function ( expression FOR REPORT )

• if

  Use with Then and Else.

  Syntax

  IF (condition is true) THEN (action) ELSE (alternate action)

• in

  Determines if a value exists in a given list of values.

  Syntax

  exp1 IN (exp_list)

• in_range

  Determines if an item exists in a given list of constant values or ranges.

  Syntax

  expression IN_RANGE { constant : constant [ , constant : constant ] }

• is missing

  Determines if a value is undefined in the data.

  Syntax

  value IS MISSING

• is null

  Determines if a value is undefined in the data.

  Syntax

  value IS NULL

• is not missing

  Determines if a value is defined in the data.

  Syntax

  value IS NOT MISSING

• is not null

  Determines if a value is defined in the data.

  Syntax

  value IS NOT NULL

• like

  Determines if a string matches the pattern of another string.

  Syntax

  string1 LIKE string2

• lookup

  Finds and replaces data with a value you specify. It is preferable to use the CASE construct.

  Syntax

  LOOKUP (name) in (value1 --> value2) default (expression)

  Example

  lookup([Country]) in ('Canada'-->([List Price] * 0.60), 'Australia'-->([List Price] * 0.80)) default([List Price])

• not

  Returns true if the condition is false, otherwise returns false.

  Syntax

  NOT arg

• or

  Returns true if either of the two conditions on both sides of the expression is true.

  Syntax

  arg1 OR arg2

• prefilter

  Performs a summary calculation before applying the summary filter.

  Syntax

  summary ([expression] PREFILTER)

• rows

  Counts the number of rows output by the query. Use with Count().

  Syntax

  count(ROWS)

• starts with

  Determines if a string starts with a given string.

  Syntax

  string1 STARTS WITH string2

• then

  Use with If or Case constructs.

  Syntax

  IF (condition) THEN ...., or CASE expression WHEN expression THEN .... END

• when

  Use with Case construct.

  Syntax

  CASE [expression] WHEN .... END

Related Topics:

• Concatenating Strings—How to concatenate strings in expression editor.
• Common Functions—Some common functions for use in Expression Editor.
• Constructs—About the constructs available in Expression Editor.
• Using the Expression Editor—How to use the Expression Editor in Report Studio reports.

Copyright © 2018 Daptiv Solutions, LLC. All Rights Reserved