Pentaho Home

Mondrian Documentation

MDX Specification

What is MDX? 

MDX stands for 'multi-dimensional expressions'. It is the main query language implemented by Mondrian.

MDX was introduced by Microsoft with Microsoft SQL Server OLAP Services in around 1998, as the language component of the OLE DB for OLAP API. More recently, MDX has appeared as part of the XML for Analysis API. Microsoft proposed MDX as a standard, and its adoption among application writers and other OLAP providers is steadily increasing.

What is the syntax of MDX? 

A basic MDX query looks like this:

SELECT {[Measures].[Unit Sales], [Measures].[Store Sales]} ON COLUMNS,
  {[Product].members} ON ROWS
FROM [Sales]
WHERE [Time].[1997].[Q2]

It looks a little like SQL, but don't be deceived! The structure of an MDX query is quite different from SQL.

Since MDX is a standard language, we don't cover its syntax here. (The Microsoft SQL Server site has an MDX specification; there's also a good tutorial in Database Journal.) This specification describes the differences between Mondrian's dialect and the standard dialect of MDX.

Mondrian-specific MDX 

StrToSet and StrToTuple 

The StrToSet() and StrToTuple() functions take an extra parameter.

Parsing 

Parsing is case-sensitive.

Parameters 

Pseudo-functions Parameter() and ParamRef() allow you to create parameterized MDX statements.

A call to the Parameter function defines a parameter's name, data type and default value. A parameter always has a default value (it makes life simpler for the UI). If the parameter is a member, then the data type is the name of a hierarchy that the parameter must belong to. This metadata potentially allows a user interface to generate a list of values for the parameter.

The purpose of the ParamRef function is if you want to define a parameter once and use it several times in the same query. You could use Parameter several times but you'd have to make sure the definitions (data type, default value) are identical otherwise mondrian would complain.

The olap4j API supports parameters as you would expect. Once you've prepared a statement, you can call PreparedOlapStatement.getParameterMetaData(). This returns an OlapParameterMetaData, which is a subclass of the JDBC ParameterMetaData object.

Cast operator 

The Cast operator converts scalar expressions to other types. The syntax is

Cast(<Expression> AS <Type>)

where <Type> is one of:

  • BOOLEAN
  • NUMERIC
  • INTEGER
  • STRING

For example,

Cast([Store].CurrentMember.[Store Sqft], INTEGER)

returns the value of the [Store Sqft] property as an integer value.

IN and NOT IN 

IN and NOT IN are Mondrian-specific functions. For example:

SELECT {[Measures].[Unit Sales]} ON COLUMNS,
   FILTER([Product].[Product Family].MEMBERS,
          [Product].[Product Family].CurrentMember NOT IN
              {[Product].[All Products].firstChild,
               [Product].[All Products].lastChild}) ON ROWS
FROM [Sales]

MATCHES and NOT MATCHES 

MATCHES and NOT MATCHES are Mondrian-specific functions which compare a string with a Java regular expression. For example, the following query finds all employees whose name starts with 'sam' (case-insensitive):

SELECT {[Measures].[Org Salary]} ON COLUMNS,
   Filter({[Employees].MEMBERS},
          [Employees].CurrentMember.Name MATCHES '(?i)sam.*') ON ROWS
FROM [HR]

The MATCHES operator takes a Java compliant regular expression syntax. Mondrian can evaluate the MATCHES operator natively by pushing it down to the underlying RDBMS. Not all dialects support the evaluation of regular expressions, and most do not support the full extent of the Java syntax. Mondrian relies on the database dialect SPI to convert Java regular expressions into something the database can understand. If the database does not support regular expressions, Mondrian will evaluate the results in memory, using the full Java compliant syntax.

Visual Basic for Applications (VBA) functions 

Since the first implementation of MDX was as part of Microsoft SQL Server OLAP Services, the language inherited the built-in functions available in that environment, namely the Visual Basic for Applications (VBA) specification. This specification includes functions for conversion (CBool, CInt, IsNumber), arithmetic (Tan, Exp), finance (NPer, NPV), and date/time (DatePart, Now). Even though Mondrian cannot interface with Visual Basic, it includes a large number of VBA functions to allow MDX queries written in a Microsoft environment to run unchanged.

This document describes which VBA functions are available in mondrian; for more detailed descriptions of all VBA functions, see Visual Basic Functions. Note that that document includes some VBA functions which are not implemented in mondrian.

Comments 

MDX statements can contain comments. There are 3 syntactic forms for comments:

// End-of-line comment

-- End-of-line comment

/* Multi-line
comment */

Comments can be nested, for example

/* Multi-line
comment /* Comment within a comment */
*/

Format Strings 

Every member has a FORMAT_STRING property, which affects how its raw value is rendered into text in the user interface. For example, the query

WITH MEMBER [Measures].[Profit] AS '([Measures].[Store Sales] - [Measures].[Store Cost])',
FORMAT_STRING = "$#,###.00"
SELECT {[Measures].[Store Sales], [Measures].[Profit]} ON COLUMNS,
{[Product].CurrentMember.Children} ON ROWS
FROM [Sales]

yields cells formatted in dollar and cent amounts.

Members defined in a schema file can also have format strings. Measures use the formatString attribute:

<Measure name="Store Sales" column="store_sales" aggregator="sum" formatString="#,###.00"/>

and calculated members use the <CalculatedMemberProperty> sub-element:

<CalculatedMember name="Profit" dimension="Measures" formula="[Measures].[Store Sales] - [Measures].[Store Cost]">
  <CalculatedMemberProperty name="FORMAT_STRING" value="$#,##0.00"/>
</CalculatedMember>

Format strings use Visual Basic formatting syntax; see class mondrian.util.Format for more details.

A measure's format string is usually a fixed string, but is really an expression, which is evaluated in the same context as the cell. You can therefore change the formatting of a cell depending upon the cell's value.

The format string can even contain 'style' attributes which are interpreted specially by JPivot. If present, JPivot will render cells in color.

The following example combines a dynamic formula with style attributes. The result is that cells are displayed with green background if they are less than $100,000, or a red background if they are greater than $100,000:

WITH MEMBER [Measures].[Profit] AS
   '([Measures].[Store Sales] - [Measures].[Store Cost])',
  FORMAT_STRING = Iif([Measures].[Profit] < 100000, '|#|style=green', '|#|style=red')
SELECT {[Measures].[Store Sales], [Measures].[Profit]} ON COLUMNS,
  {[Product].CurrentMember.Children} ON ROWS
FROM [Sales]

Order of sets

