Column5 is the leading edge of Enterprise Performance Management and strives to provide technical tools which enable personal growth to benefit you and your organization. This blog will provide users high level functional help using dimension properties to automatically display related members

In some reports a key dimension member is fixed or determined by a user selection and other related members need to be displayed for comparison purposes.  If these members vary, a fixed member selection in a report cannot be used.  These selections could be performed manually each time a report is run by either keying the members into cells or the use of EPMSelectMember formulas for users to click on.

These methods require manual effort by users, potentially on multiple reports each period, and carry the risk of incorrect or inconsistent selections by users who are not fully familiar with the structure of the dimension.  The need for these multiple selections goes against the best practice of automating reports as far as possible.

An alternative to automate these related selections is to create dimension properties that are populated with the related dimension IDs.  These properties can then be linked to cells in the relevant axis of the report to select the relevant comparison member.

Category Dimension Scenario

The below scenario relating to the selection of Category dimension members are examined in this document:

Actual Reports need to compare Actuals against the following for variance analysis.

• Current Forecast
• Budget

In both cases, the above two category members vary over time as new forecasts are prepared and a new budget is prepared each year.

Forecast Reports used in the forecasting process need to report against:

• Prior forecast
• Budget

Each forecast has a different prior forecast as each forecast becomes the prior to the next.

The following properties need to be added to the category dimension to enter these comparison members:

• FCAST_COMP – Forecast comparison
• BUDGET_COMP – Budget comparison
• PRIOR_FCAST - Prior forecast

The Actual Reports need to have the following property formulae entered, where cell D30 is the location of the Actual category member.  These formulae, which return the relevant comparison categories need to be linked to the relevant cells of the column axis containing the Category dimension.  The relevant members are then populated from the Actual category automatically each time the report is run.

=EPMMemberProperty(,D30," FCAST_COMP ")

=EPMMemberProperty(,D30," BUDGET_COMP ")

The Forecast Reports need to have the following property formulae entered, where cell C15 is the location of an EPMContextMember in which the user selects the current forecast category.  These formulae, which return the relevant comparison categories need to be linked to the relevant cells of the column axis containing the Category dimension.  This functionality enables a single report to be used for each forecast without needing to be updated.

=EPMMemberProperty(,C15," PRIOR_FCAST ")

=EPMMemberProperty(,C15," BUDGET_COMP ")

The system administrator needs to maintain the Category dimension as follows:

• The FCAST_COMP and BUDGET_COMP properties against the Actual category member each time the version being reported against changes.
• When a new forecast member is created for use, the PRIOR_FCAST and BUDGET_COMP properties must be populated.

Advantages

This approach requires that the system administrator maintains the dimensions as discussed above.  This additional effort is most likely more than outweighed by the following benefits:

• Saving user effort in selecting multiple dimension members manually in multiple reports each period.
• Determination of appropriate related member is made by an administrator who is knowledgeable about dimension members and structures rather than users who may make errors.
• Consistent member selection automation in reports.

Through EPM Academy, your organization will gain valuable insights on how to speed up acceptance and adoption, reduce rollout time and costs, and improve project visibility and organizational perception. Learn all about our upcoming BPC Logic Bootcamp