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