Skip to end of metadata
Go to start of metadata

If you have not yet read them then please start with the main eazyBI concepts and create reports tutorial. This tutorial will explain how to define new calculated members using MDX calculation formulas.

Each cube dimension contains one or more hierarchies with one or more hierarchy levels. Each hierarchy level contains dimension members. There are stored members that imported from source files or applications. Additionally, you can define calculated members using calculation formulas. The calculations will run during each report execution and be available alongside the stored members. Calculation formulas are defined using the MDX query language.

There is a special dimension Measures which contains all imported measures and properties. Calculated members defined in the Measures dimension typically are called calculated measures.

Please watch demo videos in this documentation page to see in action typical definitions and usage of calculated members.

Define new calculated member

If you would like to define new calculated member then expand corresponding dimension (e.g. Measures) and expand Calculated members section:

On this page:

If you would like to define a new calculated member then click on the Define new link. If you would like to edit (or delete) an existing calculated member then click on the edit link. If you do not have rights to edit calculated members in the current account then you will see just the show link which will show the definition of an existing calculated member. When you define a new calculated member or edit an existing calculated member you will see the following calculated member definition form:

Each calculated member should have a name (unique within dimension) and a calculation formula (the rest of the tutorial will teach how to write calculation formulas). In addition, you can specify how calculated member value should be formatted (e.g. as an integer, decimal, date or using default formatting). From the right sidebar you can quickly select other members, dimensions, operators or functions to insert them in the calculation formula.

After defining a calculated member formula press Update to save it or Delete to delete an existing calculated member or Cancel to discard any changes. If a calculated member formula will be invalid then a corresponding error message will be displayed.

Do not modify or delete pre-defined calculated members that were created by source application import – their original definition will be recreated next time when you will perform the source application import. If you need to create a modified version of an existing pre-defined calculated member then copy its calculation formula and create a new calculated member with a different name and modify copied calculation formula for the new calculated member.

Let’s start writing some calculation formulas! Following examples will use Sales demo cube dimensions and measures to illustrate creation of calculation formulas.

Dimension, hierarchy, level and member names

When writing calculation formulas you will need to reference dimensions, dimension hierarchies, hierarchy levels and other existing dimension members. In MDX query language all names are enclosed in square brackets [].

To reference a dimension you just enclose its name in square brackets, e.g. [Customers][Time] or [Measures].

If dimension has just one hierarchy then you can reference the primary hierarchy in the same way as dimension, e.g. [Customers] or [Measures]. When you import Time dimension then it automatically will create a main hierarchy (with Year, Quarter, Month and Day levels) as well as a Weekly hierarchy (with Year, Week and Day levels). [Time] will reference Time dimension main hierarchy but [Time.Weekly] will reference Time dimension Weekly hierarchy.

When you expand specific dimension and expand All hierarchy level members then you see the names of all main hierarchy levels. You can reference particular hierarchy level with [dimension or hierarchy name].[level name]. E.g. [Customers].[City] references City level of the main hierarchy of the Customers dimension.

In case of the Time dimension use [Time].[Year][Time].[Quarter][Time].[Month][Time].[Day] to reference the main hierarchy levels and [Time.Weekly].[Year][Time.Weekly].[Week][Time.Weekly].[Day] to reference the Weekly hierarchy levels.

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 [Customers].[All Customers]. As default All member can be renamed it is safer to use [Customers].DefaultMember to get the same result.

In case of the Time dimension there are two default All members for each hierarchy – [Time].DefaultMember and [Time.Weekly].DefaultMember. In calculation formulas you can use expression [Time].CurrentHierarchy.DefaultMember to get either [Time].DefaultMember or [Time.Weekly].DefaultMember depending on which Time hierarchy is used in a report.

When you want to reference top level dimension members then you use [dimension or hierarchy name].[member name]. E.g. [Customers].[USA] will reference USA member from the top level Country level. To reference detailed level dimension members you need to specify full “hierarchy path” to this member, e.g. [Customers].[USA].[CA] to reference a CA member in the State Province level under a USA parent member. Or use [Customers].[USA].[CA].[San Francisco] to reference a city and so on.

