Skip to Content
Technical Articles

Implementing Time Buckets in SAP ABAP CDS Views

Introduction

When reporting on large size financial, CO-PA or other applications’ dataset, it is often required to aggregate information by time intervals further called time buckets and compare revenues and expenses in two or more time buckets; e.g., Year 2016 vs Year 2015, March 2019 vs March 2018, … etc.

Developing such a functionality strictly in ABAP program is quite complex.

The time buckets could be implemented in native SAP HANA modelling by developing a set of attribute and calculation views. Joining time buckets final calculation view with the application data calculation view will produce report with data aggregated by time buckets.

The time buckets could be identified by Time Bucket ID; e.g.:

  • TODAY – Today
  • CY – Current Year
  • CYTD – Current Year to Date
  • CYCM – Current Year Current Month
  • CYCMTD – Current Year Current Month to Date
  • CYR4W – Current Year Rolling 4 Weeks
  • CYR12W – Current Year Rolling 12 Weeks
  • PY – Previous Year
  • PYTD – Previous Year to Date
  • PYCM – Previous Year Current Month
  • PYCMTD – Previous Year Current Month to Date
  • PYR4W – Previous Year Rolling 4 Weeks
  • PYR12W – Previous Year Rolling 12 Weeks

Time Buckets in ABAP Application Server

The same functionality of native HANA time buckets calculation view could be developed alternatively on ABAP Application Server side using SAP ABAP CDS Views with Parameters. As such, it could be easily accessed from both ABAP programs and HTML5 applications once exposed to the external world via SAP Gateway.

For example, the generic ZABL_TBUCKET_DLL ABAP CDS View for time buckets could be JOINed in ZABA_TBDEMO_20TBUCKET_DDL ABAP CDS View with application data coming from ZABA_TBDEMO_10REVEXP_DDL ABAP CDS View using the DLL ASSOCIATION statement as shown below:

@AbapCatalog.sqlViewName: 'ZATBD20TBDBV'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Apply Time Buckets & Sum Revenue & Expenses'
define view ZABA_TBDEMO_20TBUCKET_DDL
  with parameters
    p_today : char8
  as select from ZABL_TBUCKET_DDL( p_today:$parameters.p_today )
  association [1..*] to ZABA_TBDEMO_10REVEXP_DDL as _adoc 
                     on ZABL_TBUCKET_DDL.cal_date = _adoc.doc_date
{
  key cast(TO_DATE as char8)       as to_date,
      time_bucket_id,
      time_bucket_name,
      cal_year,
      PERIOD,
      count(*)                     as rcount,
      sum(_adoc.revenue)           as revenue,
      sum(_adoc.expenses)          as expenses
}
group by

  TO_DATE,
  time_bucket_id,
  time_bucket_name,
  cal_year,
  PERIOD

With some additional massaging to add PROFIT and PR_RATIO measures and introducing labels, the final ABAP CDS View is shown in table below:

@AbapCatalog.sqlViewName: 'ZATBDDBV'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Time Buckets Demo'
@OData.publish:true
define view ZABA_TBDEMO_DDL
  with parameters
    p_today : char8
  as select from ZABA_TBDEMO_40PR2DEC_DDL( p_today:$parameters.p_today )
{
      @EndUserText.label : 'Report Date'
  key to_date,
      @EndUserText.label : 'Time Bucket Id'
      time_bucket_id,
      @EndUserText.label : 'Time Bucket Name' 
      time_bucket_name,
      @EndUserText.label : 'Calendar Year'
      cal_year,
      @EndUserText.label : 'Period'
      PERIOD,
      @EndUserText.label : 'Rec. Count'
      rcount,
      @EndUserText.label : 'Revenue'
      revenue,
      @EndUserText.label : 'Expenses'
      expenses,
      @EndUserText.label : 'Profit'
      profit,
      @EndUserText.label : 'P/R Ratio[%]'
      pr_ratio
}

 

The ZABA_TBDEMO_DDL ABAP CDS View could be called from ABAP program as shown in the following table:

REPORT zaba_zaba_tbdemo_ddl.

PARAMETERS:
  p_date TYPE char8 DEFAULT '20171114'.

DATA: go_alv_table TYPE REF TO cl_salv_table.

