A fellow consultant reached out for help on solving a problem with building a report with a specific requirement regarding periods in the columns. Like many things here on the blog, I'm adding the solution here for everyone (including me) to reference and find in the future.
The problem involves reporting a range of periods. The customer wanted to select a period in the point of view and then show the remaining periods in the quarter (not the year, which would be a simple RANGE function). So (assuming a calendar year setup) if the user selects January then show February and March and if the user selects February then only show March. With Essbase calculation scripting there are functions called LSIBLINGS and RSIBLINGS that can do this (show siblings before and after the current member). But in reporting, either within Narrative Reporting or within Reporting within the applications, there are no such functions.
My initial thought was to specify all the periods in the columns and then apply a bunch of conditional suppresses. It would work but I knew there was a better way, so I reached out to super star Opal Alapat for help.
So, here's what to do: use the INTERSECT function. The function returns all members that meet all of a list of specified criteria. The issue is figuring out which criteria to use where the combination yields the desired result.
For this particular problem, there are two criteria to specify. First, use the SIBLINGS function to return all of the siblings of the current point of view period (which will be user specified). If a user selects January, this function will return February and March (which coincidentally meets the requirement). But if the user selects February, the function returns January and March (which doesn't work).
The second criteria filters out the prior periods. Use the RANGE function to show periods from the current point of view to December (or the last period if a non-calendar application). So if the user point of view is February, then this function will return all months February to December.
This is where the magic of the INTERSECT function comes in. The function looks at the two criteria and returns only the members that are in all the criteria sets. Here's what it looks like in the member selector and after returning to the grid.
The solution works well: both easy to set up and understand. And avoids the hassle of creating a bunch of conditional suppresses.
Bonus: If there is a need to report months before the current point of view in the quarter, then change the RANGE to go from January (or the first period) to the current point of view.
