Page Comparison

What is a calculated member and when to use calculated members

This tutorial explains how to define new calculated members and measures using MDX expressions (or formulas). If those words do not make sense to you, we advise getting to know the main flex.bi concepts and learn how to create reports and then come back to this section again.

What is MDX?

MultiDimensional eXpressions (MDX) is a query language for querying and manipulating the multidimensional data stored in OLAP (online analytical processing) data cubes. MDX syntax is similar SQL syntax or to spreadsheet formulas. Below is an example of a very simple MDX query:

...

MDX implementations vary, and many MDX documentation resources are specific to certain niche products or standards.

MDX Used in flex.bi

Calculated members and calculated measures can be used to add business logic to a report and a data cube. Most common use cases for calculated members and measures are to group particular items of interest, dynamically change time according to the current date or selected period and, most importantly, derive new measurements from existing data. Calculated members and measures are defined using the MDX (multidimensional expressions) query language which is the main query language implemented by Mondrian (https://mondrian.pentaho.com/documentation/mdx.php)

What you should know

Mastering how to write MDX expressions is initially quite difficult. Don't worry, you will get along quite well if you will follow some ground rules. MDX is a language and to use it for calculations it is enough to understand some base words (data types and functions) and how to use a dictionary (list of available functions).

...

[Time].[2019].[Q2 2019].[May 2019]

In MDX query language, all names of measures, dimensions, dimension hierarchies, hierarchy levels, and dimension members are enclosed in square brackets []. To reference a dimension, you just enclose its name in square brackets, e.g., [Customers], [Measures], [Time]. To reference a particular member, enclose each name of the path in square brackets, e.g., [Customers].[USA].[CA], [Time.Weekly].[2019].[W31, Jul 29 2019] or [Measures].[Store Sales].

• If a dimension has just one hierarchy, it automatically is also the primary hierarchy. And you can refer to the primary hierarchy in the same way as a dimension by mentioning just a dimension name, e.g. [Customers] or [Measures].
• If a dimension has two or more hierarchies (like Time dimension), then you should refer dimension name and hierarchy name. For example, a reference to  Time dimension Weekly hierarchy is [Time.Weekly]. In Time dimension primary hierarchy (year, quarter, month day) does not have a specific name, and you can refer to it by mentioning just a dimension name [Time].

Each dimension typically will have a default All member which can be used to get totals of measures by this dimension. If Customers dimension has a default All member named All Customers, then you can reference it with by its name [Customers].[All Customers] or [Customers].CurrentHierarchy.DefaultMember to get the same result. In Measures, all measures are at the top level, and you can reference them with as [Measures].[Store Sales], [Measures].[Store Cost] etc.

How to define a new calculated member

...

Dimension members have some default properties like .Name and .Key as well as they can have additional custom properties. flex.bi source application import (e.g. from HansaWorld, Tilde or Jira) is also importing additional dimension fields from source systems. Note that a property holds information about a particular member and could be anything that describes that member details. 

For example, a User dimension member can be a person (user) and the property of each user can be an address (string), age (number), date of birth (date). In reports, properties are displayed only at a particular user level.

To access and retrieve property values, we recommend using MDX function get(). This function will return property value if a member has such property or an empty result if no property is defined for the current dimension level.

For example, Jira import adds the Invoice date the property for all imported Invoices. The following expression returns the Invoice date property value for the Invoice dimension current member:

...

-- annotations.drill_through_non_empty=false 
During drill through issue the calculation will be executed over all issues imported in the account ignoring any report context. It could slow down the drill through issues output significantly!

-- annotations.copy_as_user_defined=true
Enable the option to edit template measures in the recipient account. Changes made in those measures won't be overwritten during data imports.

Formatting 

When creating a new calculated measure you can choose the format for the output of results. 

...

Shortcuts

You can use the following keyboard shortcuts in the formula editor:

...