Whether you are a consultant building reports for a client, or you are a report builder for your own company, chances are you will be asked to create a rolling three-month report. In this blog, I will discuss how to create a rolling three-month report for Planning using FR Studio. As you are probably aware, Planning does not handle year crossovers the same way HFM does. In turn, you will have to get creative in order to have a dynamic report.
In the report example above, the user is able to select a particular Period and Scenario/Version they want to display and then the next two sequential months are displayed as well. In the example above, the Point of View selected for Period is June and the Scenario/Version is Forecast/Working. Therefore Forecast/Working data for June through August is displayed.
This report also has a column for QTD which totals up the three columns displayed. The issue is when November or December is selected and either one or two of the additional months rolls over to the next year. As mentioned above, Planning does not handle year crossovers the same way HFM does so Advanced Suppression has to be used in order to display the proper months and years.
The first column in the report is driven by the Point of View selected by the user. I have added Advanced Suppression to the column that states if the Member Name for Period Equals Nov or The Member Name for Period Equals Dec, the column should be suppressed. I have a second column, B, which is always hidden, but this column is also used to be able to include the Member Names in the header area.
The next two columns represent the offset value to dynamically attain the rolling three months. In this example June is selected. Therefore column C is June + 1, or July. Column D is June + 2, or August. Columns E and F are hardcoded to Nov and Dec and are suppressed based on the suppression result of columns C and D.
Guide to Oracle Cloud: 5 Steps to Ensure a Successful Move to the Cloud
Explore key considerations, integrating the cloud with legacy applications and challenges of current cloud implementations.
Columns G and H display if the report requires the result to cross years. And lastly, columns I, J, and K provide QTD values and of course have their own suppression scenarios.
The following table lists the selections for each column – Actuals:
In addition to the first grouping of columns, Budget columns are displayed as well for the same months selected for the Scenario/Version POV. And lastly, a variance column is displayed between the Scenario/Version POV and Budget for the QTD columns.
The following table lists the selections for each column – Budget:
The following table lists the selections for each column – Variance:
Unfortunately, creating a report which crosses years requires several columns, but what the user is left with when running the report is quite clean; they are none the wiser to the effort required to create a dynamically, rolling, monthly report.
How do we acheive this Cloud fusion since we don’t have Month and year as separate dimensions
I have not had any experience with Fusion; I work primarily with Hyperion applications. But as a thought, since Month and Year are combined into one dimension for Fusion, you wouldn’t have to worry about having those two dimensions in the column. I’m assuming the issue you are running into is when the next month-year member would be a parent. Can you include in your Month/Year dimension to only include bottom level members? Otherwise you may have to create suppression similarly to how I did it with Nov and Dec, but you would have to do it for each of the parent member possibilities. I apologize I don’t have a definitive answer for you as I have not worked with Fusion before.