In BW modeling in DTP, many times you have to give a filter where you need to select current calendar month or current fiscal month dynamically while loading the data.
Case 1: Filter based on current calendar month
Here you assume that there is a source field having month value. Now you want to load only those records where the value of the source field in source is current calendar month. Simplest way of doing same is to give the filter value like 01.2016 in the respective source field. But in this way, you have to change the filter value every time the month changes.
You have to define a routine against the source field in DTP. Click on the routine button and give a name.
In the screen that appears, there is a line – “Insert the code below this line”, comment out the following lines below it
Declare variables to hold the day, month and year values from the system date and also declare a variable which is of the same data type as the source field
DATA: ZDATE LIKE SY-DATUM, * System date is in the format YYYYMMDD ZYEAR TYPE N LENGTH 4, ZMONTH TYPE N LENGTH 2, ZDAY TYPE N LENGTH 2, ZSOURCE TYPE C LENGTH 32. *The bold values are specific to your source field type. ZYEAR = ZDATE + 0(4). * Stores current year YYYY ZMONTH = ZDATE + 3(2). * Stores current month MM ZDAY = ZDATE + 5(2). * Stores current day DD
Once you have the current month value, you have to fill in the DTP range as illustrated below. You can also select a range of values based on the current month selected (like last 6 months from current month, or current quarter etc by giving the suitable high and low range)
l_t_range-fieldname = '/BIC/XYZ'. *XYZ is the technical name of your source field l_t_range-option = 'EQ'. l_t_range-sign = 'I'. l_t_range-low = ZMONTH. append lt_range. **... ** if l_idx <> 0. ** modify l_t_range index l_idx. ** else. ** append l_t_range. ** endif. p_subrc = 0.
This code will fill the DTP filter with the current month value based on the system date.
Case 2: Filter based on current fiscal month
Many companies follow fiscal period and they want the same fiscal period wise selection in the BI reports. For example, some companies follow the April to March fiscal year for their year-end closing whereas some companies follow the January to December calendar year.
This information is stored in the T009 table in the SAP BW system as fiscal year variant.
Lets say you want to load the current fiscal month data according to the Z1 fiscal year variant.
You have to convert the current month to the fiscal month by using a function module in the DTP routine.
CALL FUNCTION 'DATE_TO_PERIOD_CONVERT' EXPORTING I_DATE = SY-DATUM I_PERIV = 'Z1' IMPORTING E_BUPER = ZMONTH E_GJAHR = ZYEAR.
Rest of the code will be same as described in case 1.
NOTE: All the images in the post are copyright of the SAP BW tool.