A Step-by-Step Guide to Calculate Number of Work Days in SAP HANA Studio
- by Vipul Goyal, SAP Analytics Senior Consultant, Accenture
- February 26, 2015
A common requirement for any project management reporting is to calculate the number of business days between two dates, excluding holidays, which have already been used on the project. These dates can be planned start and end dates, actual start and end dates, or baseline start and end dates for a project, phase, or task assigned to the given project. The solution example used is to calculate the number of work days in SAP HANA studio, but the same concept can be used in any other BI applications with an SAP ERP system as the source system. In this example, learn how to use this solution for SAP Portfolio and Project Management operational reports using SAP HANA as its BI platform.
By reading this article, you will learn:
- How to do run-time calculations of the number of business days between any two dates inside SAP HANA information views using SAP HANA studio
- About the factory date concept in the SAP ERP system and how to use it with SAP HANA studio
- How to create custom dates based on the SAP ERP Central Component factory calendar using ABAP code
Data from the SAP ERP Central Component (ECC) system is loaded to SAP HANA in real time using the SAP Landscape Transformation (SLT) process. Information views are then created using SAP HANA studio to model the unstructured data, split across multiple tables into one single object (called a data model) as per enterprise-specific rules and processes. Afterwards information views can be exposed to various front-end reporting tools like Universe Designer, Web Intelligence, MicroStrategy, and OLAP Analysis to provide structured, company-specific information.
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.
A basic knowledge of ABAP, SAP HANA modeling, and SAP Landscape
Transformation (SLT) processes is required for understanding this
Would you like to see this full item?