Using negative date offset in ABAP CDS view report using input parameters

[bhat_vaidya2]

Hi,

I have a requirement to create a report that should show current year and last 4 years of data using ABAP CDS Views based on a single input parameters selection. I am struggling to figure out how to get the aggregate the sum of last 4 years of data using negative date offset.

below is my code

CODE 1 - Derive all values from RSEG table

@AbapCatalog.sqlViewName: 'ZCDS_RSEG_V2'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'invoice table'
define view ZCDS_RSEG_NEW as select from zrseg_view {

//    key belnr,
//    key buzei,
    key gjahr,
    CONCAT ( gjahr ,  '01') as calmonth,
     werks,
    sum (wrbtr) as amount
    
}
group by gjahr,
         werks

Result - sum of amount from rseg table by year and plant

CODE 2 : CDS view report - In last12months amount, I should get about 42.5 million

@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Invoices'
@VDM.viewType: #CONSUMPTION
@Analytics.query: true
@OData.publish: true
define view ZCDS_INVOICE with parameters 
p_plant: werks_d,
p_year: gjahr,
p_year1: abap.dats 

//p_month: calmonth
as select from ZCDS_RSEG_NEW {

@AnalyticsDetails.query.axis:#ROWS
@EndUserText.label: 'local Plant'
@AnalyticsDetails.query.totals:#SHOW
werks as loc_plant,

@AnalyticsDetails.query.axis:#COLUMNS
@EndUserText.label: 'current year amount'
@AnalyticsDetails.query.decimals: 0
@DefaultAggregation:#SUM

case when gjahr= $parameters.p_year then 
amount end as cur_year,

@AnalyticsDetails.query.axis:#COLUMNS
@EndUserText.label: 'current year amount'
@AnalyticsDetails.query.decimals: 0
@DefaultAggregation:#SUM

//case when gjahr between $parameters.p_year and '2016' then 
//amount end as last4years

case when gjahr between $parameters.p_year - 4 and $parameters.p_year then 
amount end as last4years

}

where werks=$parameters.p_plant
group by werks,
amount,
//calmonth,
gjahr

[Sandra_Rossi]

You are talking about last 12 months (so it may span 2 years, e.g. June {p_year-1} to May {p_year}), but I see only selection on one year (from January to December) in your CDS view.

But you are also talking about last 12 years, or last 3 years? (12 months or 12 years or 36 months?)

If you want last 3 years, you may select data where year is between {p_year-2} and {p_year}.

I also don't understand why you group by year and month if anyway you have already filtered data from the last 12 months (or 12 years).

Please clarify your request.

[bhat_vaidya2]

Hi,

thanks for your response.

I have now modified my code but getting syntax error when using negative offset.

[bhat_vaidya2]

I have now resolved it by using table function and abap class add_months method for the date manipulation.

 gjahr between ADD_MONTHS(:p_year,-4) and  :p_year