All measures are in the top level of Measures dimension and you can reference them with as [Measures].[Store Sales][Measures].[Store Cost] etc.

Simple arithmetic calculations

 

Now that you know how to reference other existing measures and other dimension members we can create a simple arithmetic calculations.

If we have [Measures].[Store Sales] and [Measures].[Store Cost] measures then we can define a new calculated measure Profit (which will have full name [Measures].[Profit]) with a formula:

[Measures].[Store Sales] - [Measures].[Store Cost]

When you will use this new measure Profit in your eazyBI reports then for any combination with other dimension values this formula will calculate a difference between a Store Sales measure value and a Store Cost measure value.

You can use defined calculated measures also in other calculated measures that you define later. For example, you can now define [Measures].[Margin %] with a formula:

[Measures].[Profit] / [Measures].[Store Sales]

and it will calculate margin as a number from 0 to 1. If you would like to display results as a percentage value then change Formatting of this calculated measure to use an integer or decimal percentage formatting.

You can perform arithmetic calculations also for calculated members in the other dimension. For example, you can define a [Customers].[West coast] calculated member in the Customers dimension with a formula:

[Customers].[USA].[CA] + [Customers].[USA].[OR] + [Customers].[USA].[WA]

and now when you will combine in reports [Customers].[West coast] with [Measures].[Store Sales] you will get a total sales for all these three states together. If you will combine it with [Measures].[Profit] you will get a total profit for all these three states.

There is a default hidden [Measures].[Fact Count] measure which will return number of fact rows in a cube database table. You can use it to calculate simple average values, for example, define [Measures].[Average Store Sales] as

[Measures].[Store Sales] / [Measures].[Fact Count]

Tuples

 

When you use [Measures].[Store Sales] in a formula then it will calculate Store Sales measure value for the current context of each report cell. Current context includes corresponding row and column dimension members of the report cell as well as selected page dimension members.

If you would like to override in a measure value calculation some current context dimension member with a different member then you need to use a tuple of this measure and other dimension member (or several dimension members). MDX syntax for tuples is (member_1, member_2, ..., member_n). For example, if you would like to get Store Sales measure value for all customers then in a formula you should use a tuple:

( [Measures].[Store Sales], [Customers].DefaultMember )

This formula means that Store Sales should be calculated using all dimension members from the current context except Customers dimension for which default member (All Customers) should be used.

Tuples are frequently used to calculate percentage of a measure value from some total value. Let’s define a calculated measure [Measures].[Sales / customers total %] with a formula (and percentage formatting)

[Measures].[Store Sales] / ([Measures].[Store Sales], [Customers].DefaultMember)

When in report you will combine [Measures].[Sales / customers total %] with, for example, [Customers].[USA].[CA] then you will see percentage of California sales from the total customer sales.

Maybe you don’t want to see a percentage from the total sales but a percentage from customer hierarchy direct parent sales (e.g. for City level show percentage from corresponding State sales). In this case you can use the CurrentMember dimension or hierarchy property to access the current context dimension member. For example, [Customers].CurrentMember will return the current member in the Customers dimension for which the formula is evaluated. Or in other words if Customers dimension is placed on report rows then by using [Customers].CurrentMember we know for which row the formula is evaluated.

If we have the current member then we can navigate to other dimension members relative from this member. [Customers].CurrentMember.Parent will return parent member in Customers hierarchy for the current member. Ancestor([Customers].CurrentMember, [Customers].[Country]) will move from current member up to “ancestor” in Country level.

So we can define a calculated measure [Measures].[Sales / parent customer %] with a formula:

[Measures].[Store Sales] / ([Measures].[Store Sales], [Customers].CurrentMember.Parent)

Please see also DefaultContext function documentation which can be used to override the context for evaluation of measures.

Moving in time

