All eazyBI for Jira eazyBI for Confluence Private eazyBI

Calculated members in other dimensions

On this page:

Set of dimension members

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 the list of members enclosed in curly braces, e.g.


{[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:


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


There are several other useful functions for working with sets. Here are some examples of 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 the 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.

Aggregate members in other dimensions

You can group several same-level members, for example, states of the West coast, into one member so you cold more conveniently use it on report pages as a filter or other calculations. In the dimension, create a new calculated member using Aggregate(set) function. When you will combine such calculated members in a report together with another 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


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

Members used in the function Aggregate 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 a calculated member [Customers].[US without west coast] with a formula:

    --list of exceptions

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

You can also aggregate dimension members using Filters and wildcards. For example, you could define a new calculated member[Customers].[La cities] with a formula:

    [Customers].CurrentMember.Name MATCHES 'La.*'

That would aggregate members from a "Customers" dimension at level "City"  based on the filter condition (city name starts with "La" prefix).