MDX Functions
The below example shows that there is often a correlation between a version of a “standard” MDX syntax and the Cognos 8 syntax. Report Studio also provides extensions and added capabilities to MDX that simplify complex calculations and aggregations.
| Standard MDX | Cognos MDX |
| union( [Golf Equipment].Children, [Camping Equipment].Children ) | union( children( [Golf Equipment] ), children( [Camping Equipment] ) ) |
| OR | |
| { [Golf Equipment].Children, [Camping Equipment].Children } | |
| OR | |
| { [Golf Equipment].Children } + {[Camping Equipment].Children } | |
MDX functions can be broken down into five categories:
1. Member functions – used for retrieving specific members and their properties.
2. Set functions – working with sets of members.
3. Level and hierarchy functions – information on dimension and hierarchies in them.
4. Member summary functions – total, aggregate, percentage values on a set or report.
5. Logic functions – if, contains, is null, etc.
* Report Studio generates the MDX query for the report writer as they drag data items into a list or crosstab. This section introduces key MDX concepts by examining how MDX is utilized by Report Studio through its graphical interface.
Tuple : function is used whenever a report writer needs to explicitly retrieve a value. The syntax for tuple() from the Report Studio tip is:
tuple ( member { , member } )
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.
MDX Member : Report Studio uses the MDX members() function to return all [Product line] members when producing the underlying MDX query for the list. The members() function will return the set of members in the [Product line] level of the products dimension. The definition of the members function in Report Studio is:
members ( hierarchy | level )
This 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.
“Returns the set of members” is easy to visualize using Report Studio. Examining the “Insertable Objects” pane source tab (Figure 3), the [Product line] level of the [Products] dimension will show the members of the [Product line] level, [Camping Equipment] through [Personal Accessories].
Sets :Similar to the members() function, Report Studio uses the MDX set() function to create a set of members when specific members are dragged onto a report layout. Using a crosstab (to avoid nesting in a list), [Camping Equipment] and [Outdoor Protection] could be placed into the rows of a crosstab and [Revenue] to the measures.
set([Camping Equipment], [Outdoor Protection]) instead of dragging individual members.
Sets are the key to driving many reports, including creating custom aggregations over
sets of data, or choosing what data to display.
