Report Studio

Here is a list of some of the more common functions for use in Expression Editor

• nullif

  Returns NULL if exp1 equals exp2, otherwise returns exp1.

  Syntax

  nullif( exp1, exp2 )

• _format

  Associates a format with the expression. The keyword can be PERCENTAGE_0, PERCENTAGE_1 or PERCENTAGE_2.

  Syntax

  _format ( expr , keyword  )

  Example

  _format( [Unit Sale Price] / [Unit Price] , PERCENTAGE_2 )

  Result: 0.75123 displayed as 75.12%

• _round

  Returns the numeric expression rounded to the integer_exp places right of the decimal point. Note: integer_exp MUST be a non-negative integer.

  Syntax

  _round ( numeric_exp, integer_exp )

  Example

  _round ( 1220.42369, 2 )

  Result: 1220.42

• abs

  Returns the absolute value of numeric_exp. The sign of negative values is changed to positive.

  Syntax

  abs ( numeric_exp )

  Example 1

  abs ( 15 )

  Result: 15

  Example 2

  abs ( -15 )

  Result: 15

• ancestor

  Returns the ancestor of the specified member at either the specified (named) level or the specified number of levels above the member. Note: The result is not guaranteed to be consistent when there is more than one such ancestor.

  Syntax

  ancestor ( member, level | integer )

• ancestors

  Returns all the ancestors of a member at a specified level, or distance above the member. (Most data sources support only one ancestor at a specified level, but some support more than one. Hence the result is a member set.)

  Syntax

  ancestors ( member , level | index )

• bottomCount

  This function sorts a set according to the value of "numeric_exp" evaluated at each of the members of "set_exp", and returns the bottom "index_exp" members.

  Syntax

  bottomCount ( set_exp , index_exp , numeric_exp )

• bottomPercent

  This function is similar to bottomSum, but the threshold is "numeric_exp1" percent of the total.

  Syntax

  bottomPercent ( set_exp , numeric_exp1 , numeric_exp2 )

• bottomSum

  This function sorts on "numeric_exp2", evaluated at the corresponding member of "set_exp", and picks up the bottommost elements whose cumulative total is at least numeric_exp1.

  Syntax

  bottomSum ( set_exp , numeric_exp1 , numeric_exp2 )

• caption

  Returns the caption values of the specified argument.

  Syntax

  caption ( level | member | set_exp )

• cast

  Converts an expression to a specified data type. Some data types allow for a length and precision to be specified. Make sure that the target is of the appropriate type and size.

  Syntax

  cast ( expression, datatype_specification )

  Example 1

  cast ( '123' , integer )

  Result: 123

  Example 2

  cast ( 12345 , VARCHAR ( 10 ) )

  Result: a string containing 12345

  Notes

  • You can specify the following datatypes: CHARACTER, VARCHAR, CHAR, NUMERIC, DECIMAL, INTEGER, SMALLINT, REAL, FLOAT, DATE, TIME, TIMESTAMP, TIME WITH TIME ZONE, TIMESTAMP WITH TIME ZONE, and INTERVAL.
  • When you convert a value of type TIMESTAMP to type DATE, the time portion of the timestamp value is ignored.
  • When you convert a value of type TIMESTAMP to type TIME, the date portion of the timestamp is ignored.
  • When you convert a value of type DATE to type TIMESTAMP, the time components of the timestamp are set to zero.
  • When you convert a value of type TIME to type TIMESTAMP, the date component is set to the current system date.
  • When you type cast to an INTERVAL type, you must specify one of the following interval qualifiers: YEAR, MONTH, or YEAR TO MONTH for the year-to-month interval datatype; DAY, HOUR, MINUTE, SECOND, DAY TO HOUR, DAY TO MINUTE, DAY TO SECOND, HOUR TO MINUTE, HOUR TO SECOND, or MINUTE TO SECOND for the day-to-second interval datatype. It is invalid to convert one interval datatype to the other (for instance because the number of days in a month is variable). Note that you can specify the number of digits for the leading qualifier only, i.e. YEAR(4) TO MONTH, DAY(5).
  • Errors will be reported if the target type and size are not compatible with the source type and size.
