Now you know you to navigate to individual dimension members. But quite often you would like to perform operations on set of dimension members, for example, find sum or average value of some measure over 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 set as range of sequential dimension level members then you can specify first and 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 exact range of members but would like to get all dimension hierarchy level members. You can do it with Members method, for example, to get all months in Time dimension:Time.Month.Members
When you want to test which members will be returned by some set expression then you can use SetToStr functions to create string of concatenated member full names. For example, define calculated measure Measures.test all months with formulaSetToStr(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 set of children members using dimension hierarchy (in this example all cities in California)
- Descendants(Customers.USA, Customers.City) returns set of member descendants at 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.ChildrenThere are additional methods that you can use to get just first or last members of these sets - FirstChild, LastChild, FirstSibling, LastSibling. \\\\\\\\\\\\\\\\\\\\\\\\\\
| 