SAP HANA studio does not include any standard functionality for calculating the number of work days using calculated measures at run time in SAP HANA information views. However, by using the factory-calendar calculated measures feature of the SAP ERP system, you can calculate the number of business (work) days. This is a critical need in any BI project, especially in areas such as SAP ERP, SAP Product Lifecycle Management (PLM), and SAP Portfolio and Project Management (PPM) applications.

Project managers need to be able to accurately estimate the cost of projects. As part of their calculations, they need to have a method for counting the number of business (work) days (excluding holidays) between the project’s planned start and end dates. In addition, they need to be able to factor in the unit rate per day for tasks or resources, and how to divide the amount paid by the number of business days the resource worked on the project. Most reporting tools lack standard functionality to calculate business (work) days between two dates. Using the solution I describe, users learn how to calculate the number of work days using SAP HANA information views.

This method uses the standard factory calendar maintained in the SAP ERP Central Component (ECC) system. I show how you can extract data from this calendar using an ABAP program and store the data in a custom table. This custom table holds work and non-work day information for each day in a calendar year. It has a unique ID that is used to select the right calendar for the company to use to calculate the number of work days. For example, if a business requirement is to see work days belonging to one city, province (ON), or country, you can select a corresponding ID to provide the appropriate calendar for that use. Business users can ask for a design-time filter or run-time filter on this ID to calculate number of work days. They can only select one calendar at a time.

You can build the factory calendar table in ECC using an ABAP program. You can populate the calendar table with more than 20 years of data by executing the program only once depending on how many years the factory calendar is configured for in the application. The amount of historical information included in the calendar depends on your company’s requirements and the estimated length of the project. Because you can fill the factory calendar for multiple years in one go, no separate scheduling is required for this ABAP program. I show how you to create a new transaction code on the application side to populate the table. After the factory calendar is populated, it replicates to SAP HANA through an SLT process.

When the table is available in SAP HANA studio, the last step is to join this calendar within the content views (an attribute or analytic view as per your requirement). Using an example scenario, I show you how to calculate the number of work days.

Designing the Solution

First, I explain what objects are required for this solution and how the data flows from each object. Figure 1 shows the data flow and the necessary components for designing this solution.

Figure 1

Data flow diagram

The ABAP program reads the information from the SAP ERP standard factory calendar and stores it in a custom table (e.g., Z_FACTORY_CAL ). This data is stored in this custom table in the ECC system. This table (Z_FACTORY_CAL) is replicated and its data is extracted to SAP HANA studio.

There are two main development layers for this solution. One is the ECC design stage and the other is the SAP HANA studio design stage. I cover both in detail in the following sections.

SAP ECC Design

The first step in the SAP ECC design stage is to develop the ABAP program. The standard function module DATE_CONVERT_TO_FACTORYDATE is the core of the ABAP program to be created to fill in the factory calendar. Standard SAP applications (SAP Enterprise Portal or reports) also use this functional module to calculate the number of work days.

The main purpose of this function module is to convert the date into numeric values. These integral values are calculated so that each day represents one integer (numeric) value. Each value is incremented by one from the previous day’s value if it is a work day, but, if it is a non-work day, the value remains the same as the previous day. Table 1 shows a few examples.

As shown in Table 1, 14-Dec-2013 and 15-Dec-2013 are weekend dates and therefore non-work days. Therefore, the corresponding integral value of these dates remains the same as 13-Dec-2013.

Another important input parameter called CORRECT_OPTION allows you to move a non-work day to the next or previous work day. This option would be necessary if your company wanted to include the start and end dates when calculating the difference. For example, if CORRECT_OPTION = (+) is used, the difference between 15-Dec and 20-Dec is six days, but if CORRECT_OPTION = (–) is used, the difference is five days.

To create the ABAP program in the SAP ERP system to create a custom table for the factory calendar, follow these steps:

1. Create an ABAP program with a selection screen with input parameters by year (e.g., the program is based on yearly records).
2. For each year in the range, add code to generate a record with every day in the year and assign each day to an actual date.
3. For each day in the calendar year, call function module DATE_CONVERT_TO_FACTORYDATE (shown in Figure 2). Get the export parameters DATE and FACTORYDATE and assign them to the WORK_DATE and FACTORY_DATE, respectively.
4. Use the CORRECT_OPTION = ‘+’ as input.
5. Repeat steps 3 and 4 and use the CORRECT_OPTION = ‘–’ as input.
6. Insert all the records in a custom table.