As you can move to a different dimension hierarchy levels you can also move to a different dimension members in the same dimension. It is typically used in the Time dimension when you want to compare measures between different time periods or aggregate a time period range.

For example, a calculated measure [Measures].[Sales monthly growth] with a formula:

[Measures].[Store Sales] - ([Measures].[Store Sales], [Time].CurrentMember.PrevMember)

will calculate a [Measures].[Store Sales] growth comparing to the previous Time dimension member period. If this calculated measure will be combined in a report with a month in the Time dimension then it will show a growth comparing to the previous month. If it will be combined with a year then it will show the growth comparing to the previous year.

There are several functions that will help you “to move in time”:

  • [Time].CurrentMember.PrevMember returns the previous member, which contains data, in the same hierarchy level (it will return an empty member for the first member)
  • [Time].CurrentMember.NextMember will return the next member
  • [Time].CurrentMember.Lag(2) will return the previous member with a distance 2 (use any number for the argument, Lag(1) is the same as PrevMember)
  • [Time].CurrentMember.Lead(2) will return the next member with a distance 2 (Lead(1) is the same as NextMember)
  • ParallelPeriod([Time].[Year], 1, [Time].CurrentMember) will return the “parallel” Time member one year ago (e.g. for the Day level member Jan 01 2012 it will be the day Jan 01 2011 but for the Month level member Jan 2012 it will be the month Jan 2011)
  • OpeningPeriod([Time].[Day], [Time].CurrentMember) will return the first descendant of the current Time member at the Day level (the first day of a year, a quarter or a month which is defined in the Time dimension)
  • ClosingPeriod([Time].[Day], [Time].CurrentMember) will return the last descendant of the current Time member at the Day level (the last day of a year, a quarter or a month which is defined in the Time dimension)

If cube has only one Time dimension then the [Time].CurrentMember argument can be omitted as it will be used by default. So you can use shorter expressions ParallelPeriod([Time].[Year], 1)OpeningPeriod([Time].[Day])ClosingPeriod([Time].[Day]).

Sometimes you would like to test if your time navigation expression is working as you expect (before using it in further calculations). Then you can use .Name member property to get a member name as a calculation formula result. For example, define calculated measure [Measures].[test opening day] with a formula:

OpeningPeriod([Time].[Day], [Time].CurrentMember).Name

and use it in report together with the Time dimension members and see if you get the expected result for the Time dimension members at different levels.

Sets

Now you know how to navigate to individual dimension members. But quite often you would like to perform operations on a set of dimension members. For example, you would like to get a sum or an average value of some measure over a selected set of dimension members.

The easiest way how to use sets in calculation formulas is to use list of members enclosed in curly braces, e.g.

{[Customers].[USA].[CA], [Customers].[USA].[OR], [Customers].[USA].[WA]} {[Time].[2011], [Time].[2012]}

If you would like to select a set as a range of sequential dimension level members then you can specify the first and the last member and use : between them. For example, this will create a set of dates from Jan 01 2012 to Jan 15 2012:

[Time].[2012].[Q1 2012].[Jan 2012].[Jan 01 2012]:[Time].[2012].[Q1 2012].[Jan 2012].[Jan 15 2012]

Quite often you don’t want to specify the exact range of members but would like to get all dimension hierarchy level members. You can do it with Members method. This will get all months in the Time dimension:

[Time].[Month].Members

When you want to test which members will be returned by some set expression then you can use the SetToStr function to create a string of concatenated member full names. For example, define a calculated measure [Measures].[test all months] with a formula:

SetToStr([Time].[Month].Members)

There are several other useful functions for working with sets. Here are some examples how to use them:

  • [Customers].[USA].[CA].Children returns a set of children members using a dimension hierarchy (in this example all cities in California)
  • Descendants([Customers].[USA], [Customers].[City]) returns set of member descendants at the specified hierarchy level (in this example all cities in USA)
  • [Customers].[USA].[CA].[San Francisco].Siblings returns all members which have the same parent as this member (in this example all cities in California), it is the same as using [Customers].[USA].[CA].[San Francisco].Parent.Children

