The “Range” function is a good option; however, the “Range” function can only be used when ‘Year’ and ‘Period’ are separate dimensions. If the outline combines the ‘Year’ and ‘Period’ into one dimension then the “Range” function is rendered useless. In this case, you can use the “RelativeMember” function. When using the “RelativeMember” function, you can use ‘Current Point of View for Period’ to make the report dynamic. Both methods are great and provide well designed results. In this post we are going to show you how to leverage the “RelativeMember” function; to learn how to use “Range” function refer to post “Hyperion Financial Reporting: Build a Rolling Reports – Range.”

The steps below detail how to build a 13-month rolling balance sheet for a company whose fiscal year is different than the calendar year. 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
  • 13 columns needed – ignore the spacer columns in the screen shots
• Place the Year dimension on the columns

Step 2:

• Double-click the Year dimension cell in the first column

Step 3:

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

Step 4:

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

Step 5:

• Select Current Point of View for Year then add it to the selected member pane
• Click OK

Step 6:

• In the Edit Range Function dialog box, enter -12 in the Value box for Offset
• Click OK

Step 7:

• Repeat steps 2-6 but increase the offset by one between each column up to the last column
  • Column A; offset =-12
  • Column B; offset =-11
  • Column C; offset =-10
  • Column D; offset =-9
  • Column E; offset =-8
  • Column F; offset =-7
  • Column G; offset =-6
  • Column H; offset =-5
  • Column I; offset =-4
  • Column J; offset =-3
  • Column K; offset =-2
  • Column L; offset =-1
• The last column (in this case column M) will be Current Point of View for Year.

Your report should be dynamic based on the POV selection of the user.

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

0 responses