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.
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:
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:
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:
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:
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