Welcome to the deep dive session 2 for SpinifexIT Easy Reporter. For the first deep dive refer this.
Scenario: Recently I came across a seemingly easy report requirement to give details of employee and his/her current position details along with current position commenced date. But they say devil is in the details!
When I started analyzing this requirement in detail, noticed that the way SAP stores this data on Org Assignment table PA0001 poses a challenge in that it will not be a straightforward drag and drop of the Employee ID, Position ID and PA0001 start date. Reason – PA0001 entries are for various reasons like Cost Centre changes, etc. but the Position might have remained the same. So picking up the next previous date position ID and the start date will not be the correct commencement date. Let us see how this could be in PA0001
In the above case, the logic is to keep checking from top to bottom for a Position ID change. And as soon as that happens, the report needs to output the 01.01.2019 as the current position commencement date.
Again, being an ABAP programmer at heart, I can imagine an easy code to get this done. But from a Spinifex report, I thought of using the “Up to today” radiobutton on the 7th tab “Review and Test Report” to get this report developed.
When you want to use the “Up to today” radiobutton, the columns you pick for reporting need to have the following field settings as shown below –
I prefer to use the Infotype PA0001 field of Position rather than the Pre-Delivered Position field in the Org Management cluster of fields on the left available. Reason being the pre-delivered seems to be usable only once in the report. It vanishes from the left side menu and you cannot list the field on your output again.
With these two settings above, when you unit test for these fields, you will notice the report outputs all the positions for the employee with the respective start dates.
Now use the Advanced Settings in the columns by scrolling down. This opens up the “Merge Settings” and on the right side, you will notice are 3 key configurations you can work with.
Notice how I have set the “primary key” as the Employee ID and the Position ID combination. With this setting turned on, the report will have only one record per this combination. Also note that you need to set the checkbox of “Activate Record Merge” on as below on the right side.
The Step 2 on the right side is to then set the configurations for the other fields, other than the “primary key”. So I set the Current Position Commencement Date as “Overwrite with last”. What this does is it compare the “primary key” on PA0001 and picks up the oldest date for that primary key. Which is exactly the current position commencement date we needed!
Another best practice I like to use, based on my ABAP programming background is to set the other fields same as “Overwrite with last”. If you leave it to default value of “Merge” the report may start showing multiple records for the “primary key” which we do not want in the output of the report.
I normally do not change anything on the Step 3 sort options but would like to see some use case if anyone has used it to meet reporting requirements. When you run this report now, you should be getting the following output as desired –
I would love to hear other interesting reporting scenarios and how the features in SpinifexIT ER were used. As always with many things, there is more than one way to achieve your goals!
Scenario: Recently I came across a seemingly easy report requirement to give details of employee and his/her current position details along with current position commenced date. But they say devil is in the details!
When I started analyzing this requirement in detail, noticed that the way SAP stores this data on Org Assignment table PA0001 poses a challenge in that it will not be a straightforward drag and drop of the Employee ID, Position ID and PA0001 start date. Reason – PA0001 entries are for various reasons like Cost Centre changes, etc. but the Position might have remained the same. So picking up the next previous date position ID and the start date will not be the correct commencement date. Let us see how this could be in PA0001
| PA0001-PERNR | PA0001-BEGDA | PA0001-PLANS | My comments |
Org Assignment Emp No. | Org Assignment Start Date | Org Assignment Position ID | |
| 12345 | 01.05.2019 | P9876 | Current position P9876 |
| 12345 | 01.02.2019 | P9876 | This entry in PA0001 occurred due to a cost centre change. P9876 remains same. |
| 12345 | 01.01.2019 | P9876 | This entry in PA0001 occurred due to some other action. Position P9876 remains same. |
| 12345 | 01.11.2018 | P4536 | Position is different here... |
In the above case, the logic is to keep checking from top to bottom for a Position ID change. And as soon as that happens, the report needs to output the 01.01.2019 as the current position commencement date.
Again, being an ABAP programmer at heart, I can imagine an easy code to get this done. But from a Spinifex report, I thought of using the “Up to today” radiobutton on the 7th tab “Review and Test Report” to get this report developed.
When you want to use the “Up to today” radiobutton, the columns you pick for reporting need to have the following field settings as shown below –
I prefer to use the Infotype PA0001 field of Position rather than the Pre-Delivered Position field in the Org Management cluster of fields on the left available. Reason being the pre-delivered seems to be usable only once in the report. It vanishes from the left side menu and you cannot list the field on your output again.
With these two settings above, when you unit test for these fields, you will notice the report outputs all the positions for the employee with the respective start dates.
Now use the Advanced Settings in the columns by scrolling down. This opens up the “Merge Settings” and on the right side, you will notice are 3 key configurations you can work with.
Notice how I have set the “primary key” as the Employee ID and the Position ID combination. With this setting turned on, the report will have only one record per this combination. Also note that you need to set the checkbox of “Activate Record Merge” on as below on the right side.
The Step 2 on the right side is to then set the configurations for the other fields, other than the “primary key”. So I set the Current Position Commencement Date as “Overwrite with last”. What this does is it compare the “primary key” on PA0001 and picks up the oldest date for that primary key. Which is exactly the current position commencement date we needed!
Another best practice I like to use, based on my ABAP programming background is to set the other fields same as “Overwrite with last”. If you leave it to default value of “Merge” the report may start showing multiple records for the “primary key” which we do not want in the output of the report.
I normally do not change anything on the Step 3 sort options but would like to see some use case if anyone has used it to meet reporting requirements. When you run this report now, you should be getting the following output as desired –
| Emp No. | Current position commencement date | Current position ID | My comments |
| 12345 | 01.01.2019 | P9876 | Current position P9876 and the commencement date on this position |
I would love to hear other interesting reporting scenarios and how the features in SpinifexIT ER were used. As always with many things, there is more than one way to achieve your goals!
- SAP Managed Tags:
4 Comments