• ceil

  Returns the smallest integer greater than or equal to numeric_exp.

  Syntax

  ceil ( numeric_exp )

• ceiling

  Returns the smallest integer greater than or equal to numeric_exp.

  Syntax

  ceiling ( numeric_exp )

  Example 1

  ceiling ( 4.22 )

  Result: 5

  Example 2

  ceiling ( -1.23 )

  Result: -1

• char_length

  Returns the number of characters in string_exp.

  Syntax

  char_length ( string_exp )

  Example

  char_length ( 'Canada' )

  Result: 6

• character_length

  Returns the number of characters in string_exp.

  Syntax

  character_length ( string_exp )

  Example

  character_length ( 'Canada' )

  Result: 6

• children

  Returns the set of children of a specified member.

  Syntax

  children ( member )

• closingPeriod

  Returns the last sibling among the descendants of a member at a specified level. Typically used with a time dimension.

  Syntax

  closingPeriod ( level [, member ] )

• coalesce

  Returns the first non-null argument (or null if all arguments are null). The Coalesce function takes two or more arguments.

  Syntax

  coalesce ( exp_list )

• completeTuple

  Similar to "tuple", identifies a cell location (intersection) based on the specified members, each of which must be from a different dimension. However, completeTuple implicitly includes the default member from all dimensions not otherwise specified in the arguments, rather than the current member. The value of this cell can be obtained with the "value" function.

  Syntax

  completeTuple ( member { , member } )

• cousin

  Returns the child member of member2 with the same relative position as the member1 is under its parent.

  Syntax

  cousin ( member1 , member2 )

• current_date

  Returns a date value representing the current date of the computer that the database software runs on.

  Syntax

  current_date

  Example

  current_date

  Result: 2003-03-04

• current_time

  Returns a time with time zone value, representing the current time of the computer that runs the database software.

  Syntax

  current_time

  Example

  current_time

  Result: 16:33:11+05:00

• current_timestamp

  Returns a datetime with time zone value, representing the current timestamp of the computer that runs the database software.

  Syntax

  current_timestamp

  Example

  current_timestamp

  Result: 2003-03-03 16:40:15.535000+05:00

• currentMember

  Returns the current member of the hierarchy during an iteration. If the specified hierarchy is not present in the context in which the expression is being evaluated, its default member is assumed.

  Syntax

  currentMember ( hierarchy )

• defaultMember

  Returns the default member of a hierarchy.

  Syntax

  defaultMember ( hierarchy )

• descendants

  Returns the set of descendants of a set of members at a specified level(qualified name) or distance(integer 0..n) from the root. Duplicates will be removed from the set. Multiple options may be specified (separated by a space) to determine which members are to be returned.

  Syntax

  descendants ( set_expr , level | distance [ , { self | before | beforewithmember | after } ] )

  Example

  descendants([national].[Line].[Line].[Line1]->:[PC].[Line (Root)].[Dishwashers], 2, SELF AFTER)

  Result: Result: AcmeWash MR AcmeWash AcmeWash HE

  Notes

  • self: Only the members at the specified level are included in the final set (this is the default behaviour in the absence of any options).
  • before: If there are any intermediate levels between the member's level and the one specified, members from those levels are included. If the level specified is the same as the member upon which the function is applied, the member is included in the final set.
  • beforewithmember: If there are any intermediate levels between the member's level and the one specified, members from those levels are included. The member upon which the function is applied is also included in the final set.
  • after: If other levels exist after the specified level, members from those levels are included in the final set.

• emptySet

  Returns an empty member set for the specified hierarchy.

  Syntax

  emptySet ( hierarchy )

• except

  Returns the members of "set_exp1" that are not also in "set_exp2". Duplicates are retained only if the optional keyword ALL is supplied as the third argument.

  Syntax

  except ( set_exp1 , set_exp2 [,ALL] )

• exp

  Returns e raised to the power of numeric_exp. The constant e is the base of the natural logarithm. See also log.

  Syntax

  exp ( numeric_exp )

  Example

  exp ( 2 )

  Result: 7.389056

