Step by Step Getting Working Days from SAP Calendars by Universe on SAP ERP

Getting Working Days from SAP Calendars by Universe SAP ERP: In this topic I will mention about how to calculate working days for Business Objects layers using SAP ERP calendar by creating a Universe on top of an ABAP Function Module, including ABAP development. Number of working days will be very necessary primarily in HR related reporting.

First, we will need a Function Module which imports “date_from” and “date_to” and exports number of working “days”. The logic of the Function Module will be calculating total number of days and than substracting holiday days which will return by the standart “HOLIDAY_GET” function module.

Getting Working Days from SAP Calendars by Universe SAP ERP

In R/3, create the function module “ZBO_WORKING_DAYS” with the following parameters that you can find in the ABAP code. Additively, Function Module will export an E_TABLE returning record based holiday days.

Since we will connect to function outside of R/3, Don’t miss to mark the Function Module as Remote-Enabled and flag import and export parameters as “Pass Value”.

Here is the ABAP code:

FUNCTION ZBO_WORKING_DAYS.

*”———————————————————————-

*”*”Local Interface:

*”  IMPORTING

*”    VALUE(DATE_FROM) TYPE  DATS

*”    VALUE(DATE_TO) TYPE  DATS

*”  EXPORTING

*”    VALUE(DAYS) TYPE  INT4

*”  TABLES

*”      E_TABLE STRUCTURE  ISCAL_DAY

*”———————————————————————-

DATA: lv_lines type sy–tabix.

*calculate number of total days between two days

DAYS = DATE_TO – DATE_FROM + 1.

*get holidays from the standart “HOLIDAY_GET” function module

*Go to SCAL t-code to find the technical name of your calendars

CALL FUNCTION ‘HOLIDAY_GET’

EXPORTING

  HOLIDAY_CALENDAR                = ‘Z1’ “Technical name of holiday calendar

  FACTORY_CALENDAR                = ‘Z1’ “Technical name of factory calendar

  DATE_FROM                        = DATE_FROM

  DATE_TO                          = DATE_TO

* IMPORTING

*  YEAR_OF_VALID_FROM              =

*  YEAR_OF_VALID_TO                =

*  RETURNCODE                      =

  TABLES

    HOLIDAYS                        = E_TABLE[]

* EXCEPTIONS

*  FACTORY_CALENDAR_NOT_FOUND      = 1

*  HOLIDAY_CALENDAR_NOT_FOUND      = 2

*  DATE_HAS_INVALID_FORMAT          = 3

*  DATE_INCONSISTENCY              = 4

*  OTHERS                          = 5

          .

IF SY–SUBRC <> 0.

* Implement suitable error handling here

ENDIF.

“get the total number of holiday “days from the record count of the holidays internal table

DESCRIBE TABLE E_TABLE[] LINES lv_lines.

“subtract holiday days from total number of days and find number of working days

DAYS = DAYS – lv_lines.

ENDFUNCTION.

Open Information Design Tool and insert an Relational SAP R/3 connection to access the “ZBO_WORKING_DAYS” Function Module we just created.

 

When you select “SAP” from the list, two drivers will appear for R/3 connection. To decide which one to use, in ERP go on the menu System –> Status and check “Component Version”. In my case, since the component version is SAP ECC 6.0, I am using SAP ERP 6 Java Connector.

 

Insert SAP ERP system parameters and create the connection shortcut for your IDT project.

 

Create Data Foundation “DF_ERPDEMO”. Since “ZBO_WORKING_DAYS” asks for two obligatory date parameters, on Data Foundation layer we will insert two date prompts. We will make it on “Parameters and List of Values” section. The prompts will be named as “date_from” and “date_to”. Uncheck “Allow multiple values.”

 

Go to “Connections” section and find “ZBO_WORKING_DAYS”. In the list under “ABAPFunction” node , Functions Modules are divided into groups according to Function Group names. Here it is contained in “ZBWFG” Function Group.

 

You will see two function objects under ZBWFG, because “ZBO_WORKING_DAYS” function module has two outputs. For “Tables” parameter of the Function Module, one more function object appears in IDT with the name “ZBO_WORKING_DAYS-EXPORT_CH_E_TABLE”.

After drag and drop “ZBO_WORKING_DAYS”, a pop up comes for mapping Function Module parameters with  the Data Foundation parameters. We map parameters with each other. After that, do the same for “ZBO_WORKING_DAYS-EXPORT_CH_E_TABLE”.

Create a Business Layer “BL_ERPDEMO” and assign the Data Foundation. I have created two folders for each output of the function, drag and drop the fields under each folder. Turn “Days” field with the name “Natural Number” into a Measure with aggreate function “Sum”.

 

You can see the final structure of the Business Layer as in the following. I have unchecked “Limit size of resut set to” option, though we won’t exceed the limit in this case. We can now publish the Universe to the BI Platform.

Create a new Web Intelligence document and select the regarding Universe. Create two queries to get both “Days” and “E_Table”.

Answer the prompts with the following date values or anything you need.

The output will be number of working days as one record and calendar based holiday days table.