Summary:
The purpose of this document is to illustrate how exceptions can be set for Date key figures in BEx queries. The way BEx handles exceptions creates a challenge when trying to set them for date key figures. This document explains how to overcome these challenges.
Report Requirements:
HR Training Compliance reports generally include the business need of highlighting employee’s training gaps. Training gaps could come in the form of expired qualifications; the skill has to be renewed or updated. Proactively business would like to be aware of these cases before they occur, therefore the need to identify soon-to-expire qualifications too.
A way to identify either expired or expiring soon qualifications is to highlight them with different colors in a qualifications / employee matrix. Comparing qualifications expiry date versus a key date (evaluated date) it is possible to determine their compliance status. In order to fulfill business requirement BEx Query Designer exception feature can be leveraged.
BEx Query Designer Exceptions feature:
Two types of exceptions can be defined in BEx Query Designer: status exceptions or trend exceptions. Status exceptions evaluate the current value of a key figure (KF > $5000), while trend exceptions evaluate key figure changes, such as deviation (σKF > 5%).
For highlighting qualifications’ compliance, status exceptions type are sufficient. These can be defined in the exception editor.
In this business case the evaluated key figure (expiry date) has data type date (DATS). A date key figure can be either defined directly in the cube or created via a replacement path formula variable referencing a characteristic:
BEx query designer interprets threshold values defined in exceptions as numbers. This means that is not possible to enter a date directly as a threshold value.
However, system can be tricked by entering it in SAP date number format: 20,130,731 instead of 31.07.2013
This trick will do the work for those scenarios where the evaluation date (Value) is static, which is not a very common one.
Exceptions with Formula Variables:
It is possible to specify dynamic values as thresholds by using formula variables with processing type Customer Exit. The formula variable must have the dimension ‘date’. Standard formula variables provided by SAP as business content can also be used, i.e. 0F_ADAY – Formula Variable for Current Calendar Day.
Specify your desired Alert Level and Operator, Click on the Entry Variable icon to select the required formula variable. Then click on Transfer button.
SAP standard variable 0F_ADAY will be sufficient for scenarios where qualification’s expiry date is evaluated against the current calendar day. Not always the case as some time it is necessary to see how things were in a past date. This past date is usually provided in the form of a Ready for Input Variable, either as a specific calendar day or as a calendar month/year. The later requires the last day of the month to be derived from specified month.
The following steps show how to tackle the scenario where qualification’s expiry date is compared to a calendar date derived from a calendar month/year Ready for input variable. For this purpose standard variable 0CMLMON2 is used.
Step 1: Create a Formula Variable with processing type ‘Customer Exit’ and dimension ‘date’.
A second formula variable could be required when identifying soon-to-expire qualifications. Like qualifications expiring in the next 90 days.
Step 2: Maintain formula variables ABAP code
In transaction SE38 maintain the following code in ABAP program ZXRSRU01. This code generates the values for both formula variables based on Ready for Input variable 0CMLMON2:
when 'ZHR_FVCE_KEYDATE00'.
if i_step = 2.
READ TABLE i_t_var_range INTO l_s_var_range
WITH KEY vnam = '0CMLMON2'.
if sy-subrc = 0.
lv_date = sy-datum.
if lv_date(6) EQ l_s_var_range-low(6).
l_s_range-low = lv_date.
l_s_range-sign = 'I'.
l_s_range-opt = 'EQ'.
APPEND l_s_range TO e_t_range.
else.
concatenate l_s_var_range-low '01' into lv_datum.
call function 'SLS_MISC_GET_LAST_DAY_OF_MONTH'
EXPORTING
DAY_IN = lv_datum
IMPORTING
LAST_DAY_OF_MONTH = lv_date
EXCEPTIONS
DAY_IN_NOT_VALID = 1
others = 2.
IF SY-SUBRC = 0.
l_s_range-low = lv_date.
l_s_range-sign = 'I'.
l_s_range-opt = 'EQ'.
APPEND l_s_range TO e_t_range.
ENDIF.
endif.
endif.
endif.
when 'ZHR_FVCE_KEYDATE90'.
if i_step = 2.
READ TABLE i_t_var_range INTO l_s_var_range
WITH KEY vnam = '0CMLMON2'.
if sy-subrc = 0.
lv_date = sy-datum.
if lv_date(6) EQ l_s_var_range-low(6).
lv_date = sy-datum + 90.
l_s_range-low = lv_date.
l_s_range-sign = 'I'.
l_s_range-opt = 'EQ'.
APPEND l_s_range TO e_t_range.
else.
concatenate l_s_var_range-low '01' into lv_datum.
call function 'SLS_MISC_GET_LAST_DAY_OF_MONTH'
EXPORTING
DAY_IN = lv_datum
IMPORTING
LAST_DAY_OF_MONTH = lv_date
EXCEPTIONS
DAY_IN_NOT_VALID = 1
others = 2.
IF SY-SUBRC = 0.
add 90 to lv_date.
l_s_range-low = lv_date.
l_s_range-sign = 'I'.
l_s_range-opt = 'EQ'.
APPEND l_s_range TO e_t_range.
ENDIF.
endif.
endif.
endif.
Extra Note: Numbers of days to evaluate soon-to-expire qualifications (90 days) can also be made dynamic by implementing an extra Ready for Input variable to capture the number of days. Not showed in this post.
Step 3: Define exception’s required alert levels.
The system processes alert levels and associated threshold sequentially. When multiple alert levels and thresholds are defined for a single key figure, the system displays the lowest alert level.
Indicate the Key Figure the exception is defined on and what data cells will be affected by the alert.
Make sure the exception is active then run the query to see the result.
Additional Considerations:
BEx Query exceptions can only take effect if the key figure the exception is defined on is included in the query output. When the key figure is hidden by either ‘Always Hide’ or ‘Hide (Can be shown)’ display options, the exception won’t take place. It is this tool behavior that rules out the alternative of having the exception defined on other key figure than the expiry date itself, unless additional key figures are allowed in the query result. When additional key figures are allowed in the output a different approach can be followed by choosing to display the exception on an key figure (expiry date) different from the one (compliance status) the exception is defined on.
Business Objects tools like Web Intelligence or Analysis, when available, could save the effort of defining query exceptions by using conditional formatting rules directly in the reporting tool.
Useful Links:
https://help.sap.com/saphelp_nw70ehp1/helpdata/en/43/21b4cd14cd06f4e10000000a422035/content.htm
- SAP Managed Tags:
