Specifying UDAs in Formulas in Aggregate Storage Outlines

UDAs are words or phrases that you create for a member. For example, in Sample.Basic, top-level members of the Market dimension have the UDA Small Market or the UDA Major Market.

The Major Market example used in this topic shows how to create a formula for a member that shows the sum of sales for all major market members. The example assumes that a new member (Major Market Total) has been added to Sample.Basic.

  1. MDX provides a Boolean function, IsUDA, which Returns TRUE if a member has the associated UDA tag. The following syntax returns TRUE if the current member of the Market dimension has the UDA “Major Market”:

    IsUda([Market].CurrentMember, "Major Market")
  2. A Filter function, when used with IsUDA (as shown in the following syntax), cycles through each member of the Market dimension and returns a value for each member that has the Major Market UDA:

    Filter([Market].Members, IsUda([Market].CurrentMember, "Major Market"))
  3. The Sum function adds the values returned by the Filter function; for the Major Market example, the following formula is produced:

    Sum (Filter([Market].Members, IsUda([Market].CurrentMember, "Major Market")))

    This formula is attached to the Major Market Total member.