Arithmetic Expression on CDS view input parameter

[andreacalo]

Hi everyone,

I have a requirement to create a report through CDS view with 4 columns for showing net amounts in: (1) Year and Month of selection, (2) Year and up to month of selection, (3) Previous Year and Month of selection, (4) Previous Year and up to month of selection. Year and Month are selected through view parameters p_year and p_month.

As this is going to be visualised via Fiori Launchpad I opted for using the Analytical Query notation usable on core data services. I have no issue with creating the first two columns, but I'm struggling to create columns 3 and for 4 because of the 1-year offset I should apply to the year parameter.

Here is the source code:

@AbapCatalog.sqlViewName: 'ZTEST_REP_Q1'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'TEST QUERY'
@OData.publish: true
@VDM.viewType: #CONSUMPTION
@Analytics.query: true 

define view ZTEST_QUERY
with parameters 
@EndUserText.label: 'Year'
p_year: numc4,
@EndUserText.label: 'Month'
p_month: numc2

as select from ZCDS_REP_VIEW
{  
    @AnalyticsDetails.query.axis:#ROWS
    @EndUserText.label: 'Salesman'
    salesman,
    @AnalyticsDetails.query.axis:#ROWS
   @EndUserText.label: 'Product'
    product,
    
    @AnalyticsDetails.query.axis: #COLUMNS
  @Semantics.amount.currencyCode: 'Currency'
  @Aggregation.default: #SUM
  @EndUserText.label: 'Monthly Amount'
  case
    when Year = $parameters.p_year
     and Month = $parameters.p_month  then amount
  end as amount,
  currency,
  
    @AnalyticsDetails.query.axis: #COLUMNS
  @Semantics.amount.currencyCode: 'Currency'
  @Aggregation.default: #SUM
  @EndUserText.label: 'Amount YTD'
  case
    when Year = $parameters.p_year
     and Month between '01' and $parameters.p_month  then amount
  end as amount_ytd,
  
	@AnalyticsDetails.query.axis: #COLUMNS
  @Semantics.amount.currencyCode: 'Currency'
  @Aggregation.default: #SUM
  @EndUserText.label: 'Monthly Amount (-1)'
  case
    when Year = $parameters.p_year - 1
     and Month = $parameters.p_month  then amount
  end as amount_py,
  
    @AnalyticsDetails.query.axis: #COLUMNS
  @Semantics.amount.currencyCode: 'Currency'
  @Aggregation.default: #SUM
  @EndUserText.label: 'Amount YTD (-1)'
  case
    when Year = $parameters.p_year - 1
     and Month between '01' and $parameters.p_month  then amount
  end as amount_ytd_py
  
  }

In particular, when adding '- 1' in case for column 3 and 4 an " unexpected word '-' " error results in Eclipse. I've tried changing the data type for both the parameter and the year variable but neither seem to work.

Could you help me fix this apparently small issue? How should I type the '-1' offset correctly for parameter p_year?

[Sandra_Rossi]

Because it's NUMC type? What if you change the type to a numeric type?

NB: also make sure '-' is the minus character, not a dash-like character.

[andreacalo]

I tried switching to a numeric type and the error is not resolved. I made sure the operand was written correctly but it is still consider unexpected. I employed different operands (+, * and /) and still got the same error.

[Sandra_Rossi]

I just looked at the CDS documentation. It seems to say that a complex case condition accepts only simple operands, while the first operand of a "simple case" can be an arithmetic expression.

https://help.sap.com/doc/abapdocu_latest_index_htm/latest/en-US/index.htm?file=abencds_cond_expr_cas...

https://help.sap.com/doc/abapdocu_latest_index_htm/latest/en-US/index.htm?file=abencds_case_expressi...

[andreacalo]

Thanks for you help Sandra. Unfortunately I'm still unable to translate this into a solution for the error I have on my source code. Do you have any suggestions on how I should change the code?