In many instances, it can be useful to build flexibility into Hyperion Financial Reports. In this post, we will enable users to select multiple years of data with a single user prompt. In cases like the example below, where a report has many columns spanning multiple years, the most direct solution can create a challenging multi-prompt solution for end users.
In this entry, we will walk through how a single user prompt can dynamically populate related columns. In the example below, column D produces the same year as column C per built-in ‘same-as’ functionality.
Normally a report designer could use the ‘relativemember’ function to adjust the output of a dimension member or substitution variable. However, the RelativeMember function is not compatible with the same-as function. If we attempt to adjust the year in column D by -1, the same-as function overrides the RelativeMember function, producing identical years on both columns. To dynamically populate [proposed year -1] into column B, we will need to take another approach. It helps in this instance to understand that identical user prompts combine into a single prompt when the report is run from Hyperion’s Planning interface. In addition, HFR’s ‘relative member’ function is compatible with user prompts. Combining these facts, we can create a relative member prompt in column B to produce [prompted year -1]. In member selection on Year in column B, select relative member from the functions tab: Insert ‘Prompt for [Dimension] as the member name, and the appropriate offset, -1 in this example. After pressing ‘ok,’ HFR will allow the designer to define the dimension prompt. We want to be very deliberate in setting this, as it will need to be created identically for each additional prompt. After saving the second column with a relative member prompt, running the report in HFR will reveal both prompts and their identical construction. The output reveals that despite identical prompts, the relative member function has pointed the Adopted column to the appropriate year. We can apply this same method to the first two columns, again ensuring the member prompts are built 100% identically. After closing out of member select, the relative member snippet should appear as in the screen below: Running the report in HFR reveals all four prompts, all with identical construction However, when the same report is kicked off via the planning front end; Hyperion combines all identical prompts by default. The output produces all of the expected years.
Using this approach, we can minimize the number of user prompts while maximizing Hyperion Financial Reporting flexibility.