Custom display formats

Members of level-based dimensions, and the Time dimension in particular, have an inherent display problem that can make their use awkward in templates and reports. However, a solution is available through custom display formats.

To make page dimension members appear exactly as you want, you use custom display properties, which allow you to define a custom Display Format. You define custom display formats using the Custom Display Properties dialog.

The level-based dimension display problem

Dimensions can have two kinds of structure: parent-child and level-based.

Member properties function differently in these structures:

  • In a parent-child dimension, any member property exists for all the members, regardless of their level.
  • In a level-based dimension, a member property is always and only associated with a single level in that dimension.

This difference means that when you want to display the contents of a member property in a data view or template:

  • With a parent-child dimension, you can simply choose the member property, regardless of its location in the dimension structure.
  • With a level-based dimension, you can show the member property only for members belonging to the selected level.

Example problem

The following image shows a parent-child dimension (Organization) with the custom member property Manager:

The Manager property can be used at any level in Organization, regardless of whether the selected member represents a Cost Center, a Department, or a Division.

The next image shows a level-based dimension (Product) consisting of two levels (Category and Product); the members in the upper level are all product categories (Cars, Trucks) and the members at the bottom level are individual products (Delorean, Flatbed, and so on):

If we want to add the custom member property Manager to Products, the same as above for Organization, we must add it at each level. But after adding it for one level (Catergory), at the next level (Product) we encounter the following:

Duplicate member property names in a dimension are not allowed. The workaround for this is to give each member a unique name:

Now we are able to add manager names at both levels. The following image shows the Category level selected:

Likewise, if we selected a member from the Product level, such as Edsel, under Custom Member Properties we would see Product Manager.

Back to the original problem

The above workaround solves the problem of storing manager names at different levels in a level-based dimension.

But this only brings us back to our original problem, the one this topic addresses: How to display the appropriate member in a level-based dimension. That is, what if we want to display the appropriate manager name as part of the member display in a template or in Ad Hoc Analysis? We now have two properties available that contain manager names, but only one of them is the right one in a given instance.

Is a solution available?

The solution

The Custom Display Properties dialog provides the answer, by allowing us to define a Display Format for the dimension, as follows:

&name(&mp[Category Manager]&mp[Product Manager])

The Display Format in the above example will show the member Name followed by the manager name in parentheses. It solves the problem of having to choose between two manager properties by simply including both properties. The reason this works is as follows:

  • Either Category Manager or Product Manager will be empty, while the other will contain the name of a manager.
  • Putting them together and inside parentheses gives us the display format we want.

Summary

The above workaround for the Product level-based dimension was created as follows:

  1. We defined custom member properties for the Product dimension (Category Manager and Product Manager).
  2. We opened the dimension's Custom Display Properties dialog.
  3. In the dialog we defined a Display Format that we added to both Category Manager and Product Manager.

How to show the year for each member of Time

Time is a level-based dimension. Probably the single most common use case for custom display properties is showing the Year for each member of Time (either alone or as part of the member display).

 Note:  With the Time dimension, a potential source of confusion stems from the fact that by default Time already contains a Year member property. This intrinsic member property is used to link the level below it to the Years level in the Time dimension. Consequently, in a Time dimension consisting of the levels Year, Quarter, Month, only members belonging to the Quarter level are populated with a value in the intrinsic Year member property.

To include the Year in a custom member property display for all members of Time (assuming a level structure of Year/Quarter/Month) you can use the workaround described above in the Product dimension example.

The steps are as follows:

  1. In Model Manager, for the Time dimension's Months level, create a member property called, for example, YearForMonth.

  2. Open the Time dimension's Custom Display Properties dialog.

  3. Select YearForMonth and in Display Format type: &mp[Year]&mp[YearForMonth].

The result is that, for every member of Time, the Year associated with that member is displayed, regardless of their location in the dimension structure.

What happens at each level is as follows:

  • Months level: the member property Year is empty but that is okay because the member property YearForMonth provides the year.
  • Quarters level: here the opposite occurs--Year provides the year associated with the member whereas YearForMonth is empty.
  • Years level: here both Year and YearForMonth are empty; this triggers a special feature: whenever a member property Display Format definition results in an empty string for a member, the member’s Name is automatically displayed (which in this case is the year).