START-OF-SELECTION.
  SELECT * FROM zaba_tbdemo_ddl(  p_today = @p_date )
         INTO TABLE @DATA(gt_out).

  cl_salv_table=>factory( IMPORTING r_salv_table = go_alv_table CHANGING t_table = gt_out ).

  go_alv_table->display( ).

The resulting table is displayed in the ALV Grid as shown on the following screen:

The ZABA_TBDEMO_DDL ABAP CDS View could be also consumed by SAPUI5 or SAP Fiori HTML5 application after it is exposed to the external world in SAP Gateway. The same data as shown on screen above will be passed by SAP Gateway in XML or JSON  format to the calling HTML5 application. It looks as follows in JSON format:

{
  "d" : {
    "results" : [
      {
        …
        "p_today" : "20171114",
        "to_date" : "20171114",
        "time_bucket_id" : "TODAY",
        "time_bucket_name" : "TODAY",
        "cal_year" : 2017,
        "PERIOD" : "TODAY",
        "rcount" : 1066,
        "revenue" : "14493899.00",
        "expenses" : "11798003.00",
        "profit" : "2695896.00",
        "pr_ratio" : "18.60",
        …
      },
      {
        …
        "p_today" : "20171114",
        "to_date" : "20171114",
        "time_bucket_id" : "CYCMTD",
        "time_bucket_name" : "CURRENT YEAR & CURRENT MONTH TO DATE",
        "cal_year" : 2017,
        "PERIOD" : "NOV",
        "rcount" : 14871,
        "revenue" : "201154129.00",
        "expenses" : "165726136.00",
        "profit" : "35427993.00",
        "pr_ratio" : "17.61",
        …
      },  
      …
    ]
  }
}

 

The above data was retrieved using SAP Gateway Client. It is also shown below in the XML format rather than in JSON format as above:

The same results are produced when executing ZABA_TBDEMO_DDL ABAP CDS View from HANA Studio:

Time Bucket Implementation in ABAP CDS Views

The Time Buckets Views were implemented as generic ABAP CDS Views that could be used in any business application containing date related data that should be grouped by time intervals. The final ZABL_TBUCKET_DLL ABAP CDS View could be JOINed with any date dependent business data

Implementation of Time Buckets ABAP CDS Views could be divided into:

For technical reasons custom ZABL_CAL_* tables were used to define calendar rather than standard SAP HANA calendar tables. Before implementing Time Bucket Views, several ZABL_CAL_*_DDL Calendar ABAP CDS Views were implemented to join several ZABL_CAL* calendar tables and to calculate additional attributes; e.g.: prev_year_curr_month date or cal_date_py12w previous year rolling 12 weeks date to name the few. Many of these additional calculated attributes were needed to define Time Buckets in ZABL_TBUCKET_*_DDL ABAP CDS Views.

Note the naming conventions of ABAP CDS Views above. The ZABL prefix indicates package they belong to. The second token in name; e.g., CAL or TBUCKET, indicates functionality; i.e., basic Calendar ABAP CDS Views or Time Bucket ABAP CDS Views respectively. The third token in ABAP CDS View name indicates dependency level followed by a brief description of functionality; i.e., level 10 are initial ABAP CDS Views, level 20 is the next level ABAP CDS View that calls level 10 view, etc. Multiple levels are needed due to limitation of ABAP CDS View syntax; e.g., you cannot define in the same ABAP CDS View a calculated measure that uses as a component of the expression another calculated measure. Additionally, having multiple level ABAP CDS Views allows to keep logic reasonably simple. The same prefixes and 10, 20, … level numbers allow to keep all related views together in logical order on the list of Data Definition components in SAP HANA Studio.

Incrementing level numbers by 10, allows to add in the future, if needed, some additional levels; e.g., 15, and keep intact the principals of the naming convention.

The final view for the specific package and functionality does not contain in name the level; e.g., ZAB_TBUCKET_DDL.

The DDL suffix in all ABAP CDS Views indicates that component was defined in DDL.

The complete implementation of Time Buckets ABAP CDS Views consist of 4 custom calendar tables, 5 Basic Calendar ABAP CDS Views and 15 Time Buckets ABAP CDS Views.

The implantation of selected ABAP CDS Views is shown in the following sections of this blog.

Selected Basic Calendar ABAP CDS Views Implementation

ZABL_CAL_10JOIN_DDL ABAP CDS View

