Skip to Content

Dear community!

Recently I was asked to suggest solution for ABAP CDS-view which should demostrate distance in working days between two dates (begin_date and end_date) of workflow steps. The CDS-view should be used in SAP Smart Business framework to present data for KPI visualisation.

I found interesting blog regarding the factory calendar consumption in HANA calculation view. But I need ABAP-CDS because of SAP Smart Business framework: it’s easy to generate OData-service from CDS-view to be further consumed by the framework.

 

Let’s say I have table with stepid, date_start and date_end fields. And I need to get distance in workdays (according local factory calendar). Result should be like this:

Two different approaches I can suggest:

  1. Create ABAP-CDS view based on AMDP-procedure with SQL-SELECT inside. The SELECT uses basic data table and built-in SQL-function WORKDAYS_BETWEEN to be applied to date_start and date_end.
  2. Create ABAP-CDS view based on basic data table joined with “distance table”. “Distance table” contains workday distances for any combinations of dates in particular range, i.e. for any dates between 01.01.2017 and 31.12.2018. The table is filled once by custom ABAP-report and will contain less than 200000 records for 2 years. Not many for SAP HANA.

“Distance table” can be like this:

And its sample data:

As you can see, according RU factory calendar there is the long holiday period in the beginning of the year!

ABAP-CDS view can be like this…

.. and returns resultset as showed in the 1st screenshot in the blog.

Approach 2 seems to be better (from my point of view): it’s more flexible. You can use the “distance table” anyway, both in CDS-views or just open SQL.. And I believe it’s more fast that approach with WORKDAYS_BETWEEN function. But I didn’t conduct performance tests…

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Joachim Rees

    Nice solution you found there!

    I would hope that there is a build-in function for this, soon.

    Creating and filling the extra table looks like a lot of overhead! 🙁

     

    (0) 
  2. Uladzislau Pralat

    Hi Sergey,

    200000 records for 2 years? Seriously? You can achieve the same with 730 records for the same 2 years. Create a table with two fields:

    1. Date
    2. Business Day Sequence Number

    Join to this table two times in your CDS view. First with from date and second time with to date. Your distance in business days is a difference between two found Business Day Sequence Numbers. Easy.

    Regards, Uladzislau

    (1) 

Leave a Reply