Figure 2

Sample function call to convert the date to integer values

The table name in my example is Z_FACTORY_CAL. Table 2 shows the table structure.

ZZWORKDAYIND

Table 3 shows the definition and purpose of each of the parameters used in function module DATE_CONVERT_TO_FACTORYDATE.

To view the details in this screen, execute transaction code SCAL. Figure 3 shows the configuration of various calendars in the SAP ERP system.

Figure 3

Configuration of the factory calendar in the SAP ERP system

Once the ABAP program is ready, run this program for the year range for which you configured the particular calendar. In the ECC system execute transaction code SE38, enter the ABAP program name, and click the execute icon. The screen in Figure 4 appears, where you enter the Year (range), the Factory Calendar ID, and click the execute icon to run the program. In my example, the Ontario calendar (ID 96) was configured from 2011 to 2030. As you can load multiple years of data at once, there is no need for separate scheduling in this program.

Figure 4

An example ABAP program selection screen

SAP HANA Studio Design

In this section, I show you how to design a final solution to calculate the number of work days in SAP HANA studio. First, replicate the factory calendar table created previously in SAP HANA studio. Then complete the following steps on the SAP HANA side to implement the final solution so that you can calculate the number of work days:

1. Replicate table Z_FACTORY_CAL using SLT.
2. Add this table to all the attribute views that contain the two dates between which the difference needs to be found.
3. Join table Z_FACTORY_CAL with all the date fields to fetch the integral value field.
4. Fetch the integral value field from Z_FACTORY_CAL in the output section of the attribute view.
5. Repeat steps 1 through 4 for all the dates in the attribute view. (Note: Add the Z_FACTORY_CAL parameter in the attribute view for as many times as the date fields appear in the attribute view.)
6. Once the integral value fields of the dates are available, create a simple calculated column in the SAP HANA attribute to subtract these integral values. This column provides the number of work days between any two dates.

After you apply these steps to each attribute view, you can calculate the number of work days between any two types of dates (e.g., actual start and finish dates, planned start and finish dates, or baseline start and finish dates). Table 4 shows an example of this calculation.

Now let’s look at each of these steps in detail.

Step 1. Replicate Table Z_FACTORY_CAL

Open SAP HANA studio > Goto Modeller Perspective > Quick Launch > Data Provisioning (Figure 5).

Figure 5

The SAP HANA studio modeler screen

Click the Data Provisioning… link to go to the Data Load Management screen (Figure 6). Data provisioning is used to replicate or extract function module Z_FACTORY_CAL from the ECC system to the SAP HANA system.

Figure 6

Data provisioning

Click the Replicate… button (Figure 6), which opens the screen in Figure 7.

Figure 7

Select the table to be replicated or extracted

Figure 7 shows all the tables available for replication on the left (Tables for Selection). In this screen you can select tables to be extracted from the ECC system to the SAP HANA system. Select the appropriate table (e.g., Z_FACTORY_CAL), click the Add button, and the table name appears in the right box (Selected, Object Name). Click the Finish button.

After replication is complete, the table is available in its schema with the appropriate data. Open HANA studio, navigate to the new table in the SAP HANA studio Catalog folder, and click to open. From the context menu that opens select the Open Definition option to open its definition (Figure 8).

Figure 8

The definition for table Z_FACTORY_CAL in SAP HANA studio

Figure 9 shows what the data looks like in SAP HANA studio. Note that the highlighted rows, 20131214 (e.g., 14 Dec – Saturday) and 20131215 (e.g., 15 Dec – Sunday), have the same integral values as 20131216 (Dec 16 – Monday). Similarly, row 20131225(25 Dec – Christmas holiday) has same integral value as 20131226 (next work day). Therefore, when the difference is calculated between two dates (for example, 27 Dec and 13 Dec), its integral values are subtracted (e.g., 1022 – 1013 = 9 days) and this number corresponds to the number of work days between 27 Dec and 13 Dec 2013.

Figure 9

Z_FACTORY_CAL data in SAP HANA studio

Step 2. Design an Attribute View

