Skip to Content

Reporting on Time Dependent Data

Introduction

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:

I.

b <= v_from

e >= v_from

  and

II.

b <= v_to

e >= v_from

and

Figure1.PNG

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.

Example

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.

Figure2.PNG

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.

Figure3.PNG

Click the Replacement Path tab and define the Replacement Rule to be based on the validity period of the record (ZHR_0DATEFROM_VAR0004).

Figure5.PNG

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.

Figure6.PNG

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.

Figure7.PNG

The key figure should now look like this:

Figure8.PNG

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:

Figure9.PNG

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.   

Figure10.PNG

The RKF for condition [II] should look like this:

Figure11.PNG

Defining the Record Count Formula

Finally, we will create the following key figure based on Formula to count active records:

Figure12.PNG


Query Definition

The final query should look like this:

Figure13.PNG

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply