- SAP Managed Tags:
Abstract
There is often the request to fetch the latest record from non-effective dated records, as e.g. the highest educational qualification, the latest previous employer or the last performance rating given to an employee. There is no standard functionality to derive such details in one shot but there is a workaround which is presented in this blogpost in detail.
Introduction
As long as an object is effective dated, it is trivial to derive in a report the currently valid record, e.g. the current job title from job information or the marital status of an employee from personal information. However, there are many data which are stored in non-effective dated tables and objects. Examples are:
- Annual performance ratings based on performance and objective evaluations (typically, valid from 1 January until 31 December of the respective year)
- Education data with the school/university, a start date and end date together with the respective qualification (e.g. bachelor, master or doctoral degree)
- Data about the previous employments with the employer name, start date and end date of the previous employment, together with the activities and tasks in the respective previous companies
In many reporting use cases, however, it is required to derive just the latest (=newest) record and not all data records. Figure 1 shows as an example the calculated overall performance ranking of “Andree Durant” (in that system, the 2017-record is the latest = newest record).
Figure 1 – Talent profile data of sample employee Andree Durant
Figure 2 shows the simple report output, which is based on a query that uses “Basic User Information” and the performance forms. You see that the all data are shown, i.e. 6 records for the years 2012 until 2017 with the respective “Calculated Overall Performance Ratings”. The reporting challenge is deriving just the latest performance rating, i.e. 2,89 valid for 2017; so that the report shows just a single row for employee “Andree Durant”. This blogpost describes the process how to achieve that. Of course, the approach can also be used for other cases (e.g. those mentioned in the introduction above or any other).
Figure 2 – Report output of a simple query based on “Basic User Information” and the performance forms
Solution
In the following the required steps are described to get just the latest record instead of the full history.
Creation of the query, e.g. “Basic User Information” -> “Form” -> “Performance”. Select the fields which are relevant in your case, such as e.g. username, user ID, employees name, form start date / form end date and the “Calculated Overall Performance Rating”.
Now create following calculated columns:
- Calculated column “DAYSBETWEENENDDATEANDTODAY - Days Between End Date and Today” as shown in the upper part of Figure 3.
Important: Use “MIN” as ‘Aggregation Type” for this newly created calculated column (as indicated in the lower part of Figure 3). - In addition, create a second calculated column “DUMMY - dummy” as indicated in the middle part of Figure 3 (an explanation why “DUMMY - dummy” is needed and how it is used follows below).
Figure 3 – Definition of the two query calculated columns
Create a calculated measure of type “Restriction” called “Minimum Days (Constant Selection)” as indicated in Figure 4. In particular take case on the following:
- It is important to declare the measure as a “constant” (see red arrow in Figure 4).
- Unfortunately the declaration of a measure as “constant” is only possible for a “Restricted Measure”. That means it is required to add a filter criteria. Here, it is recommended to use a (dummy) condition which is always fulfilled. Exactly here, it’s recommended to use the calculated column “dummy” mentioned above, i.e. to use a filter on "dummy = "a" (see blue box in Figure 4).
- Use all fields used in the “Cross-tab” except the “User ID” and “Username” as “Constant Dimension” (see yellow box in Figure 4).
Figure 4 – Definition of calculated measure “Minimum Days (Constant Selection)”
As you can see in Figure 5, “Minimum Days (Constant Selection)” shows constantly the smallest value of the Day Difference for all records of the respective user.
Figure 5 – Report output with the previously created measures
As a final step, it is required to filter the records for the latest record, i.e. “latest” = record where “Days Between End Date and Today” is equal to “Minimum Days (Constant Selection)”. To achieve this, the following 3 steps are required:
- Creation of the “Calculated Measure” called “Filter (Days = Min Days)”, see Figure 6
- Conversion of the measure “Filter (Days = Min Days)” into a dimension “Filter as Dimension” as indicated in Figure 7: This step is needed since it is not possible to apply a filter in a “cross-tab” for a measure but only for a dimension.
- Apply a filter to the “cross-tab” in the following way: “Filter as Dimension” Equals “1”
Figure 6 - Definition of calculated measure “Filter (Days = Min Days)”
Figure 7 - Definition of Measure-Based Dimension “Filter as Dimension”
Compare the outcome of the table as shows in Figure 8 with the expected outcome as indicated in Figure 2. You will realize that the outcome looks as expected.
Figure 8 – Report output after the filter “Filter as Dimension” = “1” is applied
As described in the beginning, of course, the same approach can also be applied for all other use cases when it’s needed to derive the latest record from non-effective dated records, e.g. to derive the highest educational qualification (in that case the query can be based on “Basic User Information” -> “Formal Education”) or to derive the previous employer (in that case the query might be based on “Basic User Information” -> “Previous Employment”).
Remark: This blog post was created with the support of Bastian Posch, Senior Service Advisor - Germany, HCM Cloud Services
1 Comment
| User | Count |
|---|---|
| 6 | |
| 4 | |
| 3 | |
| 3 | |
| 3 | |
| 3 | |
| 2 | |
| 2 | |
| 2 | |
| 2 |