This ABAP CDS View JOINs ZABL_CAL table with ZABL_CAL_MONTHT table and ZABL_CAL_01LEAP_NPY_DLL CDS View. Moreover, it calculates additional attributes; e.g., today_prev_year, today_eofm,, …

@AbapCatalog.sqlViewName: 'ZLCAL10JOINDBV'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Joining Calendar Tables'
@OData.publish: false
define view ZABL_CAL_10JOIN_DDL
  with parameters
    p_today : abap.dats
  as select from zabl_cal
  association [1..1] to ZABL_CAL_01LEAP_NPY_DDL as _leap  on zabl_cal.cal_year = _leap.leap_year
  association [1..1] to zabl_cal_montht   as _month on zabl_cal.cal_month = _month.month_of_year and
                                                      _month.language   = 'E'
{
  key date_str,
      cast(date_str as abap.dats)                                     as cal_date,
      _leap.leap_year_flag                                            as leap_year_flag,
      cal_year,
      cal_quarter,
      cal_month,
      _month.month_str                                                as month_c3,
      day_of_month,
      day_of_week,
      day_of_year,
      week_of_year,
      // Get Number of Days between Today & Calendar Date
      dats_days_between(cast(date_str as abap.dats), :p_today)        as days_between,
      // Curr Date as in Input Parameter
      :p_today                                                        as today_curr_year,
      // Current Date a Year Ago
      case _leap.leap_year_flag
        when 1 then dats_add_days(:p_today, -366, 'NULL')
               else dats_add_days(:p_today, -365, 'NULL')
      end                                                             as today_prev_year,
      case _leap.leap_year_flag
        when 1 then dats_add_days(:p_today, 366, 'NULL')
               else dats_add_days(:p_today, 365, 'NULL')
      end                                                             as today_next_year,
      // Get :P_TODAY Beginning of Month Date
      concat(substring(cast(:p_today as abap.char(8)), 1, 6), '01')   as today_bofm,
      // Get :P_TODAY End of Month Date
      cast(dats_add_days(cast(concat(substring(cast(dats_add_days(cast(concat(substring(cast(:p_today as abap.char(8)), 1, 6), '28') as abap.dats),5,'NULL')as abap.char(8)), 1, 6), '01') as abap.dats), -1, 'NULL') as abap.char(8))
                                                                      as today_eofm,
      // Get :P_TODAY Beginning of Year
      concat(substring(cast(:p_today as abap.char(8)), 1, 4), '0101') as today_bofy,
      // Get :P_TODAY End of Year
      concat(substring(cast(:p_today as abap.char(8)), 1, 4), '1231') as today_eofy,
      // Get CAL_DATE Beginning of Month Date
      concat(substring(date_str, 1, 6), '01')                         as date_bofm, // Get Beginning of Year Date for :P_DATE
      // Get CAL_DATE End of Month Date
      cast(dats_add_days(cast(concat(substring(cast(dats_add_days(cast(concat(substring(date_str, 1, 6), '28') as abap.dats),5,'NULL')as abap.char(8)), 1, 6), '01') as abap.dats), -1, 'NULL') as abap.char(8))
                                                                      as date_eofm,
      // Get CAL_DATE Beginning of Year
      concat(substring(date_str, 1, 4), '0101')                       as date_bofy,
      // GET CAL_DATE End of Year
      concat(substring(date_str, 1, 4), '1231')                       as date_eofy
}

 

ZABL_CAL_20L1ATTR_DDL CDS View

This ABAP CDS View extends ZABL_CAL_10JOIN_DDL CDS View by defining additional attributes that are based on attributes added to ZABL_CAL_10JOIN_DDL ABAP CDS View. Note, that attributes calculated in ABAP CDS View cannot be used to define additional attributes in the same ABAP CDS View. Therefore, we must define multiple layers of ABAP CDS Views.

