<img src="https://certify.alexametrics.com/atrk.gif?account=3HHNq1DlQy20Y8" style="display:none" height="1" width="1" alt="">

SAP Analytics
Thought Leadership

Within BPC Automatically Display Related Members

Posted by
Michael Sheard
Michael Sheard
on Wed, Nov 06, 2013 @ 09:11 AM

Column5 Technical ServicesColumn5 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.


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

BPC Logic Bootcamp

Topics: Enterprise Performance Management (EPM), Training


Recent Posts

Posts by Topic

see all

3  S T E P S

To Enhance Your EPM Performance:

 1.Get Your Ultimate Guide to Improving SAP EPM Performance

ebook 2

 Learn what could be contributing to your poor performance and how to diagnose common problems. Get tips that will empower you and your team to improve the  performance  of your system in order to get the most out of your SAP EPM Investment.

Get the e-Book


2.  Test Your BPC Performance 

Benchmark 2

Get a BPC performance report card and custom report to identify performance issues you may not even know you have.

Test my BPC


  3. Get Your EPM Assessment  


Our assessment delivers a complete, best practice roadmap for you to follow.  We’ll work together to assess what you need to pivot your financial forecasting, planning, reporting and analysis to handle the compressed requirements from the volatile COVID-19 environment. 

New Assessment