Advanced options

The Advanced pane is one of the panes of the List Designer . In Advanced, you can change the behavior and representation of a list.

See List Designer

General

The General section displays the data source of the list (database alias, cube, dimension and hierarchy).

Hierarchy

This table describes the hierarchy options:
Option Description
Distinct Here you specify whether elements are displayed that exist several times in a list. If this value is set to True, these elements are only displayed once.
Hierarchized Displays the hierarchy by levels.
Invert hierarchy Inverts the hierarchy.
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 describes the list options:
Option Description
Cacheable By default, server lists are cached and stored 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 could take two minutes until these changes are updated in the caches of the other users. The size of the cache can be changed in Repository Administration > Repository Registrations > Projects > Report Catalogs > Settings.

If a report is being frequently updated by a number of concurrent users, the benefits of caching may be outweighed by the need to update the report more frequently. In such cases, it may be beneficial to set Cacheable to False.

Description

MDX

MDX statements allow advanced users to create customized structural filters, text filters and sorts. They can be used instead of the inbuilt functionality of the List Designer or can be combined with it (for example, use structure selection in the List Designer to structure a list, but use an MDX statement to sort it).

The MDX functions, EXCEPT, INTERSECT and UNION in the MDXType list are used to specify how custom MDX statements are combined with the List Designer. To use the List Designer or MDX exclusively, select Use list definition or Use Custom MDX.

This example is based on the Best Practices OLAP database supplied with Office Plus.

  1. Create an Ad-hoc report on the Analysis cube, using the Product dimension in the row position and the Region dimension in the column position.
  2. Click the Product dimension to enable the List Designer.
  3. In the Custom MDX field, specify:[Product].[All Tires].[Car Tires Winter]
  4. Create this filter:Caption=Summer Tires
  5. In the MDXType list in the Advanced pane, select Use list definition. The report displays only Summer Tires.
  6. Select Use Custom MDX. The report displays Winter Tires.
  7. Select Union. The report displays Summer Tires and Winter Tires (the UNION function creates a logical OR relationship).
  8. Select EXCEPT. The report displays Summer Tires (the EXCEPT function subtracts the MDX select from the List Designer selection).
  9. Select INTERSECT. The report is blank (the INTERSECT function creates a logical AND relationship).

Subset

With the SUBSET function set to True, these parameters are enabled:

  • Count
  • Base Element
  • Start

With these parameters, you define a subset to control which elements of a local list are displayed.

You define the number of elements to be displayed in the subset (Count). You then 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.

In the Sales cube of the Best Practices OLAP database is a Product list containing Product Groups, Product Sub-Groups and individual products. A three-element subset with All Products as both its Base Element and its Start, displays the first three elements of the Product hierarchy. In this case, these are All Products, the first product sub-group and the first individual element of that sub-group. This subset is defined as Count=3,Base Element=All Products, 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. Similarly, changing the start point to 2 displays just the first three individual products.

Parent topic: Lists