• extract

  Returns an integer representing the value of datepart (year, month, day, hour, minute, second) in datetime_exp.

  Syntax

  extract ( datepart , datetime_exp )

  Example 1

  extract ( year , 2003-03-03 16:40:15.535 )

  Result: 2003

  Example 2

  extract ( hour , 2003-03-03 16:40:15.535 )

  Result: 16

• filter

  Returns the set resulting from filtering a specified set based on the boolean condition. Each member is included in the result if and only if the corresponding value of "boolean_exp" is true.

  Syntax

  filter ( set_exp , boolean_exp )

• firstChild

  Returns the first child of a member.

  Syntax

  firstChild ( member)

• firstSibling

  Returns the first child of the parent of a member.

  Syntax

  firstSibling ( member )

• floor

  Returns the largest integer less than or equal to numeric_exp.

  Syntax

  floor ( numeric_exp )

  Example 1

  floor ( 3.22 )

  Result: 3

  Example 2

  floor ( -1.23 )

  Result: -2

• generate

  This function evaluates "set_exp2" for each member of "set_exp1" and joins the resulting sets by union. If ALL is specified, duplicates in the result are retained.

  Syntax

  generate ( set_exp1 , set_exp2 [ , ALL ] )

• head

  Returns the first "index_exp" elements of "set_exp". The default for "index_exp" is 1.

  Syntax

  head ( set_exp [ , index_exp ] )

• hierarchize

  This function orders the members of a set in a hierarchy. Members in a level are sorted in their natural order, which is the default ordering of the members along a dimension when no other sort conditions are specified.

  Syntax

  hierarchize ( set_exp )

• hierarchy

  Returns the hierarchy that contains the specified level, member or member set.

  Syntax

  hierarchy ( level | member | set_exp )

• intersect

  Returns the intersection of two input sets. The result retains duplicates only when the optional keyword ALL is supplied as the third argument.

  Syntax

  intersect ( set_exp1 , set_exp2 [ , ALL ] )

• item

  Returns a member from a specified location within a set. The index into the set is zero based

  Syntax

  item ( set_exp , index )

• lag

  Returns the sibling member that is a specified number of positions prior to a specified member.

  Syntax

  lag ( member , index_exp )

• lastChild

  Returns the last child of a specified member.

  Syntax

  lastChild ( member )

• lastPeriods

  Returns the set of members from the same level that ends with the specified member. The number of members returned is the absolute value of "integer_exp". If "integer_exp" is negative, members following and including the specified member are returned. Typically used with a time dimension.

  Syntax

  lastPeriods ( integer_exp , member )

• lastSibling

  Returns the last child of the parent of a specified member.

  Syntax

  lastSibling ( member )

• lead

  Returns the sibling member that is a specified number of positions following a specified member.

  Syntax

  lead ( member , index_exp )

• level

  Returns the level of a member.

  Syntax

  level ( member )

• levels

  Returns the level in the hierarchy whose distance from the root is specified by "index".

  Syntax

  levels ( hierarchy , index )

• ln

  Returns the natural logarithm of the numeric_exp.

  Syntax

  ln ( numeric_exp )

  Example

  ln ( 4 )  

  Result: 1.38629

• localtime

  Returns a time value, representing the current time of the computer that runs the database software.

  Syntax

  localtime

  Example

  localtime

  Result: 16:33:11

• localtimestamp

  Returns a datetime value, representing the current timestamp of the computer that runs the database software.

  Syntax

  localtimestamp

  Example

  localtimestamp

  Result: 2003-03-03 16:40:15.535000

• lower

  Returns string_exp with all uppercase characters shifted to lowercase.

  Syntax

  lower ( string_exp )

  Example

  lower ( 'ABCDEF' )

  Result: 'abcdef'

