Substring and Group by in CDS-View

[EnnoWulff]

Hey there!

I wanted to create a CDS-View where the Flight price of /DMO/BOOKING is grouped by Airline and year and month of flight date.

I tried this but it does not work because the group by clause only referrs to FLIGHT_DATE and not the created fields:

@AbapCatalog.sqlViewName: 'ZDOJOTOTALFLP'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Dojo total flight price by airline'
define view zdojo_total_flp as select from /dmo/booking  {
    key carrier_id as CarrierId,
    key substring(flight_date, 1, 4) as FlightYear,
    key substring(flight_date, 5, 2) as FlightMonth,
    sum(flight_price) as TotalFlightPrice
}
group by
   flight_date, carrier_id

GROUP BY only accepts origin Fields of the data source but not the created fields FlightYear and FlightMonth.

Is this requirement possible in CDS-Views?

Thanks

Enno

[ChristianGünter]

AFAIK that's not possible. However as it is almost always the case when dealing with CDS the answer is stacked views. You can split your view in a base view, where you do the string processing and field renaming, and on top another view (Interface-View, Projection-View or Consumption-View or whatever currently the correct name for that is, I lost sight regarding that) where you do the aggregation. That should work.

BR Christian

[EnnoWulff]

Thanks for your answer christian.guenter ! I already thought so... ☹️

[Marian_Zeis]

enno.wulff

This is also my best solution.
There are then more CDS View in total, but the structure is clear and the grouping hides nowhere.
It's not the smartest solution, but the easiest to maintain.

[keremkoseoglu]

You can prepare a CDS table function and write AMDP code behind it. You can achieve your goal within AMDP.

[EnnoWulff]

Thanks kerem.koseoglu for that hint. I just tried a quick attempt but was not successful. ☹️

I will keep in mind and try later.

[EnnoWulff]

Thanks for your suggestion!

Unfortunately the error is the same:

[...]
GROUP BY
   FlightYear, carrier_id

The column FlightYear is unknown.

[AndreaUS]

Hi,

CDS DDL - CDS View Entity, $projection

In CDS view entities, it is possible to reuse expressions defined in the SELECT list, as in you example above FlightYear and FlightMonth. These elements can be addressed with $projection.

Example: concat($projection.FlightYear, 'x') as reuse

But this works only in the SELECT list and in an association ON-clause. It does not work in GROUP BY.

[roberto_forti]

Hi Andrea,

Thanks for answering. I am going to read more about that.

[EnnoWulff]

Thanks for this addition, aschlott ! I will also have an eye on it.

[roberto_forti]

roberto4tisap12 minutes ago

"group by = The fields must be specified using the same names as the fields in the data source. The current CDS view cannot be specified using alternative element names, which are defined with "AS"."

define view zdojo_total_flp as select from /dmo/booking  {
    key carrier_id as CarrierId,
    key flight_date,
        cast( substring(flight_date, 1, 4) as abap.int4 ) as FlightYear,
        cast( substring(flight_date, 5, 2) as abap.int2 ) as FlightMonth,
    sum(flight_price) as TotalFlightPrice
}
group by
   flight_date, carrier_id

[Martin_4]

Hi,

the problem is basically related to the fact the CDS is missing a proper (standard) time dimension.

With associating a CDS Standard Time Dimension with the view (view zdojo_total_flp) from above
with the flight_date there is no such work arround necessary.

You would than associate the CDS Standard Time Dimension with Your flight_date that is:
- Clean design
- more readable
-Standardized approcach
... and than the group by isnt a problem at all any more.

The lack of a proper CDS Time dimension seems to be more a SAP politcal / marketing thing 🙂

S4 CDS = Operative Reporting: wo. Time Dimension
BW = Tactical- / Strategic- Reporting: with Time Dimension
:)?

Thats why You best implement a basic Version of a Timedimension yourself
to solve at most Your problems for one, which pays the afford easely out:
Easiest and at first nothing more than a table with a generic
Calday, Calmonth, Calyear filled with an ABAP Report for a couple of Calyears.

A more simple simple workarround would be the use of a
CDS system entity SERIES_GENERATE_DATE.

@SAP:
The CDS Standard Time dimension should also be availabe in a variant to to have a / the factoty calandars to analyze on base of factory days....

Best Martin

[EnnoWulff]

Thanks for your addition analytics4you_2023

[Martin_4]

Hi,

to solve this for one and any type of time and date ,

best You associate I_CalendarDate with /dmo/booking

try this:

/dmo/booking-flight_date = I_CalendarDate-CalendarDate

Than You can sun and grou by with whatever type of date or time You want.

Best Martin

[Martin_4]

Hi,

try this to get a full hold of any type of time You need:

Real usefull Date or Time CDS as a Calendar- or Time- dimensions: I_CalendarDate

Best Martin

[EnnoWulff]

This is a really odd solution... 😄

But solves my problem for dates until 31.12.2100. thanks!