The “RelativeMember” function is a good option; however, the “RelativeMember” function can only be used when ‘Year’ and ‘Period’ are in the same dimension. If the outline includes seperate ‘Year’ and ‘Period’ dimensions then the “RelativeMember” function is rendered useless. In this case, you can use the “Range” function. When using the “Range” function, you can use substitution variables or ‘Current Point of View for Period’ to designate the Start and End member. Both methods are great and provide well designed results. In this post we are going to show you how to leverage the “Range” function; to learn how to use RelativeMember refer to post “Hyperion Financial Reporting: Build a Rolling Reports – Relative Member.”

The steps below detail how to build a 13-month rolling balance sheet. We will start at a high level, assuming that the report is partially built. If anyone would like me to go into more detail on selecting member functions, respond in the comments area.

Step 1:

• Create a grid with the desired row and column configuration
  • Only two columns needed
• Place the Year and Period dimensions on the columns

Step 2:

• The first column should be ‘Current POV for Year’
• The second column should be ‘Relative to Current Point of View for Year Offset by 1’
• After selecting the functions for the Year dimension, double-click the Period dimension cell in the first column

Step 3:

• In the Select Members dialog box, click on the Functions tab
• Highlight the Range function and click the blue add arrow
• The Edit Range Function dialog box will appear

Step 4:

• In the Edit Range Function dialog box, click the Value box for start member
• The lookup icon will appear to the right of the box
• Click the lookup icon to edit the parameter value

Step 5:

• When selecting the parameter value you have two options:
  • Current Point of View for Period
    • Requires the end user to select the starting Period member when opening a report
  • Substitution Variables
    • Requires an administrator to update a substitution variable every month
  • Both have advantages however, for this example we will use a sub. var.
• Expand the sub. var. folder, select the respective sub. var. then add it to the selected member pane
• Click OK

Step 6:

• In the Edit Range Function dialog box, click the Value box for end member
• The lookup icon will appear to the right of the box
• Click the lookup icon to edit the parameter value
• Locate the December member then add it to the selected pane
• Click OK

Step 7:

• Double-click the Period dimension cell in the second column
• Repeat steps 3-6, but:
  • Select Jan for the start member
  • Select your sub. var. for he end member

Your report should now be dynamic based on a substitution variable.

Thank you! If you have any questions or comments, please feel free to contact me at jmonteith@thebean.co

0 responses