Custom Members

What are Custom Members and How are They Used

Define custom MDX formula members (calculations) for use in Dashboards in one central location. For example, let’s say you define a custom formula for the Account dimension. The formula includes a specific company, department, and account combination. You can access Dashboards and build charts for several Dashboards with the Account dimension for which the formula was built.

The benefit of this feature is that formulas can be reused allowing for rapid chart production and more flexible and complex display of data.

Custom Members can be used on the horizontal or vertical axis within the chart. A few examples of the formulas that you can define and use in Dashboards follows.

To sort products in descending order and returns the top 5 products with the highest values:

TopCount({Order({{{Descendants({[Product].&[1]},,LEAVES)}}},([Time].&[5080],[Measures].[MTD]),desc)},5)

To calculate variance between budget and forecast for the given period using substitution variables:

((@CURBDGT@,@CURMTH@)-(@CURFCST@,@CURMTH@))/(@CURFCST@,@CURMTH@)

To get data for a specific combination of dimension members.

([Account].&[174],[Company].&[109],[Department].&[1])

To get the sum of values for accounts.

sum({[Account].&[197] , [Account].&[198] , [Account].&[199] , [Account].&[201] , [Account].&[202] , [Account].&[344] , [Account].&[203] , [Account].&[474] , [Account].&[204] , [Account].&[205] , [Account].&[206] , [Account].&[207] , [Account].&[208]})

For the descendants of a member in the hierarchy and to order them within a hierarchy.

Hierarchize({{Descendants({[Account].&[194]},,LEAVES)}})

To calculate variance between actual and budget using substitution variables.

@ACTUAL@-@CURBDGT@

To calculate variance between the previous month actual and current month budget using substitution variables.

([Scenario].&[1],@CURMTH-1@)-(@CURBDGT@,@CURMTH@)

How to Set Up Custom Members

1. Create a formula by navigating to Maintenance > Reports > Cube Settings.

2. Click the Custom Members tab.

   

3. Click Add. The Add Custom Member page displays as shown below.

   

4. Enter a code and name.

5. Select the Reporting Area for which you want to build the formula.

6. Select Account for Dimension to build the formula on the Account dimension. Or, select any dimension for which you want to build a formula.

7. Select the Account dimension from the list-box next to the Members / Functions buttons.

8. Drag and drop the account dimension member to the Rule Builder pane. In the example below step 13, Balance Sheet (Account 269) is selected.

9. Select the Company dimension from the list-box next to the Members / Functions buttons.

10. Drag and drop the company dimension member to the Rule Builder pane. Make sure you add a comma after the account dimension.

11. Select the Department dimension from the list-box next to the Members / Functions buttons.

12. Drag and drop the department dimension member to the Rule Builder pane. Make sure you add a comma after the company dimension.

13. Enclose the rule in parenthesis. The rule is shown below.

    

14. Click Save. You just created an MDX formula on the account dimension that pulls data from a combination of account, company, and department. Now, use the formula in a chart in Dashboards.

How to Use a Custom Member on a Chart in a Dashboard

Now, you can use this custom member when designing a chart. In this example, a custom member named Sum was created on the Account dimension. It totals several accounts shown in the image below. Use the Sum custom member when the Account dimension is selected for a chart(s).

1. Access Dashboards.

2. Drag and drop a chart to the dashboard.

3. For the x or y axis, select Account for dimension.

4. Under Custom Members, select the Sum customer member as shown below. Continue to build the chart by selecting additional dimensions and members.