Calculated Year in Group by

Question

I am working on ABAP CDS view and stuck somewhere, actually i have date and amount field and i have to show sum of amount year wise, but when i run below query it didnt group by on year and show year multiple time

@AbapCatalog.sqlViewName: 'ZTEST'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Sales Cash Flow Report'
@VDM.viewType: #CONSUMPTION
@Analytics.dataCategory: #CUBE

define view ZRESV07 as select from vicdcfpay as PAY
{
left(PAY.dfaell,4) as Year
sum(PAY.bkond) as Amount
}
group by PAY.dfaell

Are you using ABAP CDS or HANA CDS? You specified two HANA tags but in question body you says ABAP. Their implementation differ greatly and suggested solution van bee different too — Suncatcher, Oct 28 '19 at 22:10

score 1 · Answer 1 · answered Feb 05 '18 at 07:37

Your original query will show output rows that you will think as duplicates. This is because your GROUP BY clause is based on dfaell which includes the date, month and year data as well. So your query will show aggrageted sums for each day identified with dfaell. You need to modify dfaell with LEFT(dfaell,4)

In SQL you can manage your requirement as follows

 select 
    LEFT(dfaell,4) as Year,
    SUM(bkond) as Amount,
    swhrkond
from vicdcfpay
group by
    LEFT(dfaell,4),
    swhrkond

Of course you can ommit swhrkond both from Select list and from Group By list at the same time, but since it is a reference field from bkond, it is good to keep it in the output result too.

But what I experienced with CDS view is that, it does not allow LEFT() function in the GROUP BY clause

So I what I can suggest you as a solution

Create two CDS views

In first one, you can use following SQL query

  select 
      LEFT(dfaell,4) as YearErdat,
      bkond,
      swhrkond
  from vicdcfpay as PAY

In the second query, reference this CDS and use GROUP BY clause

select 
    YearErdat,
    sum(bkond) as Amount,
    swhrkond
from ZCDSVIEW1
group by YearErdat, swhrkond

Thats what i have done but creating two view for this thing is not good way of working..Thanks for your reply — Arun Gupta, Feb 06 '18 at 11:27
I am not sure but CDS view did not let me do casting and grouping in a single view. But if you have the option to create native HANA database views, or may be you can try an AMDP for fetching data you can use single query — Eralper, Feb 06 '18 at 11:34

Oguz · Answer 2 · 2018-02-02T06:39:21.187

-1

You are grouping by day, you should group by the year, instead. Like:

@AbapCatalog.sqlViewName: 'ZTEST'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Sales Cash Flow Report'
@VDM.viewType: #CONSUMPTION
@Analytics.dataCategory: #CUBE

define view ZRESV07 as select from vicdcfpay as PAY
{
left(PAY.dfaell,4) as dfaell
sum(PAY.bkond) as Amount
}
group by dfaell

edited Feb 02 '18 at 06:39

answered Feb 02 '18 at 05:53

Oguz

1,867
1
17
24

Thanks for revert but the Year is calculated field, it will not come in group by, if i take it in group by error says "Column Year is not Known" – Arun Gupta Feb 02 '18 at 06:33
I did not try it, but it may work. Can you try to give the same name as table field? I edited my answer, too. – Oguz Feb 02 '18 at 06:40
If i give the same field name e.g PAY.dfaell or dfaell, it didnt group by on dfaell, actually it show multiple records of same dfaell. – Arun Gupta Feb 02 '18 at 06:55
Grouping by calculated field is not possible – Suncatcher Oct 28 '19 at 22:07

Calculated Year in Group by

2 Answers2