Hi experts!
I´ve an quite easy and I guess common case for asymmetric reporting and wonder if this is possible in SAC.
Data Model has the following dimensions:
Data model contains the following data:
Note the following:
- For version Actual and Budget the ForecastMonth is set to #
- In Version Forecast the dimension ForecastMonth indicates in which month the forecast was made (e.g. Jan 23, Feb 23)
- Employee Smith has no Budget and Forecast Data for Jan 23
- Employee Smith has no Actual data for Mar 23
- Employee Bryce has Budget data only
A report in a table should show data for all employees having data in version Actual, Budget or Forecast. Forecast data should be displayed for a selected Forecast Month only.
The result looks like this:
Following data is expected to be displayed but not shown:
- Budget for Andy Smith in Mar 23
- Budget for Adam Swan in Mar 23!
Configuration of the table is as following:
Remark: Consider the filter on version = Actual. This results in showing only cells where Actuals are stored.
The columns are defined as restricted accounts with constant selection as following:
Restricted Account "Budget" is same as "Actual", but selects version Budget.
Now the table filter is changed to version = Budget
The result is as following:
In this case Actual values for Andy Smith in Jan 23 are not displayed!
How can I achieve to show lines for employees and dates whenever data for Actual, Budget or Forecast is available?
Approach 1: Remove filter for dimension version in the table is not possible, because a single version must be always selected.
Approach 2: Insert dimension version as column and select all versions leads to this wrong presentation.
Are there any other approaches / ideas to achieve the correct presentation, which means to see all employees and months if there is any data for Actuals, Budget or Forecast?
Thank you, Thomas
- SAP Managed Tags:
Accepted Solutions (0)
Answers (4)
thkarner
thkarnerI recreated your scenario. You need to create 3 restricted measures on Amount measure with constant selection
- Actual : Version - Actual, Forecast Month - #
- Budget : Version - Budget, Forecast Month - #
- Forecast: Version - Forecast, Forecast Month - Input Control on Forecast Month dimension.
Enable constant selection for both version and Forecast Month.
Create the report with Employee on rows and Measures on Column. Keep the unbooked option enabled for both dimension in rows. (This looks like a bug that removing the unbooked option does not bring data). You may raise this to SAP to see their reponse on this.
Hope this helps !!
Nikhil
Thanks for your efforts. With option Unbooked for Employee (and Date) my report looks like this.
Also other employees without values are shown (Laurent Bunch, Lynn Roche).
If I tick off “Null in Tables Rows” to get rid of empty lines the same wrong result is shown.
Null suppression is obviously based on selected version (in my case Actual).
The lines for Andy Smith and Adam Swan in Mar 23 are not shown anymore. As it looks like a bug I´ll report this to SAP. Thank you.
I found a solution/workaround.
I´ve added version as columns and selected in filter all 3 version Actuals, Budget and Forecast.
Option booked values for employee. Result looks like this:
Then I´ve used “Hide Column” to hide all duplicate columns. Result looks like this as it should.
Hi Jef, set all options as you described. When deleting version dim it´s mandatory to choose one single version. I chose Actual.
Result is as following. I think constant selection is necessary to "break the dimensions" (version and forecast month).
| User | Count |
|---|---|
| 77 | |
| 10 | |
| 7 | |
| 6 | |
| 6 | |
| 6 | |
| 6 | |
| 6 | |
| 5 | |
| 4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.