In my example in this section, I calculate the difference between the forecast start date and planned start date, and, similarly, between the forecast end date and planned end date.

First, create a new attribute view in SAP HANA studio. In my example, I use YAT_PROJECT_DAT. Add the CGPL_PROJECT table (or add any table with dates). This table contains project master data and required start and end dates set up by users. In my example you need four dates: forecast start date, forecast end date, planned start date, and planned end date.

To calculate the number of work days between any of these two dates, you need to identify the integral value of all four dates. To achieve this, add the Z_FACTORY_CAL table inside the attribute view four times (as shown in Figure 10).

Figure 10

Attribute view joins

Next, join the planned start date field with the actual date field (e.g., field ZZPLANSTART of CGPL_PROJECT with parameter ACTUAL_DATE of Z_FACTORY_CAL). Then include the FACTORY_DATE parameter in the output of the attribute view, which represents the integral value of the ZZPLANSTART date (Figure 11).

Figure 11

Rename the new attribute view output fields

Repeat these steps for the remaining fields. Keep in mind that each field needs to be joined with one of the tables added previously. Refer to Figure 10.

Next, you need to apply the relevant filter to the factory calendar table to restrict data to one calendar. Apply a filter on field FCALID = 96 for table Z_FACTORY_CAL inside the attribute view (to restrict the factory calendar data to the Ontario calendar in my example) and WORKDAY_IND = ‘+’ (to move a non-work day to the next work day). Note: Use WORKDAY_IND = ‘–’ if the requirement is to use the previous work day as a non-work day.

The next step is to rename the four FACTORY_DATE fields added previously. Use something similar to what is shown in Figure 11 so that you have meaningful names.

The last step is to create calculated columns to calculate the number of work days.

Step 3. Create Calculated Columns

To create calculated columns in the attribute view to calculate the difference between any two dates, you need to create a calculated column in the same attribute view you designed in the previous step. Go to the Output section of the attribute view, right-click the Calculated Columns folder, and click the New… button in the context window. This action opens the screen shown in Figure 12.

Figure 12

Calculated column definition

In Figure 12, populate the Name (e.g., WRK_DAYS_PLNSTD_FORSTDT) and Label (e.g., WRK_DAYS_PLNSTD_FORSTDT) fields. In the Data Type field select INTEGER from the drop-down list of options. Add a simple subtraction logic expression (“PRJ_FORST_FADT” – “PRJ_PLNST_FADT”) for the renamed dates (from above). Click the OK button (not shown). This column now stores the number of work days between the forecast start date and the planned start date. Repeat these steps for the calculated column WRK_DAYS_PLNFINDT_FORFINDT using the logic expression “PRJ_FORFIN_FADT” – “PRJ_PLNFIN_FADT”, as shown in Figure 13. Click the New… button again, and this column now stores the number of work days between the forecast end date and the planned end date. Finally, in the attribute view, click the Data Preview button (not shown) to review the data and confirm that the logic is working.

Figure 13

Calculated column definition

Figure 14 shows an example of the data preview and output. The first row shows a PLANFINISH column with a planned finish date of 20140711 and a Forecast_Finish column with a forecast finish date of 20140813. The actual number of days between these dates is 33, but there are only 23 work days in this range, as reflected by the calculated measure WRK_DAYS_PLNFINDT_FORFINDT.

Figure 14

Data preview of attribute view YAT_PROJECT_DAT

These steps need to be repeated in every attribute view that has dates and needs calculated columns for the number of work day calculation. These attribute views are later joined to analytic and calculation views. They will be exposed to front-end tools such as Universe Designer, Web Intelligence, MicroStrategy, and OLAP Analysis when these measures can be directly used to provide work days to the users.

Also, if users have ad-hoc reporting requirements these calculated columns can be created directly on the front-end tools as well by using simple subtraction of the required integral fields.

Vipul Goyal

Vipul Goyal is a Senior SAP Analytics Consultant for Accenture, mainly focused on SAP analytics technologies like SAP HANA, BW, and BusinessObjects. He has SAP implementation experience in the retail, construction, oil and gas, and telecommunications industries. Vipul is certified in SAP HANA and SAP BW and has led many successful SAP analytics implementations for a variety of clients.

You may contact the author at gemini.vipul@gmail.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.