With the recent licensing changes in June 2019, Oracle EPM Cloud customers are quickly jumping onto the Narrative Reporting reports bandwagon. Some are migrating over their existing FR (Financial Reporting) reports, while others are becoming newly acquainted.

One of the first questions I get from customers is, how do I keep my reports dynamic? Good for you for not wanting to hard-code! And…you’ve come to the right place! I have a handful of member assignment features that you should be using with Narrative Reporting reports on a regular basis to avoid the pitfalls of hard-coding.

But before we begin, let’s make sure we’re all speaking the same language. When you place dimensions in your Narrative Reporting report, which will essentially create the structure of the report, they will go into the POV, rows, and/or columns (and note that at least one dimension needs to go into the rows and at least one needs to go into the columns for reports). Then you’ll need to select specific members for each dimension. You could just cherry pick everything and keep it all hard-coded, but that’s pretty inefficient in most cases. What if the dimension hierarchy changes? What if the report points to current year, and the year rolls over? Then you have to go in and manually re-select your members, which could take some time. That’s fine if you have one report to maintain, but what if you have 12? 20? 100? That maintenance time starts to add up.

As much as is possible, you should use member assignment functions and other like features to keep your reports dynamic and administration light. There are already quite a few basic functions that are built into the product that serve this purpose, as shown below:

For instance, do you have your balance sheet programmed into a dimension? Why not use a DESCENDANTS member function on that sub-hierarchy to dynamically select it instead? Do you need to display months across the columns? Use the BOTTOM function to grab those from your Time dimension. You get the idea.

Below, I recommend 5 features that you should be using in your regular reports development process. There are a ton more functions and features not listed (and some more notable than others), so hopefully this whets your appetite to look into he wonderful world of dynamic reporting options. You’ll be grateful for the time that these will save you!

Feature #1: Substitution Variables

Difficulty level: easy

Let’s start easy and work our way up. If your reports data source is something like OAC Essbase, Planning Cloud, and/or Financial Consolidation & Close, then you are probably using substitution variables in your data source. And if you’re using substitution variables in those tools, then you should absolutely be using them within the Narrative Reporting reports product!

Common use cases for substitution variables that are also used in reporting are current year, current month, current version, prior (everything)…

To find these for use…when you go to select your members for a dimension in the report, from the Member Selector window select the “Substitution Variables” option from the drop-down at the bottom of the screen instead:

Any substitution variables available for that dimension will display and you can select them (marked with a prefixed ampersand):

Note: If you don’t have any substitution variables created in the data source, don’t hesitate to create them. Substitution variables are commonly created for reporting-only use cases.

The advantages of leveraging substitution variables within reports is simple: 1) one version of the truth…as it comes to variables. The variable values are centrally maintained in the data source solution, so you don’t have to have duplicate them within reports, which could possibly cause them to become out of sync. 2) In addition, variables are much more dynamic than hard-coding. Use them as much as possible!

Feature #2: Prompts

Difficulty level: intermediate

Maybe it’s not up to you to determine all of the members that should exist in a report. Maybe your user needs to select some of them.

Oh wait, did you not know that you can have users select their own members? They can already do this within the POV (assuming that you don’t have it locked down).

But what if your user wants to select more than a single member, like for column headers? Or maybe your users have trouble remembering to change their POVs and they want the system to prompt them to select members. Enter prompts.

First, to find the Prompt function, you will need to select the fx function icon next to a member within the Member Selector screen and then choose the bottom option “View All Functions”:

Then find and highlight the “Prompt” function:

You will need to fill out a few parameters for your new prompt. First, what text do you want to show the user to help guide them into their selection? Next, what members should the user’s choices be limited to? Do you also want the user to be able to select multiple members or just one? Finally, what should the default member be? (recommendation: select something that most users would choose) Your final prompt parameters might look something like this when you’re done:

And when the user runs the report, they will first be prompted by report prompts, and it will look something like this:

The advantages of prompts is that it forces the user to stop and make a member selection. It also allows them to select multiple members of a single dimension, when applicable. There are some interesting limitations on prompts, however. Read Oracle’s documentation to learn more.

Feature #3: Saved Selections

Difficulty level: intermediate

Back in the FR days, we used to use a function called “Same As”. This was similar to an Excel cell reference formula. It was used often in column headers, and report developers would use the “Same As” function to repeat the same member across multiple columns. This function was deprecated in Narrative Reporting reports and replaced with a new feature called “Saved Selections”. Saved Selections are not exactly the same. I like to think of them as more like variables for reports, except that they can be combined with other member assignment features.