There are additional methods that you can use to get just the first or the last member of these sets – FirstChildLastChildFirstSiblingLastSibling.

Sets of time periods

There are additional useful set selection methods for Time periods

  • PeriodsToDate([Time].[Year], [Time].CurrentMember) returns all periods from the beginning of the current member in the specified level (in this example from the beginning of the current year) until the specified member.
  • YTD([Time].CurrentMember) is a shorter version of the same function (an abbreviation from Year-to-date).
    Note that even though Weekly hierarchy also has Year level, this function will only work in default Time hierarchy. For weekly hierarchy use the PeriodsToDate([Time.Weekly].[Year]).
  • QTD([Time].CurrentMember) returns a quarter-to-date set of members
  • MTD([Time].CurrentMember) returns a month-to-date set of members
  • WTD([Time.Weekly].CurrentMember) returns a week-to-date set of members

As mentioned earlier If you have just one Time dimension in your cube then you can use even shorter expressions YTD()QTD(), MTD() and WTD() as by default they will receive as an argument the current member of the time dimension – [Time].CurrentMember. But sometimes you need to pass a different argument to these functions. For example, if you would like to compare current year-to-date aggregates with year-to-date aggregates a year ago then you can use the following expression to get a year-to-date set for the corresponding Time dimension member a year ago:

YTD(ParallelPeriod([Time].[Year], 1, [Time].CurrentMember))

And if you like shorter expression then you can omit the current member argument here as well – YTD(ParallelPeriod([Time].[Year], 1)). Most of the time related functions will use [Time].CurrentMember as a default member argument.

If you want to get a number of last periods from the Time dimension then you can use the LastPeriods function. For example:

LastPeriods(3, [Time].CurrentMember)

will return a set with the current Time member and the two previous members. If you would like to get the current and the next two Time periods then use negative value -3 instead of 3. And you can omit the last argument for this function as well and use LastPeriods(3).

When you want to get the current Time dimension member in the Weekly hierarchy then you need to use [Time.Weekly].CurrentMember member expression. Sometimes you need to write a formula which should use either [Time].CurrentMember (when creating e.g. monthly report) or [Time.Weekly].CurrentMember (when creating weekly report). In this case you can use

[Time].CurrentHierarchyMember

which will return either the Time main hierarchy or the Weekly hierarchy current member depending on which Time hierarchy you use in your report.

Set operations

There are several operations that you can perform on sets:

  • {set1, set2, ..., setn} returns a union of two or more sets
  • Except(set1, set2) returns set1 members but removes any members that are in set2
  • Head(set, number) returns a set with the first number of members from the original set (if number is not specified then a set from the first set member is returned)
  • Tail(set, number) returns the last number of members from a set
  • set.Item(position) returns one member from a set with the specified position (starting from zero). So if you would like to get the first member of a set you can use the expression Head(set).Item(0)

Quite frequently you would like to filter set members using some condition. You can do this with Filter(set, condition). For example, in this way you can filter all cities with sales larger than 1000:

Filter([Customers].[City].Members, [Measures].[Store Sales] > 1000)

Within the condition expression [Customers].CurrentMember references the current set member for which the condition is evaluated. For example, this will return all cities which name starts with San (using MATCHES operator with regular expression)

Filter([Customers].[City].Members, [Customers].CurrentMember.Name MATCHES 'San .*')

Other typical function that is used in conditions is IsEmpty. This expression will return all cities which have non-empty sales amount:

Filter(
  [Customers].[City].Members,
  NOT IsEmpty([Measures].[Store Sales])
)

Previously a simple set to string function SetToStr was mentioned that is useful for expression testing purposes. But if you would like to format set results in a customized way then you can use Generate(set, string_expression, separator_string). For example, the following expression will return city names concatenated using comma where there is no sales amount recorded:

Generate(
  Filter(
    [Customers].[City].Members,
    IsEmpty([Measures].[Store Sales])
  ),
  [Customers].CurrentMember.Name,
  ', '
)