@AbapCatalog.sqlViewName: 'ZLCAL20L1ATTRDBV'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED 
@EndUserText.label: 'Add Level 1 Attributes'
@OData.publish: false
define view ZABL_CAL_20L1ATTR_DDL
  with parameters
    p_today : abap.dats
  as select from ZABL_CAL_10JOIN_DDL( p_today:$parameters.p_today )
{
  key date_str,
      cal_date,
      leap_year_flag,
      case leap_year_flag
        when 1 then dats_add_days(cal_date, -366, 'NULL')
               else dats_add_days(cal_date, -365, 'NULL')
      end                                                             as cal_date_prev_year,
      case leap_year_flag
        when 1 then dats_add_days(cal_date, 366, 'NULL')
               else dats_add_days(cal_date, 365, 'NULL')
      end                                                             as cal_date_next_year,
      cal_year,
      cal_quarter,
      cal_month,
      month_c3,
      day_of_month,
      day_of_week,
      day_of_year,
      week_of_year,
      days_between,
      today_curr_year,
      today_prev_year,
      today_next_year,
      cast(cast(substring(cast(today_curr_year as abap.char(8)),1,4) as abap.numc(4)) as abap.int4)
                                                                      as curr_year,
      cast(cast(substring(cast(today_curr_year as abap.char(8)),5,2) as abap.numc(2)) as abap.int4)
                                                                      as curr_year_curr_month,
      cast(cast(substring(cast(today_prev_year as abap.char(8)),1,4) as abap.numc(4)) as abap.int4)
                                                                      as prev_year,
      cast(cast(substring(cast(today_prev_year as abap.char(8)),5,2) as abap.numc(2)) as abap.int4)
                                                                      as prev_year_curr_month,
      today_bofm,
      today_eofm,
      today_bofy,
      today_eofy,
      date_bofm,
      date_eofm,
      date_bofy,
      date_eofy
}

ZABL_CAL_30L2ATTR_DDL ABAP CDS View

This ABAP CDS View extends ZABL_CAL_20_L1ATTR_DDL CDS View by defining additional attributes that are based on attributes added to ZABL_CAL_20_L1ATTR_DDL CDS View. Note, that attributes calculated in ABAP CDS View cannot be used to define additional attributes in the same ABAP CDS View. Therefore, we must define multiple layers of ABAP CDS Views.

@AbapCatalog.sqlViewName: 'ZLCAL30L2ATTRDBV'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Add Level 2 Attributes Dependent on Level 1'
@OData.publish:false
define view ZABL_CAL_30L2ATTR_DDL 
  with parameters
    p_today : abap.dats
  as select from ZABL_CAL_20L1ATTR_DDL( p_today:$parameters.p_today )
{
  key date_str,
      cal_date,
      dats_add_days(cal_date, 28, 'NULL') as cal_date_4w,
      dats_add_days(cal_date, 84, 'NULL') as cal_date_12w,
      // _leap.leap_year_flag as leap_year_flag,
      cal_date_prev_year,
      cal_date_next_year,
      dats_add_days(cal_date_prev_year, 28, 'NULL') as cal_date_py4w,
      dats_add_days(cal_date_prev_year, 84, 'NULL') as cal_date_py12w,
      dats_add_days(cal_date_next_year, 28, 'NULL') as cal_date_ny4w,
      dats_add_days(cal_date_next_year, 84, 'NULL') as cal_date_ny12w,
      cal_year,
      cal_quarter,
      cal_month,
      month_c3,
      day_of_month,
      day_of_week,
      day_of_year, 
      week_of_year,
      days_between,
      today_curr_year,
      today_prev_year,
      today_next_year,
      curr_year,
      curr_year_curr_month,
      case curr_year_curr_month
           when 1 then 12
           else curr_year_curr_month - 1
      end as curr_year_prev_month,
      prev_year,
      prev_year_curr_month,
      case prev_year_curr_month
           when 1 then 12
           else prev_year_curr_month - 1
      end as prev_year_prev_month,
      today_bofm,
      today_eofm,
      today_bofy,
      today_eofy,
      date_bofm,
      date_eofm,
      date_bofy,
      date_eofy
}

ZABL_CAL_DDL CDS View

This final Calendar ABAP CDS View is a projection on ZABL_CAL_30L2ATTR_DDL CDS View hiding calculation of additional attributes.

