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:
- 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