Aggregates

Now that you know how to select different sets of dimension members you can calculate different aggregated values from these sets:

  • Sum(set, numeric_expression) calculates numeric_expression for each set member and returns a sum of all these results. For example, Sum(LastPeriods(3), [Measures].[Store Sales]) will calculate the total sales for the last three Time periods starting from the current Time dimension member.
  • Count(set) returns a count of set members. Count(set, ExcludeEmpty) will return a count of set members for which corresponding measure values are not empty.
  • Avg(set, numeric_expression) calculates the average of numeric_expression in set
  • Max(set, numeric_expression) returns the maximum value of numeric_expression in set
  • Min(set, numeric_expression) returns the minimum value of numeric_expression in set
  • Median(set, numeric_expression) returns the median value of numeric_expression in setTime difference calculations

eazyBI defines an additional function DateDiffDays(from_date, to_date) which will return a difference in days between two dates. It can be used together with Now() function (which returns the current time) to get a distance in days between selected Time dimension member and the current date, for example

DateDiffDays([Time].CurrentMember.StartDate, Now())

In addition eazyBI defines a function DateAddDays(date, number_of_days) which will return a new date in the past (if number_of_days is negative) or in the future (if number_of_days is positive). For example, this will return a date which is 5 days from the current Time dimension member date.

DateAddDays([Time].CurrentMember.StartDate, 5)

Sometimes it is useful to get the Time dimension member which corresponds to the actual current date. This can be done with eazyBI specific dimension hierarchy level property CurrentDateMember. For example, these expressions will return the members for the month of the current date and the week of current date:

[Time].[Month].CurrentDateMember
[Time.Weekly].[Week].CurrentDateMember

If there is no Time dimension level member that corresponds to the current date (e.g. if data for current month or week are not yet imported) then CurrentDateMember will return the last period before the current date which is present in the Time dimension corresponding level.

There is also an additional eazyBI specific dimension hierarchy level method DateMember for which you can provide a dynamic date expression argument and get the corresponding member (or the last period before that date which is present in the Time dimension). For example:

[Time].[Day].DateMember('7 days ago')

If you want to select a subset of Time dimension level members between specified dates then you can use DateBetween function to filter level members using a date range expressions. For example, the following expression will return set of last 7 days from [Time].[Day] level members:

Filter(
  [Time].[Day].Members,
  DateBetween([Time].CurrentMember.StartDate,'7 days ago','today')
)

Aggregate members in other dimensions

In the beginning of this tutorial we defined a [Customers].[West coast] calculated member in the Customers dimension with a formula:

[Customers].[USA].[CA] + [Customers].[USA].[OR] + [Customers].[USA].[WA]

But it would be better if we could expand or drill into West coast when using it in reports. To enable that you need to define calculated member using Aggregate(set) function. When you will combine such calculated member in a report together with other measure then you will get an aggregated result (by default a sum) of this measure over the specified set of members. So if you will define a [Customers].[West coast] calculated member with a formula

Aggregate({
  [Customers].[USA].[CA],
  [Customers].[USA].[OR],
  [Customers].[USA].[WA]
})

then you will get the same result as previously but, in addition, you will be able to expand or drill into West coast member and see the detailed results for CAOR, and WA.

Members used in Aggregate function will appear in the report in the same order as they are included in the function, therefore, it could be used to rearrange the order of the dimension members in report rows.

In a similar way, you can aggregate more complex set expressions. For example, you could define [Customers].[US without west coast] calculated member with a formula:

Aggregate(
  Except(
    [Customers].[USA].Members,
    { [Customers].[USA].[CA],
      [Customers].[USA].[OR],
      [Customers].[USA].[WA] }
  )
)

which will calculate the aggregate of all US states without west coast states and you can drill into these states as well.

Conditions

You can write formulas with conditions using IIF and CASE functions. For simple IF / THEN / ELSE type of conditions use IIF(condition, if_true_expression, if_false_expression) (notice that there are two I letters in IIF), for example

