Consider the following case: A user-defined dimension used for customers or vendors has thousands of members. The members are stored in a flat hierarchy so there are no parents that generate subtotals. Instead of parents, members are tagged with a text value in one of the text fields that are used as attributes. We want to show all the members that match a text value and show the total amount for all the members.

For example, UD2 uses the Text2 field as a possible filter for the customer or vendor family as follows: We want to create a cube view that shows all the activity for customers or vendors that are tagged with “EHR” in Text2. It is easy enough to get the list of members to show up in a cube view by using the “where” statement in the member filter:  Getting the sum of the rows, however, is more challenging. Using the GetDataCell formula needs the exact number of rows that are generated by the filter, although this may not be feasible because the number of rows may change depending on the filter. It also is going to be a very long formula. To make this work dynamically, we need to create a custom solution.

Let’s review two possible methods to solve this problem: Member Formula and Business Rule.

## Member Formula Method

If there are a limited number of attribute filters all stored in the same text field, using member formulas is an easy and effective solution. Member formulas are fast and efficient and can be used anywhere in the application (e.g., cube views, quick views, XFGetCell). In this case, DynamicCalc works just fine. The first step is to create a member for each text filter. I like to use UD8 for situations like this: Each new member is set as a DynamicCalc formula type and has the following formula: The formula uses the following command to create a list of members using the same filter used in the cube view (row 29): It then loops through each member in that list, retrieves the value of the cell from the cube, and adds that value to an aggregate total (rows 31-34): The formula then stores the aggregate total (amountTotal) in the UD8 member at the intersection of UD2#None (row 40): We can now view the data intersection in the cube views: The new member shows the total for the filtered rows: If a new attribute filter is added, we simply add a new UD8 member with the same name and copy the formula from one of the existing members. The formula does not need to be modified because all the parameters in it are dynamic.