on 12-29-2021 6:39 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
93 | |
11 | |
10 | |
9 | |
9 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.