• member

  Defines a member based on the specified expression in the specified hierarchy. "string1" is used to identify the member created by this function it must be unique in the query, and must be different from any other member in the same hierarchy. "string2" is used as the caption of the member; if it is absent, the caption is empty. If the hierarchy is omitted, the measure dimension is assumed. Note: All calculations used as grouping items whose sibling items are other calculations or member sets, should be explicitly assigned to a hierarchy using this function, otherwise the results are not predictable. The only exception to this is where the calculation involves only members of the same hierarchy as the siblings. In that case the calculation is assumed to belong to that hierarchy.

  Syntax

  member ( value_exp [ , string1 [ , string2 [ , hierarchy ] ] ] )

• members

  Returns the set of members in a hierarchy or level. In the case of a hierarchy, the order of the members in the result is not guaranteed; if a predictable order is required, an explicit ordering function (such as hierarchize) must be used.

  Syntax

  members ( hierarchy | level )

• mod

  Returns the remainder (modulus) of integer_exp1 divided by integer_exp2. The integer_exp2 must not be zero or an exception condition is raised.

  Syntax

  mod ( integer_exp1, integer_exp2 )

  Example

  mod ( 20 , 3 )

  Result: 2

• nestedSet

  Returns the set of members of set_expr2 evaluated in the context of the current member of set_expr1.

  Syntax

  nestedSet ( set_expr1 , set_expr2 )

• nextMember

  Returns the next member in the level to which the specified member exists.

  Syntax

  nextMember ( member )

• octet_length

  Returns the number of bytes in string_exp.

  Syntax

  octet_length ( string_exp )

  Example 1

  octet_length ( 'ABCDEF' )

  Result: 6

  Example 2

  octet_length ( '' )

  Result: 0

• openingPeriod

  Returns the first sibling member among the descendants of a member at a specified level. Typically used with a time dimension.

  Syntax

  openingPeriod ( level [ , member ] )

• order

  Arranges members of a specified set, as determined from the set of values created by evaluating "value_exp" for each value of the set, and modified by the third parameter. There are two varieties of order: hierarchized (ASC or DESC) and non-hierarchized (BASC or BDESC, where B stands for "break hierarchy"). The hierarchized ordering first arranges members according to their position in the hierarchy. Then it orders the children of each member according to "value_exp". The non-hierarchized ordering arranges members in the set without regard to the hierarchy. In the absence of an explicit specification, ASC is the default.

  Syntax

  order ( set_exp , value_exp [ , ASC | DESC | BASC | BDESC ] )

• ordinal

  Returns the zero-based ordinal value (distance from the root level) of the specified level.

  Syntax

  ordinal ( level )

• parallelPeriod

  Returns a member from a different period in the same relative position as a specified member. This function is similar to the "Cousin" function, but is more closely related to time series. It takes the ancestor of "member" at "level" (call it "ancestor"); then it takes the sibling of "ancestor" that is offset (follows) by "int exp" positions, and returns the descendants of that sibling in the same relative position as the specified member as under "ancestor".

  Syntax

  parallelPeriod ( level , int_exp  , member )

• parent

  Returns the member that is the parent of the specified member.

  Syntax

  parent ( member )

• periodsToDate

  Returns a set of sibling members from the same level as a given member, as constrained by a specified level. It locates the ancestor of "member" at "level", and returns that ancestor's descendants at the same level as "member", up to and including "member". Typically used with a time dimension.

  Syntax

  periodsToDate ( level , member )

• position

  Returns integer value representing the position of the first string_exp in the second string_exp or 0 when the first string_exp is not found.

  Syntax

  position ( string_exp , string_exp )

  Example 1

  position ( 'C' , 'ABCDEF' )

  Result: 3

  Example 2

  position ( 'H' , 'ABCDEF' )

  Result: 0

• power

  Returns numeric_exp1 raised to the power numeric_exp2. If numeric_exp1 is negative then numeric_exp2 must result in an integer value.

  Syntax

  power ( numeric_exp1, numeric_exp2 )

  Example

  power ( 3 , 2 )

  Result: 9

• prevMember

  Returns the member that immediately precedes the specified member in the same level.

  Syntax

  prevMember ( member )

