Viewing current period vs. quarter-to-date vs. year-to-date is a common layout used in financial reports.   Often this layout is managed using substitution variables that automatically pass in the current period / QTD / YTD members.   Or you could use range functions with hidden columns to retrieve additional periods and then sum them up for presentation back to the user.

In this blog I will share another option that leverages a delivered FR function, without needing substitution variables or hidden columns.

The Match Function

In the Financial Report studio tool, there is a member function available called “Match”.   A common use of this function would be to dynamically select members that all match a particular naming convention.   For example, if all your revenue accounts started with A_41xx, but the accounts were not organized in a hierarchy structure that met the reporting need of grouping all revenue together, you could use the match parameter to dynamically select all the revenue accounts.

Variation on a theme

So coming back to my report, my user requirement was for three columns of data.  The first column was a single period value, picked by the user in their POV, not driven by a variable.  The second column was the Quarter-to-date value based on the same user-selected base period.  And the third column was the Year-to-date value, again for the same base period.  In the outline, I had QTD and YTD members for each period (leveraging shared members).

Because the initial base period needed to be user-selected, I could not just use substitution variables to drive the period member for the three columns.  The other options I looked at were: 1) the hidden column technique (which was fine for YTD, but a challenge for Quarter-to-date); and 2) prompting the to user pick all three periodic values to show in the report (“Aug”, “QTD Aug”, “YTD Aug”).   But that felt clumsy and probably a bit annoying from a user perspective.  What I really wanted was a ‘concatenate’ capability.   After looking at all the functions available, I wondered if you could use the Match function to do exactly that – concatenate the “QTD “ and “YTD “ strings to the user POV period value.

Turns out it works fabulously!   Here are the steps:

  1. In the QTD column, I choose the match function:

2. In the function value, I type “QTD Current Point of View for Period”

3. I follow the same process for the YTD column.   In the end, my report layout looks like this:

 

The end result

When the user runs the report, they only need to put in the single period member:

 

And the report comes back with that period, plus the respective QTD and YTD retrievals!

 

Questions? Comments?  Feel free to reach us at:

Amy Stine, astine@ecapitaladvisors.com

Jon Harvey, jharvey@ecapitaladvisors.com

Jon Harvey

Jon has been a part of the Oracle team at eCapital Advisors since 2009 and is passionate about educating and teaching clients and team members how to apply the technology to drive business value within organizations. An Oracle Certified Expert in both Hyperion Planning and Essbase, Jon oversees the technical aspects of EPM project delivery and managed services. In addition, he’s responsible for ensuring the continued growth of the practice’s technical capabilities and serves as the primary interface between eCapital and the Oracle user community.

Comments are closed.