@AbapCatalog.sqlViewName: 'ZLCAL_DBV'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Calendar View'
@OData.publish:false
define view ZABL_CAL_DDL
  with parameters
    p_today : abap.dats
  as select from ZABL_CAL_30L2ATTR_DDL( p_today:$parameters.p_today )
{
  key date_str,
      cal_date,
      cal_date_4w,
      cal_date_12w,
      cal_date_py4w,
      cal_date_py12w,
      cal_date_ny4w,
      cal_date_ny12w,
      cal_date_prev_year,
      cal_date_next_year,
      cal_year,
      cal_quarter,
      cal_month,
      month_c3,
      day_of_month,
      day_of_week,
      day_of_year,
      week_of_year,
      days_between,
      today_curr_year,
      today_prev_year,
      today_next_year,
      curr_year,
      curr_year_curr_month,
      curr_year_prev_month,
      prev_year,
      prev_year_curr_month,
      prev_year_prev_month,
      today_bofm,
      today_eofm,
      today_bofy,
      today_eofy,
      date_bofm,
      date_eofm,
      date_bofy,
      date_eofy
}

The above ABAP CDS View produces the following results for P_TODAY = 20171114:

 

Time Buckets ABAP CDS Views Implementation

All Time Bucket ABAP CDS Views are based on ZABL_CAL_DDL ABAP CDS View. They add to it 4 attributes that identify Time Bucket by Id, Name, Period it belongs to and Date it was generated for:

  • TO_DATE – date from the CDS View parameter
  • TIME_BUCKET_ID – time bucket id
  • TIME_BUCKET_NAME – time bucket name
  • PERIOD – period id

The Time Bucket ABAP CDS Views were implemented for the following Time Buckets:

  • TODAY – Today
  • CY – Current Year
  • CYTD – Current Year to Date
  • CYCM – Current Year Current Month
  • CYCMTD – Current Year Current Month to Date
  • CYR4W – Current Year Rolling 4 Weeks
  • CYR12W – Current Year Rolling 12 Weeks
  • PY – Previous Year
  • PYTD – Previous Year to Date
  • PYCM – Previous Year Current Month
  • PYCMTD – Previous Year Current Month to Date
  • PYR4W – Previous Year Rolling 4 Weeks
  • PYR12W – Previous Year Rolling 12 Weeks

Sample Current Year Current Month Time Bucket ABAP CDS View

The sample Time Bucket Level 10  ABAP CDS View ZABL_TBUCKET_10CYCM_DDL for the CYCM – Current Year Current Month Time Bucket is shown in the following table:

@AbapCatalog.sqlViewName: 'ZLTBCYCMDBV'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Current Year Current Month Time Bucket'
@OData.publish:false
define view ZABL_TBUCKET_10CYCM_DDL
  with parameters
    p_today : abap.dats
  as select from ZABL_CAL_DDL( p_today:$parameters.p_today )
{
  key date_str,
      cal_date,
      cal_year,
      cal_quarter,
      cal_month,
      month_c3,
      day_of_month,
      day_of_week,
      day_of_year,
      week_of_year,
      days_between,
      today_curr_year,
      today_prev_year,
      curr_year,
      curr_year_curr_month,
      curr_year_prev_month,
      prev_year,
      prev_year_curr_month,
      prev_year_prev_month,
      today_bofm,
      today_eofm,
      today_bofy,
      today_eofy,
      date_bofm,
      date_eofm,
      date_bofy,
      date_eofy,
      :p_today                                              as TO_DATE,
      cast('CYCM' as abap.char(10))                         as time_bucket_id,
      cast('CURRENT YEAR & CURRENT MONTH' as abap.char(40)) as time_bucket_name,
      cast(month_c3 as abap.char(10))                       as PERIOD
}
where
      cal_year  = curr_year
  and cal_month = curr_year_curr_month

 

Sample Current Year Rolling 12 Weeks Time Bucket ABAP CDS View

The sample Time Bucket Level 10  ABAP CDS View ZABL_TBUCKET_10CYR12W_DDL for the CYR12W – Current Year Rolling 12 Weeks Time Bucket is shown in the following table:

define view ZABL_TBUCKET_10CYR12W_DDL
  with parameters
    p_today : abap.dats
  as select from ZABL_CAL_DDL( p_today:$parameters.p_today )
{
  key date_str,
      cal_date,
      cal_year,
      cal_quarter,
      cal_month,
      month_c3,
      day_of_month,
      day_of_week,
      day_of_year,
      week_of_year,
      days_between,
      today_curr_year,
      today_prev_year,
      curr_year,
      curr_year_curr_month,
      curr_year_prev_month,
      prev_year,
      prev_year_curr_month,
      prev_year_prev_month,
      today_bofm,
      today_eofm,
      today_bofy,
      today_eofy,
      date_bofm,
      date_eofm,
      date_bofy,
      date_eofy,
      :p_today                                               as TO_DATE,
      cast('CYR12W' as abap.char(10))                        as time_bucket_id,
      cast('CURRENT YEAR ROLLING 12 WEEKS' as abap.char(40)) as time_bucket_name,
      cast('ROLL-12W' as abap.char(10))                      as PERIOD
}
where
      cal_year     =  curr_year
  and cal_date     <= :p_today
  and cal_date_12w >  :p_today

