Custom Members/Formulas

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@)

On the Scenario Dimension:

1. Actual vs Budget Variance

   [Scenario].&[1] - @CURBDGT@

2. Actual vs Prior Year Variance

   [Scenario].&[1].@CURYR@ - [Scenario].&[1].@CURYR-1@

3. Actual vs Forecast Variance

   [Scenario].&[1] - @CURFCST@

4. Actual vs Budget Variance %

   ([Scenario].&[1]-@CURBDGT@)/@CURBDGT@

On the Time Dimension:

1. Periods To Date

   ([Time].[Year], @CURMTH@)

2. Compare Last Month

   {Hierarchize({ParallelPeriod([Time].[Month], 1, @CURMTH@),@CURMTH@})}

3. Compare Last Qtr Same Period

   {Hierarchize({ParallelPeriod([Time].[Month], 3, @CURMTH@),@CURMTH@})}

4. Compare Last Year Same Period

   {Hierarchize({ParallelPeriod([Time].[Month], 12, @CURMTH@),@CURMTH@})}

5. Trailing 12 Months

   @CURMTH@.Lag(11):@CURMTH@

6. Rolling 6 Quarters

   @CURQTR@:@CURQTR@.Lead(5)

7. Rolling 12 Months

   @CURMTH@:@CURMTH@.Lead(11)

8. Trailing 3 Years

   @CURYR@.Lag(2):@CURYR@

In Practice: Custom Member Setup

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.

    In Practice: Using 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).

    

15. Access Dashboards.

16. Drag and drop a chart to the dashboard.

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

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

    

Pre-Defined Custom Members/Formulas

The Custom Members below are pre-defined in each Reporting Area (cube) where the Dashboards module is enabled. Reporting areas are; Financial and Workforce.
Scenario:

• Actual vs Budget Variance

• Actual vs Prior Year Variance

• Actual vs Forecast Variance

• Actual vs Budget Variance %

Time:

• Periods To Date

• Compare Last Month

• Compare Last Qtr Same Period

• Compare Last Year Same Period

• Trailing 12 Months

• Rolling 6 Quarters

• Rolling 12 Months

• Trailing 3 Years

Measures:

• Half Year To Date (HYTD)

Custom Members are populated for the Time and Scenario dimensions using substitution variables. So, for example, if you click Edit for the Compare Last Month Custom Member, you will see that the @CURMTH@ substitution variable is used to build the formula.