Using BEx Query Designer to Select Active Records over a Date Interval
Reporting on Time Dependent Data
In HR reporting the time dependent nature of data presents certain challenges. In this document we will discuss a commonly encountered requirement to report on InfoTypes over a range of dates.
Selection of Data
Let b and e represent the respective begin and end dates of a record. If we want to select all records which fall within the interval v_from and v_to (within grey area), then at least one of the following conditions must be satisfied:
Note: If we apply conditions I and II to each interval in Figure 1, it is clear that the only intervals selected exist within the grey area.
Suppose we have a DSO containing Basic Pay (InfoType 0008) data. For the purposes of this demonstration we wish to get a count of active records over a user entered date range. The report will list the employee, wage type and a count of active records.
We can accomplish this in BEx Query Designer by using a combination of Restricted Key Figures (RKF) for conditions I and II and Formulas to display rows falling within the specified date range.
Defining the Restricted Key Figures
First, we will create a RKF for condition [I] based on the Key Figure for Number of Records.
Note: In this example the user will enter a date range into the variable ZHR_0DATEFROM_VAR0004. We will use this variable in our replacement paths.
Next, we will need to define two replacement path variables; one is defined on the Valid From characteristic and the other is defined on the Valid To characteristic.
Click the Replacement Path tab and define the Replacement Rule to be based on the validity period of the record (ZHR_0DATEFROM_VAR0004).
Click the radio button titled From Value in the Use Interval section and click OK.
Now we can define the first part of condition I. Drag the Valid From characteristic into the RKF and click Restrict.
Select Value Ranges from the first drop-down menu and use Less Than or Equal To on the replacement path variable we previously created. Add this condition to the Chosen Selections and click OK.
The key figure should now look like this:
Now we need to define the second part of condition [I] which requires creating a replacement path variable exactly as we did above except we will define it on the Valid To characteristic. Define this restriction to be Greater than or Equal To the replacement path variable. Our RKF for condition [I] is complete and should look like this:
The next step is to define a RKF for condition [II] using the same process as above. In this case we will need a new replacement path variable defined on the Valid From characteristic which uses the To Value of our date range variable.
The RKF for condition [II] should look like this:
Defining the Record Count Formula
Finally, we will create the following key figure based on Formula to count active records:
The final query should look like this: