DeepSee MDX Reference
Contents
Server:
docs2
Instance:
LATEST
User:
UnknownUser
[
Home
] >
[
InterSystems DeepSee
] >
[
DeepSee MDX Reference
]
Go to:
About This Book
Basic Rules
Expression Types
MDX Statements and Clauses
MDX Functions
Intrinsic Properties
NOW Member for Time Levels
Quick Function Reference
Identifiers
Comments
Numeric Expressions
String Expressions
Logical Expressions
Tuple Expressions
Member Expressions
Level Expressions
Hierarchy Expressions
Set Expressions
Measure Search Expressions
Quality Measure Expressions
%FILTER Clause
CREATE MEMBER Statement
CREATE SET Statement
DRILLFACTS Statement
DRILLTHROUGH Statement
DROP MEMBER Statement
DROP SET Statement
FORMAT_STRING Clause
SELECT Statement
SET Statement
SOLVE_ORDER Clause
WHERE Clause
WITH Clause
%ALL
%CELL
%CELLZERO
%FIRST
%KPI
%LABEL
%LAST
%LIST
%LOOKUP
%MDX
%NOT
%OR
%SEARCH
%SPACE
%TERMLIST
%TIMERANGE
%TIMEWINDOW
%TOPMEMBERS
AGGREGATE
ALLMEMBERS
ANCESTOR
AVG
BOTTOMCOUNT
BOTTOMPERCENT
BOTTOMSUM
CHILDREN
CLOSINGPERIOD
COUNT
COUSIN
CROSSJOIN
CURRENTMEMBER
DESCENDANTS
DISTINCT
EXCEPT
FILTER
FIRSTCHILD
FIRSTSIBLING
HEAD
HIERARCHISE
HIERARCHIZE
IIF
INTERSECT
ISNULL
LAG
LASTCHILD
LASTSIBLING
LEAD
LOG
LOOKUP
MAX
MEDIAN
MEMBERS
MIN
NEXTMEMBER
NONEMPTYCROSSJOIN
OPENINGPERIOD
ORDER
PARALLELPERIOD
PARENT
PERCENTILE
PERCENTILERANK
PERIODSTODATE
POWER
PREVMEMBER
PROPERTIES
RANK
ROUND
SIBLINGS
SQRT
STDDEV
STDDEVP
STDEV
STDEVP
SUBSET
SUM
TAIL
TOPCOUNT
TOPPERCENT
TOPSUM
UNION
VAR
VARIANCE
VARIANCEP
VARP
VISUALTOTALS
Intrinsic Properties
Key Values
NOW Member
Search
:
Contents
Preface :
About This Book
Reference A:
Basic Rules
Identifiers
— This section discusses identifiers in DeepSee MDX.
Comments
— This section discusses comments in DeepSee MDX queries and in stand-alone MDX expressions used within model definitions.
Reference B:
Expression Types
Numeric Expressions
— This section describes how to create and use numeric expressions in DeepSee MDX.
String Expressions
— This section describes how to create and use string expressions in DeepSee MDX.
Logical Expressions
— This section describes how to create and use logical expressions in DeepSee MDX.
Tuple Expressions
— This section describes how to create and use tuple expressions in DeepSee MDX.
Member Expressions
— This section describes how to create and use member expressions in DeepSee MDX.
Level Expressions
— This section describes how to create and use level expressions in DeepSee MDX.
Hierarchy Expressions
— This section describes how to create and use hierarchy expressions in DeepSee MDX.
Set Expressions
— This section describes how to create and use set expressions in DeepSee 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 a DeepSee 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 a DeepSee extension to MDX.
Reference C:
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 a DeepSee 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.
Reference D:
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 a DeepSee extension to MDX.
%CELL
— Returns the value of another cell in a pivot table, by position. This function is a DeepSee 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 DeepSee 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 a DeepSee extension to MDX.
%KPI
— Returns a value from a KPI or plugin. This function is a DeepSee 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 DeepSee 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 a DeepSee extension to MDX.
%LIST
— Returns a comma-separated list of values, given a set of values. This function is a DeepSee extension to MDX and is intended for use in KPIs.
%LOOKUP
— Returns one value from a term list. This function is a DeepSee 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 DeepSee extension to MDX.
%NOT
— Enables you to exclude a single member of a given level. This function is a DeepSee extension to MDX.
%OR
— Enables you to combine multiple members into a single member, for efficiency and to avoid double-counting. This function is a DeepSee 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 a DeepSee 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 a DeepSee extension to MDX.
%TIMERANGE
— Enables you to define a range of time members, possibly open-ended. This function is a DeepSee 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 a DeepSee 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 a DeepSee 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 a DeepSee 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 a DeepSee 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.
Reference E:
Intrinsic Properties
Intrinsic Properties
— This section lists the intrinsic properties for levels in DeepSee cubes.
Key Values
— This section describes how DeepSee generates KEY values for level members.
Reference F:
NOW Member for Time Levels
NOW Member
— This section provides information on the NOW member for date/time levels. This syntax is a DeepSee extension to MDX.
Appendix A:
Quick Function Reference
Copyright
© 1997-2017, InterSystems Corp.
[Home]
[Top of Page]
Build:
Caché v2017.1.1 (111)
Last updated:
2017-05-17 15:19:32
Source:
D2RMDX.xml