• roleValue

  Returns the value of the attribute that is associated with the role whose name is specified by "string" within the specified context. The second argument is optional only in a number of limited circumstances, where it can be derived from other context. Applications can be made portable across different data sources and models by accessing attributes by role, rather than by query item ID. (For dimensionally modelled relational data sources, assignment of roles is the modeller's responsibility.) Intrinsic roles that are defined for members of all data source types include: '_businessKey', '_memberCaption', '_memberDescription', '_memberUniqueName'.

  Syntax

  roleValue ( string [ , member | set_exp ] )

  Example

  roleValue ( '_memberCaption', [Sales].[Product].[Product].[Product line]->[all].[1] )

  Result: Camping Equipment

• rootMembers

  Returns the root members of a hierarchy.

  Syntax

  rootMembers ( hierarchy )

• set

  Returns the list of members defined in the expression. The members must belong to the same hierarchy.

  Syntax

  set ( member { , member } )

• siblings

  Returns the children of the parent of the specified member.

  Syntax

  siblings ( member )

• sqrt

  Returns the square root of numeric_exp. numeric_exp must be non-negative.

  Syntax

  sqrt ( numeric_exp )

  Example

  sqrt ( 9 )

  Result: 3

• subset

  Returns a subset of members from a specified set starting "index_exp1" from the beginning. If the count "index_exp2" is specified, that many members (if available) are returned. Otherwise, all remaining members are returned.

  Syntax

  subset ( set_exp, index_exp1 [ , index_exp2 ] )

• substring

  Returns the substring of string_exp that starts at position integer_exp1 for integer_exp2 characters or to the end of string_exp if integer_exp2 is omitted. The first character in string_exp is at position 1.

  Syntax

  substring ( string_exp , integer_exp1 [ , integer_exp2 ] )

  Example

  substring ( 'abdefg', 3, 2)

  Result: 'de'

• tail

  Returns the last "index_exp" elements of "set exp". The default for "index_exp" is 1.

  Syntax

  tail ( set_exp [ , index_exp ] )

• topCount

  This function sorts a set according to the values of "numeric_exp" evaluated at each of the members of "set_exp", and returns the top "index_exp" members.

  Syntax

  topCount ( set_exp , index_exp , numeric_exp )

• topPercent

  This function is similar to topSum, but the threshold is "numeric_exp1" percent of the total.

  Syntax

  topPercent ( set_exp , numeric_exp1, numeric_exp2 )

• topSum

  This function sorts on "numeric_exp2", evaluated at the corresponding members of "set_exp", and picks up the topmost elements whose cumulative total is at least "numeric_exp1".

  Syntax

  topSum ( set_exp , numeric_exp1 , numeric_exp2 )

• trim

  Returns a string_exp trimmed of leading and\or trailing blanks or trimmed of a certain character specified in match_character_exp. BOTH is implicit when first argument is not stated and blank is implicit when second argument is not stated.

  Syntax

  trim ( [ [ TRAILING | LEADING | BOTH ] [ match_character_exp ] , ] string_exp )

  Example 1

  trim ( TRAILING 'A' , 'ABCDEFA' )

  Result: 'ABCDEF'

  Example 2

  trim ( BOTH ' ABCDEF ' )

  Result: 'ABCDEF'

• tuple

  Identifies a cell location (intersection) based on the specified members, each of which must be from a different dimension. Implicitly includes the current member from all dimensions not otherwise specified in the arguments. The current member of any dimension not specified in the evaluating context is assumed to be the default member of that dimension. The value of this cell can be obtained with the "value" function.

  Syntax

  tuple (  member { , member } )

• union

  This function returns the union of 2 sets "set_exp1" and "set_exp2". The result retains duplicates only when the optional keyword ALL is supplied as the third argument.

  Syntax

  union ( set_exp1 , set_exp2 [ , ALL ] )

• unique

  Removes all duplicates from the specified set. The remaining members retain their original order.

  Syntax

  unique ( set_expr )

• upper

  Returns string_exp with all lowercase characters shifted to uppercase.

  Syntax

  upper ( string_exp )

  Example

  upper ( 'abcdef' )

  Result: 'ABCDEF'

• value

  Returns the value of the cell identified by a tuple. Note that the default member of the Measures dimension is the Default Measure

  Syntax

  value ( tuple )

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.
• Operators—About the operators 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