Combining All Level 10 Time Buckets ABAP CDS Views

All Level 10 Time Buckets ABAP CDS Views could be combined into a single ZABL_TBUCKET_20UNION_DDL ABAP CDS View. The ZABL_TBUCKET_20UNION_DDL ABAP CDS View is shown in the following table:

@AbapCatalog.sqlViewName: 'ZLTBUDBV'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Union on All Individual Time Buckets'
@OData.publish:false
define view ZABL_TBUCKET_20UNION_DDL   
       with parameters p_today: abap.dats
as 
select from ZABL_TBUCKET_11TODAY_DDL( p_today:$parameters.p_today ) { * } 
  union all
select from ZABL_TBUCKET_10CYCMTD_DDL( p_today:$parameters.p_today ) { * } 
  union all
select from ZABL_TBUCKET_10PYCMTD_DDL( p_today:$parameters.p_today ) { * }  
  union all
select from ZABL_TBUCKET_10CYCM_DDL( p_today:$parameters.p_today ) { * } 
  union all
select from ZABL_TBUCKET_10PYCM_DDL( p_today:$parameters.p_today ) { * }  
  union all
select from ZABL_TBUCKET_10CYTD_DDL( p_today:$parameters.p_today ) { * }  
  union all
select from ZABL_TBUCKET_10PYTD_DDL( p_today:$parameters.p_today ) { * }  
  union all
select from ZABL_TBUCKET_10CY_DDL( p_today:$parameters.p_today ) { * }  
  union all
select from ZABL_TBUCKET_10PY_DDL( p_today:$parameters.p_today ) { * } 
  union all
select from ZABL_TBUCKET_10CYR4W_DDL( p_today:$parameters.p_today ) { * } 
  union all
select from ZABL_TBUCKET_10PYR4W_DDL( p_today:$parameters.p_today ) { * } 
  union all
select from ZABL_TBUCKET_10CYR12W_DDL( p_today:$parameters.p_today ) { * } 
  union all
select from ZABL_TBUCKET_10PYR12W_DDL( p_today:$parameters.p_today ) { * } 

The ZABL_TBUCKET_20UNION_DDL ABAP CDS View was wrapped by the ZABL_TBUCKET_DDL ABAP CDS View to be used with application ABAP CDS Views that require grouping data by the time intervals.

The ZABL_TBUCKET_DDL ABAP CDS View exposes clearly all its arguments rather than showing them as { * } in ZABL_TBUCKET_20UNION_DDL ABAP CDS View to make it short.

The ZABL_TBUCKET_DDL ABAP CDS View is shown in the following table:

@AbapCatalog.sqlViewName: 'ZLTBDBV'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Time Buckets View'
define view ZABL_TBUCKET_DDL 
  with parameters
    p_today : abap.dats
as select from ZABL_TBUCKET_20UNION_DDL ( p_today:$parameters.p_today )
{
  key date_str,
      cal_date,
      cal_year,
      cal_quarter,
      cal_month,
      month_c3,
      day_of_month,
      day_of_week,
      day_of_year,
      week_of_year,
      days_between,
      today_curr_year,
      today_prev_year,
      curr_year,
      curr_year_curr_month,
      curr_year_prev_month,
      prev_year,
      prev_year_curr_month,
      prev_year_prev_month,
      today_bofm,
      today_eofm,
      today_bofy,
      today_eofy,
      date_bofm,
      date_eofm,
      date_bofy,
      date_eofy,
      TO_DATE,
      time_bucket_id,
      time_bucket_name,
      PERIOD
   
}

The ZABL_TBUCKET_DDL CDS View produces the following results for p_today = 20171114:

As mentioned earlier, the ZABL_TBUCKET_DLL ABAP CDS View could be JOINed with any ABAP CDS View that requires grouping data by date intervals; i.e., Time Buckets.

