Calculated members in other dimensions

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.

{[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 function SetToStr when creating 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 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 – FirstChild, LastChild, FirstSibling, LastSibling.

Aggregate members in other dimensions

At 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 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

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 the member West coast and see the detailed results for CA, OR, 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:

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.

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:

Aggregate(
  Filter(
    [Customers].[City].Members,
    [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).