Using CDS to provide aggregation of amounts in BRFplus

[Former Member]

Hello SCN-community,

I tried my first steps in the world of CDS. As you may recognized in my last blog, I have to provide a kind of HANA-readiness. The Core Data Services (CDS) are available on both platforms – HANA and non-HANA.

BRFplus - DB Lookup expression and currencies

In order to solve a problem in BRFplus, carsten.ziegler gave me the advice to take a look at CDS. I have to build a few aggregates upon the new General Ledger for some kind of statistics. The problem addresses to sum a few amount columns whose currency information points to another table. This is currently
not supported by the BRFplus database lookup expression. Technically in the case of the ledger it’s getting more complicated due to its table design.

But what if you only operate with one single currency? In fact if you have the same problem with another table, CDS is definitely something you may look
at.To give you an overview I built a small example table: 

All amount fields refer to T000-MWAER (the clients default currency). For now, we want to sum each amount column of the table in BRFplus. This gives us the following errors:

In order to solve it we think about a view in which we explicit forget the currency details. This is done by creating a CDS view. But in CDS we are able to do more. We can create a view hiding the currency information or we can map all amount columns to a constant defined currency. I prefer the last variant because we take the currency information into BRFplus and do not need to append it later to the data. But sometimes you may not need the currency so I provide examples for both implementations.

CDS-View 1: Removing currency Information

By using the CAST function we transform the values of type amount to simple decimal numbers.

CDS-View 2: Mapping all amount columns to a static defined currency

In order to give the currency information back to BRFplus we have to normalize all amount columns. This is done by defining a new currency key column. This column is annotated with @Semantics.currencyCode. To provide a constant value as currency information, I use the scalar value 'EUR' and cast it to the appropriate type. Then we have to link all currency fields of the underlying table to the new currency key column. To do this we annotate each amount column with @Semantics.amount.currencyCode and provide the currency key column as the property.

Putting it all together

With one of those CDS views we can finalize the example in BRFplus.

If we try to simulate the database lookup expression we get something like this:

CDS – Built In SQL-Functions

The example was written on a SAP NW7.40SP6 on HANA where I did not find many built in SQL-Functions.

Aggregations:

• MIN
• MAX
• AVG
• SUM

Control and/or programming:

• CAST
• CASE
• LPAD
• SUBSTRING
• MOD
• CEIL

On SCN I noticed a thread where the following functions are announced for SP8:

• ABS
• DIV
• DIVISION
• FLOOR
• ROUND
• CONCAT
• REPLACE

That’s something I will check when SP8 is officially out.

Summary

CDS with it's Advanced SQL will be one important functionality in todays application development. It even solves problems of already released projects like BRFplus. Due to the fact CDS is available for non-HANA and HANA (here with a wider feature set)  it becomes the backbone to build HANA-ready solutions I think.