Many of you may have created Dynamic reports in FR, using the User Prompts.
I once got an opportunity to create one dynamically changing report, client had 4 Forecast scenarios and an Actual scenario.
They would like to get the report in the below given format
IF a User selects Forecast 1
Report should show Forecast 1 from Oct-Sep. (No Actual should be shown)
IF a User selects Forecast 2
Report should show Forecast 2 from Jan-Sep and Actual from Oct-Dec
IF a User selects Forecast 3
Report should show Forecast 3 from Apr-Sep and Actual from Oct-Mar
IF a User selects Forecast 4
I can use a prompt to get the user input (for different forecasts), but the challenge was to show and hide Actual scenario and Forecast scenarios according to the user prompt.
I thought of making use of Advanced Suppress options and see whether that will work.
Here is the solution.
Arrange Actual scenario from Oct – Sep (hard code the values) from Column A to Column L.
Now arrange Forecast scenario from Oct – Sep (Use user prompt for Forecast scenario) from Column M to Column X.
So now I’ve to suppress the forecast scenarios according to the user selection.
From Column M to Column O
I’ve to suppress them if the scenario is Forecast 2 or Forecast 3 or Forecast 4
From Column P to Column R
I’ve to suppress if the scenario is Forecast 3 or Forecast 4
For Column S to Column U
I’ve to suppress if the scenario is Forecast 4. There is no need to suppress columns V,W&X (since they should show no matter what the selection is). For Forecast 1 all the months (Oct -Sep) should show up, so that scenario is not used in the suppress logic.
Now forecast scenarios is done, and Actual is left out.
Actual columns (A,B,C- Oct,Nov,Dec) should show if the scenario is Forecast 2. That means columns M,N and O will be suppressed. (since the suppression logic will find member Forecast 2).
Suppression logic for Actual (A,B &C)
Suppress if M or N or O is not equal to suppressed.
I’ve not used the or combination because if M is suppressed automatically N and O will be suppressed.
Similarly we can club P,Q,R and S,T,U and V,W,X
Below given is the suppression logic used for other Actual columns.
Hope this helps you in creating such dynamic reports.
Thank you it have helped me a lot 🙂