@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
@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
}
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( ).
{
"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",
…
},
…
]
}
}
@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
}
@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
}
@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
}
@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
}
@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
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
@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 ) { * }
@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
}
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
8 | |
5 | |
5 | |
4 | |
4 | |
4 | |
4 | |
4 | |
3 | |
3 |