MDX sets are ordered and may contain duplicates. (Both of these properties are at odds with the mathematical definition of 'set', so it would have been better if they were called 'lists', but we're stuck with the term 'set'.)

For most functions that return sets, Microsoft's documentation for SQL Server Analysis Services 2008 (the de facto MDX standard) does not specify the order of elements in the result set, and one might assume that MDX server could return the results in any order and still comply with the standard. However, Mondrian's implementation of MDX gives stronger guarantees: a function's result set will be in the obvious order.

For most functions, the definition of 'obvious' is obvious, so we won't spell it out in detail. For example, Filter returns elements in the same order as the set expression; Crossjoin returns the results in the order of the first set expression, then within that, by the second second expression. Similarly Generate, Union, Except. The sorting functions (Order, TopCount, BottomCount, TopPercent, Hierarchize, etc.) use a stable sorting algorithm. Metadata methods such as <Hierarchy>.Members return their results in natural order.

If you do not care about the order of results of a set expression (say because you are sorting the results later), wrap the expression the Unorder function, and mondrian may be able to use a more efficient algorithm that does not guarantee order.

Function List

These are the functions implemented in the current Mondrian release.

Name Description
$AggregateChildren Equivalent to 'Aggregate(<Hierarchy>.CurrentMember.Children); for internal use.

Syntax

<Numeric Expression> $AggregateChildren(<Hierarchy>)
()

Syntax

* Multiplies two numbers.

Syntax

<Numeric Expression> * <Numeric Expression>
* Returns the cross product of two sets.

Syntax

<Set> * <Set>
<Member> * <Set>
<Set> * <Member>
<Member> * <Member>
+ Adds two numbers.

Syntax

<Numeric Expression> + <Numeric Expression>
- Subtracts two numbers.

Syntax

<Numeric Expression> - <Numeric Expression>
- Returns the negative of a number.

Syntax

- <Numeric Expression>
/ Divides two numbers.

Syntax

<Numeric Expression> / <Numeric Expression>
: Infix colon operator returns the set of members between a given pair of members.

Syntax

<Member> : <Member>
< Returns whether an expression is less than another.

Syntax

<Numeric Expression> < <Numeric Expression>
< Returns whether an expression is less than another.

Syntax

<String> < <String>
<= Returns whether an expression is less than or equal to another.

Syntax

<Numeric Expression> <= <Numeric Expression>
<= Returns whether an expression is less than or equal to another.

Syntax

<String> <= <String>
<> Returns whether two expressions are not equal.

Syntax

<Numeric Expression> <> <Numeric Expression>
<> Returns whether two expressions are not equal.

Syntax

<String> <> <String>
= Returns whether two expressions are equal.

Syntax

<Numeric Expression> = <Numeric Expression>
= Returns whether two expressions are equal.

Syntax

<String> = <String>
> Returns whether an expression is greater than another.

Syntax

<Numeric Expression> > <Numeric Expression>
> Returns whether an expression is greater than another.

Syntax

<String> > <String>
>= Returns whether an expression is greater than or equal to another.

Syntax

<Numeric Expression> >= <Numeric Expression>
>= Returns whether an expression is greater than or equal to another.

Syntax

<String> >= <String>
AND Returns the conjunction of two conditions.

Syntax

<Logical Expression> AND <Logical Expression>
Abs Returns a value of the same type that is passed to it specifying the absolute value of a number.

Syntax

<Numeric Expression> Abs(<Numeric Expression>)
Acos Returns the arccosine, or inverse cosine, of a number. The arccosine is the angle whose cosine is Arg1. The returned angle is given in radians in the range 0 (zero) to pi.

Syntax

<Numeric Expression> Acos(<Numeric Expression>)
Acosh Returns the inverse hyperbolic cosine of a number. Number must be greater than or equal to 1. The inverse hyperbolic cosine is the value whose hyperbolic cosine is Arg1, so Acosh(Cosh(number)) equals Arg1.

Syntax

<Numeric Expression> Acosh(<Numeric Expression>)
AddCalculatedMembers Adds calculated members to a set.

Syntax

<Set> AddCalculatedMembers(<Set>)
Aggregate Returns a calculated value using the appropriate aggregate function, based on the context of the query.

Syntax

<Numeric Expression> Aggregate(<Set>)
<Numeric Expression> Aggregate(<Set>, <Numeric Expression>)
AllMembers Returns a set that contains all members, including calculated members, of the specified dimension.

Syntax

<Dimension>.AllMembers
AllMembers Returns a set that contains all members, including calculated members, of the specified hierarchy.

Syntax

<Hierarchy>.AllMembers
AllMembers Returns a set that contains all members, including calculated members, of the specified level.

Syntax

<Level>.AllMembers
Ancestor Returns the ancestor of a member at a specified level.

Syntax

<Member> Ancestor(<Member>, <Level>)
<Member> Ancestor(<Member>, <Numeric Expression>)
Asc Returns an Integer representing the character code corresponding to the first letter in a string.

Syntax

<Integer> Asc(<String>)
AscB See Asc.

Syntax

<Integer> AscB(<String>)
AscW See Asc.

Syntax

<Integer> AscW(<String>)
Ascendants Returns the set of the ascendants of a specified member.

Syntax

<Set> Ascendants(<Member>)
Asin Returns the arcsine, or inverse sine, of a number. The arcsine is the angle whose sine is Arg1. The returned angle is given in radians in the range -pi/2 to pi/2.

Syntax

<Numeric Expression> Asin(<Numeric Expression>)
Asinh Returns the inverse hyperbolic sine of a number. The inverse hyperbolic sine is the value whose hyperbolic sine is Arg1, so Asinh(Sinh(number)) equals Arg1.

Syntax

<Numeric Expression> Asinh(<Numeric Expression>)
Atan2 Returns the arctangent, or inverse tangent, of the specified x- and y-coordinates. The arctangent is the angle from the x-axis to a line containing the origin (0, 0) and a point with coordinates (x_num, y_num). The angle is given in radians between -pi and pi, excluding -pi.

Syntax

<Numeric Expression> Atan2(<Numeric Expression>, <Numeric Expression>)
Atanh Returns the inverse hyperbolic tangent of a number. Number must be between -1 and 1 (excluding -1 and 1).

Syntax

<Numeric Expression> Atanh(<Numeric Expression>)
Atn Returns a Double specifying the arctangent of a number.

Syntax

<Numeric Expression> Atn(<Numeric Expression>)
Avg Returns the average value of a numeric expression evaluated over a set.

Syntax

<Numeric Expression> Avg(<Set>)
<Numeric Expression> Avg(<Set>, <Numeric Expression>)
BottomCount Returns a specified number of items from the bottom of a set, optionally ordering the set first.

Syntax

<Set> BottomCount(<Set>, <Numeric Expression>, <Numeric Expression>)
<Set> BottomCount(<Set>, <Numeric Expression>)
BottomPercent Sorts a set and returns the bottom N elements whose cumulative total is at least a specified percentage.

Syntax

<Set> BottomPercent(<Set>, <Numeric Expression>, <Numeric Expression>)
BottomSum Sorts a set and returns the bottom N elements whose cumulative total is at least a specified value.

Syntax

<Set> BottomSum(<Set>, <Numeric Expression>, <Numeric Expression>)
CBool Returns an expression that has been converted to a Variant of subtype Boolean.

Syntax

<Logical Expression> CBool(<Value>)
CByte Returns an expression that has been converted to a Variant of subtype Byte.

Syntax

<Integer> CByte(<Value>)
CDate Returns an expression that has been converted to a Variant of subtype Date.

Syntax

<DateTime> CDate(<Value>)
CDbl Returns an expression that has been converted to a Variant of subtype Double.

Syntax

<Numeric Expression> CDbl(<Value>)
CInt Returns an expression that has been converted to a Variant of subtype Integer.

Syntax

<Integer> CInt(<Value>)
Cache Evaluates and returns its sole argument, applying statement-level caching

Syntax

Cache(<<Exp>>)
CalculatedChild Returns an existing calculated child member with name <String> from the specified <Member>.

Syntax

<Member> <Member>.CalculatedChild(<String>)
Caption Returns the caption of a dimension.

Syntax

<Dimension>.Caption
Caption Returns the caption of a hierarchy.

Syntax

<Hierarchy>.Caption
Caption Returns the caption of a level.

Syntax

<Level>.Caption
Caption Returns the caption of a member.

Syntax

<Member>.Caption
Cast Converts values to another type.

Syntax

Cast(<Expression> AS <Type>)
Children Returns the children of a member.

Syntax

<Member>.Children
Chr Returns a String containing the character associated with the specified character code.

Syntax

<String> Chr(<Integer>)
ChrB See Chr.

Syntax

<String> ChrB(<Integer>)
ChrW See Chr.

Syntax

<String> ChrW(<Integer>)
ClosingPeriod Returns the last descendant of a member at a level.

Syntax

<Member> ClosingPeriod()
<Member> ClosingPeriod(<Level>)
<Member> ClosingPeriod(<Level>, <Member>)
<Member> ClosingPeriod(<Member>)
CoalesceEmpty Coalesces an empty cell value to a different value. All of the expressions must be of the same type (number or string).

Syntax

CoalesceEmpty(<Value Expression>[, <Value Expression>...])
Correlation Returns the correlation of two series evaluated over a set.

Syntax

<Numeric Expression> Correlation(<Set>, <Numeric Expression>)
<Numeric Expression> Correlation(<Set>, <Numeric Expression>, <Numeric Expression>)
Cos Returns a Double specifying the cosine of an angle.

Syntax

<Numeric Expression> Cos(<Numeric Expression>)
Cosh Returns the hyperbolic cosine of a number.

Syntax

<Numeric Expression> Cosh(<Numeric Expression>)
Count Returns the number of tuples in a set, empty cells included unless the optional EXCLUDEEMPTY flag is used.

Syntax

<Numeric Expression> Count(<Set>)
<Numeric Expression> Count(<Set>, <Symbol>)
Count Returns the number of tuples in a set including empty cells.

Syntax

<Set>.Count
Cousin Returns the member with the same relative position under <ancestor member> as the member specified.

Syntax

<Member> Cousin(<Member>, <Ancestor Member>)
Covariance Returns the covariance of two series evaluated over a set (biased).

Syntax

<Numeric Expression> Covariance(<Set>, <Numeric Expression>)
<Numeric Expression> Covariance(<Set>, <Numeric Expression>, <Numeric Expression>)
CovarianceN Returns the covariance of two series evaluated over a set (unbiased).

Syntax

<Numeric Expression> CovarianceN(<Set>, <Numeric Expression>)
<Numeric Expression> CovarianceN(<Set>, <Numeric Expression>, <Numeric Expression>)
Crossjoin Returns the cross product of two sets.

Syntax

<Set> Crossjoin(<Set>, <Set>)
CurrentDateMember Returns the exact member within the specified dimension corresponding to the current date, in the format specified by the format parameter. If there is no such date, returns the NULL member. Format strings are the same as used by the MDX Format function, namely the Visual Basic format strings. See http://www.apostate.com/programming/vb-format.html.

Syntax

<Member> CurrentDateMember(<Hierarchy>, <String>)
CurrentDateMember Returns the closest or exact member within the specified dimension corresponding to the current date, in the format specified by the format parameter. Format strings are the same as used by the MDX Format function, namely the Visual Basic format strings. See http://www.apostate.com/programming/vb-format.html.

Syntax

<Member> CurrentDateMember(<Hierarchy>, <String>, <Symbol>)
CurrentDateString Returns the current date formatted as specified by the format parameter.

Syntax

<String> CurrentDateString(<String>)
CurrentMember Returns the current member along a dimension during an iteration.

Syntax

<Dimension>.CurrentMember
CurrentMember Returns the current member along a hierarchy during an iteration.

Syntax

<Hierarchy>.CurrentMember
DDB Returns a Double specifying the depreciation of an asset for a specific time period using the double-declining balance method or some other method you specify.

Syntax

<Numeric Expression> DDB(<Numeric Expression>, <Numeric Expression>, <Numeric Expression>, <Numeric Expression>, <Numeric Expression>)
DDB Returns a Double specifying the depreciation of an asset for a specific time period using the double-declining balance method or some other method you specify.

Syntax

<Numeric Expression> DDB(<Numeric Expression>, <Numeric Expression>, <Numeric Expression>, <Numeric Expression>)
DataMember Returns the system-generated data member that is associated with a nonleaf member of a dimension.

Syntax

<Member>.DataMember
Date Returns a Variant (Date) containing the current system date.

Syntax

<DateTime> Date()
DateAdd Returns a Variant (Date) containing a date to which a specified time interval has been added.

Syntax

<DateTime> DateAdd(<String>, <Numeric Expression>, <DateTime>)
DateDiff Returns a Variant (Long) specifying the number of time intervals between two specified dates.

Syntax

<Numeric Expression> DateDiff(<String>, <DateTime>, <DateTime>, <Integer>, <Integer>)
DateDiff Returns a Variant (Long) specifying the number of time intervals between two specified dates.

Syntax

<Numeric Expression> DateDiff(<String>, <DateTime>, <DateTime>, <Integer>)
DateDiff Returns a Variant (Long) specifying the number of time intervals between two specified dates.

Syntax

<Numeric Expression> DateDiff(<String>, <DateTime>, <DateTime>)
DatePart Returns a Variant (Integer) containing the specified part of a given date.

Syntax

<Integer> DatePart(<String>, <DateTime>, <Integer>, <Integer>)
DatePart Returns a Variant (Integer) containing the specified part of a given date.

Syntax

<Integer> DatePart(<String>, <DateTime>, <Integer>)
DatePart Returns a Variant (Integer) containing the specified part of a given date.

Syntax

<Integer> DatePart(<String>, <DateTime>)
DateSerial Returns a Variant (Date) for a specified year, month, and day.

Syntax

<DateTime> DateSerial(<Integer>, <Integer>, <Integer>)
DateValue Returns a Variant (Date).

Syntax

<DateTime> DateValue(<DateTime>)
Day Returns a Variant (Integer) specifying a whole number between 1 and 31, inclusive, representing the day of the month.

Syntax

<Integer> Day(<DateTime>)
DefaultMember Returns the default member of a dimension.

Syntax

<Dimension>.DefaultMember
DefaultMember Returns the default member of a hierarchy.

Syntax

<Hierarchy>.DefaultMember
Degrees Converts radians to degrees.

Syntax

<Numeric Expression> Degrees(<Numeric Expression>)
Descendants Returns the set of descendants of a member at a specified level, optionally including or excluding descendants in other levels.

Syntax

<Set> Descendants(<Member>)
<Set> Descendants(<Member>, <Level>)
<Set> Descendants(<Member>, <Level>, <Symbol>)
<Set> Descendants(<Member>, <Numeric Expression>)
<Set> Descendants(<Member>, <Numeric Expression>, <Symbol>)
<Set> Descendants(<Member>, <Empty>, <Symbol>)
Dimension Returns the dimension that contains a specified hierarchy.

Syntax

<Dimension>.Dimension
Dimension Returns the dimension that contains a specified hierarchy.

Syntax

<Hierarchy>.Dimension
Dimension Returns the dimension that contains a specified level.

Syntax

<Level>.Dimension
Dimension Returns the dimension that contains a specified member.

Syntax

<Member>.Dimension
Dimensions Returns the dimension whose zero-based position within the cube is specified by a numeric expression.

Syntax

<Dimension> Dimensions(<Numeric Expression>)
Dimensions Returns the dimension whose name is specified by a string.

Syntax

<Dimension> Dimensions(<String>)
Distinct Eliminates duplicate tuples from a set.

Syntax

<Set> Distinct(<Set>)
DrilldownLevel Drills down the members of a set, at a specified level, to one level below. Alternatively, drills down on a specified dimension in the set.

Syntax

<Set> DrilldownLevel(<Set>)
<Set> DrilldownLevel(<Set>, <Level>)
<Set> DrilldownLevel(<Set>, <Empty>, <Numeric Expression>)
DrilldownLevelBottom Drills down the bottommost members of a set, at a specified level, to one level below.

Syntax

<Set> DrilldownLevelBottom(<Set>, <Numeric Expression>)
<Set> DrilldownLevelBottom(<Set>, <Numeric Expression>, <Level>)
<Set> DrilldownLevelBottom(<Set>, <Numeric Expression>, <Level>, <Numeric Expression>)
<Set> DrilldownLevelBottom(<Set>, <Numeric Expression>, <Empty>, <Numeric Expression>)
DrilldownLevelTop Drills down the topmost members of a set, at a specified level, to one level below.

Syntax

<Set> DrilldownLevelTop(<Set>, <Numeric Expression>)
<Set> DrilldownLevelTop(<Set>, <Numeric Expression>, <Level>)
<Set> DrilldownLevelTop(<Set>, <Numeric Expression>, <Level>, <Numeric Expression>)
<Set> DrilldownLevelTop(<Set>, <Numeric Expression>, <Empty>, <Numeric Expression>)
DrilldownMember Drills down the members in a set that are present in a second specified set.

Syntax

<Set> DrilldownMember(<Set>, <Set>)
<Set> DrilldownMember(<Set>, <Set>, <Symbol>)
Except Finds the difference between two sets, optionally retaining duplicates.

Syntax

<Set> Except(<Set>, <Set>)
<Set> Except(<Set>, <Set>, <Symbol>)
Exists Returns the the set of tuples of the first set that exist with one or more tuples of the second set.

Syntax

<Set> Exists(<Set>, <Set>)
Exp Returns a Double specifying e (the base of natural logarithms) raised to a power.

Syntax

<Numeric Expression> Exp(<Numeric Expression>)
Extract Returns a set of tuples from extracted dimension elements. The opposite of Crossjoin.

Syntax

Extract(<Set>, <Dimension>[, <Dimension>...])
FV Returns a Double specifying the future value of an annuity based on periodic, fixed payments and a fixed interest rate.

Syntax

<Numeric Expression> FV(<Numeric Expression>, <Numeric Expression>, <Numeric Expression>, <Numeric Expression>, <Logical Expression>)
FV Returns a Double specifying the future value of an annuity based on periodic, fixed payments and a fixed interest rate.

Syntax

<Numeric Expression> FV(<Numeric Expression>, <Numeric Expression>, <Numeric Expression>, <Numeric Expression>)
FV Returns a Double specifying the future value of an annuity based on periodic, fixed payments and a fixed interest rate.

Syntax

<Numeric Expression> FV(<Numeric Expression>, <Numeric Expression>, <Numeric Expression>)
Filter Returns the set resulting from filtering a set based on a search condition.

Syntax

<Set> Filter(<Set>, <Logical Expression>)
FirstChild Returns the first child of a member.

Syntax

<Member>.FirstChild
FirstQ Returns the 1st quartile value of a numeric expression evaluated over a set.

Syntax

<Numeric Expression> FirstQ(<Set>)
<Numeric Expression> FirstQ(<Set>, <Numeric Expression>)
FirstSibling Returns the first child of the parent of a member.

Syntax

<Member>.FirstSibling
Fix Returns the integer portion of a number. If negative, returns the negative number greater than or equal to the number.

Syntax

<Integer> Fix(<Value>)
Format Formats a number or date to a string.

Syntax

<String> Format(<Member>, <String>)
<String> Format(<Numeric Expression>, <String>)
<String> Format(<DateTime>, <String>)
FormatCurrency Returns an expression formatted as a currency value using the currency symbol defined in the system control panel.

Syntax

<String> FormatCurrency(<Value>, <Integer>, <Integer>, <Integer>, <Integer>)
FormatCurrency Returns an expression formatted as a currency value using the currency symbol defined in the system control panel.

Syntax

<String> FormatCurrency(<Value>, <Integer>, <Integer>, <Integer>)
FormatCurrency Returns an expression formatted as a currency value using the currency symbol defined in the system control panel.

Syntax

<String> FormatCurrency(<Value>, <Integer>, <Integer>)
FormatCurrency Returns an expression formatted as a currency value using the currency symbol defined in the system control panel.

Syntax

<String> FormatCurrency(<Value>, <Integer>)
FormatCurrency Returns an expression formatted as a currency value using the currency symbol defined in the system control panel.

Syntax

<String> FormatCurrency(<Value>)
FormatDateTime Returns an expression formatted as a date or time.

Syntax

<String> FormatDateTime(<DateTime>, <Integer>)
FormatDateTime Returns an expression formatted as a date or time.

Syntax

<String> FormatDateTime(<DateTime>)
FormatNumber Returns an expression formatted as a number.

Syntax

<String> FormatNumber(<Value>, <Integer>, <Integer>, <Integer>, <Integer>)
FormatNumber Returns an expression formatted as a number.

Syntax

<String> FormatNumber(<Value>, <Integer>, <Integer>, <Integer>)
FormatNumber Returns an expression formatted as a number.

Syntax

<String> FormatNumber(<Value>, <Integer>, <Integer>)
FormatNumber Returns an expression formatted as a number.

Syntax

<String> FormatNumber(<Value>, <Integer>)
FormatNumber Returns an expression formatted as a number.

Syntax

<String> FormatNumber(<Value>)
FormatPercent Returns an expression formatted as a percentage (multipled by 100) with a trailing % character.

Syntax

<String> FormatPercent(<Value>, <Integer>, <Integer>, <Integer>, <Integer>)
FormatPercent Returns an expression formatted as a percentage (multipled by 100) with a trailing % character.

Syntax

<String> FormatPercent(<Value>, <Integer>, <Integer>, <Integer>)
FormatPercent Returns an expression formatted as a percentage (multipled by 100) with a trailing % character.

Syntax

<String> FormatPercent(<Value>, <Integer>, <Integer>)
FormatPercent Returns an expression formatted as a percentage (multipled by 100) with a trailing % character.

Syntax

<String> FormatPercent(<Value>, <Integer>)
FormatPercent Returns an expression formatted as a percentage (multipled by 100) with a trailing % character.

Syntax

<String> FormatPercent(<Value>)
Generate Applies a set to each member of another set and joins the resulting sets by union.

Syntax

<Set> Generate(<Set>, <Set>)
<Set> Generate(<Set>, <Set>, <Symbol>)
Generate Applies a set to a string expression and joins resulting sets by string concatenation.

Syntax

<String> Generate(<Set>, <String>)
<String> Generate(<Set>, <String>, <String>)
Head Returns the first specified number of elements in a set.

Syntax

<Set> Head(<Set>)
<Set> Head(<Set>, <Numeric Expression>)
Hex Returns a String representing the hexadecimal value of a number.

Syntax

<String> Hex(<Value>)
Hierarchize Orders the members of a set in a hierarchy.

Syntax

<Set> Hierarchize(<Set>)
<Set> Hierarchize(<Set>, <Symbol>)
Hierarchy Returns a level's hierarchy.

Syntax

<Level>.Hierarchy
Hierarchy Returns a member's hierarchy.

Syntax

<Member>.Hierarchy
Hour Returns a Variant (Integer) specifying a whole number between 0 and 23, inclusive, representing the hour of the day.

Syntax

<Integer> Hour(<DateTime>)
IIf Returns one of two tuples determined by a logical test.

Syntax

<Tuple> IIf(<Logical Expression>, <Tuple>, <Tuple>)
IIf Returns one of two dimension values determined by a logical test.

Syntax

<Dimension> IIf(<Logical Expression>, <Dimension>, <Dimension>)
IIf Returns one of two hierarchy values determined by a logical test.

Syntax

<Hierarchy> IIf(<Logical Expression>, <Hierarchy>, <Hierarchy>)
IIf Returns one of two level values determined by a logical test.

Syntax

<Level> IIf(<Logical Expression>, <Level>, <Level>)
IIf Returns boolean determined by a logical test.

Syntax

<Logical Expression> IIf(<Logical Expression>, <Logical Expression>, <Logical Expression>)
IIf Returns one of two member values determined by a logical test.

Syntax

<Member> IIf(<Logical Expression>, <Member>, <Member>)
IIf Returns one of two numeric values determined by a logical test.

Syntax

<Numeric Expression> IIf(<Logical Expression>, <Numeric Expression>, <Numeric Expression>)
IIf Returns one of two set values determined by a logical test.

Syntax

<Set> IIf(<Logical Expression>, <Set>, <Set>)
IIf Returns one of two string values determined by a logical test.

Syntax

<String> IIf(<Logical Expression>, <String>, <String>)
IPmt Returns a Double specifying the interest payment for a given period of an annuity based on periodic, fixed payments and a fixed interest rate.

Syntax

<Numeric Expression> IPmt(<Numeric Expression>, <Numeric Expression>, <Numeric Expression>, <Numeric Expression>, <Numeric Expression>, <Logical Expression>)
IPmt Returns a Double specifying the interest payment for a given period of an annuity based on periodic, fixed payments and a fixed interest rate.

Syntax

<Numeric Expression> IPmt(<Numeric Expression>, <Numeric Expression>, <Numeric Expression>, <Numeric Expression>, <Numeric Expression>)
IPmt Returns a Double specifying the interest payment for a given period of an annuity based on periodic, fixed payments and a fixed interest rate.

Syntax

<Numeric Expression> IPmt(<Numeric Expression>, <Numeric Expression>, <Numeric Expression>, <Numeric Expression>)
IRR Returns a Double specifying the internal rate of return for a series of periodic cash flows (payments and receipts).

Syntax

<Numeric Expression> IRR(<Array>, <Numeric Expression>)
IRR Returns a Double specifying the internal rate of return for a series of periodic cash flows (payments and receipts).

Syntax

<Numeric Expression> IRR(<Array>)
IS Returns whether two objects are the same

Syntax

<Member> IS <Member>
<Level> IS <Level>
<Hierarchy> IS <Hierarchy>
<Dimension> IS <Dimension>
<Tuple> IS <Tuple>
IS EMPTY Determines if an expression evaluates to the empty cell value.

Syntax

<Member> IS EMPTY
<Tuple> IS EMPTY
IS NULL Returns whether an object is null

Syntax

<Member> IS NULL
<Level> IS NULL
<Hierarchy> IS NULL
<Dimension> IS NULL
InStr Returns the position of the first occurrence of one string within another. Implements very basic form of InStr

Syntax

<Numeric Expression> InStr(<String>, <String>)
InStrRev Returns the position of an occurrence of one string within another, from the end of string.

Syntax

<Integer> InStrRev(<String>, <String>, <Integer>, <Integer>)
InStrRev Returns the position of an occurrence of one string within another, from the end of string.

Syntax

<Integer> InStrRev(<String>, <String>, <Integer>)
InStrRev Returns the position of an occurrence of one string within another, from the end of string.

Syntax

<Integer> InStrRev(<String>, <String>)
Int Returns the integer portion of a number. If negative, returns the negative number less than or equal to the number.

Syntax

<Integer> Int(<Value>)
Intersect Returns the intersection of two input sets, optionally retaining duplicates.

Syntax

<Set> Intersect(<Set>, <Set>, <Symbol>)
<Set> Intersect(<Set>, <Set>)
IsDate Returns a Boolean value indicating whether an expression can be converted to a date..

Syntax

<Logical Expression> IsDate(<Value>)
IsEmpty Determines if an expression evaluates to the empty cell value.

Syntax

<Logical Expression> IsEmpty(<String>)
<Logical Expression> IsEmpty(<Numeric Expression>)
Item Returns a member from the tuple specified in <Tuple>. The member to be returned is specified by the zero-based position of the member in the set in <Index>.

Syntax

<Member> <Tuple>.Item(<Numeric Expression>)
Item Returns a tuple from the set specified in <Set>. The tuple to be returned is specified by the zero-based position of the tuple in the set in <Index>.

Syntax

<Member> <Set>.Item(<Numeric Expression>)
Item Returns a tuple from the set specified in <Set>. The tuple to be returned is specified by the member name (or names) in <String>.

Syntax

<Set>.Item(<String> [, ...])
LCase Returns a String that has been converted to lowercase.

Syntax

<String> LCase(<String>)
LTrim Returns a Variant (String) containing a copy of a specified string without leading spaces.

Syntax

<String> LTrim(<String>)
Lag Returns a member further along the specified member's dimension.

Syntax

<Member> <Member>.Lag(<Numeric Expression>)
LastChild Returns the last child of a member.

Syntax

<Member>.LastChild
LastPeriods Returns a set of members prior to and including a specified member.

Syntax

<Set> LastPeriods(<Numeric Expression>)
<Set> LastPeriods(<Numeric Expression>, <Member>)
LastSibling Returns the last child of the parent of a member.

Syntax

<Member>.LastSibling
Lead Returns a member further along the specified member's dimension.

Syntax

<Member> <Member>.Lead(<Numeric Expression>)
Left Returns a specified number of characters from the left side of a string.

Syntax

<String> Left(<String>, <Integer>)
Len Returns the number of characters in a string

Syntax

<Numeric Expression> Len(<String>)
Level Returns a member's level.

Syntax

<Member>.Level
Levels Returns the level whose position in a hierarchy is specified by a numeric expression.

Syntax

<Level> <Hierarchy>.Levels(<Numeric Expression>)
Levels Returns the level whose name is specified by a string expression.

Syntax

<Level> <Hierarchy>.Levels(<String>)
Levels Returns the level whose name is specified by a string expression.

Syntax

<Level> Levels(<String>)
LinRegIntercept Calculates the linear regression of a set and returns the value of b in the regression line y = ax + b.

Syntax

<Numeric Expression> LinRegIntercept(<Set>, <Numeric Expression>)
<Numeric Expression> LinRegIntercept(<Set>, <Numeric Expression>, <Numeric Expression>)
LinRegPoint Calculates the linear regression of a set and returns the value of y in the regression line y = ax + b.

Syntax

<Numeric Expression> LinRegPoint(<Numeric Expression>, <Set>, <Numeric Expression>)
<Numeric Expression> LinRegPoint(<Numeric Expression>, <Set>, <Numeric Expression>, <Numeric Expression>)
LinRegR2 Calculates the linear regression of a set and returns R2 (the coefficient of determination).

Syntax

<Numeric Expression> LinRegR2(<Set>, <Numeric Expression>)
<Numeric Expression> LinRegR2(<Set>, <Numeric Expression>, <Numeric Expression>)
LinRegSlope Calculates the linear regression of a set and returns the value of a in the regression line y = ax + b.

Syntax

<Numeric Expression> LinRegSlope(<Set>, <Numeric Expression>)
<Numeric Expression> LinRegSlope(<Set>, <Numeric Expression>, <Numeric Expression>)
LinRegVariance Calculates the linear regression of a set and returns the variance associated with the regression line y = ax + b.

Syntax

<Numeric Expression> LinRegVariance(<Set>, <Numeric Expression>)
<Numeric Expression> LinRegVariance(<Set>, <Numeric Expression>, <Numeric Expression>)
Log Returns a Double specifying the natural logarithm of a number.

Syntax

<Numeric Expression> Log(<Numeric Expression>)
Log10 Returns the base-10 logarithm of a number.

Syntax

<Numeric Expression> Log10(<Numeric Expression>)
MIRR Returns a Double specifying the modified internal rate of return for a series of periodic cash flows (payments and receipts).

Syntax

<Numeric Expression> MIRR(<Array>, <Numeric Expression>, <Numeric Expression>)
Max Returns the maximum value of a numeric expression evaluated over a set.

Syntax

<Numeric Expression> Max(<Set>)
<Numeric Expression> Max(<Set>, <Numeric Expression>)
Median Returns the median value of a numeric expression evaluated over a set.

Syntax

<Numeric Expression> Median(<Set>)
<Numeric Expression> Median(<Set>, <Numeric Expression>)
Members Returns the set of members in a dimension.

Syntax

<Dimension>.Members
Members Returns the set of members in a hierarchy.

Syntax

<Hierarchy>.Members
Members Returns the set of members in a level.

Syntax

<Level>.Members
Members Returns the member whose name is specified by a string expression.

Syntax

<Member> Members(<String>)
Mid Returns a specified number of characters from a string.

Syntax

<String> Mid(<String>, <Integer>, <Integer>)
Mid Returns a specified number of characters from a string.

Syntax

<String> Mid(<String>, <Integer>)
Min Returns the minimum value of a numeric expression evaluated over a set.

Syntax

<Numeric Expression> Min(<Set>)
<Numeric Expression> Min(<Set>, <Numeric Expression>)
Minute Returns a Variant (Integer) specifying a whole number between 0 and 59, inclusive, representing the minute of the hour.

Syntax

<Integer> Minute(<DateTime>)
Month Returns a Variant (Integer) specifying a whole number between 1 and 12, inclusive, representing the month of the year.

Syntax

<Integer> Month(<DateTime>)
MonthName Returns a string indicating the specified month.

Syntax

<String> MonthName(<Integer>, <Logical Expression>)
Mtd A shortcut function for the PeriodsToDate function that specifies the level to be Month.

Syntax

<Set> Mtd()
<Set> Mtd(<Member>)
NOT Returns the negation of a condition.

Syntax

NOT <Logical Expression>
NPV Returns a Double specifying the net present value of an investment based on a series of periodic cash flows (payments and receipts) and a discount rate.

Syntax

<Numeric Expression> NPV(<Numeric Expression>, <Array>)
NPer Returns a Double specifying the number of periods for an annuity based on periodic, fixed payments and a fixed interest rate.

Syntax

<Numeric Expression> NPer(<Numeric Expression>, <Numeric Expression>, <Numeric Expression>, <Numeric Expression>, <Logical Expression>)
Name Returns the name of a dimension.

Syntax

<Dimension>.Name
Name Returns the name of a hierarchy.

Syntax

<Hierarchy>.Name
Name Returns the name of a level.

Syntax

<Level>.Name
Name Returns the name of a member.

Syntax

<Member>.Name
NextMember Returns the next member in the level that contains a specified member.

Syntax

<Member>.NextMember
NonEmptyCrossJoin Returns the cross product of two sets, excluding empty tuples and tuples without associated fact table data.

Syntax

<Set> NonEmptyCrossJoin(<Set>, <Set>)
Now Returns a Variant (Date) specifying the current date and time according your computer's system date and time.

Syntax

<DateTime> Now()
OR Returns the disjunction of two conditions.

Syntax

<Logical Expression> OR <Logical Expression>
Oct Returns a Variant (String) representing the octal value of a number.

Syntax

<String> Oct(<Value>)
OpeningPeriod Returns the first descendant of a member at a level.

Syntax

<Member> OpeningPeriod()
<Member> OpeningPeriod(<Level>)
<Member> OpeningPeriod(<Level>, <Member>)
Order Arranges members of a set, optionally preserving or breaking the hierarchy.

Syntax

<Set> Order(<Set>, <Value>, <Symbol>)
<Set> Order(<Set>, <Value>)
Ordinal Returns the zero-based ordinal value associated with a level.

Syntax

<Level>.Ordinal
PPmt Returns a Double specifying the principal payment for a given period of an annuity based on periodic, fixed payments and a fixed interest rate.

Syntax

<Numeric Expression> PPmt(<Numeric Expression>, <Numeric Expression>, <Numeric Expression>, <Numeric Expression>, <Numeric Expression>, <Logical Expression>)
PPmt Returns a Double specifying the principal payment for a given period of an annuity based on periodic, fixed payments and a fixed interest rate.

Syntax

<Numeric Expression> PPmt(<Numeric Expression>, <Numeric Expression>, <Numeric Expression>, <Numeric Expression>, <Numeric Expression>)
PPmt Returns a Double specifying the principal payment for a given period of an annuity based on periodic, fixed payments and a fixed interest rate.

Syntax

<Numeric Expression> PPmt(<Numeric Expression>, <Numeric Expression>, <Numeric Expression>, <Numeric Expression>)
PV Returns a Double specifying the present value of an annuity based on periodic, fixed payments to be paid in the future and a fixed interest rate.

Syntax

<Numeric Expression> PV(<Numeric Expression>, <Numeric Expression>, <Numeric Expression>, <Numeric Expression>, <Logical Expression>)
ParallelPeriod Returns a member from a prior period in the same relative position as a specified member.

Syntax

<Member> ParallelPeriod()
<Member> ParallelPeriod(<Level>)
<Member> ParallelPeriod(<Level>, <Numeric Expression>)
<Member> ParallelPeriod(<Level>, <Numeric Expression>, <Member>)
ParamRef Returns the current value of this parameter. If it is null, returns the default value.

Syntax

<Value> ParamRef(<String>)
Parameter Returns default value of parameter.

Syntax

<String> Parameter(<String>, <Symbol>, <String>, <String>)
<String> Parameter(<String>, <Symbol>, <String>)
<Numeric Expression> Parameter(<String>, <Symbol>, <Numeric Expression>, <String>)
<Numeric Expression> Parameter(<String>, <Symbol>, <Numeric Expression>)
<Member> Parameter(<String>, <Hierarchy>, <Member>, <String>)
<Member> Parameter(<String>, <Hierarchy>, <Member>)
Parent Returns the parent of a member.

Syntax

<Member>.Parent
Percentile Returns the value of the tuple that is at a given percentile of a set.

Syntax

<Numeric Expression> Percentile(<Set>, <Numeric Expression>, <Numeric Expression>)
PeriodsToDate Returns a set of periods (members) from a specified level starting with the first period and ending with a specified member.

Syntax

<Set> PeriodsToDate()
<Set> PeriodsToDate(<Level>)
<Set> PeriodsToDate(<Level>, <Member>)
Pi Returns the number 3.14159265358979, the mathematical constant pi, accurate to 15 digits.

Syntax

<Numeric Expression> Pi()
Pmt Returns a Double specifying the payment for an annuity based on periodic, fixed payments and a fixed interest rate.

Syntax

<Numeric Expression> Pmt(<Numeric Expression>, <Numeric Expression>, <Numeric Expression>, <Numeric Expression>, <Logical Expression>)
Power Returns the result of a number raised to a power.

Syntax

<Numeric Expression> Power(<Numeric Expression>, <Numeric Expression>)
PrevMember Returns the previous member in the level that contains a specified member.

Syntax

<Member>.PrevMember
Properties Returns the value of a member property.

Syntax

<Member>.Properties(<String Expression>)
Qtd A shortcut function for the PeriodsToDate function that specifies the level to be Quarter.

Syntax

<Set> Qtd()
<Set> Qtd(<Member>)
RTrim Returns a Variant (String) containing a copy of a specified string without trailing spaces.

Syntax

<String> RTrim(<String>)
Radians Converts degrees to radians.

Syntax

<Numeric Expression> Radians(<Numeric Expression>)
Rank Returns the one-based rank of a tuple in a set.

Syntax

<Integer> Rank(<Tuple>, <Set>)
<Integer> Rank(<Tuple>, <Set>, <Numeric Expression>)
<Integer> Rank(<Member>, <Set>)
<Integer> Rank(<Member>, <Set>, <Numeric Expression>)
Rate Returns a Double specifying the interest rate per period for an annuity.

Syntax

<Numeric Expression> Rate(<Numeric Expression>, <Numeric Expression>, <Numeric Expression>, <Numeric Expression>, <Logical Expression>, <Numeric Expression>)
Rate Returns a Double specifying the interest rate per period for an annuity.

Syntax

<Numeric Expression> Rate(<Numeric Expression>, <Numeric Expression>, <Numeric Expression>, <Numeric Expression>, <Logical Expression>)
Rate Returns a Double specifying the interest rate per period for an annuity.

Syntax

<Numeric Expression> Rate(<Numeric Expression>, <Numeric Expression>, <Numeric Expression>, <Numeric Expression>)
Rate Returns a Double specifying the interest rate per period for an annuity.

Syntax

<Numeric Expression> Rate(<Numeric Expression>, <Numeric Expression>, <Numeric Expression>)
Replace Returns a string in which a specified substring has been replaced with another substring a specified number of times.

Syntax

<String> Replace(<String>, <String>, <String>, <Integer>, <Integer>, <Integer>)
Replace Returns a string in which a specified substring has been replaced with another substring a specified number of times.

Syntax

<String> Replace(<String>, <String>, <String>, <Integer>, <Integer>)
Replace Returns a string in which a specified substring has been replaced with another substring a specified number of times.

Syntax

<String> Replace(<String>, <String>, <String>, <Integer>)
Replace

Syntax

<String> Replace(<String>, <String>, <String>)
Right Returns a Variant (String) containing a specified number of characters from the right side of a string.

Syntax

<String> Right(<String>, <Integer>)
Round Returns a number rounded to a specified number of decimal places.

Syntax

<Numeric Expression> Round(<Numeric Expression>, <Integer>)
Round Returns a number rounded to a specified number of decimal places.

Syntax

<Numeric Expression> Round(<Numeric Expression>)
SLN Returns a Double specifying the straight-line depreciation of an asset for a single period.

Syntax

<Numeric Expression> SLN(<Numeric Expression>, <Numeric Expression>, <Numeric Expression>)
SYD Returns a Double specifying the sum-of-years' digits depreciation of an asset for a specified period.

Syntax

<Numeric Expression> SYD(<Numeric Expression>, <Numeric Expression>, <Numeric Expression>, <Numeric Expression>)
Second Returns a Variant (Integer) specifying a whole number between 0 and 59, inclusive, representing the second of the minute.

Syntax

<Integer> Second(<DateTime>)
SetToStr Constructs a string from a set.

Syntax

<String> SetToStr(<Set>)
Sgn Returns a Variant (Integer) indicating the sign of a number.

Syntax

<Integer> Sgn(<Numeric Expression>)
Siblings Returns the siblings of a specified member, including the member itself.

Syntax

<Member>.Siblings
Sin Returns a Double specifying the sine of an angle.

Syntax

<Numeric Expression> Sin(<Numeric Expression>)
Sinh Returns the hyperbolic sine of a number.

Syntax

<Numeric Expression> Sinh(<Numeric Expression>)
Space Returns a Variant (String) consisting of the specified number of spaces.

Syntax

<String> Space(<Integer>)
Sqr Returns a Double specifying the square root of a number.

Syntax

<Numeric Expression> Sqr(<Numeric Expression>)
SqrtPi Returns the square root of (number * pi).

Syntax

<Numeric Expression> SqrtPi(<Numeric Expression>)
Stddev Alias for Stdev.

Syntax

<Numeric Expression> Stddev(<Set>)
<Numeric Expression> Stddev(<Set>, <Numeric Expression>)
StddevP Alias for StdevP.

Syntax

<Numeric Expression> StddevP(<Set>)
<Numeric Expression> StddevP(<Set>, <Numeric Expression>)
Stdev Returns the standard deviation of a numeric expression evaluated over a set (unbiased).

Syntax

<Numeric Expression> Stdev(<Set>)
<Numeric Expression> Stdev(<Set>, <Numeric Expression>)
StdevP Returns the standard deviation of a numeric expression evaluated over a set (biased).

Syntax

<Numeric Expression> StdevP(<Set>)
<Numeric Expression> StdevP(<Set>, <Numeric Expression>)
Str Returns a Variant (String) representation of a number.

Syntax

<String> Str(<Value>)
StrComp Returns a Variant (Integer) indicating the result of a string comparison.

Syntax

<Integer> StrComp(<String>, <String>, <Integer>)
StrComp Returns a Variant (Integer) indicating the result of a string comparison.

Syntax

<Integer> StrComp(<String>, <String>)
StrReverse Returns a string in which the character order of a specified string is reversed.

Syntax

<String> StrReverse(<String>)
StrToMember Returns a member from a unique name String in MDX format.

Syntax

<Member> StrToMember(<String>)
StrToSet Constructs a set from a string expression.

Syntax

<Set> StrToSet(<String>[, <Dimension>...])
StrToTuple Constructs a tuple from a string.

Syntax

<Tuple> StrToTuple(<String>)
String

Syntax

<String> String(<Integer>, <Integer>)
StripCalculatedMembers Removes calculated members from a set.

Syntax

<Set> StripCalculatedMembers(<Set>)
Subset Returns a subset of elements from a set.

Syntax

<Set> Subset(<Set>, <Numeric Expression>)
<Set> Subset(<Set>, <Numeric Expression>, <Numeric Expression>)
Sum Returns the sum of a numeric expression evaluated over a set.

Syntax

<Numeric Expression> Sum(<Set>)
<Numeric Expression> Sum(<Set>, <Numeric Expression>)
Tail Returns a subset from the end of a set.

Syntax

<Set> Tail(<Set>)
<Set> Tail(<Set>, <Numeric Expression>)
Tan Returns a Double specifying the tangent of an angle.

Syntax

<Numeric Expression> Tan(<Numeric Expression>)
Tanh Returns the hyperbolic tangent of a number.

Syntax

<Numeric Expression> Tanh(<Numeric Expression>)
ThirdQ Returns the 3rd quartile value of a numeric expression evaluated over a set.

Syntax

<Numeric Expression> ThirdQ(<Set>)
<Numeric Expression> ThirdQ(<Set>, <Numeric Expression>)
Time Returns a Variant (Date) indicating the current system time.

Syntax

<DateTime> Time()
TimeSerial Returns a Variant (Date) containing the time for a specific hour, minute, and second.

Syntax

<DateTime> TimeSerial(<Integer>, <Integer>, <Integer>)
TimeValue Returns a Variant (Date) containing the time.

Syntax

<DateTime> TimeValue(<DateTime>)
Timer Returns a Single representing the number of seconds elapsed since midnight.

Syntax

<Numeric Expression> Timer()
ToggleDrillState Toggles the drill state of members. This function is a combination of DrillupMember and DrilldownMember.

Syntax

<Set> ToggleDrillState(<Set>, <Set>)
<Set> ToggleDrillState(<Set>, <Set>, <Symbol>)
TopCount Returns a specified number of items from the top of a set, optionally ordering the set first.

Syntax

<Set> TopCount(<Set>, <Numeric Expression>, <Numeric Expression>)
<Set> TopCount(<Set>, <Numeric Expression>)
TopPercent Sorts a set and returns the top N elements whose cumulative total is at least a specified percentage.

Syntax

<Set> TopPercent(<Set>, <Numeric Expression>, <Numeric Expression>)
TopSum Sorts a set and returns the top N elements whose cumulative total is at least a specified value.

Syntax

<Set> TopSum(<Set>, <Numeric Expression>, <Numeric Expression>)
Trim Returns a Variant (String) containing a copy of a specified string without leading and trailing spaces.

Syntax

<String> Trim(<String>)
TupleToStr Constructs a string from a tuple.

Syntax

<String> TupleToStr(<Tuple>)
TypeName Returns a String that provides information about a variable.

Syntax

<String> TypeName(<Value>)
UCase Returns a string that has been converted to uppercase

Syntax

<String> UCase(<String>)
Union Returns the union of two sets, optionally retaining duplicates.

Syntax

<Set> Union(<Set>, <Set>)
<Set> Union(<Set>, <Set>, <Symbol>)
UniqueName Returns the unique name of a dimension.

Syntax

<Dimension>.UniqueName
UniqueName Returns the unique name of a hierarchy.

Syntax

<Hierarchy>.UniqueName
UniqueName Returns the unique name of a level.

Syntax

<Level>.UniqueName
UniqueName Returns the unique name of a member.

Syntax

<Member>.UniqueName
Unorder Removes any enforced ordering from a specified set.

Syntax

<Set> Unorder(<Set>)
Val Returns the numbers contained in a string as a numeric value of appropriate type.

Syntax

<Numeric Expression> Val(<String>)
ValidMeasure Returns a valid measure in a virtual cube by forcing inapplicable dimensions to their top level.

Syntax

<Numeric Expression> ValidMeasure(<Tuple>)
Value Returns the value of a measure.

Syntax

<Member>.Value
Var Returns the variance of a numeric expression evaluated over a set (unbiased).

Syntax

<Numeric Expression> Var(<Set>)
<Numeric Expression> Var(<Set>, <Numeric Expression>)
VarP Returns the variance of a numeric expression evaluated over a set (biased).

Syntax

<Numeric Expression> VarP(<Set>)
<Numeric Expression> VarP(<Set>, <Numeric Expression>)
Variance Alias for Var.

Syntax

<Numeric Expression> Variance(<Set>)
<Numeric Expression> Variance(<Set>, <Numeric Expression>)
VarianceP Alias for VarP.

Syntax

<Numeric Expression> VarianceP(<Set>)
<Numeric Expression> VarianceP(<Set>, <Numeric Expression>)
VisualTotals Dynamically totals child members specified in a set using a pattern for the total label in the result set.

Syntax

<Set> VisualTotals(<Set>)
<Set> VisualTotals(<Set>, <String>)
Weekday Returns a Variant (Integer) containing a whole number representing the day of the week.

Syntax

<Integer> Weekday(<DateTime>, <Integer>)
Weekday Returns a Variant (Integer) containing a whole number representing the day of the week.

Syntax

<Integer> Weekday(<DateTime>)
WeekdayName Returns a string indicating the specified day of the week.

Syntax

<String> WeekdayName(<Integer>, <Logical Expression>, <Integer>)
Wtd A shortcut function for the PeriodsToDate function that specifies the level to be Week.

Syntax

<Set> Wtd()
<Set> Wtd(<Member>)
XOR Returns whether two conditions are mutually exclusive.

Syntax

<Logical Expression> XOR <Logical Expression>
Year Returns a Variant (Integer) containing a whole number representing the year.

Syntax

<Integer> Year(<DateTime>)
Ytd A shortcut function for the PeriodsToDate function that specifies the level to be Year.

Syntax

<Set> Ytd()
<Set> Ytd(<Member>)
_CaseMatch Evaluates various expressions, and returns the corresponding expression for the first which matches a particular value.

Syntax

Case <Expression> When <Expression> Then <Expression> [...] [Else <Expression>] End
_CaseTest Evaluates various conditions, and returns the corresponding expression for the first which evaluates to true.

Syntax

Case When <Logical Expression> Then <Expression> [...] [Else <Expression>] End
{} Brace operator constructs a set.

Syntax

{<Member> [, <Member>...]}
|| Concatenates two strings.

Syntax

<String> || <String>


Author: Julian Hyde; last modified May 2011.
Version: $Id$ (log)
Copyright (C) 2005-2011 Pentaho