Defining lists with Custom MDX and Edit Formula options

You can define lists with MDX formulas. To display the Edit Custom MDX dialog select the <Edit Formula> or Custom MDX options wherever they appear in the List Designer. For example, you can create a filter with custom MDX.

The Edit Custom MDX dialog contains these buttons:

  • Elements: Displays a list of elements which can be used.
  • Variables: Displays lists of variables and objects which can be used.
  • MDX: Displays the multidimensional expressions that can be used.
  • Functions: Displays a list of functions to use in the formula.

Text:

="text"

For example, ="element_unique_name".

Object property:

=ObjectName.PropertyName where ObjectName is the name of a global variable, global list or a local object. PropertyName is the name of a property of the specified object. for example, text or attribute.

For example, =Variable_name.Text or

=ListView2.Attribute

A combination of texts and object properties joined with an ampersand (&)

=[ObjectName.Property]&["Text"]

For example, =Variable.Text & "Level"

Examples

The examples are based on the Samples application and use a hyperblock created from the Product dimension.

Use a global variable in structure selection:

Create a global variable named UniqueProduct. Set its value as: 
[Product].[All Tires].[Car Tires Summer]
In the List Designer, click Structure Selection.
  1. Click Select Children.
  2. From the Elements list in Selection Settings, select <Edit Formula>.
  3. In the Edit Custom MDX dialog, click Variables and double-click the UniqueProduct variable in the list of global variables.

View Mode lists the two children of 'Summer Tires'.

Use MDX functions in structure selection:

These examples use an MDX function to return the children of the first child element of the Product dimension (All Season Tires).

  1. In the List Designer, click Structure Selection.
  2. Click Add Custom MDX in the toolbar.
  3. In the Edit Custom MDX dialog, specify [Product].firstchild and click OK.

You can use this method to achieve the same result:

  1. In the List Designer, click Structure Selection.
  2. Click Select Children.
  3. From the Elements list in Selection Settings, select <Edit Formula>.
  4. In the Edit Custom MDX dialog, click Elements and double-click the element of which to display the children. For example, All Tires.
  5. The string [Product].[All Tires] is displayed.
  6. Change the string to [Product].firstChild and click OK.

Use global lists:

To reference a global list in a cell, use this syntax: 
=List.ListName.Text

For example, =List.Product.Text returns the first element of the list.