IIF([Measures].[Profit] > 0, 'Profit', 'Loss')

If there are many conditions then it is easier to use a CASE function, for example

CASE
WHEN [Measures].[Profit] > 1000
THEN 'Big profit'
WHEN [Measures].[Profit] > 0
THEN 'Small profit'
ELSE 'Loss'
END

If all conditions are comparisons of the same expression to different expected values then the other CASE form can be used, for example

CASE [Time].CurrentMember.Level.Name
WHEN 'Month' THEN
  Sum(LastPeriods(3), [Measures].[Store Sales])
WHEN 'Day' THEN
  Sum(LastPeriods(90), [Measures].[Store Sales])
END

In IIF and CASE conditions standard comparison operators can be used (=<<=<>>>=) as well as ANDOR and NOT operators as well as several specific operators:

  • IS returns whether two objects are the same, for example, [Customers].CurrentMember IS [Customers].DefaultMember (which will be true if Customers current member is the default All Customers member)
  • IN and NOT IN returns whether a member is in a set, for example [Customers].CurrentMember IN [Customers].[USA].[CA].Children

Member properties

Dimension members has some default properties (like .Name and .Key) as well as they can have additional custom properties. eazyBI source application import (e.g. from Basecamp, Highrise or Jira) are also importing additional dimension fields from source systems. MDX has standard Properties function to access member properties. eazyBI defines an additional get (and also with a longer name getProperty) function which will return an empty result instead of an exception if no property is defined for the current dimension level.

For example, Jira import adds the Created at property for all imported issues. The following expression returns the Created at property value for the Issue dimension current member:

[Issue].CurrentMember.get('Created at')

Date type conversion

Sometimes you might need to convert string expression to an integer, a decimal or a date expression (for example, to convert Highrise custom field string value to corresponding type to be able to use it in further calculations). There are several functions available for data type conversions:

  • CInt(value) returns a value converted to an integer
  • CDbl(value) returns a value converted to a double floating number type (should be used when results should be decimal)
  • eazyBI defines additional function DateParse(value) which will try to convert value to a date value using different date formats (for example both 2012-01-31 and Jan 31 2012 will be converted to the correct date). There is a MDX standard CDate function but it supports less date formats.
  • DateParse function also supports dynamic date expressions as offset from today (learn more about them in date filters help page). For example, you can use DateParse('today') or DateParse('30 days ago') or DateParse('1 week from now').

Default values

Sometimes you want to return a default value if some measure or function will return an empty value. In these cases you can use CoalesceEmpty(expression, default_value) function, for example:

CoalesceEmpty([Measures].[Store Sales], 0)

Comments

It is possible to write comments in calculation formulas. Use comments either to describe some non-obvious complex calculations or also commenting is valuable during debugging of the calculation formulas. When something is not working as expected then comment all formula lines and leave uncommented just some part of the formula that you would like to debug.

-- one line comment
expression -- comment until end of line
/* multi line
   comment
*/

Special comments with annotations

Available on eazyBI Cloud or starting from version 4.0 of eazyBI add-on for Jira or Private eazyBI.

You can add special comments in the formulas with annotations for the calculated member. These annotations are used in the eazyBI user interface to group calculated members or to allow or disable certain actions:

-- annotations.group=...
Put the calculated member in the specified group in the Calculated members section of the dimension in the report builder.

-- annotations.disable_drill_into=true
Do not allow the Drill into action for this calculated member.

-- annotations.disable_drill_across=true 
Do not allow the Drill across action for this calculated member.

-- annotations.disable_drill_through=true 
Do not allow the Drill through action for this calculated member.

-- annotations.disable_drill=true 
Disable all drill actions for this calculated member.

List of all MDX functions

This tutorial covered most frequently used MDX functions and examples how to use them. If you didn’t find what you need then take a look at the list of all MDX functions.

If you have any unclear questions or issues when writing calculation formulas then please contact eazyBI support.

  • No labels