Using the Data Source to Work with Member Properties

You can modify the properties of new and existing members during a dimension build by:

  • Including member properties in a field in the data source

  • Leaving the data source field empty to reset the property to the default value, or to remove the formula or UDA

In the data source, put the properties in the field directly following the field containing the members that the properties modify. For example, to specify that member Margin% not roll up into its parent and not be shared:

  1. Position the ~ property (which indicates that the member should not roll up into its parent) and the N property (which indicates that the member should not be shared) after the Margin% field. For example:

    Margin%  Margin%  ~ N Sales

    Note:

    Margin % being repeated because of Alias.
  2. Set the field type for the properties fields to Property.

    See Setting Field Type Information.

Removing a formula, UDA, or attribute, or resetting a property to its default value, includes the following additional step: Leave the field NULL or empty in the data source.

Table 12-1 lists all member codes used in the data source to assign properties to block storage outline members. (For a list of properties that can be assigned to aggregate storage outline members, see Rules File Differences for Aggregate Storage Dimension Builds.)

Table 12-1 Member Property Codes for Block Storage Outlines

Code Description

%

Express as a percentage of the current total in a consolidation

*

Multiply by the current total in a consolidation

+

Add to the current total in a consolidation

-

Subtract from the current total in a consolidation

/

Divide by the current total in a consolidation

~

Exclude from the consolidation

^

Exclude from all consolidations in all dimensions

A

Treat as an time balance average item (applies to accounts dimensions only)

B

Exclude data values of zero or #MISSING in the time balance (applies to accounts dimensions only)

E

Treat as an expense item (applies to accounts dimensions only)

F

Treat as a time balance first item (applies to accounts dimensions only)

G

Reset two-pass calculation to false (applies to accounts dimensions only). Also see property code T in this table.

J

Reset expense item to false (applies to accounts dimensions only). Also see property code E in this table.

K

Reset the time balance property to NONE (applies to accounts dimensions only). Also see property codes A, F, and L in this table.

L

Treat as a time balance last item (applies to accounts dimensions only)

M

Exclude data values of #MISSING from the time balance (applies to accounts dimensions only)

N

Never allow data sharing

O

Tag as label only (store no data)

P

Reset the time balance skip option to NONE (applies to accounts dimensions only). Also see property codes B, M and Z in this table.

S

Set member as stored member (non-Dynamic Calc and not label only)

T

Require a two-pass calculation (applies to accounts dimensions only)

X

Create as Dynamic Calc

Z

Exclude data values of zero from the time balance (applies to accounts dimensions only)