Use cases would include special member selections, like those tied to a user prompt, or a member set that is commonly used (like months of a year, etc.). I’m sure there are more that I’m  not thinking of.

Saved Selections are set up centrally through a report grid. Once set up, they can be recycled.

To set one up, go through the normal member selector process. Select your member or function, and ensure that it shows up in the left-hand Selections pane. If you have multiple selections in the Selections pane, that’s fine – just make sure to highlight the one that you want to turn into a Saved Selection. Use the cog wheel actions menu and choose “Save Selection”:

You’ll be asked to name it. Once you name it, it will be selected automatically.

To find and use existing Saved Selections, select the Saved Selections option in the drop-down at the bottom of the screen. Any that already exist for that dimension will be displayed and can be selected. They will be listed with the custom name created for them, and will also show the current value in parenthesis:

Note that you can also manage existing Saved Selections through this same screen – just use the action menu next to it to edit, rename, delete, or duplicate It.

You will know that a Saved Selection has been already used for a member selection, as it will show both the Saved Selection name and it’s value, as shown for the Entities POV dimension below:

As Saved Selections are like variables, they offer the same advantages of any type of variable object. They are dynamic and centrally maintained within a single grid/report.

Feature #4: RelativeMember

Difficulty level: intermediate

RelativeMember is a function. It can be used to move forward (using +) or backward (using -) a certain number of members within a single dimension. For instance, if you are looking at the months of a regular calendar year and you were focused on Jan, you could use RelativeMember in conjunction with +1 to move forward one member to Feb, or +2 to move forward two members to Mar. Along the same lines, if you were focused on Dec you could use RelativeMember in conjunction with -1 to move backward to Nov.

In a typical use case, you would use this with a report member that is not fixed. For instance, if you had Dec hard-coded on the report, you wouldn’t use RelativeMember to pick Nov. However, if Dec were a substitution variable or a Saved Selection (something where the value changes periodically), then you would use RelativeMember with that substitution variable or Saved Selection to go backward or forward based on that changing value.

After adding the function RelativeMember, you need to define the anchoring member that you want to be relative to, then how far from that member as well as what direction you want to go. In the example below, RelativeMember is being used in conjunction with a Saved Selection:

The biggest advantage to using this function is being able to combine it with a moving target member to dynamically select another member in the same dimension.

Feature #5: CurrentPOV

Difficulty level: difficult

CurrentPOV is a carryover function from FR, and it’s fantastic. It allows you to turn a single row or column dimension into a row/column dimension + a POV dimension.

Wait…huh?

I know, it sounds crazy, but it’s super useful. One of the most common use cases is when the Time or Years dimension is in the columns and you want the user to select the anchoring member, and then select other rows and columns respective to the user’s selection. I know that what I just wrote is probably very confusing, so let’s look at some visuals.

For instance, let’s say you have a report that compares some time period to a prior time period in columns, but you leave it up to the user to select the anchoring time period. How would you set that up? Here’s an example:

In Column A I have the CurrentPOV function for the Years dimension (which is the 2nd one in the column header). As you can see, this means that the Years dimension is now in both the Columns and the POV.

Mind blown, I know.

So that example is all fine and dandy as is, but honestly does not really require the use of the CurrentPOV function yet. However, this report intends to compare the user’s selected POV year to the respective prior year. So, to keep going, in Column B, I have combined RelativeMember with CurrentPOV to force the selection of the previous year as well. So if the user has 2017 as their POV selection, the report would look something like this (and note that the Forecast columns, C and D, are pointing to a different year and have nothing to do with the CurrentPOV function so just ignore them):

The user has 2017 in their POV, which means that column A also has 2017. Column B is relative to the user’s selection by going back one year, so it correctly reflects the year 2016.

If the user changes their Years POV to 2018, the columns automatically adjust to reflect the new selection:

There are several advantages to this function. 1) You can allow the user to control some of the rows and columns of a report. 2) You can use the CurrentPOV in conjunction with other functions to increase function flexibility. 3) You are forcing member selection through one area, the POV, which could make report interaction a bit easier for users.

There are some limitations to this function as well. The online documentation states that you can’t combine it with a prompt. Also note that because this is a “POV” function, that means that the user can only select a single member at a time.

~~~~~~~~~~~~~~~~~~~~~

In summary, there are a number of interesting and creative ways to make your Narrative Reporting reports dynamic. The above are just a handful. I highly encourage you to take a look at the full functions and features lists to decrease your reports maintenance so you can spend that time on more valuable tasks!