OneStream Cube View Tip: How to Sum Cell Amounts for Members Filtered by Text Field
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.
Business Rule Method
The member formula method is quick and easy to use. There may be situations, however, in which a member formula may not be the best approach (e.g., when many filter attributes are not all stored in the same text field across multiple dimensions). In this example, it may not be feasible to create new UD8 member for each filter attribute. It also would require modifying the member formula for each text field. This is where we utilize the business rule functionality that OneStream provides.
The business rule can be called by the cube view and utilizes the FinanceFunctionType.DataCell parameter, which is triggered when a business rule is called from a cube view. The business rule method uses the same logic as the member formula method (i.e., it generates a list of members and loops through them to aggregate their values). The big difference is that this method allows us to specify the text field, the value of the attribute, and even the dimension. We can then pass these values to the business rule as parameters that are provided in each cell.
Using a business rule to calculate the total sum is more dynamic but requires more development time. On the other hand, it also provides added functionality and requires less maintenance since we don’t need to create a new member each time a new attribute filter is added.