Advanced list designer options

In the Advanced pane, you can change the behavior and representation of a list.

General

The General section has four fields which define the data source of a list. That is, the data connection, cube, dimension, and hierarchy on which the list is based. Each field contains the <Edit Formula> option. Use this, for example, to make the dimension dependent on a variable. This is required when you create dynamic reports in which, for example, users might be able to use drag and drop actions to exchange the column and row dimensions. In this case you might store the column and row dimensions in separate variables and make the dimensions dependent on those variables.

Hierarchy

This table shows the hierarchy options in the Advanced pane.
Option Description
Distinct Specifies whether elements that exist several times in a list are displayed. You can suppress elements that have the same unique names, or suppress elements that have the same names.

Select Show all to display all elements, including duplicates.

You can specify a formula to control suppression of duplicates.

Hierarchized Displays the hierarchy by levels. Each level is indented.
Invert hierarchy Inverts the hierarchy so that children are displayed above parents.
Secure structure when drilling down
Caution: 
If used incorrectly, this option can result in elements being hidden and inaccessible.
Lists can be calculated statically or dynamically. Static calculation loads all the elements of the list from the server at once. Dynamic calculation loads elements only as you expand the list.

Which method is used can affect performance, and various factors determine which calculation method is used in each situation. For example, static calculation is always used if the list is not hierarchized.

Dynamic calculation uses the .children property of lists. Often this is is the fastest method of calculating a list.

The Secure structure when drilling down option forces use of the .children property, regardless of the structure of the list.

If you use structure selection, filters, or custom MDX you can legitimately create a broken hierarchy. For example, you might exclude North/South America from this hierarchy: World>North/South America>North America.

In this example, if Secure structure when drilling down is True, no results are returned when you expand the World element.

List

This table shows the list options in the Advanced pane:

Option Description
Cacheable

By default, server lists are cached and saved in memory from where they can be retrieved more efficiently than from the database server. This is particularly useful for frequently used lists. The status of the cache is checked every two minutes to maintain data consistency. The data consistency must be checked, because every instance (user) has a separate cache. If a list is changed by a user, it can take up to two minutes before these changes are updated in the caches of other users.

If a report is frequently updated by many concurrent users, the benefits of caching might be outweighed by the need to update the report more frequently. In such cases, set Cacheable to False.

Description Optionally, add a text description of a list.

Subset

When the SUBSET function set to True, three parameters are enabled. They are Count, Base element and Start. With these, you define a subset that controls which elements of a local list are displayed.

You define the number of elements to be displayed in the subset (Count). Then you specify the point in the hierarchy where the subset should start (Start) in relation to an element you select (Base element). The start point can be the same as the base element or it can be a number of elements below it.

Example

The Product dimension of the Analysis cube is a product list containing product groups, product sub-groups and individual products. A three-element subset with All Products as its base element and as its start, displays the first three elements of the Product hierarchy (’All Tires’, the first product sub-group and the first element of that sub-group). This subset is defined as Count=3, Base element =All Tires, Start =0.

Changing the start from 0 (the same as the base element) to 1 moves the subset one element down the hierarchy. It now displays the first sub-group and the first two individual products.

Changing the start point to 2 displays only the first three individual products.