# InterSystems MDX Reference

- About This Book
- Basic Rules
- Identifiers – This section discusses identifiers in InterSystems MDX.
- Comments – This section discusses comments in InterSystems MDX queries and in stand-alone MDX expressions used within model definitions.
- Expression Types
- Numeric Expressions – This section describes how to create and use numeric expressions in InterSystems MDX.
- String Expressions – This section describes how to create and use string expressions in InterSystems MDX.
- Logical Expressions – This section describes how to create and use logical expressions in InterSystems MDX.
- Tuple Expressions – This section describes how to create and use tuple expressions in InterSystems MDX.
- Member Expressions – This section describes how to create and use member expressions in InterSystems MDX.
- Level Expressions – This section describes how to create and use level expressions in InterSystems MDX.
- Hierarchy Expressions – This section describes how to create and use hierarchy expressions in InterSystems MDX.
- Set Expressions – This section describes how to create and use set expressions in InterSystems MDX.
- Measure Search Expressions – This section describes how to create and use measure search expressions, which enable you to access rows from the fact table based on the value of a measure for the facts themselves (that is, at the lowest level rather than at an aggregate level). These expressions are an InterSystems extension to MDX.
- Quality Measure Expressions – This section describes how to create and use quality measure expressions, which provide access to the values of quality measures. These expressions are an InterSystems extension to MDX.
- MDX Statements and Clauses
- %FILTER Clause – Applies a filter to a SELECT statement; describes how to slice the results of a SELECT statement. This clause is similar to WHERE except that you can include multiple %FILTER clauses in a statement. %FILTER is an InterSystems extension to MDX.
- CREATE MEMBER Statement – Creates a calculated member that can be used within the current session.
- CREATE SET Statement – Creates a named set that can be used within the current session.
- DRILLFACTS Statement – Displays the lowest-level data associated with the first cell of results of a given SELECT statement, using the fact and dimension tables defined by the cube.
- DRILLTHROUGH Statement – Displays the lowest-level data associated with the first cell of results of a given SELECT statement.
- DROP MEMBER Statement – Removes a calculated member defined earlier in the current session.
- DROP SET Statement – Removes a named set defined earlier in the current session.
- FORMAT_STRING Clause – Used with a definition of a calculated member, this clause specifies the display format for the data.
- SELECT Statement – Executes a query and returns the results. This section describes the basic syntax.
- SET Statement – Creates a pivot variable for use in the current session, for the purpose of development. This statement is available only in the MDX shell.
- SOLVE_ORDER Clause – Used with a definition of a calculated member, this clause specifies the order in which to apply the definition of this calculated member relative to other calculated members. This clause is relevant only if the query contains calculated members on both axes.
- WHERE Clause – Applies a filter to a SELECT statement; describes how to slice the results of a SELECT statement.
- WITH Clause – Defines one or more calculated members, named sets, or parameters for use in the SELECT statement.
- MDX Functions
- %ALL – Enables you to use a member while ignoring any ROW and COLUMN context that uses the hierarchy to which this member belongs. This function is an InterSystems extension to MDX.
- %CELL – Returns the value of another cell in a pivot table, by position. This function is an InterSystems extension to MDX.
- %CELLZERO – Returns the value of another cell in a pivot table or returns zero if that cell has no value. This function is a Business Intelligence extension to MDX.
- %FIRST – Returns the value of the given measure (or other numeric expression) evaluated for the first non-empty member of a set. This function is an InterSystems extension to MDX.
- %KPI – Returns a value from a KPI or plug-in. This function is an InterSystems extension to MDX.
- %LABEL – Given an MDX expression, returns the same expression with a different label for use as a row or column header. %LABEL can also specify formatting for the row or column. This function is a Business Intelligence extension to MDX.
- %LAST – Returns the value of the given measure (or other numeric expression) evaluated for the last non-empty member of a set. This function is an InterSystems extension to MDX.
- %LIST – Returns a comma-separated list of values, given a set of values. This function is an InterSystems extension to MDX and is intended for use in KPIs.
- %LOOKUP – Returns one value from a term list. This function is an InterSystems extension to MDX.
- %MDX – Executes an MDX query outside of the context of the current query and then returns a single result. This function is a Business Intelligence extension to MDX.
- %NOT – Enables you to exclude a single member of a given level. This function is an InterSystems extension to MDX.
- %OR – Enables you to combine multiple members into a single member, for efficiency and to avoid double-counting. This function is an InterSystems extension to MDX.
- %SEARCH – Returns a measure search expression that you can use with the WHERE and %FILTER clauses.
- %SPACE – Inserts a blank row or column with no label. This function is an InterSystems extension to MDX.
- %TERMLIST – Enables you to create a set of members based on a term list. When used with the %OR function, %TERMLIST is particularly useful for filtering. This function is an InterSystems extension to MDX.
- %TIMERANGE – Enables you to define a range of time members, possibly open-ended. This function is an InterSystems extension to MDX.
- %TIMEWINDOW – Returns a set of members of a time dimension that match the given range template.
- %TOPMEMBERS – Returns a set of all members of the first level in the given hierarchy. Or, given a level, it returns a set of all the members of that level. This function is an InterSystems extension to MDX.
- AGGREGATE – Returns the aggregate value for a given measure (or of the current measure), across all elements of a set, according to the aggregation logic of the measure.
- ALLMEMBERS – Returns a set of all members of the given level or hierarchy. Or returns a set of all members of the first hierarchy of a dimension. In either case, any calculated members are also returned.
- ANCESTOR – Returns the ancestor of the given member, within the given level.
- AVG – Returns the average value of a given expression (or of the current measure), across all elements of a set that have a non-null value for that expression.
- BOTTOMCOUNT – Sorts a set and returns a subset from its lower-valued end, given a desired element count.
- BOTTOMPERCENT – Sorts a set and returns a subset from its lower-valued end, given a cutoff percentage that is applied to a total across members.
- BOTTOMSUM – Sorts a set and returns a subset from its lower-valued end, given a cutoff value that is applied to a total across elements.
- CHILDREN – Returns a set that contains the children, if any, of a specified member.
- CLOSINGPERIOD – Returns the last descendent member of the given level, at the same level as the given member. This function is intended primarily for use with time levels.
- COUNT – Returns the count of elements in the given set.
- COUSIN – Given a reference member and a member of a higher level in the same hierarchy, this function finds the ancestor of the reference member at that higher level, determines the relative position of the reference member to that ancestor, and then returns the descendent of the higher member that has the same relative position. This function is intended primarily for use with time levels.
- CROSSJOIN – Returns a set of tuples formed by the cross-product of the specified sets.
- CURRENTMEMBER – Enables you to refer to a member programmatically within an iteration through the members of a hierarchy.
- DESCENDANTS – Returns the members that are the descendants of a given member, within the specified level or levels.
- DISTINCT – Examines a set, removes duplicate elements, and returns a set of the remaining elements.
- EXCEPT – Examines two sets and returns a set that consists of the elements of the first set, except for any elements that are also in the second set. This function optionally eliminates duplicates in that set.
- FILTER – Examines a set and returns the subset in which the given expression is true for each element. The set order is unchanged.
- FIRSTCHILD – Returns the first child of the given member.
- FIRSTSIBLING – Returns the first sibling of the given member.
- HEAD – Returns a subset from the start of a set, using the current order of the set.
- HIERARCHISE – Synonym for HIERARCHIZE.
- HIERARCHIZE – Given a set, returns a set that is in hierarchical order (the order specified by the hierarchy).
- IIF – Returns one of two values, depending on the value of a given logical expression.
- INTERSECT – Returns a set that consists of the elements that occur in both of the two given sets, optionally eliminating duplicates in that set.
- ISNULL – Evaluates a scalar MDX expression and returns either its value or an alternative value (if the value of the expression is null). This function is an InterSystems extension to MDX.
- LAG – Given a level member and a nonnegative integer, this function counts backward in the level and returns a previous member. The details are different for time dimensions and data dimensions.
- LASTCHILD – Returns the last child of the given member.
- LASTSIBLING – Returns the last sibling of the given member.
- LEAD – Given a level member and a nonnegative integer, this function counts forward in the level and returns a later member. The details are different for time dimensions and data dimensions.
- LOG – Returns the base-ten logarithm of the given numeric value.
- LOOKUP – Looks up a given key in a term list and returns a substitute string. This function enables you to perform string replacements within a query. This function is an InterSystems extension to MDX.
- MAX – Returns the maximum value of a given expression (or of the current measure), across all elements of a set.
- MEDIAN – Returns the value closest to the median value, for a given expression (or of the current measure), across all elements of a set that have a non-null value for that expression.
- MEMBERS – Returns a set of all members of the given level or hierarchy, not including any calculated members.
- MIN – Returns the minimum non-null value of a given expression (or of the current measure), across all elements of a set.
- NEXTMEMBER – Returns the next member of the level to which the given member belongs. The details are different for time dimensions and data dimensions.
- NONEMPTYCROSSJOIN – Returns a set that consists of the cross-product of the given sets, excluding any tuples that are null.
- OPENINGPERIOD – Returns the first descendent member of the given level, at the same level as the given member. This function is intended primarily for use with time levels.
- ORDER – Returns a set that is ordered as specified.
- PARALLELPERIOD – Given a reference member, a parent level of that member, and an integer, this function counts backward in the parent level, finds a previous member in that level, and then returns its child that has the same position as the reference member.
- PARENT – Returns the member that is the parent of the given member.
- PERCENTILE – Evaluates a given expression (or the current measure), across all elements of a set, and returns the value that is at a given percentile level.
- PERCENTILERANK – For a given numeric value, this function evaluates a given expression (or the current measure), across all elements of a set, and returns the percentile rank of that expression — the percentage of values that are the same or lower.
- PERIODSTODATE – Returns the set of child or descendent members of the given level, up to and including the given member. This function is intended primarily for use with time levels.
- POWER – Returns the given numeric value raised to the power of the second argument.
- PREVMEMBER – Returns the previous member of the level to which the given member belongs. The details are different for time dimensions and data dimensions.
- PROPERTIES – Returns the value of the given property, for the given member.
- RANK – Returns an integer that indicates the rank of the given member, within the given set.
- ROUND – Evaluates a numeric MDX expression and returns a rounded value. This function is an InterSystems extension to MDX.
- SIBLINGS – Returns a set that contains the specified member and all its siblings.
- SQRT – Returns the square root of the given numeric value.
- STDDEV – Returns the standard deviation of a given expression (or of the current measure), across all elements of a set.
- STDDEVP – Returns the population standard deviation of a given expression, across all elements of a set.
- STDEV – Synonym for STDDEV.
- STDEVP – Synonym for STDDEVP.
- SUBSET – Returns a set of elements from a given set, by position. The first member is at position 0.
- SUM – Returns the sum of a given expression (or of the current measure), across all elements of a set.
- TAIL – Returns a subset from the end of a set, using the current order of the set.
- TOPCOUNT – Sorts a set and returns a subset from its higher-valued end, given a desired element count.
- TOPPERCENT – Sorts a set and returns a subset from its higher-valued end, given a cutoff percentage that is applied to a total across set elements.
- TOPSUM – Sorts a set and returns a subset from its higher-valued end, given a cutoff value that is applied to a total across elements.
- UNION – Returns a set that consists of the elements of the two given sets, optionally eliminating duplicates.
- VAR – Returns the variance of a given expression (or of the current measure), across all elements of a set.
- VARIANCE – Synonym for VAR.
- VARIANCEP – Synonym for VARP.
- VARP – Returns the population variance of a given expression, across all elements of a set.
- VISUALTOTALS – Given a set of members in hierarchical order, returns that set with its visual totals. In the visual totals, the actual value for any higher-level member is replaced with the sum of the values for the children that are included in the query.
- Intrinsic Properties
- Intrinsic Properties – This section lists the intrinsic properties for levels in cubes.
- Key Values – This section describes how the system generates KEY values for level members.
- NOW Member for Time Levels
- NOW Member – This section provides information on the NOW member for date/time levels. This syntax is an InterSystems extension to MDX.
- Quick Function Reference