The usage of final ZABL_TBUCKET_DLL ABAP CDS View is shown at the beginning of this blog.

Since Time Buckets are implemented as ABAP CDS Views, they could be used directly in ABAP programs or SAP Fiori and HTML5 Applications once they are exposed to the external world in SAP Gateway.

 

 

3 Comments
You must be Logged on to comment or reply to a post.
  • Hi Adam,

    Thanks for sharing!

    This is a very good reference for CDS view development involving time buckets (structure), which is very normal.

     

    Here is my two cents on the vertical output format of the final time bucket view ZABL_TBUCKET_DLL:

    it’s a union of 13 underlying bucket views, means, when p_today = 20171114, date 20171101 will appear several time in the result set, since it’s part of CY, CYTD, CYCM and so on.Then when the join from this view to a transaction data view happens, records will be duplicated a few times (if the requirement is to show CY, CYTD, CYCM measures in the report at the same time).

    Will this affect the query performance and consume much more memory when the transaction table is a big one?

    An alternative way is to make view ZABL_TBUCKET_DLL a wide one with many flag columns to mark each date relevant to CY or CYTD or CYCM, which can avoid the data duplication.But not sure how easy/feasible it is to create a big view with many case statements to get those flag columns.

    Regards,

    Matt

    • Hi Matt:

      Thank you for your comment.

      Yes, you are right, there are many implementations possible – including one with flags that you suggested.

      In real projects implementations that I have experienced, there are usually 2 time buckets selected to compare; e.g., Current Year-Current Month data with Previous Year-Current Month data, and this should not cause any significant performance problem.

      As shown in CDS snippet below, the WHERE clause on TIME_BUCKET_ID will limit early in the game, the amount of data selected from both ZABL_TBUCKET_DDL ABAP CDS View as well  the application view:

      define view ZABA_ADOC_10TBA_DDL
        with parameters
          p_today : char8
        as select from ZABL_TBUCKET_DDL( p_today:$parameters.p_today )
        association [1..*] to zaba_adoc as _adoc on ZABL_TBUCKET_DDL.cal_date = _adoc.doc_date
      {
        key cast(TO_DATE as char8)       as to_date,
            time_bucket_id,
            time_bucket_name,
            cal_year,
            PERIOD,
            sum(_adoc.prop_tax)          as prop_tax,
            sum(_adoc.advertising)       as advertising,
            sum(_adoc.admin)             as admin,
            sum(_adoc.payrol)            as payrol,
            sum(_adoc.utilities)         as utilities,
            sum(_adoc.insurance)         as insurance,
            sum(_adoc.sales)             as sales,
            sum(_adoc.rent)              as rent,
            sum(_adoc.royalties)         as royalties,
            sum(_adoc.frenchise)         as frenchise
      } 
      where time_bucket_id = 'CYCM' or time_bucket_id = 'PYCM'
      group by
        TO_DATE,
        time_bucket_id,
        time_bucket_name,
        cal_year,
        PERIOD

      Additionally, keeping UNION operation in level 20 ABAP CDS View makes it easy to add a new level 10 Time Bucket ABAP CDS View.  You just to create a new level 10 ABAP CDS View, e.g., CYH1 – for Current Year First Half of Year and add 2 lines of code to level 20 UNION ABAP CDS View.

      If you would want to add time buckets for CYH1, PYH1, CYH2 and PYH2, you would have to add only 8 lines of code to level 20 ABAP CDS View as shown below:

      ...
        union all
      select from ZABL_TBUCKET_10CYH1_DDL( p_today:$parameters.p_today ) { * } 
        union all
      select from ZABL_TBUCKET_10PYH1_DDL( p_today:$parameters.p_today ) { * }  
        union all
      select from ZABL_TBUCKET_10CYH2_DDL( p_today:$parameters.p_today ) { * } 
        union all
      select from ZABL_TBUCKET_10PYH2_DDL( p_today:$parameters.p_today ) { * }  
        union all
      ...

      Best regards,

      Adam Baryla

  • Hello,

    I am interested in using that kind of CDS VIEW for my CO-PA reporting (for example ZABL_TBUCKET_DLL)

    Where Can I find them or using them?

    Thank you so much in advanced,

    Best Regards,

     

    Mayte Andreola