Challenge #5 – Variable Needed for Report Calculation

October 12th, 2016

This is the last post in a five-part blog series providing you with the FRS challenges I’ve run into most often and tips for solving them.

In this example, I had a Report that did a calculation where an Account balance was divided by a Variable that corresponded to the Period number. This data was not maintained in the application and the Report Administrator had to change the Variable in the Report each month. This made the Report static and a maintenance problem so the challenge was to make it dynamic. This solution assumes that the main Report uses Grid1 and that Period is a POV selection.

  1. Create a second Grid in the Report that would always be hidden with the Periods in the Columns. One Dimension needs to be in the Rows so pick one that is Fixed in Grid1 and set the same.
  2. Create 12 Period Columns which all pull Current Point of View for Period
  3. Add a Formula Row (2) and use Custom Formula in the cell for each Period to add the corresponding Variable needed
  4. Add Conditional Suppression to each Period column as indicated to Suppress it unless the Current POV corresponds to its particular Period
  5. Add a Formula Column (B) that will sum up all of the Period Columns (B – M). With the Conditional Suppression only the month corresponding to the Current POV should be unsuppressed and therefore the formula in Column A will only pick up that Variable.

Be sure to select “Hide Grid” in the Grid Properties under Suppression screen to prevent Grid2 from showing on the Report after validating that it is functioning as expected.

In Grid1 which is the main Report, add a Formula Row or Column somewhere in the Report to pull in the Variable from Grid2. The location will vary depending on the intended use of the Variable. If they are only used to calculations and not intended to be shown the Row or Column can be hidden.

Use a custom formula in a cell to pull in the Variable from Grid2. In this example, the formula would be – Grid2.Cell[2, A] and in the picture above, this formula is sitting in Grid1.Cell[2,B].

Row 3 of this Report is demonstrating using the Period Variable to calculate an Average of Recruiting Expenses from the Year-to-Date amount in Column B.

Below is how the Report appears in Workspace when it is run for July with both Grid1 and Grid2 unsuppressed to illustrate the result.

Grid2 at the bottom returns a 7 because of the Conditional Suppression being applied to all other Periods. The Sum formula under Variable is only picking up the 7. Then the formula in Grid1 is pulling that Variable in and using it to do the Average Calculation.


About TopDown Team

The TopDown Team includes members of TopDown Consulting who want to let the community know about webcasts, conferences, and other events. The team also conducts interviews on various EPM industry topics.

Leave a Reply

Your email address will not be published. Required fields are marked *